Hive存储格式

一、官网Hive存储格式
官网所列：

file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)
  | AVRO        -- (Note: Available in Hive 0.14.0 and later)
  | JSONFILE    -- (Note: Available in Hive 4.0.0 and later)
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname

实际业务中我们只需掌握：

file_format:
  : SEQUENCEFILE
  | TEXTFILE    -- (Default, depending on hive.default.fileformat configuration)
  | RCFILE      -- (Note: Available in Hive 0.6.0 and later)
  | ORC         -- (Note: Available in Hive 0.11.0 and later)
  | PARQUET     -- (Note: Available in Hive 0.13.0 and later)

二、存储格式测试
1、SEQUENCEFILE 序列化(K-V)

image.png

我Hive中一张textfile为存储格式views表，大小为18.1M

[hadoop@hadoop000 data]$ hadoop fs -du -s -h /user/hive/warehouse/g6_hadoop.db/views
18.1 M  18.1 M  /user/hive/warehouse/g6_hadoop.db/views

现在我们创建一张和views一样表结构的views_seq表，只是存储格式改为SEQUENCEFILE

create table views_seq(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
) row format delimited fields terminated by '\t'
stored as sequencefile ;

将views表数据插入views_seq表中

insert into table views_seq select * from views;

对比原始大小：

[hadoop@hadoop000 data]$ hadoop fs -du -s -h /user/hive/warehouse/g6_hadoop.db/views
18.1 M  18.1 M  /user/hive/warehouse/g6_hadoop.db/views
[hadoop@hadoop000 data]$ hadoop fs -du -s -h /user/hive/warehouse/g6_hadoop.db/views_seq
19.6 M  19.6 M  /user/hive/warehouse/g6_hadoop.db/views_seq
[hadoop@hadoop000 data]$

会发现会比原始数据还要大，所以此存储格式一般很少用

2、RCFILE 行列混合
现在我们创建一张和views一样表结构的views_rc表，只是存储格式改为RCFILE，并将views表数据插入views_rc表中。

对比原始大小：

[hadoop@hadoop000 data]$ hadoop fs -du -s -h /user/hive/warehouse/g6_hadoop.db/views
18.1 M  18.1 M  /user/hive/warehouse/g6_hadoop.db/views
[hadoop@hadoop000 data]$ hadoop fs -du -s -h /user/hive/warehouse/g6_hadoop.db/views_rc
17.9 M  17.9 M  /user/hive/warehouse/g6_hadoop.db/views_rc

查询没有做什么优化，只是节省了10%的存储空间

3、 $\color{red}{ORC}$

image.png

ORC引入stripes的概念，从而使查询性能非常好，生产上大多数都使用的ORC。

现在我们创建一张和views一样表结构的views_orc表，只是存储格式改为orc，并将views表数据插入views_rc表中。

create table views_orc(
track_time string,
url string,
session_id string,
referer string,
ip string,
end_user_id string,
city_id string
) row format delimited fields terminated by '\t'
stored as orc ;
---------------------------------------------------------------------------------------------------------
insert into views_orc select * from views;

对比原始大小：

[hadoop@hadoop000 data]$ hadoop fs -du -s -h /user/hive/warehouse/g6_hadoop.db/views
18.1 M  18.1 M  /user/hive/warehouse/g6_hadoop.db/views
[hadoop@hadoop000 data]$ hadoop fs -du -s -h /user/hive/warehouse/g6_hadoop.db/views_orc
2.8 M  2.8 M  /user/hive/warehouse/g6_hadoop.db/views_orc

之所以数据大小少了这么多是因为ORC默认采用了 ZLIB 压缩，我们去掉压缩后数据量在7.7M所有，也会比原始大小小很多，节省了很多空间。

4、 $\color{red}{PARQUET}$ 性能(查询、压缩存储)与ORC差不多，生产上可以随便选择，不过大数据还是会选择ORC，因为ORC的压缩比PARQUET好一点

三、从查询方面来看这个几个存储格式的优劣
1、原始格式(textfile)

hive (g6_hadoop)> select count(*) from views where session_id='f55598cafba346eb217ff3fbd0de2930';
Query ID = hadoop_20190420005050_84924cd6-5269-4d72-993e-056d36dac0d7
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1555685664231_0011, Tracking URL = http://hadoop000:8088/proxy/application_1555685664231_0011/
Kill Command = /home/hadoop/soul/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1555685664231_0011
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2019-04-20 01:27:19,121 Stage-1 map = 0%,  reduce = 0%
2019-04-20 01:27:26,441 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.02 sec
2019-04-20 01:27:33,724 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.28 sec
MapReduce Total cumulative CPU time: 3 seconds 280 msec
Ended Job = job_1555685664231_0011
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.28 sec   HDFS Read: 19022693 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 280 msec
OK
_c0
10
Time taken: 22.757 seconds, Fetched: 1 row(s)

HDFS Read: 19022693 所有数据都load进来了
Time taken: 22.757 seconds

2、SEQUENCEFILE

hive (g6_hadoop)> select count(*) from views_seq where session_id='f55598cafba346eb217ff3fbd0de2930';
Query ID = hadoop_20190420005050_84924cd6-5269-4d72-993e-056d36dac0d7
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1555685664231_0012, Tracking URL = http://hadoop000:8088/proxy/application_1555685664231_0012/
Kill Command = /home/hadoop/soul/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1555685664231_0012
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2019-04-20 01:29:21,115 Stage-1 map = 0%,  reduce = 0%
2019-04-20 01:29:28,432 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.62 sec
2019-04-20 01:29:34,714 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.77 sec
MapReduce Total cumulative CPU time: 3 seconds 770 msec
Ended Job = job_1555685664231_0012
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.77 sec   HDFS Read: 20509194 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 770 msec
OK
_c0
10
Time taken: 21.403 seconds, Fetched: 1 row(s)

HDFS Read: 20509194
Time taken: 21.403
3、RCFILE

hive (g6_hadoop)> select count(*) from views_rc where session_id='f55598cafba346eb217ff3fbd0de2930';
Query ID = hadoop_20190420005050_84924cd6-5269-4d72-993e-056d36dac0d7
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1555685664231_0013, Tracking URL = http://hadoop000:8088/proxy/application_1555685664231_0013/
Kill Command = /home/hadoop/soul/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1555685664231_0013
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2019-04-20 01:30:39,670 Stage-1 map = 0%,  reduce = 0%
2019-04-20 01:30:45,944 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.62 sec
2019-04-20 01:30:52,201 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.78 sec
MapReduce Total cumulative CPU time: 2 seconds 780 msec
Ended Job = job_1555685664231_0013
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.78 sec   HDFS Read: 3725353 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 780 msec
OK
_c0
10
Time taken: 20.54 seconds, Fetched: 1 row(s)

HDFS Read: 3725353
Time taken: 20.54 seconds

4、ORC

hive (g6_hadoop)> select count(*) from views_orc where session_id='f55598cafba346eb217ff3fbd0de2930';
Query ID = hadoop_20190420005050_84924cd6-5269-4d72-993e-056d36dac0d7
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1555685664231_0014, Tracking URL = http://hadoop000:8088/proxy/application_1555685664231_0014/
Kill Command = /home/hadoop/soul/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1555685664231_0014
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2019-04-20 01:32:02,728 Stage-1 map = 0%,  reduce = 0%
2019-04-20 01:32:08,961 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 1.42 sec
2019-04-20 01:32:16,253 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 2.66 sec
MapReduce Total cumulative CPU time: 2 seconds 660 msec
Ended Job = job_1555685664231_0014
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 2.66 sec   HDFS Read: 1257473 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 2 seconds 660 msec
OK
_c0
10
Time taken: 21.202 seconds, Fetched: 1 row(s)

HDFS Read: 1257473
Time taken: 21.202

5、PARQUET

hive (g6_hadoop)> select count(*) from views_parquet where session_id='f55598cafba346eb217ff3fbd0de2930';
Query ID = hadoop_20190420005050_84924cd6-5269-4d72-993e-056d36dac0d7
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks determined at compile time: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1555685664231_0015, Tracking URL = http://hadoop000:8088/proxy/application_1555685664231_0015/
Kill Command = /home/hadoop/soul/app/hadoop-2.6.0-cdh5.7.0/bin/hadoop job  -kill job_1555685664231_0015
Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1
2019-04-20 01:34:27,219 Stage-1 map = 0%,  reduce = 0%
2019-04-20 01:34:33,531 Stage-1 map = 100%,  reduce = 0%, Cumulative CPU 2.06 sec
2019-04-20 01:34:40,785 Stage-1 map = 100%,  reduce = 100%, Cumulative CPU 3.3 sec
MapReduce Total cumulative CPU time: 3 seconds 300 msec
Ended Job = job_1555685664231_0015
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 1  Reduce: 1   Cumulative CPU: 3.3 sec   HDFS Read: 2687019 HDFS Write: 3 SUCCESS
Total MapReduce CPU Time Spent: 3 seconds 300 msec
OK
_c0
10
Time taken: 21.342 seconds, Fetched: 1 row(s

HDFS Read: 2687019
Time taken: 21.342

虽说数据量很小，测试不是也别准，但是总和各种还是会发现生产上ORC更加合适

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 159,458评论 4赞 363
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 67,454评论 1赞 294
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 109,171评论 0赞 243
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 44,062评论 0赞 207
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 52,440评论 3赞 287
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 40,661评论 1赞 219
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 31,906评论 2赞 313
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 30,609评论 0赞 200
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 34,379评论 1赞 246
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 30,600评论 2赞 246
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 32,085评论 1赞 261
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 28,409评论 2赞 254
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 33,072评论 3赞 237
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 26,088评论 0赞 8
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 26,860评论 0赞 195
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 35,704评论 2赞 276
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 35,608评论 2赞 270

Hive存储格式

推荐阅读更多精彩内容