Hive DDL学习

一. 建表语法

1. CREATE TABLE 按给定名称创建表，如果表已经存在则抛出异常。
   可使用if not exists 规避。
2. EXTERNAL 关键字。创建外部表，否则创建的是内部表(管理表)
   删除内部表时，数据和表的定义同时被删除；
   删除外部表时，仅仅删除了表的定义，数据保留；
   在生产环境中，多使用外部表；
3. comment 表的注释
4. partition by 对表中数据进行分区，指定表的分区字段
5. clustered by 创建分桶表，指定分桶字段
6. sorted by 对桶中的一个或多个列排序，较少使用
7. 存储子句
8. stored as SEQUENCEFILE|TEXTFILE|RCFILE
   如果文件数据是纯文本，可以使用 STORED AS TEXTFILE（缺省）；
   如果数据需要压缩，使用 STORED AS SEQUENCEFILE（二进制序列文件）
9. LOCATION 表在HDFS上的存放位置
10. TBLPROPERTIES 定义表的属性
11. AS 后面可以接查询语句，表示根据后面的查询结果创建表
12. LIKE like 表名，允许用户复制现有的表结构，但是不复制数据

1. 内外部表转换

-- 内部表转外部表
alter table t1 set tblproperties('EXTERNAL'='TRUE');
-- 查询表信息，是否转换成功
desc formatted t1;
-- 外部表转内部表。EXTERNAL 大写，false 不区分大小
alter table t1 set tblproperties('EXTERNAL'='FALSE');
-- 查询表信息，是否转换成功
desc formatted t1;

小结

• 建表时
  - 如果不指定external关键字，创建的是内部表；
  - 指定external关键字，创建的是外部表；
• 删表时
  - 删除外部表时，仅删除表的定义，表的数据不受影响
  - 删除内部表时，表的数据和定义同时被删除
• 外部表的使用场景
  - 想保留数据时使用。生产多用外部表

2. 分区表

实质：将表的数据存储在不同的子目录中，每一个子目录对应一个分区。
作用：只查询部分分区数据时，可避免全表扫描，提高查询效率。

--分区表创建和数据加载
partitioned by （column type）
-- 加载数据。
load data local inpath "/xx/xx.dat" into table xx partition(column="xxx");
--查看分区
show partitions tablename

备注：

• 分区字段不是表中已经存在的数据，可以将分区字段看成伪列

分隔符设定

--分隔符设置开始语句
row format delimited 
--设置字段与字段间的分隔符
fields terminated by 
--设置一个复杂类型（array，struct）字段的各个item之间的分隔符
collection items terminated by
--设置一个复杂类型（Map）字段的key value之间的分隔符 
map keys terminated by
--设行与行之间的分隔符
lines terminated by

代码如下（示例）：

create table if not exists t3 (
	id int
	,name string
	,hobby array<string>
	,addr map<string,string>)
	partitioned by (dt string)
	row format delimited
	fields terminated by ';'
	collection items terminated by ','
	map keys terminated by ':'
	;
	

新增分区并设置数据

-- 增加一个分区，不加载数据
alter table xx add partition(column='xxx');
-- 增加多个分区，不加载数据
alter table xx add partition(column1='xxx') partition(column2='xxx');
-- 增加多个分区。准备数据
hdfs dfs -cp /xx/xx.dat/tablename/column=xxx1
/xx/xx.dat/tablename/column=xxx2
hdfs dfs -cp /xx/xx.dat/tablename/column=xxx1
/xx/xx.dat/tablename/column=xxx3
-- 增加多个分区。加载数据
alter table xx add partition(column='xx2') location
'/xx/xx.dat/tablename/column=xxx2' 
partition(column='xx3') location
'/xx/xx.dat/tablename/column=xxx3' 

修改分区的hdfs路径

alter table xx partition(column='xxx') set location
'/xx/xx.dat/tablename/column=xxx5';

删除分区

-- 可以删除一个或多个分区，用逗号隔开
alter table xx drop partition(column='xxx'),partition(column='xxxx');

3. 分桶表

将数据按照指定的字段进行分成多个桶中去，即将数据按照字段进行划分，数据按照字段划分到多个文件当中去。
原理：Hive中分桶字段.hashCode%分桶个数

--创建分桶表
create table xxx(
...
)
clustered by (column) into 3 buckets
...
--创建普通表
create table xxx_common(
... //column名和类型与分桶表一致
)
...
--普通表加载数据
--通过 insert ... select ...给桶表加载数据
insert into table xx select * from xx_common
-- 观察分桶数据。数据按照：(分区字段.hashCode) % (分桶数) 进行分区

备注：

• 网上有资料说要使用分区表需要设置 hive.enforce.bucketing=true，
  那是Hive1.x 以前的版本；Hive 2.x 中，删除了该参数，始终可以分桶

二. 修改表和删除表

-- 修改表名 rename
alter table xx rename to xx1;
-- 修改列名 change column
alter table xx change column xx xx1 int;
-- 修改字段类型 change column
alter table xx change column xx xx string;
-- The following columns have types incompatible with the existing columns in their respective positions
-- 修改字段数据类型时，要满足数据类型转换的要求。如int可以转为string，但是string不能转为int
-- 增加字段 add columns
alter table xx add columns (xx string);
-- 删除字段：replace columns
-- 这里仅仅只是在元数据中删除了字段，并没有改动hdfs上的数据文件
alter table xx replace columns(
xx1 string, xx2 string, xx3 int);
-- 删除表
drop table xx;

总结

• 主要对象：数据库，表
• 表的分类：
  • 内部表
    • 删除表时，同时删除元数据和表数据
  • 外部表 external
    • 删除表时，仅删除元数据，保留表中数据
    • 生产环境多使用外部表
  • 分区表 partitioned by
    • 按照分区字段将表中的数据放置在不同的目录中，提高SQL查询的性能
  • 分桶表 clustered by (column) into ？ buckets
    • 按照分桶字段，将表中数据分开
    • 分桶字段.hashCode % 分桶数据
• 主要命令
  • create 生成表
  • alter
  • drop 删除表