大数据核心原理与实践专栏

为何分区分桶

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

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

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

分区

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

深入理解 Hive 分区分桶

单值分区

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

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

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

3、动态分区创建

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





CREATE TABLE <table_name>



 PARTITIONED BY ([<spk> <data_type>, ... ,] <dpk> <data_type>, [<dpk>



<data_type>,...]);



-- ...略

4、动态分区写入

静态分区键要用 <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>;

范围分区

单值分区每个分区对应于分区键的一个取值，而每个范围分区则对应分区键的一个区间，只要落在指定区间内的记录都被存储在对应的分区下。分区范围需要手动指定，分区的范围为前闭后开区间 [最小值, 最大值)。最后出现的分区可以使用 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)



);

分桶

说完分区，我们来继续搞分桶。对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表压缩后的数据）。通常做法是先分区后分桶。

转载：https://blog.csdn.net/whdxjbw/article/details/82219022

总结：
1.Hive支持索引，但是Hive的索引与关系型数据库中(主键一定包含唯一性索引)的索引并不相同，比如，Hive不支持主键或者外键。Hive的索引目的是提高Hive表指定列的查询速度。没有索引时，类似’WHERE tab1.col1 = 10’ 的查询，Hive会加载整张表或分区，然后处理所有的rows，但是如果在字段col1上面存在索引时，那么只会加载和处理文件的一部分。与其他传统数据库一样，增加索引在提升查询速度时，会消耗额外资源去创建索引表和需要更多的磁盘空间存储索引。
2.索引和分区最大的区别就是索引不分割数据库，分区分割数据库。索引其实就是拿额外的存储空间换查询时间，但分区已经将整个大数据库按照分区列拆分成多个小数据库了。
3.分区和分桶最大的区别就是分桶随机分割数据库，分区是非随机分割数据库。因为分桶是按照列的哈希函数进行分割的，相对比较平均；而分区是按照列的值来进行分割的，容易造成数据倾斜。其次两者的另一个区别就是分桶是对应不同的文件（细粒度），分区是对应不同的文件夹（粗粒度）。
注意：普通表（外部表、内部表）、分区表这三个都是对应HDFS上的目录，桶表对应是目录里的文件

Hive的分区使用HDFS的子目录功能实现。每一个子目录包含了分区对应的列名和每一列的值。但是由于HDFS并不支持大量的子目录，这也给分区的使用带来了限制。我们有必要对表中的分区数量进行预估，从而避免因为分区数量过大带来一系列问题。

桶是通过对指定列进行哈希计算来实现的，通过哈希值将一个列名下的数据切分为一组桶，并使每个桶对应于该列名下的一个存储文件。