最近生产环境有将近 8 亿条,数据大小约 1T 左右的某类型客户业务数据需要查询分析。但是发现执行一次 hive sql 筛选数据需要耗时近几千秒,这样的性能指标实在太不理想太慢。所以需要对 hive 查询性能进行优化,在查询相关资料发现 hive 查询优化有如下几种方式:
针对以上几种性能优化方法,分析目前业务数据特点,决定使用分区表来提高查询性能,具体如何操作,下面详细解答。
由于我们的业务数据是伴随着用户交易时产生的,比较符合时序序列数据的特点,且每天数据量较为平均,日均数据量在 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 倍。当然,后续肯定还有优化的空间,下一篇将使用查询引擎进行查询优化。