本文目的是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由更好的优化，会导致文件存储和生成数据的得分提高