2020-07-10

Hive常用DDL操作

一、Database

1.1查看数据列表

1	show databases;

1.2 使用数据库

1	USE database_name;

1.3 新建数据库

语法：

CREATE (DATABASE|SCHEMA) [IF NOT EXISTS] database_name   --DATABASE|SCHEMA 是等价的
  [COMMENT database_comment] --数据库注释
  [LOCATION hdfs_path] --存储在 HDFS 上的位置
  [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.4 查看数据库信息

语法：

1	DESC DATABASE [EXTENDED] db_name; --EXTENDED 表示是否显示额外属性

示例：

1	DESC DATABASE EXTENDED hive_test;

1.5 删除数据库

语法：

1	DROP (DATABASE\|SCHEMA) [IF EXISTS] database_name [RESTRICT\|CASCADE];

默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 CASCADE 级联删除。

示例：

1	DROP DATABASE IF EXISTS hive_test CASCADE;

二、创建表

2.1 建表语法

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];   --从查询结果创建表

2.2 内部表

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";

2.3 外部表

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 命令可以查看表的详细信息

2.4 分区表

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';

2.5 分桶表

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  --按照员工编号散列到四个 bucket 中
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_bucket';

2.6 倾斜表

通过指定一个或者多个列经常出现的值（严重偏斜），Hive 会自动将涉及到这些值的数据拆分为单独的文件。在查询时，如果涉及到倾斜值，它就直接从独立文件中获取数据，而不是扫描所有文件，这使得性能得到提升。

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)  --指定 empno 的倾斜值 66,88,100
  ROW FORMAT DELIMITED FIELDS TERMINATED BY "\t"
  LOCATION '/hive/emp_skewed';

2.7 临时表

临时表仅对当前 session 可见，临时表的数据将存储在用户的暂存目录中，并在会话结束后删除。如果临时表与永久表表名相同，则对该表名的任何引用都将解析为临时表，而不是永久表。临时表还具有以下两个限制：

不支持分区列；
不支持创建索引。

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";

2.8 CTAS创建表

支持从查询语句的结果创建表：

1	CREATE TABLE emp_copy AS SELECT * FROM emp WHERE deptno='20';

2.9 复制表结构

语法：

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

2.10 加载数据到表

加载数据到表中属于 DML 操作，这里为了方便大家测试，先简单介绍一下加载本地数据到表中：

1 2	-- 加载数据到 emp 表中 load data local inpath "/usr/file/emp.txt" into table emp;

其中 emp.txt 的内容如下，你可以直接复制使用，也可以到本仓库的resources 目录下载：

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

三、修改表

大多数的表属性可以通过ALTER TABLE语句来进行修改。这种操作会修改元数据，但不会修改数据本身。这些语句可用于修改表模式中出现的错误、改变分区路径，以及其他一些操作。

3.1 重命名表

语法：

1	ALTER TABLE table_name RENAME TO new_table_name;

示例：

1	ALTER TABLE emp_temp RENAME TO new_emp; --把 emp_temp 表重命名为 new_emp

3.2 增加修改和删除表分区

增加分区

1
2
3

ALTER TABLE log messages ADD IF NOT EXISTS PARTITION(year=2011,month=1,day=1)LOCATION"/1ogs/2011/01/01'
PARTITION(year =2011,month=1,day=2)LOCATION1/logs/2011/01/02'
PARTITION(year=2011,month=1,day=3)LOCATION"/1ogs/2011/01/03'

当使用Hive v0.8.0或其后的版本时，在同一个查询中可以同时增加多个分区。一如既往，IF NOT EXISTS也是可选的，而且含义不变。

修改分区路径

1 2	ALTER TABLE 1og messages PARTITION (year=2011,month=12,day=2) SET LOCATION 's3n://ourbucket/1ogs/2011/01/02';

删除分区

1	ALTER TABLE log messages DROP IF EXISTS PARTITION(year=2011,month=12, day=2);

3.3 修改列信息

语法：

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];

示例：

-- 修改字段名和类型
ALTER TABLE emp_temp CHANGE empno empno_new INT;
 
-- 修改字段 sal 的名称 并将其放置到 empno 字段后
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';

3.4 新增列

示例：

1	ALTER TABLE emp_temp ADD COLUMNS (address STRING COMMENT 'home address');

3.5删除或者替换列

1
2
3

ALTER TABLE log messages REPLACE COLUMNS(
hours_mins_secs INT COMMENT'hour,minute,seconds from timestamp', severity STRING COMMENT 'The message severity'
message STRING COMMENT 'The rest of the message');

这个语句实际上重命名了之前的hms字段并且从之前的表定义的模式中移除了字段server 和process_id.因为是ALTER语句，所以只有表的元数据信息改变了。

四、清空表/删除表

4.1 清空表

语法：

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);

4.2 删除表

语法：

1	DROP TABLE [IF EXISTS] table_name [PURGE];

可以选择是否使用IF EXITST关键子。如果没有使用这个关键字而且表并不存在的话，那么将会抛出一个错误信息。

内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
外部表：只会删除表的元数据，不会删除 HDFS 上的数据；

五、其他命令

5.1 Describe

查看数据库：

1	DESCRIBE\|Desc DATABASE [EXTENDED] db_name; --EXTENDED 是否显示额外属性

查看表：

1	DESCRIBE\|Desc [EXTENDED\|FORMATTED] table_name --FORMATTED 以友好的展现方式查看表详情

5.2 Show

1. 查看数据库列表

-- 语法
SHOW (DATABASES|SCHEMAS) [LIKE 'identifier_with_wildcards'];

-- 示例：
SHOW DATABASES like 'hive*';

LIKE 子句允许使用正则表达式进行过滤，但是 SHOW 语句当中的 LIKE 子句只支持 *（通配符）和 |（条件或）两个符号。例如 employees，emp *，emp * | * ees，所有这些都将匹配名为 employees 的数据库。

2. 查看表的列表

-- 语法
SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

-- 示例
SHOW TABLES IN default;

3. 查看视图列表

1	SHOW VIEWS [IN/FROM database_name] [LIKE 'pattern_with_wildcards']; --仅支持 Hive 2.2.0 +

4. 查看表的分区列表

1	SHOW PARTITIONS table_name;

5. 查看表/视图的创建语句

1	SHOW CREATE TABLE ([db_name.]table_name\|view_name);

参考资料

LanguageManual DDL

Hive编程指南