Hive外部表应用

生产环境一般都是采用Flume将日志导入HDFS中,为减少磁盘占用,Flume也会将日志lzo压缩后写入HDFS中。
为了提高hive性能,一般还会对外部表进行日期、关键词分区。
对性能控而言,还可以将lzo源表转换为orc表,这样也可能大幅提高hive性能。

创建外部分区表

创建一个外部分区表heartbeat,其文件存储在/hive/data/heartbeat中。

1
2
3
4
5
6
7
create external table heartbeat(
id String,
ip String,
time String
) partitioned by (
date String
) location '/hive/data/heartbeat';

加载外部分区

一般都基于日期分区,但是此时查询heartbeat表将查询不到任何数据,因为hive不会自动加载日期分区。

可以通过如下sql加载日期分区:

1
ALTER TABLE heartbeat ADD PARTITION (date='2014-09-22') location '/hive/data/heartbeat/2014-09-22/';

如果在生产环境,那么需要针对每一天都执行一下ADD PARTITION,传统的方式就是通过定时任务动态执行加载分区。

传统方式—定时任务加载分区

以下为通过cron定时任务调用python脚本加载分区的python代码片段:

1
2
3
4
5
6
schema = '?'
table = '?'
location = location + date + '/'
sql = '/opt/hive/bin/hive -e "use %s; ALTER TABLE %s ADD PARTITION (date=\'%s\') location \'%s\'"'\
% (schema, table, date, location)
os.system(sql)

极端方式—一次性加载分区

该思路源于链接:http://wtfbigdata.vickiboykis.com/2013/07/19/automatically-creating-external-partitions-in-hive-with-a-python-script/

大致内容就是在创建外部表时,直接通过python脚本把未来一年的分区一次性创建完毕。

hive加载分区时,如果这个分区目录不存在,hive则会主动创建一个空目录。因此这种方式其实还是有借鉴价值的,不过我在生产环境不会采用。

采用lzo压缩

如果采用flume收集日志,则可以设置hdfs中日志存储格式为lzo压缩,这样可以大幅降低文件大小。

此时可以在创建hive外部表时,指定inputformat为lzo压缩:com.hadoop.mapred.DeprecatedLzoTextInputFormat

具体实例如下(关注stored as部分):

1
2
3
4
5
6
7
8
9
create external table heartbeat(
id String,
name String
) partitioned by (date String)
row format delimited fields terminated by ','
stored as
inputformat 'com.hadoop.mapred.DeprecatedLzoTextInputFormat'
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
location '/path/';

lzo压缩能够大幅降低日志文件的大小,大概能缩小至原大小的20%左右。

转换为ORC格式Hive表

可以将hive表转换为orc格式存储,这种格式一方面能够大幅降低磁盘占用,另一方面能够大幅提高执行效率。

将hive表转换为orc格式表之前,需要分两步执行。

首先需要根据hive表创建orc格式的hive表,列与列直接对应上:

1
2
3
4
5
6
create table sp_orc.heartbeat(
id String,
name String
)
partitioned by (date string)
stored as orc;

然后就可以将textfile格式的源hive表分区导入orc表中:

1
2
insert into table sp_orc.heartbeat partition (date='2014-09-01')
select id, name from sp_logs.heartbeat where date='2014-09-01';

需要注意的是,如果源表与orc表结构完全相同,那么以上sql中的子查询就可以直接采用select *了。

简单测试一下lzo压缩带来的性能变化。

原始数据

原始数据有23690132条,查询如下:

1
2
3
4
5
6
7
8
hive> select count(\*) from heartbeat where date='2014-09-01';
Total MapReduce CPU Time Spent: 1 minutes 28 seconds 730 msec
OK
23690132
Time taken: 22.8 seconds, Fetched: 1 row(s)
hive> select id, max(power) from sp_logs.heartbeat where date='2014-09-01' group by id;
......
Time taken: 24.152 seconds, Fetched: 14213 row(s)

转换为ORC存储

可以直接在hive中执行insert into,将textfile格式的源数据表导入orc格式的新表,要注意insert into时的新旧表列对应。

1
2
3
4
5
hive> insert into table sp_orc.heartbeat partition (date='2014-09-01')
select id,power,code,ver,oper,pwd,suc,card,p,ip,time from sp_logs.heartbeat where date='2014-09-01';
Total MapReduce CPU Time Spent: 6 minutes 57 seconds 50 msec
OK
Time taken: 205.335 seconds

此处有一个坑:当使用hive动态分区时,如果有多个分区,那么partition()内的顺序必须与select … 内的顺序保持一致,否则。。。

ORC格式

ORC格式的表数据一样是23690132条,查询耗时如下:

1
2
3
4
5
6
7
hive> select count(*) from sp_orc.heartbeat where date='2014-09-01';
Total MapReduce CPU Time Spent: 8 seconds 80 msec
OK
23690132
Time taken: 22.058 seconds, Fetched: 1 row(s)
hive> select id, max(power) from sp_orc.heartbeat where date='2014-09-01' group by id;
Time taken: 41.483 seconds, Fetched: 14213 row(s)

稍微复杂的性能测试

使用一点简单的group by查询,性能对比如下:

1
2
3
4
hive> select id from sp_logs.heartbeat group by id having max(time) < '2014-09-31 12:00:00';
Time taken: 599.524 seconds, Fetched: 679982 row(s)
hive> select id from sp_orc.heartbeat group by id having max(time) < '2014-09-31 12:00:00';
Time taken: 190.929 seconds, Fetched: 679982 row(s)

使用线上的数据再进行一下简单的测试:

1
2
3
4
5
6
select count(*) from sp_prod_orc.heartbeat where date='2014-10-23';
28669162
Time taken: 24.08 seconds, Fetched: 1 row(s)
select count(*) from sp_prod_source.heartbeat where date='2014-10-23';
28684464
Time taken: 143.85 seconds, Fetched: 1 row(s)

这样看起来,orc存储的性能提升还是比较明显的。

ORC表与lzo压缩对比

6个hadoop节点,都是四核8G内存,配置一般。

LZO压缩的原始数据量为881.2MB,转换为ORC格式后数据量为298.6MB

select into语句执行耗时208.662ms

普通计数语句对比

数据量为1670754,每一行都是比较复杂的json格式,每行数据量大概有2~5kb吧。

基于JSONSerde的LZO表,count耗时102.979秒。

ORC表的count耗时为19.207毫秒。

用到的SQL如下:

1
2
select count(*) from sp_prod_source.sp_access where date='2015-02-25';
select count(*) from sp_prod_orc.sp_access where date='2015-02-25';

较为复杂的SQL计算

数据量一样。

基于JSONSerde的LZO表,执行耗时128.883秒。

ORC表的执行耗时34.557秒。

用的的SQL如下(为避免公司信息泄露,SQL已做简单修改):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT
count(*) as pv,
count(distinct ip) as uiv,
count(distinct uuid) as usv,
host,
date,
regexp_replace(regexp_replace(uri, '[^ \\w/].*', ''), '//', '/') as uri
FROM
sp_prod_source.sp_access
WHERE
date = '2015-02-25'
and (uri like '%/j/%' or uri like '%/t/%'
or uri = ''
or uri = '/')
GROUP BY host , date , regexp_replace(regexp_replace(uri, '[^ \\w/].*', ''), '//', '/');