分区、分桶

一、Hive定义分区/分桶表

Hive创建分区表语句

二、Hive分区表/分桶表描述

（1） Hive分区表描述：
分区列的值将表划分为文件夹

查询时使用“分区”列和常规列类似

查询时Hive自动过滤掉不用于提高性能的分区

（2）Hive分桶表描述：
分桶是相对分区进行更细粒度的划分

根据“桶列”的哈希函数将数据进行分桶

更高的查询处理效率

三、Hive操作分区/分桶类型及设置

（1）Hive分区

静态分区

动态分区

#使用动态分区需设置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;

（2）Hive分桶

动态分桶

#使用动态分桶需设置
set hive.enforce.bucketing = true;

四、Hive操作分区

静态分区操作示例

ALTER TABLE employee_partitioned ADD
PARTITION (year=2017,month=3) PARTITION (year=2017,month=4);
ALTER TABLE employee_partitioned DROP PARTITION(year=2017, month=4);

动态分区操作示例

INSERT INTO TABLE employee_partitioned PARTITION(year, month)SELECT name,array('Toronto') AS work_place,
named_struct(""sex","male" ,"age",30) AS sex_age,map("python",90)AS skills_score,
map("r&d" , array('developer')) AS depart_title,year(start_date) AS year,
month(start_date) AS monthFROM employee_hr;

五、Hive操作分桶

(1)创建外部表

create external table student2(id int,name string)

row format delimited fields terminated by '\t'stored as textfile

location "/tmp/student " ;

(2)查看表格式化数据

desc formatted student2;

随机抽样基于整行数据

SELECT* FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON rand()) s;

随机抽样基于指定列(使用分桶列更高效)

SELECT* FROM table_name TABLESAMPLE(BUCKET 3 OUT OF 32 ON id) s;

随机抽样基于block size

SELECT* FROM table_name TABLESAMPLE(10 PERCENT) s;SELECT* FROM table_name TABLESAMPLE(1M) s;
SELECT * FROM table_name TABLESAMPLE(10 rows) s;