Hive SQL DDL 内外部表、分区表、分桶表

Hive SQL DDL建表语法树

• “[]”中的语法可选
• “|”表示使用时，语法需要二选一
• 建表语句中语法顺序要和语法树规则保持一直
• 整体分为原生数据类型和复杂数据类型
• 原生数据类型包括：数值类型、字符串类型、时间日期类型、杂项数据类型。
• 复杂数据类型包括：array数组、map映射、struct结构、union联合体。

CREATE [TEMPORARY][EXTERNAL] TABLE [IF NOT EXISTS][db_name.]table_name
[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 DELIMITED|SERDE serde_name 	WITH SERDEPROPERTIES(property_name=property_value,...)]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES(property_name=property_value,...)];

内部表

• 内部表也被称为被Hive拥有和管理的托管表
• 默认情况下创建的表都是内部表
• Hive完全控制内部表(包括元数据和数据)的生命周期
• 删除内部表时，会删除数据以及表的元数据
• 可以通过DESCRIBE FORMATTED tablename，来获取表的元数据描述信息，其中若Table Type字段值为MANAGED_TABLE，则表示该表是内部表。

外部表

• Hive只管理外部表的元数据
• 创建一个外部表需要使用EXTERNAL关键字
• 删除外部表不会删除数据，在Hive外部仍然可以访问实际数据。
• 实际场景中，外部表搭配location语法指定数据的路径可以让数据更安全。
• 可以通过DESCRIBE FORMATTED tablename，来获取表的元数据描述信息，其中若Table Type字段值为EXTERNAL_TABLE，则表示该表是外部表。

分区表

• 当Hive表对应的数据量大，文件个数多时，为避免查询时全表扫描数据，Hive支持根据指定字段对表进行分区，分区的字段可以是日期、地域、种类等具有标识意义的字段。
• 分区字段不能是表中已经存在的字段
• 分区字段最终也会以虚拟字段的形式显示在表结构上。
• 建立分区表后，底层文件管理方式发生改变，每个文件之上多了一个文件名为分区字段名=分区值的文件夹。

分区表建表语法

• 关键字：

PARTITIONED BY(col_name data_type[COMMENT col_comment],..)

• 案例:

CREATE TABLE t_all_hero(
	id INT,
	name STRING,
	role_main STRING
)PARTITIONED BY(role STRING) --此处为分区字段
 ROW FORMAT DELIMITED
 		FIELDS TERMINATED BY "\t";

分区表数据加载 – 静态分区

• 静态分区是指分区的属性值是由用户在加载数据时手动指定的
• 语法：

LOAD DATA [LOCAL] INPATH 'filepath' INTO TABLE tablename PARTITION(分区字段='分区值'...);

• 案例：

LOAD DATA LOCAL INPATH '/root/data/aecher.txt' INTO TABLE t_all_hero PARTITION(role='sheshou');

多重分区表

• 多重分区中，分区之间是一种递进关系，可以理解为在前一个分区的基础上继续分区。
• 指定多个分区字段即可实现多重分区表，一般指定两个分区字段。
• 分区字段的顺序要符合分区之间的递进关系。

分区表数据加载 – 动态分区

• 动态分区指的是分区的字段值是基于查询结果（参数位置）自动推断出来的。
• 核心语法是insert+select
• 启动Hive动态分区，需要在Hive会话中设置两个参数：

# 是否开启动态分区功能
SET hive.exec.dynamic.partition=true;
# 指定动态分区模式，分为nonstick非严格模式和strict严格模式
# strict严格模式要求至少有一个分区为静态分区
SET hive.exec.dynamic.partition.mode=nonstrict;

• 执行动态分区插入：
  此案例为将 t_all_hero 表数据插入 t_all_hero_part_dynamic 表，并设置动态分区，t_all_hero表不是分区表。

# 这是一条语句，分区字段一定要放在数据后(位置很重要)
INSERT INTO TABLE t_all_hero_part_dynamic PARTITION(role) 
SELECT tmp.*,tmp.role_main FROM t_all_hero tmp;

分区表注意事项

• 不是必要语法规则，是一种优化手段表
• 分区字段不能是表中已有字段，不能重复
• 分区字段是虚拟字段，数据不存储在底层文件中
• 分区字段值来自手动指定或根据查询结果位置自动推断
• Hive支持多重分区

分桶表

• 分桶表对应的数据文件在底层会被分解为若干个部分
• 在分桶时要指定根据哪个字段将数据分为几个捅
• 分桶规则（桶编号相同的数据会被分到同一个桶当中）：
  Bucket number = hash_fuction(bucketing_column) mod num_buckets
  分桶编号 = 哈希方法(分桶字段值) 取模 分桶个数
• 当分桶字段是字符串或其他复杂数据类型，将从该类型派生出某个数字，比如hashcode值。

分桶表创建语法

• CLUSTERED BY(col_name)表示根据col_name字段进行分桶
• INTO N BUCKETS表示分为几桶
• • 分桶字段必须是表中已有的字段

CLUSTERED BY (col_name,col_name,...)
[SORTED BY (col_name[ASC|DESC],...)]
INTO num_buckets BUCKETS

• 案例：

-- 根据state州分为5桶，每个桶内根据cases确诊病例数倒序排序
CREATE TABLE pljnb.t_usa_covid19_bucket(
	count_date STRING,
	county STRING,
	state STRING,
	fips INT,
	cases INT
)CLUSTERED BY (state)
 SORTED BY (cases DESC)
 INTO 5 BUCKETS;

分桶表数据加载

• 需要通过插入查询的方式加载数据，不能直接上传数据。
• step1：开启分桶功能(Hive2.0后不需要设置)

SET hive.enforce.bucketing=true;

• step2：把源数据加载到普通hive中

CREATE TABLE pljnb.t_usa_covid19(
	count_date STRING,
	county STRING,
	state STRING,
	fips INT,
	cases INT
)ROW FORMAT DELIMITED FIELDS TERMINATED BY ",";

• step3：使用查找插入法将数据加载到分桶表中

INSERT INTO t_usa_covid19_bucket SELECT * FROM t_usa_covid19;

分桶好处

1. 基于分桶字段查询时，减少全表扫描
2. JOIN时可以提高MR程序效率，减少笛卡尔积数量(根据JOIN字段进行分桶)
3. 可以进行高效抽样