Hive - DDL 和DML整理

最新推荐文章于 2023-12-13 14:18:05 发布

whiteblacksheep

最新推荐文章于 2023-12-13 14:18:05 发布

阅读量180

点赞数

CC 4.0 BY-SA版权

分类专栏： Hive

本文链接：https://blog.youkuaiyun.com/whiteblacksheep/article/details/96456398

Hive 专栏收录该内容

8 篇文章

订阅专栏

本文详细介绍了Hive中的数据定义语言(DDL)，包括基本数据类型、分隔符使用、数据库和表的相关操作命令等核心内容。重点讲解了内部表与外部表的区别及应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 相关说明

DDL=Date Definition Language，数据定义语言。
学习hive的HQL的DDL相关语法，建议对照官方WIKI，因为语法参数较多，而且和SQL多有相似，不建议记忆，梳理一下，记住相关可以实现的功能和注意点以及和SQL之间的区别即可。

2.基本数据类型和分隔符

Hive基本数据类型：
string
int
bigint
float
double
boolean
date
timestamp
注意：尽量只使用string和int，其他数据类型诸如和date以及timestamp尽量使用string，boolean还可以用0和1来代替，这样比true和false还更优化。
分隔符：
\n 默认的行分隔符
^A 默认的列分隔符
\t 一般建表时指定列分隔符为tab

3.Database相关DDL

hive部署启动后，会有一个默认的hive数据库：default；
hive中的一个库对应hdfs中的一个目录；

default库对应的hdfs目录为：/user/hive/warehouse。

 hive (default)> 
               > show tables;
 OK
 tab_name
 Time taken: 0.023 seconds
 hive (default)> create  table  hello(id int,name string) row format delimited fields  terminated by ',';
 OK
 Time taken: 0.239 seconds
 hive (default)> show tables;
 OK
 tab_name
 hello
 Time taken: 0.014 seconds, Fetched: 1 row(s)
 hive (default)> [hadoop@hadoop001 data]$ 
 [hadoop@hadoop001 data]$ hadoop fs -ls  /user/hive/warehouse
 19/07/18 21:37:26 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
 Found 1 items
 drwxr-xr-x   - hadoop supergroup          0 2019-07-18 21:35 /user/hive/warehouse/hello
 [hadoop@hadoop001 data]$ 
  看到default库下创建的hello表，实际上就是/user/hive/warehouse目录下的hello目录

Create Database 语法和常用命令：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name
[COMMENT database_comment]
[LOCATION hdfs_path]
[WITH DBPROPERTIES (property_name=property_value, ...)];//以上是建库的所有写法

常用写法
默认路径下建库：

		hive (default)> 
	              > create database if not exists ruozedata_d7;
 //创建一个库，使用  if not exists 防止库存在时报错; 对应hdfs：/user/hive/warehouse/ruozedata_d7.db；对应metadata：MYSQL的HIVE库中DBS表

指定路径下建库:

		 hive (default)> create database if not exists testdb location '/ruozedata_d7';
		[hadoop@hadoop001 data]$ hadoop fs -ls / 
		drwxr-xr-x   - hadoop supergroup          0 2019-07-15 19:46 /hadoop
		drwxr-xr-x   - hadoop supergroup          0 2019-07-18 21:50 /ruozedata_d7
		drwxr-xr-x   - hadoop supergroup          0 2019-07-15 21:28 /tmp
		drwxr-xr-x   - hadoop supergroup          0 2019-07-16 10:32 /user
		[hadoop@hadoop001 data]$

Drop Database 常用语法

	hive (testdb)> 
             > drop database if exists testdb;//这种方法，只要testdb库有表，就会报下面的错误
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. InvalidOperationException(message:Database testdb is not empty. One or more tables exist.)
hive (testdb)> 
hive (testdb)> drop database if exists testdb cascade;//在后面加个CASCADE这个关键词，不管库里是否有表，都直接删库。慎用！！！
OK
Time taken: 1.435 seconds

4.Table相关DDL

创建表的常用语法

hive (default)> create table emp(empno int,ename string,job string,mgr int,hiredate string,sal double,comm double,deptno int) row format delimited fields terminated by '\t';//在default库里创建一张员工表
hive (default)> show create table emp; //查看表的创建语句
 > load data local inpath '/home/hadoop/data/emp.txt' overwrite into table emp;//将linux本系统的文件中的数据导入表中
 注意：这里的local写了就是从本地系统的文件导入数据，没写就是直接从hdfs文件系统导入数据；overwrite表示覆盖该表，如果不写overwrite则表示向该表中继续增加数据

使用CTAS从已知表克隆一个表：

hive (default)> create table  emp2  as select * from emp;
hive (default)> create table  emp3  as select empno,ename from emp;//或者只拷贝另外一张表的某几个字段生成另外的新表

拷贝表结构，不拷贝表数据

hive (default)> 
          > create table emp4 like emp;

查看表的信息

desc emp;
desc extended emp;
desc formatted emp;
-- 查看表结构，查看表更详细的信息，格式化的查看表更详细的信息

修改表的名字

ALTER TABLE 老表 RENAME TO 新表;

删除表

Drop Table 和 Truncate Table的区别

hive (default)> 
          > drop table emp4; //删除表和表中的数据
 hive (default)> truncate  table emp3;//仅仅只删除表的数据，但表还存在
OK
Time taken: 0.053 seconds
hive (default)> select * from emp3;
OK
emp3.empno      emp3.ename
Time taken: 0.021 seconds

内部表与外部表

表分为两类：Managed内部表和External外部表。
内部表和外部表的区别：
内部表在删除的时候元数据和数据均会被删除，会造成数据丢失，不安全；
外部表在删除的时候，只删除元数据，较为安全，但使用外部表的时候最好和location一起使用，重新定义存放的路径。
推荐尽量使用外部表，保证数据安全。

	hive (default)> 
              > 
              > 
              > 
              > create external table emp5( 
              > empno int,
              > ename string,
              > job string,
              > mgr int,
              > hiredate string,
              > sal double,
              > comm double,
              > deptno int
              > ) row format delimited fields terminated by '\t' 
              > location  '/d7_hive/test/';
OK
Time taken: 0.027 seconds
hive (default)> desc formatted emp5;
OK
col_name        data_type       comment
# col_name              data_type               comment             
                 
empno                   int                                         
ename                   string                                      
job                     string                                      
mgr                     int                                         
hiredate                string                                      
sal                     double                                      
comm                    double                                      
deptno                  int                                         
                 
# Detailed Table Information             
Database:               default                  
Owner:                  hadoop                   
CreateTime:             Thu Jul 18 23:22:00 CST 2019     
LastAccessTime:         UNKNOWN                  
Protect Mode:           None                     
Retention:              0                        
Location:               hdfs://hadoop001:9000/d7_hive/test       
Table Type:             EXTERNAL_TABLE           
Table Parameters:                
        EXTERNAL                TRUE                
        transient_lastDdlTime   1563463320          
                 
# Storage Information            
SerDe Library:          org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe       
InputFormat:            org.apache.hadoop.mapred.TextInputFormat         
OutputFormat:           org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat       
Compressed:             No                       
Num Buckets:            -1                       
Bucket Columns:         []                       
Sort Columns:           []                       
Storage Desc Params:             
        field.delim             \t                  
        serialization.format    \t                  
Time taken: 0.035 seconds, Fetched: 35 row(s)
hive (default)> 
上面就是创建了一张外部表，并和location一起使用，定义了该表放在hdfs文件系统的/d7_hive/test/目录下，只需要将emp.txt，通过
hadoop fs -put  /home/hadoop/data/emp.txt   /d7_hive/test/ ,然后该数据就已经导入该表了。
	[hadoop@hadoop001 data]$ hadoop fs -mkdir /d7_hive/test/
		hive (default)> 
              > 
              > 
              > 
              > select * from emp5;//查看该表
OK
emp5.empno      emp5.ename      emp5.job        emp5.mgr        emp5.hiredate   emp5.sal        emp5.comm       emp5.deptno
7839    KING    PRESIDENT       NULL    1981-11-17 00:00:00     5000.0  NULL    10
7782    CLARK   MANAGER 7839    1981-06-09 00:00:00     2450.0  NULL    10
7934    MILLER  CLERK   7782    1982-01-23 00:00:00     1300.0  NULL    10
7902    FORD    ANALYST 7566    1981-12-03 00:00:00     3000.0  NULL    20
7788    SCOTT   ANALYST 7566    1982-12-09 00:00:00     3000.0  NULL    20
7566    JONES   MANAGER 7839    1981-04-02 00:00:00     2975.0  NULL    20
7876    ADAMS   CLERK   7788    1983-01-12 00:00:00     1100.0  NULL    20
7369    SMITH   CLERK   7902    1980-12-17 00:00:00     800.0   NULL    20
7698    BLAKE   MANAGER 7839    1981-05-01 00:00:00     2850.0  NULL    30
7499    ALLEN   SALESMAN        7698    1981-02-20 00:00:00     1600.0  300.0   30
7844    TURNER  SALESMAN        7698    1981-09-08 00:00:00     1500.0  0.0     30
7654    MARTIN  SALESMAN        7698    1981-09-28 00:00:00     1250.0  1400.0  30
7521    WARD    SALESMAN        7698    1981-02-22 00:00:00     1250.0  500.0   30
7900    JAMES   CLERK   7698    981-12-03 00:00:00      950.0   NULL    30
Time taken: 0.027 seconds, Fetched: 14 row(s)
hive (default)>
              > drop table emp5;//删除该表emp5
OK
Time taken: 1.126 seconds
hive (default)> show tables;
OK
tab_name
emp
emp2
emp3
hello
Time taken: 0.011 seconds, Fetched: 4 row(s)
hive (default)> 
[hadoop@hadoop001 data]$ hadoop fs -ls  /d7_hive/test/   //虽然该表不在了，但可以看到emp.txt文件还在
19/07/18 23:32:02 WARN util.NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Found 1 items
-rw-r--r--   1 hadoop supergroup        798 2019-07-18 23:28 /d7_hive/test/emp.txt
[hadoop@hadoop001 data]$

CTAS 和 Inserting data into Hive Tables from queries 区别

两者的区别是：CTAS不需要事先创建表，在导入数据，后者从select查询语句的结果向表中导入数据，该表必须事先已经被创建，否则会报错
Inserting data into Hive Tables from queries的语法：

	hive (default)> create table hello_bak like hello;//创建一张表结构和hello表一样的空表
	hive (default)> insert overwrite  table hello_bak select *  from hello;//然后向空表中导入数据

将表中的数据导出到本地linux系统的指定目录

insert overwrite local directory '/home/hadoop/data/data' 
row format delimited fields terminated by ','
select * from emp;