hive分桶表的学习

前言：

每一个表或者分区，hive都可以进一步组织成桶，桶是更细粒度的数据划分，他本质不会改变表或分区的目录组织方式，他会改变数据在文件中的分布方式。 分桶规则：对分桶字段值进行哈希，哈希值除以桶的个数求余，余数决定了该条记录在哪个桶中，也就是余数相同的在一个桶中。桶为表加上额外结构，链接相同列划分了桶的表，可以使用map-side join更加高效。 优点：1、提高join查询效率 2、提高抽样效率

1、建表

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

create table test_bucket ( id int comment 'ID', name string comment '名字' ) comment '测试分桶' clustered by(id) into 4 buckets ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

2、插入数据

2.1 数据

buckt_data.txt

1,name1 2,name2 3,name3 4,name4 5,name5 6,name6 7,name7 8,name8 9,name9

2.2 load data

直接load data不会有分桶的效果，这样和不分桶一样，在HDFS上只有一个文件。

load data local inpath '/opt/test/buckt_data.txt' into table test_bucket;

需要借助中间表

create table text_bucket_test ( id int comment 'ID', name string comment '名字' ) comment '测试分桶中间表' ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' ;

先将数据load到中间表

load data local inpath '/opt/test/buckt_data.txt' into table text_bucket_test;

然后通过下面的语句，将中间表的数据插入到分桶表中，这样会产生四个文件。

insert into test_bucket select * from text_bucket_test;

然后我们查看分桶表的数据目录，发现好像也只有一个文件，并没有按之前的4个文件，也就是4个桶这样来划分。

分桶也就是分区，分区数量等于文件数，所以上面方法并没有分桶。

所以需要开启强制分桶：

set hive.enforce.bucketing = true; 开启强制分桶

重新导入数据：

insert into test_bucket select * from text_bucket_test;

发现组织文件的有变化：

3.1 查看结果

用sql看和用hadoop命令看每个文件，结果每个桶内都是按id升序排序的，也就是和最开始的截图是一样的

3.2 好处

因为每个桶内的数据是排序的，这样每个桶进行连接时就变成了高效的归并排序

4、提高join查询效率

假设表A和表B进行join，join的字段为id 条件：

1、两个表为大表2、两个表都为分桶表3、A表的桶数是B表桶数的倍数或因子

这样join查询时候，表A的每个桶就可以和表B对应的桶直接join，而不用全表join，提高查询效率 比如A表桶数为4，B表桶数为8，那么桶数对应关系为

表A表B0011223304152637

5、提高抽样效率

5.1 sql示例：

hive> select * from test_bucket tablesample (bucket 1 out of 2); OK 8 name8 4 name4 2 name2 6 name6 hive> select * from txt_bucket_test tablesample (bucket 1 out of 2 on id); OK 2 name2 8 name8 4 name4 6 name6

5.2 区别：

分桶表后面可以不带on 字段名，不带时默认的是按分桶字段,也可以带，而没有分桶的表则必须带按分桶字段取样时，因为分桶表是直接去对应的桶中拿数据，在表比较大时会提高取样效率

5.3 语法：

tablesample (bucket x out of y on id); x表示从哪个桶(x-1)开始，y代表分几个桶，也可以理解分x为分子，y为分母，及将表分为y份(桶)，取第x份(桶) 所以这时对于分桶表是有要求的，y为桶数的倍数或因子，

 y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了64份，当y=32时，抽取(64/32=)2个bucket的数据，当y=128时，抽取(64/128=)1/2个bucket的数据。

 x表示从哪个bucket开始抽取。例如，table总bucket数为32，tablesample(bucket 3 out of 16)，表示总共抽取(32/16=)2个bucket的数据，分别为第3个bucket和第(3+16=)19个bucket的数据。