hive分桶表join_Hive分桶表

测试数据

95001,李勇,男,20,CS

95002,刘晨,女,19,IS

95003,王敏,女,22,MA

95004,张立,男,19,IS

95005,刘刚,男,18,MA

95006,孙庆,男,23,CS

95007,易思玲,女,19,MA

95008,李娜,女,18,CS

95009,梦圆圆,女,18,MA

95010,孔小涛,男,19,CS

95011,包小柏,男,18,MA

95012,孙花,女,20,CS

95013,冯伟,男,21,CS

95014,王小丽,女,19,CS

95015,王君,男,18,MA

95016,钱国,男,21,MA

95017,王风娟,女,18,IS

95018,王一,女,19,IS

95019,邢小丽,女,19,IS

95020,赵钱,男,21,IS

95021,周二,男,17,MA

95022,郑明,男,20,MA

创建分桶表

drop table stu_buck;

create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)

clustered by(Sno) //根据Sno分桶

sorted by(Sno DESC)

into 4 buckets

row format delimited

fields terminated by ',';

设置变量,设置分桶为true, 设置reduce数量是分桶的数量个数

set hive.enforce.bucketing = true;

set mapreduce.job.reduces=4;

加载数据

load data local inpath '/mnt/hgfs/share_folder/hiveDATA/students.txt' into table stu_buck;

Loading data to table default.stu_buck

Table default.stu_buck stats: [numFiles=1, totalSize=526]

检查刚刚加载的数据

select * from stu_buck;

OK

95001 李勇 男 20 CS

95002 刘晨 女 19 IS

95003 王敏 女 22 MA

95004 张立 男 19 IS

95005 刘刚 男 18 MA

95006 孙庆 男 23 CS

95007 易思玲 女 19 MA

95008 李娜 女 18 CS

95009 梦圆圆 女 18 MA

95010 孔小涛 男 19 CS

95011 包小柏 男 18 MA

95012 孙花 女 20 CS

95013 冯伟 男 21 CS

95014 王小丽 女 19 CS

95015 王君 男 18 MA

95016 钱国 男 21 MA

95017 王风娟 女 18 IS

95018 王一 女 19 IS

95019 邢小丽 女 19 IS

95020 赵钱 男 21 IS

95021 周二 男 17 MA

95022 郑明 男 20 MA

创建测试数据表

> create table t_p(Sno int,Sname string)

row format delimited fields terminated by ',';

//结合mapreduce。map输出的数据都有分区的概念，分区的时候有一个根据key来partionar， cluster by即指定key是根据哪个字段来排序，则reduce拿到的数据就是hashKey%bucket的个数，形成bucket个数的文件，sort by:每个bucket的文件内部数据排序

distribute by指定分区字段 sort by：指定排序字段

select Sno,Sname from stu_buck cluster by (Sno);

OK

95001 李勇

95002 刘晨

95003 王敏

95004 张立

95005 刘刚

95006 孙庆

95007 易思玲

95008 李娜

95009 梦圆圆

95010 孔小涛

95011 包小柏

95012 孙花

95013 冯伟

95014 王小丽

95015 王君

95016 钱国

95017 王风娟

95018 王一

95019 邢小丽

95020 赵钱

95021 周二

95022 郑明

//只排序

select Sno,Sname from stu_buck sort by (Sno);

OK

95001 李勇

95002 刘晨

95003 王敏

95004 张立

95005 刘刚

95006 孙庆

95007 易思玲

95008 李娜

95009 梦圆圆

95010 孔小涛

95011 包小柏

95012 孙花

95013 冯伟

95014 王小丽

95015 王君

95016 钱国

95017 王风娟

95018 王一

95019 邢小丽

95020 赵钱

95021 周二

95022 郑明

select Sno,Sname from stu_buck distribute by (Sno) sort by (Sno);

OK

95001 李勇

95002 刘晨

95003 王敏

95004 张立

95005 刘刚

95006 孙庆

95007 易思玲

95008 李娜

95009 梦圆圆

95010 孔小涛

95011 包小柏

95012 孙花

95013 冯伟

95014 王小丽

95015 王君

95016 钱国

95017 王风娟

95018 王一

95019 邢小丽

95020 赵钱

95021 周二

95022 郑明

加载分桶数据到空表t_p

insert into table t_p

select Sno,Sname from stu_buck cluster by (Sno);

加载完成后查询新表数据

//hive表默认目录

dfs -ls /user/hive/warehouse/;

dfs -cat /user/hive/warehouse/t_p/000000_0;//应该也是4个同样的分桶

注：1、order by 输出数据一定全局有序，因此只有一个reducer(哪怕设置了 hive> set mapredce.job.reduces=4 运行sql时仍会被重置为1个)，会导致当输入规模较大时，需要较长的计算时间。

2、sort by不是全局排序，其在数据进入reducer前完成排序。因此，如果用sort by进行排序，并且设置mapred.reduce.tasks>1，则sort by只保证每个reducer的输出有序，不保证全局有序。

3、distribute by(字段)根据指定的字段将数据分到不同的reducer，且分发算法是hash散列。

4、(Cluster by字段) 除了具有Distribute by的功能外，还会对该字段进行排序。因此，如果分桶和sort字段是同一个时，此时，cluster by = distribute by + sort by

分桶表的作用：最大的作用是用来提高join操作的效率；

(思考这个问题：select a.id,a.name,b.addr from a join b on a.id = b.id;

如果a表和b表已经是分桶表，而且分桶的字段是id字段做这个join操作时，还需要全表做笛卡尔积吗？)

如果两个表的分桶个数不一致：那么分桶联查就没意义，但是为倍数的时候还是有意义的

insert overwrite table student_buck

select * from student cluster by(Sno) sort by(Sage); 报错,cluster 和 sort 不能共存

开会往创建的分通表插入数据(插入数据需要是已分桶, 且排序的)

可以使用distribute by(sno) sort by(sno asc) 或是排序和分桶的字段相同的时候使用Cluster by(字段)

注意使用cluster by 就等同于分桶+排序(sort)

insert into table stu_buck

select Sno,Sname,Sex,Sage,Sdept from student distribute by(Sno) sort by(Sno asc);

insert overwrite table stu_buck

select * from student distribute by(Sno) sort by(Sno asc);

insert overwrite table stu_buck

select * from student cluster by(Sno);