京东一面:讲一下你所了解的Hive分区和分桶

Hive是基于Hadoop的一个数据仓库工具，用来进行数据提取、转化、加载，这是一种可以存储、查询和分析存储在Hadoop中的大规模数据的机制。Hive数据仓库工具能将结构化的数据文件映射为一张数据库表，并提供SQL查询功能，能将SQL语句转变成MapReduce任务来执行。

同时，Hive表中存在分区、分桶的概念，我们来先讲一个小故事：

幼儿园的孩子们准备去游乐场玩耍，老师把他们分成了几组让他们去不同的游乐场，用来分散游乐场的压力。来到游乐场后，老师发现有很多窗口可以购买门票，于是又根据孩子的身高分成几组，每一组去不同的窗口排队买票。

通过上述事件，我们总结一下分区的概念，分区是指按照数据表的某个或者某些列分为多个区，区从形式上可以理解为文件夹。分桶：分桶是相对分区进行更细粒度的划分。分桶将整个数据内容按照某列属性值的hash值进行区分，按照取模结果对数据分桶。如取模结果为0的数据记录存放到一个文件，取模为1的数据存放到一个文件，取模为2的数据存放到一个文件。那么，为什么要设置分区、分桶呢？

其实分区表，逻辑上分区表与未分区表没有区别，在物理上分区表会将数据按照分区键的列值存储在表目录的子目录中，目录名为“分区键=键值”。其中需要注意的是分区键的值不一定要基于表的某一列（字段），它可以指定任意值，只要查询的时候指定相应的分区键来查询即可。我们可以对分区进行添加、删除、重命名、清空等操作。因为分区在特定的区域（子目录）下检索数据，它作用同DNMS分区一样，都是为了减少扫描成本。

分桶则是指定分桶表的某一列，让该列数据按照哈希取模的方式随机、均匀地分发到各个桶文件中。因为分桶操作需要根据某一列具体数据来进行哈希取模操作，故指定的分桶列必须基于表中的某一列（字段）。因为分桶改变了数据的存储方式，它会把哈希取模相同或者在某一区间的数据行放在同一个桶文件中。如此一来便可提高查询效率，如：我们要对两张在同一列上进行了分桶操作的表进行JOIN操作的时候，只需要对保存相同列值的桶进行JOIN操作即可。同时分桶也能让取样（Sampling）更高效。

接下来我们来讲解一下二者创建，数据写入：

一\分区

分区又可以分为单值分区和范围分区。

1. 单值分区

单值分区根据插入时是否需要手动指定分区可以分为：

单值静态分区：导入数据时需要手动指定分区。

单值动态分区：导入数据时，系统可以动态判断目标分区。

静态分区

在创建表时直接在PARTITIONED BY后面加上分区键和类型即可。（分区键不能和任何列名重名）

CREATE [EXTERNAL] TABLE table_name(

col1 type [, col2 type, ...])

-- 指定分区键和数据类型

PARTITIONED BY (partition_key type, ...)

[CLUSTERED BY ...]

[ROW FORMAT row_format]

[STORED AS TEXTFILE|ORC|CSVFILE]

[LOCATION 'file_path'];

写入数据时可以覆盖插入或者追加插入：

-- 覆盖写入

INSERT OVERWRITE TABLE table_name

PARTITION (partition_key=partition_value[, partition_key=partition_value, ...])

SELECT select_statement;

-- 追加写入

INSERT INTO TABLE table_name

PARTITION (partition_key=partition_value[, partition_key=partition_value, ...])

SELECT select_statement;

动态分区

创建方式与静态分区表完全一样，一张表可同时被静态和动态分区键分区，只是动态分区键需要放在静态分区键的后面（因为HDFS上的动态分区目录下不能包含静态分区的子目录）。

-- dpk为动态分区键， spk为静态分区键

CREATE TABLE table_name

PARTITIONED BY ([spk type, ... ,] dpk data_type, [dpk

type,...]);

-- ...略

动态分区写入数据时只需要给出分区键名称。

-- 开启动态分区支持，并设置最大分区数

set hive.exec.dynamic.partition=true;

set hive.exec.max.dynamic.partitions=2000;

INSERT (OVERWRITE | INTO) TABLE table_name PARTITION ([spk=value, ..., ] dpk, [..., dpk])

SELECT select_statement;

2. 范围分区

单值分区每个分区对应于分区键的一个取值，而每个范围分区则对应分区键的一个区间，只要落在指定区间内的记录都被存储在对应的分区下。分区范围需要手动指定，分区的范围为前闭后开区间 [最小值, 最大值)。最后出现的分区可以使用 MAXVALUE 作为上限，MAXVALUE 代表该分区键的数据类型所允许的最大值。

CREATE [EXTERNAL] TABLE table_name(

col1 type, col2 type, ...)

PARTITIONED BY RANGE (partition_key data_type, ...)

(PARTITION [partition_name] VALUES LESS THAN (cutoff),

[PARTITION [partition_name] VALUES LESS THAN (cutoff),

...]

PARTITION [partition_name] VALUES LESS THAN (cutoff|MAXVALUE))

[ROW FORMAT row_format] [STORED AS TEXTFILE|ORC|CSVFILE]

[LOCATION 'file_path'];

示例：

DROP TABLE IF EXISTS test_demo;

CREATE TABLE test_demo (value INT)

PARTITIONED BY RANGE (id1 INT, id2 INT, id3 INT)(

-- id1在(--∞,5]之间，id2在(-∞,105]之间，id3在(-∞,205]之间

PARTITION p5_105_205 VALUES LESS THAN (5, 105, 205),

-- id1在(--∞,5]之间，id2在(-∞,105]之间，id3在(205,215]之间

PARTITION p5_105_215 VALUES LESS THAN (5, 105, 215),

PARTITION p5_115_max VALUES LESS THAN (5, 115, MAXVALUE),

PARTITION p10_115_205 VALUES LESS THAN (10, 115, 205),

PARTITION p10_115_215 VALUES LESS THAN (10, 115, 215),

PARTITION pall_max values less than (MAXVALUE, MAXVALUE, MAXVALUE));

二、分桶

对Hive(Inceptor)表分桶可以将表中记录按分桶键的哈希值分散进多个文件中，这些小文件称为桶。

1. 创建分桶表

CREATE [EXTERNAL] TABLE table_name(

col1 type [, col2 type ...])

[PARTITIONED BY ...]

CLUSTERED BY (...)

[SORTED BY (...)]

INTO num_buckets BUCKETS

[ROW FORMAT row_format]

[STORED AS TEXTFILE|ORC|CSVFILE]

[LOCATION 'file_path'];

分桶键只能有一个即col_name。表可以同时分区和分桶，当表分区时，每个分区下都会有num_buckets 个桶。我们也可以选择使用 SORTED BY ...在桶内排序，排序键和分桶键无需相同。ASC 为升序选项，DESC 为降序选项，默认排序方式是升序。num_buckets 指定分桶个数，也就是表目录下小文件的个数。

2. 写入数据

因为分桶表在创建的时候只会定义Schema，且写入数据的时候不会自动进行分桶、排序，需要人工先进行分桶、排序后再写入数据。确保目标表中的数据和它定义的分布一致。

SET mapred.reduce.tasks = num_buckets;

INSERT (INTO|OVERWRITE) TABLE bucketed_table

SELECT select_statement

DISTRIBUTE BY bucket_key, [bucket_key, ...]

[SORT BY sort_key [ASC|DESC], [sort_key [ASC|DESC], ...]];

如果分桶表创建时定义了排序键，那么数据不仅要分桶，还要排序。

如果分桶键和排序键不同，且按降序排列，使用Distribute by ... Sort by分桶排序。

如果分桶键和排序键相同，且按升序排列（默认），使用 Cluster by 分桶排序，即如下：

SET mapred.reduce.tasks = num_buckets;

INSERT (INTO|OVERWRITE) TABLE bucketed_table

SELECT select_statement

CLUSTER BY bucket_sort_key, [bucket_sort_key, ...];

三、总结

本文对分区表和分桶表进行了介绍，二者其实都是对Hive细化数据管理，加快数据查询和分析。不同点在于：

分区字段不是实际的列，分桶字段必须是实际的列。

分区表的分区数量可以一直增长，而分桶表创建好之后桶的数量就固定不变了。

对于分区表和分桶表的选择，一般数据量非常大的情况下建议使用分桶表，其余情况使用分区表即可。