Hive分桶表

一：简介

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

分桶语法：创建表时使用clustered子句指定要分桶的字段和分桶的数量，也可以指定排序。

clustered by(字段名) sorted by (排序字段)  into 数量 buckets

hive分桶作用：

1. 方便抽样：在处理大规模数据集时，在开发和修改查询的阶段，如果能在数据集的一小部分数据上试运行查询，会带来很多方便。
2. 提高join查询效率: 对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大减少JOIN的数据量。

二：示例

1. 创建分桶表

create table tbl_bucket(id bigint, name string)
clustered by(id) sorted by (id) into 4 buckets
row format delimited
fields terminated by ","
lines terminated by "\n";

2. 准备测试数据

echo "1,name1\n2,name2\n3,name3\n4,name4\n5,name5\n6,name6\n7,name7" > /tmp/bucket.txt

3. 加载数据

插入分桶表不能直接使用load data加载数据，需要借助中间表来实现。

-- 创建中间表
hive> create table tbl_tmp(id bigint, name string)
row format delimited
fields terminated by ","
lines terminated by "\n";

-- 加载数据到中间表
hive> load data local inpath '/tmp/bucket.txt' into table tbl_tmp;

-- 从中间表中插入数据到分桶表
hive> insert into tbl_bucket select * from tbl_tmp;

4. 查看hdfs

hadoop fs -cat /data/hive/warehouse/test.db/tbl_bucket/000000_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_bucket/000001_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_bucket/000002_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_bucket/000003_0

hive> select * from tbl_bucket;

三：Join操作

1. 准备数据

echo "1,name1\n2,name2\n3,name3\n4,name4\n5,name5\n6,name6\n7,name7" > /tmp/user.txt
echo "1,shanghai,1\n2,beijing,1\n3,shenzhen,2\n4,hangzhou,3\n5,suzhou,4\n6,guangzhou,4\n7,zhengzhou,5" > /tmp/address.txt

2. 创建分桶表并添加数据

-- 对id进行分桶
hive> create table tbl_user(id bigint, name string)
clustered by(id) sorted by (id) into 4 buckets
row format delimited
fields terminated by ","
lines terminated by "\n";

hive> create table tbl_user_tmp(id bigint, name string)
row format delimited
fields terminated by ","
lines terminated by "\n";

hive> load data local inpath '/tmp/user.txt' into table tbl_user_tmp;
hive> insert into tbl_user select * from tbl_user_tmp;


-- 对user_id进行分桶
hive> create table tbl_address(id bigint, city string, user_id bigint)
clustered by(user_id) sorted by (user_id) into 4 buckets
row format delimited
fields terminated by ","
lines terminated by "\n";

hive> create table tbl_address_tmp(id bigint, city string, user_id bigint)
row format delimited
fields terminated by ","
lines terminated by "\n";

hive> load data local inpath '/tmp/address.txt' into table tbl_address_tmp;
hive> insert into tbl_address select * from tbl_address_tmp;

hadoop fs -cat /data/hive/warehouse/test.db/tbl_user/000000_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_user/000001_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_user/000002_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_user/000003_0


hadoop fs -cat /data/hive/warehouse/test.db/tbl_address/000000_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_address/000001_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_address/000002_0
hadoop fs -cat /data/hive/warehouse/test.db/tbl_address/000003_0

tbl_user和tbl_address相关的数据都分在同一个桶中。

3. 多表连接

select tu.id, tu.name, ta.city 
from tbl_user tu
left join tbl_address ta on tu.id = ta.user_id
where tu.id <= 3;

四：Hive 分桶和分区的区别

hdfs文件个数：

• 分桶的数量是在创建表时指定的，数量不能更改。如果要更改数量，需要重现插入数据。
• 分区的数量是根据字段枚举值的个数决定的，系统决定的，分区个数是动态增长的。

作用：

• 分区：是用于提高查询效率(避免扫描整个表，只需要扫描相关部分即可。例如日志文件可以按日期天分区，每天一个分区)
• 分桶：提高join查询效率，方便抽样

值：

• 分区中的每条数据中的分区字段值都是完全相同的
• 分桶中的每条数据的分桶字段值余上分桶数量的结构都是一样的。即分桶中的每个分桶字段值都不一样。