Skip to content

Latest commit

 

History

History
208 lines (150 loc) · 5.77 KB

3.md

File metadata and controls

208 lines (150 loc) · 5.77 KB
Raw

Hive-DDL

#创建表

建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(cil_name data_type [COMMENT col_comment],...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERD BY (col_name, col_name, ...)]
[SORTED BY (col_name [ASC|DESC],...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

默认HDFS存储路径是user/hive/warehouse/*.db

####查询数据库

显示数据库

show databases;

过滤显示数据库

show databases like 'db_hive';

查看数据库详情

desc database db_hive

切换数据库

use database db_hive

删除空数据库

drop database db_hive

强制删除数据库

drop database db_hive cascade

####创建表

建表语法

CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

（1）CREATE TABLE 创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 IF NOT EXISTS 选项来忽略这个异常。

（2）EXTERNAL关键字可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（LOCATION），Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

（3）COMMENT：为表和列添加注释。

（4）PARTITIONED BY创建分区表

（5）CLUSTERED BY创建分桶表

（6）SORTED BY

（7）ROW FORMAT DELIMITED [FIELDS TERMINATED BY char] [COLLECTION ITEMS TERMINATED BY char] [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char] | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)] 用户在建表的时候可以自定义SerDe或者使用自带的SerDe。如果没有指定ROW FORMAT 或者ROW FORMAT DELIMITED，将会使用自带的SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的SerDe，Hive通过SerDe确定表的具体的列的数据。 SerDe是Serialize/Deserilize的简称，目的是用于序列化和反序列化。

（8）STORED AS指定存储文件类型 常用的存储文件类型：SEQUENCEFILE（二进制序列文件）、TEXTFILE（文本）、RCFILE（列式存储格式文件） 如果文件数据是纯文本，可以使用STORED AS TEXTFILE。如果数据需要压缩，使用 STORED AS SEQUENCEFILE。

（9）LOCATION ：指定表在HDFS上的存储位置。

（10）LIKE允许用户复制现有的表结构，但是不复制数据。

####表案例

创建员工表和部门表，插入数据

dept.txt的内容

10	ACCOUNTING	1700
20	RESEARCH	1800
30	SALES	1900
40	OPERATIONS	1700

emp.txt的内容

7369	SMITH	CLERK	7902	1980-12-17	800.00		20
7499	ALLEN	SALESMAN	7698	1981-2-20	1600.00	300.00	30
7521	WARD	SALESMAN	7698	1981-2-22	1250.00	500.00	30
7566	JONES	MANAGER	7839	1981-4-2	2975.00		20
7654	MARTIN	SALESMAN	7698	1981-9-28	1250.00	1400.00	30
7698	BLAKE	MANAGER	7839	1981-5-1	2850.00		30
7782	CLARK	MANAGER	7839	1981-6-9	2450.00		10
7788	SCOTT	ANALYST	7566	1987-4-19	3000.00		20
7839	KING	PRESIDENT		1981-11-17	5000.00		10
7844	TURNER	SALESMAN	7698	1981-9-8	1500.00	0.00	30
7876	ADAMS	CLERK	7788	1987-5-23	1100.00		20
7900	JAMES	CLERK	7698	1981-12-3	950.00		30
7902	FORD	ANALYST	7566	1981-12-3	3000.00		20
7934	MILLER	CLERK	7782	1982-1-23	1300.00		10

创建部门表

create table if not exists test.dept(
deptno int,
dname string,
loc int
)
row format delimited fields terminated by '\t';

创建员工表

create table if not exists test.emp(
  empno int,
  ename string,
  job string,
  mgr int,
  hiredate string,
  sal double,
  comm double,
  deptno int
  )
  row format delimited fields terminated by '\t';

数据导入 load data local inpath '/opt/module/datas/dept.txt' into table test.dept;

load data local inpath '/opt/module/datas/emp.txt' into table test.emp;

管理表和外部表互相转换

查询类型

desc formatted emp

改成外部表

alter emp set tblproperties('EXTERNAL'='TRUE')

分区表

分区对应的是hdfs文件系统下面的一个独立文件夹，通过where指定分区可以提高效率

建表语句

create table dept_partition(
  deptno int,
  dname string,
  loc string
  )
  partitioned by (month string)
  row format delimited fields terminated by '\t';

加载带分区的数据进入到数据 load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='201709');

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition partition(month='2017010');

进入到hdfs文件系统，发现生成了两个独立的文件夹‘201709’和‘201710’

再通过语句可以查询到相关信息 select * from dept_partition where month='201709';

增加分区

alter table dept_partition add partition(month = '201711')

也可以创建二级分区

create table dept_partition2(
               deptno int, dname string, loc string
               )
               partitioned by (month string, day string)
               row format delimited fields terminated by '\t';

加载数据

load data local inpath '/opt/module/datas/dept.txt' into table default.dept_partition2 partition(month='201709', day='13');

修改表

重命名 ALTER TABLE table_name RENAME TO new_table_name

删除表 drop table dept_partition