hive 查询优化之动态分区详解
最近生产环境有将近 8 亿条，数据大小约 1T 左右的某类型客户业务数据需要查询分析。但是发现执行一次 hive sql 筛选数据需要耗时近几千秒，这样的性能指标实在太不理想太慢。所以需要对 hive 查询性能进行优化，在查询相关资料发现 hive 查询优化有如下几种方式：
- 使用分区表( use partition table );
- 连接优化( join table );
- 排序优化( order by );
- 合并小文件，文件数目过多，会给 HDFS 带来压力，并且会影响处理效率。
针对以上几种性能优化方法，分析目前业务数据特点，决定使用分区表来提高查询性能，具体如何操作，下面详细解答。
由于我们的业务数据是伴随着用户交易时产生的，比较符合时序序列数据的特点，且每天数据量较为平均，日均数据量在 300~500w 条左右。结合以上特点，我们针对该数据表采用时间分区即按天建数据分区的方案。
- 单分区建表操作
a.创建数据表
create database if not exist test;
use test;
create table if not exist application (
id int,
name string,
hobby array<string>,
address map<Strng,string>,
create_time string
)partitioned by (datetime string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"separatorChar" = ",",
"quoteChar" = "'",
"escapeChar" = "\\"
) STORED AS TEXTFILE;上面操作建立一张单分区表，表源文件是按照 CSV/TSV 格式来存储 text 文件，分区字段是 datetime。注意：分区字段不能和表中源字段重复。
b.导入数据
在数据导入有几种方案，第一种可以创建 hive 外部表，用 location 关键字指定表源地址。
create external table application (...)
WITH SERDEPROPERTIES (...)
partitioned by (datetime string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
location '/appdata/application';第二种直接采用 load data 加载 csv源文件
LOAD DATA LOCAL INPATH '/appdata/application/application.csv' \
OVERWRITE INTO TABLE test.application(datetime='20190418');c.查看数据及分区
select * from test.application limit 1;
show partitions test.application;可以看到数据已经导入到分区 datetime=20190418 中，show create table test.application 查看数据在 hdfs 的目录。但是这样的分区表我不能每天手动将数据导入到指定的分区，这样的操作影响效率，所以我们就需要使用到 hive 的“动态分区( dynamic partition )”。
我们的方案是先创建源数据外部表，然后采用动态分区方式从源数据表导入数据到新的分区表，和上述操作的主要区别在于：我们在数据导入的操作中不指定分区值，而是根据数据中的 create_time 自动导入到相应分区。
---- hive 源数据表
create external table application_source (...)
WITH SERDEPROPERTIES (...)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE
location '/appdata/application';---- hive 动态分区数据表
create table application (...)
WITH SERDEPROPERTIES (...)
partitioned by (datetime string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
STORED AS TEXTFILE;注意：在使用 hive 动态分区时需要修改 hive 动态分区相关参数
---- hive 参数配置
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
---- hive 数据导入
insert overwrite table application partition(datetime)
select id,name,hobby,address,create_time
from application_source;hive 先获取 select 的最后一个位置的 create_time 参数值，然后将这个值填写到 insert 语句 partition 中的 datetime 变量中，即动态分区是通过位置来对应分区值的。
这时我们再使用已经分好区的数据表执行 hive sql 筛选数据，但是此时可以手动在 sql 条件中增加时间的过滤。
select id,name,hobby,address,create_time from application where
... and create_time=20190418; 这样可以避免 hive 查询扫描全表，经过上述优化之后，查询时间由原来的上千秒减小到几百秒，查询性能提升近 10 倍。当然，后续肯定还有优化的空间，下一篇将使用查询引擎进行查询优化。
腾讯云开发者
