技本功|Hive优化之建表配置参数调优（一）

2022-03-01 05:18:50

1、创建一个普通的表

create table test_user1(id int, name string,code string,code_id string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ','；

2、查看表信息

DESCRIBE FORMATTED test_user1;

我们从该表的描述信息介绍建表时的一些可优化点。
2.1表的文件数

numFiles表示表中含有的文件数，当文件数过多时可能意味着该表的小文件过多，这时候我们可以针对小文件的问题进行一些优化，HDFS本身提供了

解决方案：

1.Hadoop Archive/HAR：将小文件打包成大文件。

2.SEQUENCEFILE格式：将大量小文件压缩成一个SEQUENCEFILE文件。

3.CombineFileInputFormat：在map和reduce处理之前组合小文件。

4.HDFS Federation：HDFS联盟，使用多个namenode节点管理文件。

除此之外，我们还可以通过设置hive的参数来合并小文件。

1.输入阶段合并

需要更改Hive的输入文件格式即参hive.input.format
默认值是org.apache.hadoop.hive.ql.io.HiveInputFormat
我们改成org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
这样比起上面对mapper数的调整，会多出两个参数，分别是mapred.min.split.size.per.node和mapred.min.split.size.per.rack，含义是单节点和单机架上的最小split大小。如果发现有split大小小于这两个值（默认都是100MB），则会进行合并。具体逻辑可以参看Hive源码中的对应类。

2.输出阶段合并

直接将hive.merge.mapfiles和hive.merge.mapredfiles都设为true即可，前者表示将map-only任务的输出合并，后者表示将map-reduce任务的输出合并，Hive会额外启动一个mr作业将输出的小文件合并成大文件。

另外，hive.merge.size.per.task可以指定每个task输出后合并文件大小的期望值，hive.merge.size.smallfiles.avgsize可以指定所有输出文件大小的均值阈值，默认值都是1GB。如果平均大小不足的话，就会另外启动一个任务来进行合并。

2.2表的存储格式

通过InputFormat和OutputFormat可以看出表的存储格式是TEXT类型，Hive支持TEXTFILE, SEQUENCEFILE, AVRO, RCFILE, ORC,以及PARQUET文件格式，可以通过两种方式指定表的文件格式：

1.CREATE TABLE … STORE AS :在建表时指定文件格式，默认是TEXTFILE
2.ALTER TABLE … [PARTITION partition_spec] SET FILEFORMAT :修改具体表的文件格式。

如果要改变创建表的默认文件格式，可以使用set hive.default.fileformat=进行配置，适用于所有表。

同时也可以使用set hive.default.fileformat.managed =进行配置，仅适用于内部表或外部表。

扩展：不同存储方式的情况

TEXT, SEQUENCE和 AVRO文件是面向行的文件存储格式，不是最佳的文件格式，因为即便只查询一列数据，使用这些存储格式的表也需要读取完整的一行数据。另一方面，面向列的存储格式(RCFILE, ORC, PARQUET)可以很好地解决上面的问题。

关于每种文件格式的说明，如下:

1.TEXTFILE

创建表时的默认文件格式，数据被存储成文本格式。文本文件可以被分割和并行处理，也可以使用压缩，比如GZip、LZO或者Snappy。然而大部分的压缩文件不支持分割和并行处理，会造成一个作业只有一个mapper去处理数据，使用压缩的文本文件要确保文件不要过大，一般接近两个HDFS块的大小。

2.SEQUENCEFILE

key/value对的二进制存储格式，sequence文件的优势是比文本格式更好压缩，sequence文件可以被压缩成块级别的记录，块级别的压缩是一个很好的压缩比例。如果使用块压缩，需要使用下面的配置：

set hive.exec.compress.output=true;
set io.seqfile.compression.type=BLOCK;
1
2
3.AVRO

二进制格式文件，除此之外，avro也是一个序列化和反序列化的框架。avro提供了具体的数据schema。

4.RCFILE

全称是Record Columnar File，首先将表分为几个行组，对每个行组内的数据进行按列存储，每一列的数据都是分开存储，即先水平划分，再垂直划分。

5.ORC

全称是Optimized Row Columnar，从hive0.11版本开始支持，ORC格式是RCFILE格式的一种优化的格式，提供了更大的默认块(256M)

6.PARQUET

另外一种列式存储的文件格式，与ORC非常类似，与ORC相比，Parquet格式支持的生态更广，比如低版本的impala不支持ORC格式。

配置同样数据同样字段的两张表，以常见的TEXT行存储和ORC列存储两种存储方式为例，对比执行速度。

TEXT存储方式

ORC存储方式

总结：

从上图中可以看出列存储在对指定列进行查询时，速度更快，建议在建表时设置列存储的存储方式。

2.3 表的压缩

对Hive表进行压缩是常见的优化手段，一些存储方式自带压缩选择，比如SEQUENCEFILE支持三种压缩选择：NONE，RECORD，BLOCK。Record压缩率低，一般建议使用BLOCK压缩。

ORC支持三种压缩选择：NONE，ZLIB，SNAPPY。我们以TEXT存储方式和ORC存储方式为例，查看表的压缩情况。

配置同样数据同样字段的四张表，一张TEXT存储方式，另外三张分别是默认压缩方式的ORC存储、SNAPPY压缩方式的ORC存储和NONE压缩方式的ORC存储，查看在hdfs上的存储情况：

TEXT存储方式

默认压缩ORC存储方式

SNAPPY压缩的ORC存储方式

NONE压缩的ORC存储方式

总结：

可以看到ORC存储方式将数据存放为两个block，默认压缩大小加起来134.69M，SNAPPY压缩大小加起来196.67M，NONE压缩大小加起来247.55M。

TEXT存储方式的文件大小为366.58M，且默认block两种存储方式分别为256M和128M。

ORC默认的压缩方式比SNAPPY压缩得到的文件还小，原因是ORZ默认的ZLIB压缩方式采用的是deflate压缩算法，比Snappy压缩算法得到的压缩比高，压缩的文件更小。

ORC不同压缩方式之间的执行速度，经过多次测试发现三种压缩方式的执行速度差不多，所以建议采用ORC默认的存储方式进行存储数据。

2.4分桶分区

Num Buckets表示桶的数量，我们可以通过分桶和分区操作对Hive表进行优化。

对于一张较大的表，可以将它设计成分区表，如果不设置成分区表，数据是全盘扫描的，设置成分区表后，查询时只在指定的分区中进行数据扫描，提升查询效率。要注意尽量避免多级分区，一般二级分区足够使用。常见的分区字段：

1.日期或者时间，比如year、month、day或者hour，当表中存在时间或者日期字段时，可以使用些字段。

2.地理位置，比如国家、省份、城市等。

3.业务逻辑，比如部门、销售区域、客户等等。

与分区表类似，分桶表的组织方式是将HDFS上的一张大表文件分割成多个文件。

分桶是相对分区进行更细粒度的划分，分桶将整个数据内容按照分桶字段属性值得hash值进行区分，分桶可以加快数据采样，也可以提升join的性能(join的字段是分桶字段)，因为分桶可以确保某个key对应的数据在一个特定的桶内(文件)，所以巧妙地选择分桶字段可以大幅度提升join的性能。

通常情况下，分桶字段可以选择经常用在过滤操作或者join操作的字段。

创建分桶表

create table test_user_bucket(idint,namestring,codestring,code_idstring) clustered by(id) into 3 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY',';

查看描述信息