Hive存储格式textfile、sequencefile、avro、parquet、rcfile、orc 对比

本文目的是hive的文件格式的生成数据时间和文件存储大小查询时间的对比。生成数据使用的都是hivesql 及配置选项，该选项暂不都适用于spark。

一：建表语句

1.简单介绍

原始文件描述：原始文件未压缩：69G 记录数：11.8亿 文件数：100
Hive引擎：tez。资源限制：输入文件大小确定，使用资源确定
测试文件类型：行压缩：text、json、sequence、avro 列压缩：parquet、orc
压缩格式配置：更改文件压缩格式的配置项：（参数类型确定文件的压缩类型及压缩格式，文件类型由建表语句确定）

    set hive.exec.compress.output=true;
    set mapreduce.output.fileoutputformat.compress=true;
    set mapreduce.output.fileoutputformat.compress.type=RECORD/BLOCK; （压缩类型）
    set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.DefaultCodec;
    set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.GzipCodec;
    set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.BZip2Codec;
    set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
    set mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzopCodec;
    set mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzoCodec;

所以需要建的表个数为：文件个数6压缩方法6压缩类型2 = 72

2.建表语句
    create table if not exists text_record/block_default/gzip/bzip/snappy/slz4/slz3( gazj string, pkg string, open_cnt bigint ) row format delimited fields terminated by '\001';
    create table if not exists jsonfile_record/block_default/gzip/bzip/snappy/slz4/slz3( gazj string, pkg string, open_cnt bigint ) row format serde 'org.apache.hive.hcatalog.data.JsonSerDe' stored as textfile;
    create table if not exists sequencefile_record/block_default/gzip/bzip/snappy/slz4/slz3( gazj string, pkg string, open_cnt bigint ) row format delimited fields terminated by '\001' stored as sequencefile;
    create table if not exists avro_record/block_default/gzip/bzip/snappy/slz4/slz3( gazj string, pkg string, open_cnt bigint ) row format delimited fields terminated by '\001' stored as avro;
    create table if not exists parquet_record/block_default/gzip/bzip/snappy/slz4/slz3( gazj string, pkg string, open_cnt bigint ) row format delimited fields terminated by '\001' stored as parquet;
    create table if not exists orc_record/block_default/gzip/bzip/snappy/slz4/slz3( gazj string, pkg string, open_cnt bigint ) row format delimited fields terminated by '\001' stored as orc;
3.插入数据

有时候集群session会回收，所需变量多设置几遍，此处列举出json的插入语句，其他格式文件SQL一致。

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=RECORD;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.DefaultCodec;
insert into jsonfile_record_default  select gazj,pkg,open_cnt from file_base   ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=RECORD;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.GzipCodec;
insert into jsonfile_record_gzip  select gazj,pkg,open_cnt    from file_base     ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=RECORD;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.BZip2Codec;
insert into jsonfile_record_bzip  select gazj,pkg,open_cnt    from file_base     ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=RECORD;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
insert into jsonfile_record_snappy  select gazj,pkg,open_cnt  from file_base   ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=RECORD;
set mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzopCodec;
insert into jsonfile_record_slz4  select gazj,pkg,open_cnt    from file_base     ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=RECORD;
set mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzoCodec;
insert into jsonfile_record_slz3  select gazj,pkg,open_cnt    from file_base     ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.DefaultCodec;
insert into jsonfile_block_default  select gazj,pkg,open_cnt from file_base   ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.GzipCodec;
insert into jsonfile_block_gzip  select gazj,pkg,open_cnt    from file_base     ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.BZip2Codec;
insert into jsonfile_block_bzip  select gazj,pkg,open_cnt    from file_base     ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
set mapreduce.output.fileoutputformat.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
insert into jsonfile_block_snappy  select gazj,pkg,open_cnt  from file_base   ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
set mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzopCodec;
insert into jsonfile_block_slz4  select gazj,pkg,open_cnt    from file_base     ;

set hive.exec.compress.output=true;
set mapreduce.output.fileoutputformat.compress=true;
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
set mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzoCodec;
insert into jsonfile_block_slz3  select gazj,pkg,open_cnt    from file_base     ;

二：存储、生成时间、查询时间

1.生成策略
  • 存储：hadoop fs -du -s -h path
  • 生成时间： insert 语句执行时间，包含最终移动文件的时间（hive生成临时目录把数据写入，任务成功后把数据移动过去，耗时较长）
  • 查询时间：select count(distinct gazj) as usernumber,count(distinct pkg) as pkgnumber,sum(open_cnt) as cntnumber from table;
2.查询资源限制
  • sparksql --master yarn --deploy-mode cluster --num-executors 20 --executor-memory 8g --executor-cores 1 --driver-memory 1g --name "filetest" --conf spark.speculation=true --conf spark.default.parallelism=60 --conf spark.sql.shuffle.partitions=60 --conf spark.speculation.interval=30000 --conf spark.speculation.quantile=0.8 --conf spark.speculation.multiplier=1.5 --conf spark.dynamicAllocation.enabled=false
3.text文件格式
文件格式、压缩大小插入数据时间查询时间文件格式、压缩大小插入数据时间查询时间
text_block_default 31.0G 90.632 2.8 min text_record_default 31.0G 93.65 3.1 min
text_block_gzip 31.0G 86.223 2.9 min text_record_gzip 31.0G 84.693 3.2 min
text_block_bzip 24.4G 101.617 6.3 min text_record_bzip 24.4G 104.93 6.5 min
text_block_snappy 50.9G 124.046 2.6 min text_record_snappy 50.9G 127.619 2.6 min
text_block_slz4 50.5G 123.966 2.7 min text_record_slz4 50.5G 127.737 2.7 min
text_block_slz3 50.5G 131.913 2.7 min text_record_slz3 50.5G 130.654 2.7 min
  • text格式压缩类型：RECORD/BLOCK 几乎相同
  • text格式压缩大小：SnappyCodec/LzopCodec/LzoCodec >> DefaultCodec/GzipCodec >> BZip2Codec
  • text格式压缩时间：LzoCodec/SnappyCodec/LzopCodec >> BZip2Codec/DefaultCodec >> GzipCodec
  • text格式查询时间：BZip2Codec >> LzoCodec/SnappyCodec/LzopCodec/DefaultCodec/GzipCodec
4.json文件格式
文件格式、压缩大小插入数据时间查询时间文件格式、压缩大小插入数据时间查询时间
json_block_default 32.9G 101.086 4.4 min json_record_default 32.9G 102.05 9.4 min
json_block_gzip 32.9G 86.127 4.4 min json_record_gzip 32.9G 104.901 9.0 min
json_block_bzip 24.8G 129.161 9.0 min json_record_bzip 24.8G 121.68 9.6 min
json_block_snappy 54.4G 152.744 4.0 min json_record_snappy 54.4G 133.117 4.0 min
json_block_slz4 54.3G 128.66 4.1 min json_record_slz4 54.3G 131.215 4.2 min
json_block_slz3 54.3G 132.583 4.1 min json_record_slz3 54.3G 134.738 4.2 min

  • json格式压缩类型：RECORD/BLOCK 几乎相同

  • json格式压缩大小：SnappyCodec/LzopCodec/LzoCodec >> DefaultCodec/GzipCodec >> BZip2Codec

  • json格式压缩时间：SnappyCodec/LzopCodec/LzoCodec/BZip2Codec >> DefaultCodec/GzipCodec

  • json格式查询时间：BZip2Codec >> GzipCodec >> LzoCodec/SnappyCodec/LzopCodec/DefaultCodec

5.sequence文件格式
文件格式、压缩大小插入数据时间查询时间文件格式、压缩大小插入数据时间查询时间
sequence_block_default 32.5G 102.114 3.0 min sequence_record_default 89.6G 231.087 5.5 min
sequence_block_gzip 32.5G 96.817 3.1 min sequence_record_gzip 103.0G 269.079 5.6 min
sequence_block_bzip 26.3G 100.782 5.9 min sequence_record_bzip 119.4G 492.76 12 min
sequence_block_snappy 52.7G 135.072 2.8 min sequence_record_snappy 94.8G 200.947 5.6 min
sequence_block_slz4 52.2G 130.064 失败 sequence_record_slz4 92.0G 211.345 失败
sequence_block_slz3 52.2G 132.897 失败 sequence_record_slz3 96.4G 213.864 失败

  • sequence格式压缩类型：RECORD >> BLOCK （BLOCK压缩明显性能更高，后续选择BLOCK）

  • sequence格式压缩算法大小：SnappyCodec/LzopCodec/LzoCodec >> DefaultCodec/GzipCodec >> BZip2Codec

  • sequence格式压缩算法时间：SnappyCodec/LzopCodec/LzoCodec >> BZip2Codec/DefaultCodec/GzipCodec

  • sequence格式查询时间：

6.avro文件格式
文件格式、压缩大小插入数据时间查询时间文件格式、压缩大小插入数据时间查询时间
avro_block_default 32.3G 97.474 4.4 min avro_record_default 32.3G 113.485 4.8 min
avro_block_gzip 32.3G 92.134 4.3 min avro_record_gzip 32.3G 103.501 4.7 min
avro_block_bzip 32.3G 82.591 4.7 min avro_record_bzip 32.3G 97.516 4.6 min
avro_block_snappy 32.3G 96.65 4.4 min avro_record_snappy 32.3G 92.976 4.5 min
avro_block_slz4 32.3G 101.621 4.4 min avro_record_slz4 32.3G 98.105 4.4 min
avro_block_slz3 32.3G 111.828 4.5 min avro_record_slz3 32.3G 105.724 4.4 min

  • avro格式压缩类型：RECORD/BLOCK 相同

  • avro格式压缩算法大小：SnappyCodec/LzopCodec/LzoCodec/DefaultCodec/GzipCodec/BZip2Codec

  • avro格式压缩算法时间：SnappyCodec/LzopCodec/LzoCodec/DefaultCodec/GzipCodec/BZip2Codec

  • avro格式查询时间：SnappyCodec/LzopCodec/LzoCodec/DefaultCodec/GzipCodec/BZip2Codec

7.parquet文件格式
文件格式、压缩大小插入数据时间查询时间文件格式、压缩大小插入数据时间查询时间
parquet_block_default 57.1G 142.172 3.3 min parquet_record_default 57.1G 159.369 3.9 min
parquet_block_gzip 28.4G 89.212 3.2 min parquet_record_gzip 28.4G 86.095 3.7 min
parquet_block_bzip 57.1G 156.452 3.3 min parquet_record_bzip 57.1G 144.316 3.6 min
parquet_block_snappy 57.1G 136.822 3.3 min parquet_record_snappy 57.1G 139.707 3.6 min
parquet_block_slz4 57.1G 141.666 3.5 min parquet_record_slz4 57.1G 146.327 3.5 min
parquet_block_slz3 45.2G 104.837 3.4 min parquet_record_slz3 45.2G 123.72 3.4 min

  • parquet格式压缩类型：RECORD/BLOCK 相同

  • parquet格式压缩算法大小：SnappyCodec/LzopCodec/BZip2Codec/DefaultCodec >> LzoCodec >> GzipCodec/

  • parquet格式压缩算法时间：SnappyCodec/LzopCodec/BZip2Codec/DefaultCodec >> LzoCodec >> GzipCodec/

  • parquet格式查询时间：SnappyCodec/LzopCodec/LzoCodec/DefaultCodec/GzipCodec/BZip2Codec

8.orc文件格式
文件格式、压缩大小插入数据时间查询时间文件格式、压缩大小插入数据时间查询时间
orc_block_default 25.9G 77.746 2.3 min orc_record_default 25.9G 78.802 2.5 min
orc_block_gzip 25.9G 69.247 2.2 min orc_record_gzip 25.9G 77.735 2.4 min
orc_block_bzip 25.9G 73.583 2.2 min orc_record_bzip 25.9G 73.086 2.4 min
orc_block_snappy 25.9G 73.607 2.1 min orc_record_snappy 25.9G 81.769 2.3 min
orc_block_slz4 25.9G 77.746 2.2 min orc_record_slz4 25.9G 74.542 2.3 min
orc_block_slz3 25.9G 73.251 2.2 min orc_record_slz3 25.9G 78.383 2.3 min

  • orc格式压缩类型：RECORD/BLOCK 相同

  • orc格式压缩算法大小：SnappyCodec/LzopCodec/BZip2Codec/DefaultCodec/LzoCodec/GzipCodec/

  • orc格式压缩算法时间：SnappyCodec/LzopCodec/BZip2Codec/DefaultCodec/LzoCodec/GzipCodec/

  • orc格式查询时间：SnappyCodec/LzopCodec/LzoCodec/DefaultCodec/GzipCodec/BZip2Codec

三：模拟得分

  • 分箱后面的数据为存在的格式，分箱得到依据为：手动分箱，存在一定不合理性，试过等频等距效果不好，暂时未学算法相关不会KS分箱。

  • 压缩空间得分：[24,33}42 [45,60}25 [60-100}4 [100+}2 =IF(B2>=100,25,(IF(B2>=60,50,(IF(B2>=45,75,100)))))

  • 插入数据得分：[60-80}11, [82,100}16, [100,130}22, [130,160}17, [160,300}5, [300+}1 =IF(C2>=300,17,(IF(C2>=160,33,(IF(C2>=130,50,(IF(C2>=100,67,(IF(C2>=82,84,100)))))))))

  • 查询数据得分：[2 ,3}11, [3 ,4}20, [4 ,5}20, [5 ,6}6, [6 ,7}5, [7 ,8}1, [8 ,10}4, [10 ,11}1, [11 ,12}1

    =IF(D2>=11,11,(IF(D2>=10,22,(IF(D2>=8,33,(IF(D2>=7,44,(IF(D2>=6,55,(IF(D2>=5,66,(IF(D2>=4,77,(IF(D2>=3,88,99)))))))))))))))

文件格式总得分排名文件格式总得分排名文件格式总得分排名
orc_block_default 299 1 orc_block_gzip 299 2 orc_block_bzip 299 3
orc_block_snappy 299 4 orc_block_slz4 299 5 orc_block_slz3 299 6
orc_record_default 299 7 orc_record_gzip 299 8 orc_record_bzip 299 9
orc_record_snappy 299 10 orc_record_slz4 299 11 orc_record_slz3 299 12
text_block_default 283 13 text_block_gzip 283 14 sequence_block_gzip 272 15
parquet_block_gzip 272 16 text_record_default 272 17 text_record_gzip 272 18
parquet_record_gzip 272 19 json_block_gzip 261 20 avro_block_default 261 21
avro_block_gzip 261 22 avro_block_bzip 261 23 avro_block_snappy 261 24
avro_record_bzip 261 25 avro_record_snappy 261 26 avro_record_slz4 261 27
sequence_block_default 255 28 json_block_default 244 29 avro_block_slz4 244 30
avro_block_slz3 244 31 avro_record_default 244 32 avro_record_gzip 244 33
avro_record_slz3 244 34 text_block_snappy 241 35 text_block_slz4 241 36
text_record_snappy 241 37 text_record_slz4 241 38 sequence_block_bzip 233 39
parquet_block_slz3 230 40 parquet_record_slz3 230 41 text_block_slz3 224 42
sequence_block_snappy 224 43 text_record_slz3 224 44 text_block_bzip 222 45
text_record_bzip 222 46 json_block_slz4 219 47 parquet_block_default 213 48
parquet_block_bzip 213 49 parquet_block_snappy 213 50 parquet_block_slz4 213 51
parquet_record_default 213 52 parquet_record_bzip 213 53 parquet_record_snappy 213 54
parquet_record_slz4 213 55 json_block_snappy 202 56 json_block_slz3 202 57
json_record_snappy 202 58 json_record_slz4 202 59 json_record_slz3 202 60
json_block_bzip 200 61 json_record_default 200 62 json_record_gzip 200 63
json_record_bzip 200 64 sequence_record_default 149 65 sequence_record_snappy 149 66
sequence_block_slz4 125 67 sequence_block_slz3 125 68 sequence_record_gzip 124 69
sequence_record_slz4 83 70 sequence_record_slz3 83 71 sequence_record_bzip 53 72

  • 结论：ORC格式数据称碾压趋势，各方面性能都较好。

  • 改测试存在部分不合理地方：生成数据的是用的是hive sql生成，但是真正使用过程中大部分使用spark，可能spark会对parquet由更好的优化，会导致文件存储和生成数据的得分提高

posted @ 2019-11-06 22:48  Kotlin  阅读(10432)  评论(0)  编辑  收藏  举报
Live2D