Hive分区和分桶

最新推荐文章于 2024-09-21 11:01:28 发布

犀牛_2046

最新推荐文章于 2024-09-21 11:01:28 发布

阅读量432

点赞数

分类专栏： # Hive 文章标签： Hive分区和分桶

原文链接：https://blog.csdn.net/whdxjbw/article/details/82219022#commentBox

版权

Hive 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

1.分区和分桶

1.1 分区和分桶优点

我们知道传统的DBMS系统一般都具有表分区的功能，通过表分区能够在特定的区域检索数据，减少扫描成本，在一定程度上提高查询效率，当然我们还可以通过进一步在分区上建立索引进一步提升查询效率。在此就不赘述了。

1.2 分区概念

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

1.3 分桶概念

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

2.分区

Hive(Inceptor)分区又分为单值分区、范围分区。单值分区又分为静态分区和动态分区。我们先看下分区长啥样。如下，假如有一张表名为persionrank表，记录每个人的评级，有id、name、score字段。我们便可以创建分区rank（注意rank不是表中的列，我们可以把它当做虚拟列），并将相应数据导入指定分区（将数据插入指定目录）。

2.1 单值分区

单值分区根据插入时是否需要手动指定分区可以分为：单值静态分区：导入数据时需要手动指定分区。单值动态分区：导入数据时，系统可以动态判断目标分区。

单值分区表的建表方式有两种：直接定义列和 CREATE TABLE LIKE。注意，单值分区表不能用 CREATE
TABLE AS SELECT 建表。而范围分区表只能通过直接定义列来建表。

2.1.1 单值静态分区

1 静态分区创建

直接在 PARTITIONED BY 后面跟上分区键、类型即可。（分区键不能和任何列重名）

CREATE [EXTERNAL] TABLE <table_name>
    (<col_name> <data_type> [, <col_name> <data_type> ...])
    -- 指定分区键和数据类型
    PARTITIONED BY  (<partition_key> <data_type>, ...) 
    [CLUSTERED BY ...] 
    [ROW FORMAT <row_format>] 
    [STORED AS TEXTFILE|ORC|CSVFILE]
    [LOCATION '<file_path>']    
    [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

2 静态分区写入

-- 覆盖写入
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>;

2.1.2 单值动态分区

1 动态分区创建

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

CREATE TABLE <table_name>
 PARTITIONED BY ([<spk> <data_type>, ... ,] <dpk> <data_type>, [<dpk>
<data_type>,...]);
-- ...略

2 动态分区写入

静态分区键要用 <spk>=<value> 指定分区值；动态分区只需要给出分出分区键名称 <dpk>。

-- 开启动态分区支持，并设置最大分区数
set hive.exec.dynamic.partition=true;
set hive.exec.max.dynamic.partitions=2000;
 
-- <dpk>为动态分区键， <spk>为静态分区键
INSERT (OVERWRITE | INTO) TABLE <table_name>
    PARTITION ([<spk>=<value>, ..., ] <dpk>, [..., <dpk>]) 
    SELECT <select_statement>;

2.2 范围分区

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

CREATE [EXTERNAL] TABLE <table_name>
    (<col_name> <data_type>, <col_name> <data_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>']    
    [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

eg：多个范围分区键的情况：

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)
);

3.分桶

说完分区，我们来继续搞分桶。对Hive(Inceptor)表分桶可以将表中记录按分桶键的哈希值分散进多个文件中，这些小文件称为桶。

创建分桶表

我们先看一下创建分桶表的创建，分桶表的建表有三种方式：直接建表，CREATE TABLE LIKE 和 CREATE TABLE AS SELECT ，单值分区表不能用 CREATETABLE AS SELECT 建表。这里以直接建表为例：

CREATE [EXTERNAL] TABLE <table_name>
    (<col_name> <data_type> [, <col_name> <data_type> ...])]
    [PARTITIONED BY ...] 
    CLUSTERED BY (<col_name>) 
        [SORTED BY (<col_name> [ASC|DESC] [, <col_name> [ASC|DESC]...])] 
        INTO <num_buckets> BUCKETS  
    [ROW FORMAT <row_format>] 
    [STORED AS TEXTFILE|ORC|CSVFILE]
    [LOCATION '<file_path>']    
    [TBLPROPERTIES ('<property_name>'='<property_value>', ...)];

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

向分桶表写入数据

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

目前有两种方式往分桶表中插入数据：

方法一：打开enforce bucketing开关。

SET hive.enforce.bucketing=true; ①
INSERT (INTO|OVERWRITE) TABLE <bucketed_table> SELECT <select_statement>
[SORT BY <sort_key> [ASC|DESC], [<sort_key> [ASC|DESC], ...]]; ②

方法二：将reducer个数设置为目标表的桶数，并在 SELECT 语句中用 DISTRIBUTE BY <bucket_key>对查询结果按目标表的分桶键分进reducer中。

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(Inceptor)中，ORC事务表必须进行分桶（为了提高效率）。每个桶的文件大小应在100~200MB之间（ORC表压缩后的数据）。通常做法是先分区后分桶。