Hive-常用的DDL语法

Hive DDL

创建数据库

create database hive_test comment '测试数据库' location '/home/hadoop/data';

显示数据库信息 EXTENDED 表示是否显示额外属性

desc database extended hive_test;

删除数据库信息

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

  • 默认行为是 RESTRICT，如果数据库中存在表则删除失败。要想删除库及其中的表，可以使用 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];   --从查询结果创建表

创建内部表

create table hive_test1.test_hive(id int,name string) comment '测试表' row format delimited fields terminated by "\t";

创建外部表

create external table hive_test1.test_hive2(id int,name string) comment '测试表' row format delimited fields terminated by '\t' location '/hadoop/ext';

分区表

create external table hive_test1.testhive3(id int, name string) comment '测试表' partitioned by (user_no string) row format delimited fields terminated by '\t' location '/home/hadoop/hive_test1/testhive3';

分桶表

create external table hive_test1.testhive4(id int, name STRING) comment '分桶表' clustered by (id) sorted by (id asc) into 4 buckets row format delimited fields terminated by '\t' location '/home/hadoop/hive_test1/test_hive4';

倾斜表

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

create external table hive_test1.test_hive5(id int,name STRING) comment '倾斜表' skewed by (id ) on (1,2,3) row format delimited fields terminated by '\t' location '/home/hadoop/hive_test1/test_hive5';

临时表

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

create temporary table hive_test1.test_hive6(id int,name STRING) row format delimited fields terminated by '\t' ;

CTAS创建表

create table test_hive7 as SELECT * FROM emp WHERE deptno='20';

复制表结构

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name --创建表表名 -- LIKE existing_table_or_view_name --被复制表的表名 -- [LOCATION hdfs_path]; --存储位置 create temporary external table if not exists hive_test1.test_hive3_copy like hive_test1.test_hive2 location '/home/hadoop/hive_test1/copy/test_hive3_copy';

加载数据到表

load data local inpath '/home/hadoop/data/a.txt' into table hive_test1.test_hive; select * from hive_test1.test_hive;

修改表

ALTER TABLE table_name RENAME TO new_table_name;

修改列

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 ADD COLUMNS (address STRING COMMENT 'home address');

清空表

  • 清空整个表或表指定分区中的数据

TRUNCATE TABLE table_name [PARTITION (partition_column = partition_col_value, ...)];

  • 目前只有内部表才能执行 TRUNCATE 操作，外部表执行时会抛出异常 Cannot truncate non-managed table XXXX。

删除表

DROP TABLE [IF EXISTS] table_name [PURGE];

  • 内部表：不仅会删除表的元数据，同时会删除 HDFS 上的数据；
  • 外部表：只会删除表的元数据，不会删除 HDFS 上的数据；
  • 删除视图引用的表时，不会给出警告（但视图已经无效了，必须由用户删除或重新创建）

其他命令

Describe 查看数据库

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

查看表

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

Show 查看数据库列表

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

查看表的列表

SHOW TABLES [IN database_name] ['identifier_with_wildcards'];

查看视图列表

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

查看表的分区列表

SHOW PARTITIONS table_name;

查看表/视图的创建语句

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