Hive表优化、表设计优化、Hive表数据优化(ORC)、数据压缩、存储优化_hive orc存储配置

Hive表优化

• 当执行查询计划时，Hive会使用表的最后一级目录作为底层处理数据的输入，即全表扫描。
  1. Step1：先根据表名在元数据中进行查询表对应的HDFS目录
  2. Step2：然后将整个HDFS中表的目录作为底层查询的输入，可以通过explain命令查看执行计划依赖的数据

Hive表设计优化

分区表结构 - 分区设计思想

• 设计思想：根据查询需求，将数据按照查询的条件(一般以时间)进行分区存储，将不同分区的数据单独使用一个HDFS目录存储。
• 分区表的构建和使用可见本专栏对应的文章。

分桶表结构 - Join问题

• 分桶表是按一定规则将数据划分到不同的文件中存储(分区表是分到不同目录)
• 如果有两张表按照相同的划分规则(按照Join的关联字段)将各自的数据进行划分，Join时就可以实现桶于桶之间的Join，避免不必要的比较。
• 分桶表的构建和操作相关SQL可见本专栏对应的文章。

Hive中的索引

• Hive的索引与关系型数据库中的索引并不相同，Hive不支持主键或外键索引。
• 索引功能从Hive0.7开始，到Hive3.0后不再支持。
• 建立索引时，Hive中会自动创建一张索引表，该表记录了该字段每个值与数据实际物理位置之间的关系。
• 当Hive中原始数据表的数据更新时，索引表不会自动更新
• 必须手动执行ALTER INDEX命令实现更新索引表，整体性能较差。
• 在实际应用中推荐使用ORC文件格式中的索引、物化视图来代替Hive Index提高查询性能。
• 语法：

1. 创建索引

CREATE INDEX idx_user_id_login ON TABLE tb_login_part(userid) AS 'COMPACT' WITH deferred REBUILD;
1

2. 构建索引：通过运行一个MapReduce程序来构建索引

ALTER INDEX idx_user_id_login ON tb_login_part REBUILD;
1

Hive表数据优化

• 为了提高对HDFS文件读写的性能，Hive提供了多种文件存储格式：TextFile、SequenceFile、ORC、Parquet等；
• 不同的文件存储格式具有不同的存储特点，有的可以降低存储空间，有的可以提高查询性能。
• Hive的文件格式在建表时指定，默认是TextFile，对应语法为：

STORED AS file_format
1

常见文件格式

TextFile

• TextFile是Hive中默认的文件格式，存储形式为按行存储。
• 优点：最简单的数据格式，可以直接查看，可以用任意分隔符进行分割，便于共享数据，可以搭配压缩一起使用。
• 缺点：耗费存储空间，I/O性能较低；结合压缩时Hive不进行数据切分合并，不能进行并行操作，查询效率低；按行存储，读取列的性能差。
• 适合小量数据的存储查询，一般用作第一层数据加载和测试使用。

SequenceFile

• 是Hadoop里用来存储序列化的键值对即二进制的一种文件格式。
• 也可以作为MapRrduce作业的输入和输出，Hive也支持这种格式。
• 加载数据时不能直接用load，因为load不经过MR程序，就实现不了SequenceFile。
• SequenceFile使用方法：

1. 创建表

CREATE TABLE tb_sogou_seq(
	stime STRING,
	userid STRING
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
 STORED AS SEQUENCEFILE;
1
2
3
4
5

2. 导入数据（不能直接load，需要通过INSERT调用MR程序实现压缩）

INSERT INTO TABLE tb_sogou_seq
SELECT * FROM tb_sogou_source;
1
2

Parquet

• 是一种支持嵌套结构的列式存储文件格式。
• 使用方法：（和SequenceFile一样不能直接load）

CREATE TABLE tb_sogou_parquet(
	stime STRING,
	userid STRING
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
 STORED AS PARQUET;

INSERT INTO TABLE tb_sogou_parquet
SELECT * FROM tb_sogou_source;
1
2
3
4
5
6
7
8

ORC

• 是一种列式存储格式
• 优点：
  1. 列式存储效率非常高
  2. 可压缩，高效的列存取
  3. 查询效率较高，支持索引
  4. 支持矢量化查询
• 缺点：
  1. 加载时性能消耗较大
  2. 需要通过textFile文件转化生成
  3. 读取全量数据时性能较差
• 适合Hive中大型的存储、查询。
• ORC不是一个单纯的列式存储格式，仍然是首先根据行组分割整个表，在每一个行组内进行按列存储。
• ORC文件是自描述的，它的元数据使用Protocol Buffers序列化，并且文件中的数据尽可能地压缩以降低存储空间的消耗。
• 用法和SequenceFile相同：

CREATE TABLE tb_sogou_parquet(
	stime STRING,
	userid STRING
)ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
 STORED AS ORC;

INSERT INTO TABLE tb_sogou_orc
SELECT * FROM tb_sogou_source;
1
2
3
4
5
6
7
8

数据压缩

• Hive的数据压缩实际上是MapReduce的压缩
• Hive的压缩就是使用了Hadoop中的压缩实现的，Hadoop中支持的压缩在Hive中都可以直接使用。
• Hive中压缩配置：

1. 开启Hive中间传输数据压缩功能

-- 1) 开启hive中间传输数据压缩功能：
SET hive.exec.compress.intermediate=true;
-- 2) 开启mapreduce中map输出压缩功能
SET mapreduce.map.output.compress=true;
-- 3) 设置mapreduce中map输出数据的压缩方式
SET mapreduce.map.output.compress.codec=org.apache.io.compress.SnappyCodec;
1
2
3
4
5
6

2. 开启Reduce输出阶段压缩

-- 1) 开启hive最终输出数据压缩功能
SET hive.exec.compress.output=true;
-- 2) 开启mapreduce最终输出数据压缩
SET mapreduce.output.fileoutputformat.compress=true;
-- 3) 设置mapreduce最终数据输出压缩方式
SET mapreduce.output.fileoutputformat.compress.codec = org.apache.hadoop.io.compress.SnappyCodec;
-- 4) 设置mapreduce最终数据输出压缩为块压缩
SET mapreduce.output.fileoutputformat.compress.type=BLOCK;
1
2
3
4
5
6
7
8

• 配合文件存储格式使用：

CREATE TABLE tb_sogou_orc_snappy
STORED AS ORC tblproperties ("orc.compress"="SNAPPY")
AS SELECT * FROM tb_sogou_source;
1
2
3

存储优化 - 避免小文件生成

• Hive的存储本质还是HDFS，由于每个小文件在HDFS中都会产生一条元数据信息，HDFS不利于小文件存储，且不利于MR程序的处理。
• MapReduce中每个小文件会启动一个MapTask计算处理，导致资源的浪费。
• Hive可以自动判断是否是小文件，如果是，则自动合并小文件。
• 参数设置：

-- 如果hive程序只有maptask，将MapTask产生的所有小文件进行合并
SET hive.merge.mapfiles=true;
-- 如果hive程序有Map和ReduceTask，将ReduceTask产生的所以小文件进行合并
SET hive.merge.mapredfiles=true;
-- 每一个合并的文件的大小
SET hive.merge.size.per.task=256000000;
-- 平均每个文件的大小，如果小于这个值就会进行合并
SET hive.merge.smallfiles.avgsize=16000000;
1
2
3
4
5
6
7
8

存储优化 - 合并输入的小文件

• 数据处理时输入小文件的情况下，Hive中提供一种输入类CombineHiveInputFormat，用于将小文件合并以后，再进行处理。
• 参数设置：

-- 设置Hive中底层MapReduce读取数据的输入类：将所有文件合并为一个大文件作为输入
SET hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
1
2

存储优化 - ORC文件索引

• 使用ORC文件时，为了加快读取ORC文件中的数据内容，ORC提供了两种索引机制：
  1. Row Group Index
  2. Bloom Filter Index
• 当数据写入数据时，可以指定构建索引，当用户查询数据时，可以根据索引提前对数据进行过滤，避免不必要的数据扫描。

Row Group Index

• 一个ORC文件包含一个或多个stripes(groups of row data即行组)，每个stripe中包含了每个column的min/max值得索引数据；
• 当查询中有大于等于小于得操作时，会根据min/max值，跳过不包含在内得stripes。
• 建立ORC表时，指定参数’orc.create.index’='true’之后，便会建立Row Group Index；
• 向表中加载数据时，对需要使用索引的字段进行排序可以提高使用效率。
• 例：

-- 1. 开启索引配置
SET hive.optimize.index.filter=true;
-- 2. 创建表并设置构建索引
CREATE TABLE tb_sogou_orc_index
	STORED AS ORC TBLPROPERTIES("orc.create.index"="true")
AS SELECT * FROM tb_sogou_source
	DISTRIBUTE BY stime
	SORT BY stime;
-- 3. 当进行范围或者等值查询(<,>,=)时就可以基于构建的索引进行查询
SELECT COUNT(*) FROM tb_sogou_orc_index WHERE stime > '12:00:00' AND stime < '18:00:00';
1
2
3
4
5
6
7
8
9
10

Bloom Filter Index

• 建表时通过表参数"orc.bloom.filter.columns"="columnName…"来指定为哪些字段建立BloomFilter索引，在生成数据时，会在每个stripe中为该字段建立BloomFilter的数据结构；
• 当查询条件中包含对该字段的等值过滤时，先从BloomFilter中获取以下stripe是否包含该值，如果不包含，则跳过该stripe。
• 例：

-- 创建表并设置构建布隆索引
CREATE TABLE tb_sogou_orc_index
	STORED AS ORC TBLPROPERTIES
("orc.create.index"="true","orc.bloom.filter.columns"="stime,userid")
AS SELECT * FROM tb_sogou_source
	DISTRIBUTE BY stime
	SORT BY stime;
-- 当stime进行范围查询(<,>)时可以走row group index，userid等值查询时走bloom filter index
SELECT COUNT(*) FROM tb_sogou_orc_index WHERE stime > '12:00:00' AND stime < '18:00:00' AND userid = '123345';
1
2
3
4
5
6
7
8
9

存储优化 - ORC矢量化查询

• Hive的默认查询执行引擎一次处理一行，而矢量化查询执行是一种Hive’针对ORC文件操作的特性，目的是按照每批1024行读取数据，并且一次性对整个记录集整合应用操作。
• 要使用矢量化查询，必须以ORC格式存储数据。
• 矢量化查询设置;

SET hive.vectorized.execution.enabled = ture;
SET hive.vectorized.execution.reduce.enabled = true;
1
2