创建hive表时格式如何选择

2019-02-10 大数据数据仓库 0 评论字数统计: 1.6k(字) 阅读时长: 8(分)

本文转载自https://www.cnblogs.com/barneywill/p/10109508.html
偶然看到这篇文章，这位老哥做的对比测试很好，当初我们在新数仓设计时，都没有对比测试的这么详细。

常用格式

textfile

需要定义分隔符，占用空间大，读写效率最低，非常容易发生冲突（分隔符）的一种格式，基本上只有需要导入数据的时候才会使用，比如导入csv文件；

ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\u0001’
LINES TERMINATED BY ‘\n’
STORED AS TEXTFILE

json

hive3.0后官方支持json格式，之前需要使用第三方，导入jar，http://www.congiu.net/hive-json-serde/，

add jar hdfs://nn/jarpath/json-udf-1.3.8-jar-with-dependencies.jar;
add jar hdfs://nn/jarpath/json-serde-1.3.8-jar-with-dependencies.jar;

占用空间最大，读写效率低，基本上只有需要导入数据的时候才会使用，比如导入json文件；

ROW FORMAT SERDE ‘org.openx.data.jsonserde.JsonSerDe’
STORED AS TEXTFILE

xml

http://central.maven.org/maven2/com/ibm/spss/hive/serde2/xml/hivexmlserde/1.0.0.0/hivexmlserde-1.0.0.0.jar

CREATE TABLE xml_bank(customer_id STRING, income BIGINT, demographics 
   map<string,string>, financial map<string,string>)
   ROW FORMAT SERDE 'com.ibm.spss.hive.serde2.xml.XmlSerDe'
   WITH SERDEPROPERTIES (
   "column.xpath.customer_id"="/record/@customer_id",
   "column.xpath.income"="/record/income/text()",
   "column.xpath.demographics"="/record/demographics/*",
   "column.xpath.financial"="/record/financial/*"
    )
    TBLPROPERTIES (
    "xmlinput.start"="<record customer",
    "xmlinput.end"="</record>"
    );

lzo

相比textfile多了lzo压缩，占用空间更小；

ROW FORMAT DELIMITED
FIELDS TERMINATED BY ‘\t’
STORED AS INPUTFORMAT
‘com.hadoop.mapred.DeprecatedLzoTextInputFormat’
OUTPUTFORMAT
‘org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat’

orc

列式存储，占用空间最小，非常适合用来做数仓；

STORED AS ORC

压缩

STORED AS ORC TBLPROPERTIES (“orc.compression”=”ZLIB”)
STORED AS ORC TBLPROPERTIES (“orc.compression”=”SNAPPY”)

注意设置orc压缩格式前一定要先设置：

set hive.exec.orc.compression.strategy=COMPRESSION;

否则压缩不生效；

parquet

列式存储，占用空间居中，如果后期使用spark来处理，parquet是最佳格式；

STORED AS PARQUET

parquet+snappy

STORED AS PARQUET TBLPROPERTIES (“parquet.compression”=”SNAPPY”)

对比测试

测试表：test_table
测试行数：10亿
测试sql类型：aggregation
测试sql：select col_1, count(1) from test_table group by col_1;
测试结果

fs	hdfs						kudu
format	textfile	lzo	parquet	parquet snappy	orc	orc snappy
capacity	464.0 G	169.4 G	177.2 G	111.3 G	71.5 G	65.7G	184 G
	100%	36%	37%	23%	15%	14%	39%
Hive2.3.4	816 s	711 s	250 s	158 s	130 s	127 s
Hive2.3.4 Tuning			251 s	163 s	109 s	96 s
Hive2.3.4 On spark2.4.0			54 s	47 s	149 s	138 s
Spark2.1.1	371 s	293 s	17 s	16 s	51 s
Spark2.4.0	496 s	297 s	16 s	16 s	21 s	21 s
Drill1.15.0			59 s	57 s	75 s	45 s
Impala2.12				15 s			16 s
Presto0.215			25 s	21 s	13 s	12 s

从数据大小和查询效率上看，表现最好的是presto+orc+snappy；
hive下最佳格式为orc snappy，数据大小最小，并且查询最快；
hive切换engine为spark后，对parquet格式的查询有一些提升，但是占用相同资源的情况下，远不如直接使用spark sql快；
spark2.3以后对orc格式相比之前有很大优化，已经很接近parquet格式；
impala+parquet+hdfs的性能和impala+kudu差不多，kudu的好处是支持实时更新；
drill看起来没有必要；
spark2.4.0中的parquet为2.4，parquet从2.5开始支持column index，预计以后的spark版本对parquet的查询会更快；
impala对orc的支持从3.1开始作为实验功能的一部分；

详细数据

yarn 200g 50core

1 hive-2.3.4

set mapreduce.map.memory.mb=4096;
set mapreduce.map.java.opts=-Xmx3072m;
hive-textfile:
Time taken: 816.202 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 1831 Reduce: 1009 Cumulative CPU: 27614.77 sec HDFS Read: 498267775168 HDFS Write: 88861 SUCCESS
Total MapReduce CPU Time Spent: 0 days 7 hours 40 minutes 14 seconds 770 msec
hive-lzo:
Time taken: 711.266 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 183 Reduce: 711 Cumulative CPU: 13949.24 sec HDFS Read: 181881436157 HDFS Write: 62935 SUCCESS
Total MapReduce CPU Time Spent: 0 days 3 hours 52 minutes 29 seconds 240 msec
hive-orc:
Time taken: 130.194 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 275 Reduce: 300 Cumulative CPU: 4368.67 sec HDFS Read: 626004573 HDFS Write: 27178 SUCCESS
Total MapReduce CPU Time Spent: 0 days 1 hours 12 minutes 48 seconds 670 msec
hive-orc snappy:
Time taken: 127.803 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 191 Reduce: 276 Cumulative CPU: 4374.74 sec HDFS Read: 580889407 HDFS Write: 25090 SUCCESS
Total MapReduce CPU Time Spent: 0 days 1 hours 12 minutes 54 seconds 740 msec
hive-orc-tuning:
Time taken: 109.539 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 275 Reduce: 300 Cumulative CPU: 3051.67 sec HDFS Read: 627064673 HDFS Write: 40321 SUCCESS
Total MapReduce CPU Time Spent: 50 minutes 51 seconds 670 msec
hive-orc snappy-tuning:
Time taken: 94.135 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 191 Reduce: 276 Cumulative CPU: 2393.92 sec HDFS Read: 581727151 HDFS Write: 37201 SUCCESS
Total MapReduce CPU Time Spent: 39 minutes 53 seconds 920 msec
hive-parquet:
Time taken: 250.786 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 642 Reduce: 744 Cumulative CPU: 10919.85 sec HDFS Read: 873784253 HDFS Write: 65806 SUCCESS
Total MapReduce CPU Time Spent: 0 days 3 hours 1 minutes 59 seconds 850 msec
hive-parquet snappy:
Time taken: 158.009 seconds, Fetched: 32 row(s)
Stage-Stage-1: Map: 367 Reduce: 467 Cumulative CPU: 6246.0 sec HDFS Read: 721915438 HDFS Write: 41707 SUCCESS
Total MapReduce CPU Time Spent: 0 days 1 hours 44 minutes 6 seconds 0 msec

### 2 hive-2.3.4 on spark-2.4.0

set spark.driver.memory=4g;
set spark.executor.memory=4g;
set spark.executor.instances=10;
hive on spark-parquet:
Time taken: 54.446 seconds, Fetched: 32 row(s)
hive on spark-parquet snappy:
Time taken: 47.364 seconds, Fetched: 32 row(s)
hive on spark-orc:
Time taken: 149.901 seconds, Fetched: 32 row(s)
hive on spark-orc snappy:
Time taken: 138.844 seconds, Fetched: 32 row(s)

### 3 impala-2.12

MEM_LIMIT=20g * 3
impala-parquet snappy:
Fetched 32 row(s) in 15.10s
+--------------+--------+----------+----------+-------+------------+-----------+---------------+---------------------------------------------------+
| Operator | #Hosts | Avg Time | Max Time | #Rows | Est. #Rows | Peak Mem | Est. Peak Mem | Detail |
+--------------+--------+----------+----------+-------+------------+-----------+---------------+---------------------------------------------------+
| 04:EXCHANGE | 1 | 211.45us | 211.45us | 32 | 50 | 208.00 KB | 0 B | UNPARTITIONED |
| 03:AGGREGATE | 3 | 2.58ms | 2.91ms | 32 | 50 | 34.03 MB | 128.00 MB | FINALIZE |
| 02:EXCHANGE | 3 | 29.23us | 30.92us | 96 | 1.04B | 32.00 KB | 0 B | HASH(cpp_addr_province) |
| 01:AGGREGATE | 3 | 13.29s | 13.97s | 96 | 1.04B | 34.05 MB | 128.00 MB | STREAMING |
| 00:SCAN HDFS | 3 | 723.09ms | 760.01ms | 1.04B | 1.04B | 36.55 MB | 88.00 MB | temp.app_ba_userprofile_prop_nonpolar_view_ext_ps |
+--------------+--------+----------+----------+-------+------------+-----------+---------------+---------------------------------------------------+
impala-kudu:
Fetched 32 row(s) in 15.61s

### 4 drill-1.15

10g+10g+1g+1g * 3
drill-parquet:
32 rows selected (59.501 seconds)
drill-parquet snappy:
32 rows selected (57.653 seconds)
drill-orc:
32 rows selected (75.749 seconds)
drill-orc snappy:
32 rows selected (45.323 seconds)

5 spark-sql –master yarn –num-executors 10 –executor-memory 4g –driver-memory 4g

5.1 spark-2.1.1

spark sql-textfile:
Time taken: 371.77 seconds, Fetched 32 row(s)
spark sql-lzo:
Time taken: 293.391 seconds, Fetched 32 row(s)
spark sql-parquet:
Time taken: 17.338 seconds, Fetched 32 row(s)
spark sql-parquet snappy:
Time taken: 16.609 seconds, Fetched 32 row(s)
spark sql-orc:
Time taken: 51.959 seconds, Fetched 32 row(s)

5.2 spark-2.4.0

spark sql-textfile:
Time taken: 496.395 seconds, Fetched 32 row(s)
spark sql-lzo:
Time taken: 297.142 seconds, Fetched 32 row(s)
spark sql-parquet:
Time taken: 16.728 seconds, Fetched 32 row(s)
spark sql-parquet snappy:
Time taken: 16.879 seconds, Fetched 32 row(s)
spark sql-orc:
Time taken: 21.432 seconds, Fetched 32 row(s)
spark sql-orc snappy:
Time taken: 21.935 seconds, Fetched 32 row(s)

6 presto

presto-parquet:
Splits: 3,182 total, 3,182 done (100.00%)
0:25 [1.04B rows, 612MB] [42.2M rows/s, 24.9MB/s]
presto-parquet snappy:
Splits: 2,088 total, 2,088 done (100.00%)
0:21 [1.04B rows, 584MB] [49.3M rows/s, 27.8MB/s
presto-orc：
Splits: 1,532 total, 1,532 done (100.00%)
0:13 [1.04B rows, 850MB] [81.7M rows/s, 66.8MB/s]
presto-orc snappy：
Splits: 1,353 total, 1,353 done (100.00%)
0:12 [1.04B rows, 1.13GB] [87.5M rows/s, 97.4MB/s]

本文链接： cpeixin.cn/2019/02/10/%E5%88%9B%E5%BB%BAhive%E8%A1%A8%E6%97%B6%E6%A0%BC%E5%BC%8F%E5%A6%82%E4%BD%95%E9%80%89%E6%8B%A9/
版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！

Brent大数据工程师 & 机器学习

一心九用的工程师