Hive 常用 DDL 操作
Database
查看数据列表
使用数据库
新建数据库
语法:
1 2 3 4
| CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name [COMMENT database_comment] [LOCATION hdfs_path] [WITH DBPROPERTIES (property_name=property_value, ...)];
|
示例:
1 2 3
| CREATE DATABASE IF NOT EXISTS hive_test COMMENT 'hive database for test' WITH DBPROPERTIES ('create'='heibaiying');
|
查看数据库信息
语法:
1
| DESC DATABASE [EXTENDED] db_name;
|
示例:
1
| DESC DATABASE EXTENDED hive_test;
|
删除数据库
语法:
1
| DROP (DATABASE|SCHEMA) [IF EXISTS] database_name [RESTRICT|CASCADE];
|
- 默认行为是
RESTRICT
,如果数据库中存在表则删除失败。
- 要想删除库及其中的表,可以使用
CASCADE
级联删除。
示例:
1
| DROP DATABASE IF EXISTS hive_test CASCADE;
|
创建表
建表语法
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name [(col_name data_type [COMMENT col_comment], ... [constraint_specification])] [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 ] [SKEWED BY (col_name, col_name, ...) ON ((col_value, col_value, ...), (col_value, col_value, ...), ...) [STORED AS DIRECTORIES] ] [ [ROW FORMAT row_format] [STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)] ] [LOCATION hdfs_path] [TBLPROPERTIES (property_name=property_value, ...)] [AS select_statement];
|
内部表
1 2 3 4 5 6 7 8 9 10
| CREATE TABLE emp( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2), deptno INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
|
外部表
1 2 3 4 5 6 7 8 9 10 11
| CREATE EXTERNAL TABLE emp_external( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2), deptno INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_external';
|
使用 desc format emp_external
命令可以查看表的详细信息如下:
分区表
1 2 3 4 5 6 7 8 9 10 11 12
| CREATE EXTERNAL TABLE emp_partition( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2) ) PARTITIONED BY (deptno INT) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_partition';
|
分桶表
1 2 3 4 5 6 7 8 9 10 11 12
| CREATE EXTERNAL TABLE emp_bucket( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2), deptno INT) CLUSTERED BY(empno) SORTED BY(empno ASC) INTO 4 BUCKETS ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_bucket';
|
倾斜表
通过指定一个或者多个列经常出现的值(严重偏斜),Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时,如果涉及到倾斜值,它就直接从独立文件中获取数据,而不是扫描所有文件,这使得性能得到提升。
1 2 3 4 5 6 7 8 9 10 11 12
| CREATE EXTERNAL TABLE emp_skewed( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2) ) SKEWED BY (empno) ON (66,88,100) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t" LOCATION '/hive/emp_skewed';
|
临时表
临时表仅对当前 session 可见,临时表的数据将存储在用户的暂存目录中,并在会话结束后删除。如果临时表与永久表表名相同,则对该表名的任何引用都将解析为临时表,而不是永久表。临时表还具有以下两个限制:
1 2 3 4 5 6 7 8 9 10
| CREATE TEMPORARY TABLE emp_temp( empno INT, ename STRING, job STRING, mgr INT, hiredate TIMESTAMP, sal DECIMAL(7,2), comm DECIMAL(7,2) ) ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t";
|
CTAS 创建表
支持从查询语句的结果创建表:
1
| CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';
|
复制表结构
语法:
1 2 3
| CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name LIKE existing_table_or_view_name [LOCATION hdfs_path];
|
示例:
1
| CREATE TEMPORARY EXTERNAL TABLE IF NOT EXISTS emp_co LIKE emp
|
加载数据到表
加载数据到表中属于 DML 操作,这里为了方便大家测试,先简单介绍一下加载本地数据到表中:
1 2
| load data local inpath "/usr/file/emp.txt" into table emp;
|
其中 emp.txt 的内容如下,你可以直接复制使用,也可以到本仓库的resources 目录下载:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
| 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800.00 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600.00 300.00 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250.00 500.00 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975.00 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250.00 1400.00 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850.00 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450.00 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 1500.00 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000.00 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500.00 0.00 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100.00 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950.00 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000.00 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300.00 10
|
加载后可查询表中数据
修改表
重命名表
语法:
1
| ALTER TABLE table_name RENAME TO new_table_name;
|
示例:
1
| ALTER TABLE emp_temp RENAME TO new_emp;
|
修改列
语法:
1 2
| ALTER TABLE table_name [PARTITION partition_spec] CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name] [CASCADE|RESTRICT];
|
示例:
1 2 3 4 5 6 7 8
| ALTER TABLE emp_temp CHANGE empno empno_new INT;
ALTER TABLE emp_temp CHANGE sal sal_new decimal(7,2) AFTER ename;
ALTER TABLE emp_temp CHANGE mgr mgr_new INT COMMENT 'this is column mgr';
|
新增列
示例:
1
| ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');
|
清空表/删除表
清空表 hive-ddl.md
语法:
1 2
| TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];
|
- 目前只有内部表才能执行 TRUNCATE 操作,外部表执行时会抛出异常
Cannot truncate non-managed table XXXX
。
示例:
1
| TRUNCATE TABLE emp_mgt_ptn PARTITION (deptno=20);
|
删除表
语法:
1
| DROP TABLE [IF EXISTS] table_name [PURGE];
|
- 内部表:不仅会删除表的元数据,同时会删除 HDFS 上的数据;
- 外部表:只会删除表的元数据,不会删除 HDFS 上的数据;
- 删除视图引用的表时,不会给出警告(但视图已经无效了,必须由用户删除或重新创建)。
其他命令
Describe
查看数据库:
1
| DESCRIBE|Desc DATABASE [EXTENDED] db_name;
|
查看表:
1
| DESCRIBE|Desc [EXTENDED|FORMATTED] table_name
|
Show
1. 查看数据库列表
1 2 3 4 5
| SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];
SHOW DATABASES like 'hive*';
|
LIKE 子句允许使用正则表达式进行过滤,但是 SHOW 语句当中的 LIKE 子句只支持 *
(通配符)和 |
(条件或)两个符号。例如 employees
,emp *
,emp * | * ees
,所有这些都将匹配名为 employees
的数据库。
2. 查看表的列表
1 2 3 4 5
| SHOW TABLES [IN database_name] ['identifier_with_wildcards'];
SHOW TABLES IN default;
|
3. 查看视图列表
1
| SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards'];
|
4. 查看表的分区列表
1
| SHOW PARTITIONS table_name;
|
5. 查看表/视图的创建语句
1
| SHOW CREATE TABLE ([db_name.]table_name|view_name);
|
参考资料