数仓工具—Hive进阶之表设计最佳实践(21)

Hive表设计最佳实践

如果你计划着将你的数据库或者数据仓库迁移到Hadoop上，有一些关键的表设计决策将严重影响整个Hive的查询性能，下面我们就看一下hive 表的最佳设计实践

表设计在整个hive 查询性能中扮演着重要的角色。合理的表设计可以减少对存储的占用、增加查询的性能，以及减少内存的使用，主要有下面几点我们要重点关注

1. 选择合适的存储格式
2. 选择合适的压缩
3. 分区表
4. 分桶表

选择合适的存储格式

Hive 使用HDFS作为数据的存储，最终表的数据是以HDFS 文件的格式存储在HDFS上的，所以应该选择合适的存储格式从而加速查询性能和提升数据检索的速度。

尽量避免使用TEXT格式、Sequence 格式，以及复杂数据结构格式 例如JSON。理想情况下ORC、Parquet 是比较适合的，如果我们是通过Hive 建设数仓，那其实Parquet 是比较好的,我们建表的时候通过STORED AS 指定存储格式即可，

CREATE TABLE IF NOT EXISTS parquet_table (
col1 int, 
col2 string ) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS PARQUET;
1
2
3
4
5
6

更多可以参考前面的文章数仓工具—Hive进阶之数据存储格式

数据压缩

尽管压缩和解压缩会消耗大量CPU功率，但它不会影响map reduce作业，因为MR作业往往受I/O限制，因此无论何时何地，建议尽可能使用压缩。

尝试使用Hadoop 和 Hive提供的支持分隔 的压缩技术，避免使用Gzip 压缩，因为不支持分隔，我们可以在建表的时候，通过TBLPROPERTIES 来指定压缩算法。

CREATE TABLE IF NOT EXISTS test_table (
col1 int, 
col2 string ) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS PARQUET
TBLPROPERTIES ("parquet.compress"="SNAPPY") ";
1
2
3
4
5
6
7

Also ensure your final and intermediate Hive output is always compressed. You have to set following variables in Hive:

为了保证最终的结果和中间临时文件也是压缩的，我们可以在hive中设置下面的变量。

set hive.exec.compress.output=true;
set hive.exec.compress.intermediate=true;
1
2

对大表进行分区

分区可以显著减少表的扫描时间，所以如果我们想要提升查询的性能，那就应该对大表进行分区，hive的分区会把数据存储在一些列的子文件夹中，例如year/month/day，所以当我们查询的时候尽量不要去扫描整个表，而是应该去扫描对于的自文件夹从而获得所需的数据。

下面是一个分区表的例子

CREATE TABLE IF NOT EXISTS test_table (
col1 int, 
col2 string ) 
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS PARQUET
PARTITIONED BY ( col3 STRING) ;
1
2
3
4
5
6
7

关于分区可以参考

1. 数仓工具—Hive的数据组织管理方式
2. 数仓工具—Hive动态分区

使用分桶

hive 分桶其实就是将分区的数据划分到几个文件中去，而这几个文件就是我们的桶，和普通文件不一样的是，它的划分是按照我们设定的规则来的，而不是Hive 默认的文件生成方式。

具体的划分方式就是根据用户指定的字段的哈希值取桶个数的余数进行划分，分桶往往适用于我们分区之后，分区里面还是包涵大量的数据，那这个时候我们可以使用分桶继续划分

CREATE TABLE bucketed_table ( 
Col1 integer, 
col2 string, 
col3 strng, 
col4 date, ... ) 
PARTITIONED BY (col4 date) 
CLUSTERED BY (col1) INTO 32 BUCKETS 
STORED AS PARQUET;
1
2
3
4
5
6
7
8

关于分桶可以参考数仓工具—Hive的数据组织管理方式

总结

这一节我们总结了一些表设计相关的最佳实践，当然我们针对具体的业务场景的时候也可以有其他的一些建表实践，但我们这里主要关注通用场景，主要有

1. 选择合适的存储格式
2. 选择合适的压缩
3. 分区表
4. 分桶表