Hive分桶表

设计原因分桶原理创建分桶表载入数据按id升序排序建表分桶分桶作用提高join 查询效率提高抽样效率抽样语法

设计原因

       Hive中分区表提供了一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可以形成合理的分区。不合理的数据分区划分方式可能导致有的分区数据过多，而某些分区没有什么数据。分桶是将数据集分解为更容易管理的若干部分的另一种技术。

分桶原理

       对分桶字段值进行哈希，哈希值除以桶的个数求余，余数决定了该条记录在哪个桶中，也就是余数相同的在一个桶中。

创建分桶表

        通过clustered by （字段名）into bucket_num buckets 分桶，意思是根据字段名分成bucket_num 个桶

create table bucket_study ( id int, name string ) clustered by(id) into 4 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

载入数据

       分桶的实质就是对分桶的字段做hash 然后存放到对应文件中，所以说如果原有数据没有按key进行hash，需要在插入分桶的时候做hash,也就是说向分桶中插入数据的时候必然要执行一次MAPREDUCE,所有分桶表的数据基本只能通过从结果集查询插入的方式进行导入。 像下面的方式不会达到分桶的效果，这样在hdfs 上只能有一个文件。

load data inpath '/user/hive/warehouse/pokes/buckt_data.txt' into table bucket_study;

需要借助中间表:

第一步：从hdfs 或本地磁盘中load 数据，导入中间表 create table bucket_temp( id int, name string ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ; load data local inpath '/opt/software/hive/hive-1.1.0-cdh5.14.0/log/buckt_data.txt' into table bucket_temp; 设置需要确保reduce 的数量与表中的bucket 数量一致 用设置参数方法 set hive.enforce.bucketing = true;

手动指定reduce 数量

set mapreduce.reduce.tasks = num; (并在SELECT 后增加CLUSTER BY 语句) 通过从中间表查询的方式完成数据导入，这样会产生四个文件 insert into bucket_study select * from bucket_temp;

4.结果 每个文件中存放的数据，可以看到除以4余数相同的在一个桶中 查询结果的顺序和文件存放的顺序是一致的

按id升序排序建表分桶

建表

create table bucket_sorted ( id int, name string ) clustered by(id) sorted by (id) into 4 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

插入数据

insert into bucket_sorted select * from bucket_temp;

用sql看和用hadoop命令看每个文件，结果每个桶内都是按id升序排序的.

分桶作用

提高join 查询效率

        获得更高的查询处理效率。桶为表加上了额外的结构，Hive 在处理有些查询时能利用这个结构。具体而言，连接两个在(包含连接列的) 相同列上划分了桶的表，可以使用Map端连接(Map-side join) 高效的实现，比如JOIN 操作，可以大大减少JOIN的数据量。 假设表A和表B进行join, join 的字段为: 条件:

两个表为大表两个表为分桶表A表的桶数是B表桶数的倍数或因子 这样join 查询的时候，表A的每个桶就可以和表B对应的桶直接join,而不用全表join,提高查询效率，比如A表桶数为4,B表桶数为8，那么桶对应关系为: 表A表B0011223304152637

提高抽样效率

        使取样(sampling)更高效。在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。

抽样语法

select * from tabname tablesample(bucket x out of y on id);

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。 y 表示抽样分成 num/y (num 表示桶的个数)份数，x 表示从 x 开始每隔y抽取 num/y 份，x的范围是 [1,y]

x=1,y=2，取2(4/y)个bucket的数据，分别桶1(x)和桶1+2 (x+y) select * from bucket_study tablesample (bucket 1 out of 2);

x=1,y=4, 取1(4/y)个bucket的数据，即桶1 select * from bucket_study tablesample (bucket 1 out of 4);

x=2,y=8, 取1/2(4/y)个bucket的数据，即桶x的一半，在第二个桶中取一个桶一半的数据 select * from bucket_study tablesample (bucket 2 out of 8);

参考: https://www.jianshu.com/p/922e2e37ae22 https://blog.csdn.net/u010003835/article/details/80911215