为什么要用分桶表?
1 为了抽样:在处理大规模数据集时,在开发和修改查询的阶段,可以使用整个数据集的一部分进行抽样测试查询、修改。可以使得开发更高效。
2 为了提高查询:连接两个在(包含连接列的)相同列上划分了桶的表,可以使用 Map 端连接 (Map-side join)高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列,如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以,可以大大较少JOIN的数据量。
什么是分桶表?
将指定列 按照一定规则 分文件存储数据。
分桶的规则
怎么分桶
0 原始数据
story.txt 小说数据
9 天龙八部 10 射雕英雄传 11 神雕侠侣 12 笑傲江湖 13 鹿鼎记 14 倚天屠龙记 15 亮剑 16 潜伏 17 天道 18 士兵突击 19 三国演义 20 西游记 21 水浒传
person.txt 人物数据
1 乔峰 9 2 段誉 9 3 虚竹 9 4 阿朱 9 5 王语嫣 9 6 梦姑 9 7 郭靖 10 8 黄蓉 10 9 黄药师 10 10 欧阳锋 10 11 一灯大师 10 12 洪七公 10 13 杨过 11 14 小龙女 11 15 李莫愁 11 16 老顽童 11 17 金轮法王 11 18 梅姑 11 19 令狐冲 12 20 任盈盈 12 21 岳不群 12 22 任我行 12 23 风清扬 12 24 东方不败 12 25 韦小宝 13 26 建宁公主 13 27 双儿 13 28 沐剑屏 13 29 曾柔 13 30 阿珂 13 31 张无忌 14 32 光明左使 14 33 紫衫龙王 14 34 白眉鹰王 14 35 金毛狮王 14 36 青翼蝠王 14 37 李云龙 15 38 赵刚 15 39 楚云飞 15 40 丁伟 15 41 孔捷 15 42 魏和尚 15 43 余则成 16 44 王翠平 16 45 李涯 16 46 吴敬中 16 47 陆桥山 16 48 马奎 16 49 丁元英 17 50 芮小丹 17 51 韩楚风 17 52 欧阳雪 17 53 肖亚文 17 54 林雨峰 17 55 许三多 18 56 成才 18 57 高成 18 58 伍六一 18 59 袁朗 18 60 史今 18 61 刘备 19 62 关羽 19 63 张飞 19 64 诸葛亮 19 65 曹操 19 66 周瑜 19 67 唐僧 20 68 孙悟空 20 69 猪八戒 20 70 沙和尚 20 71 牛魔王 20 72 红孩儿 20 73 宋江 21 74 卢俊义 21 75 鲁智深 21 76 林冲 21 77 武松 21 78 吴用 21
1 创建普通表
实现
create database db_1; use db_1; -- # 1 创建普通表 create table tb_story( id int, name string ) row format delimited fields terminated by '\t' ; create table tb_person( id int, name string, story_id int ) row format delimited fields terminated by '\t' ; -- 2 加载数据: -- 2.1 先通过浏览器 将原始文件 上传到hdfs的 input 目录 -- 2.2 实现 加载数据 load data inpath '/input/story.txt' into table tb_story; load data inpath '/input/person.txt' into table tb_person; -- 3 测试 select * from tb_story; select * from tb_person;
2 创建分桶表 且 加载数据
分桶表语法
create table 表名(列名1 类型1, 列名2 类型2, ...) clustered by (id) sorted by(id) into 2 bucket ... ...;
clustered by (id) 表示根据 id 分桶
sorted by(id) 表示根据id排序
into 2 bucket 表示分几桶
实现1 不带排序
建表
create table tb_story_bucket( id int, name string ) clustered by (id) into 3 buckets row format delimited fields terminated by '\t' ;
插入数据
insert into tb_story_bucket select * from tb_story;
查看表的真实内容
有分桶表的查询
select * from tb_story_bucket where id = 12;
无分桶表的查询
select * from tb_story where id = 12;
实现2 带排序
建表
create table tb_story_bucket_sort ( id int, name string ) clustered by (id) sorted by (id asc) into 3 buckets row format delimited fields terminated by '\t' ;
插入数据
insert into tb_story_bucket_sort select * from tb_story;
查看表的真实内容
实现3 创建人物表 且 加载数据
实现
-- 创建分桶表 create table tb_person_bucket_sort( id int, name string, story_id int ) clustered by (story_id) sorted by (story_id) into 3 buckets row format delimited fields terminated by '\t' ; -- 向分桶表插入数据 insert into tb_person_bucket_sort select * from tb_person;
图解
3 抽样
语法格式:
select * from 表名 tablesample (bucket x out of y on 列)
y 确定要几桶
x 确定要 第 几桶on 列表示根据哪一列抽样准备工作 sql语句create table tb_story_bucket_sort_9 ( id int, name string ) clustered by (id) sorted by (id asc) into 9 buckets row format delimited fields terminated by '\t' ; insert into tb_story_bucket_sort_9 select * from tb_story;
举例
1.
select * from tb_story_bucket_sort_9 tablesample (bucket 1 out of 3 on id)
2.
select * from tb_story_bucket_sort tablesample (bucket 2 out of 18 on id)
4 执行计划 了解
用户提交HiveSQL查询后,Hive会把查询语句转换为MapReduce作业
Hive会自动完成整个执行过程,一般情况下,我们并不用知道内部是如何运行的。
执行计划可以告诉我们查询过程的关键信息, 用来帮助我们判定优化措施是否已经生效。语法explain sql语句
查看执行计划
代码
explain select * from tb_story_bucket_sort tablesample ( bucket 1 out of 3 on id);
图示
5 多表关联
1 普通关联
sql
select t1.*, t2.* from tb_story t1 inner join tb_person t2 on t1.id=t2;
图解
问题:如果 数据 多了, reduce压力就会很大, 效率很低
2 map join 优化
原理
前提条件
1 是否自动尝试 map join
set hive.auto.convert.join=true;
2 根据大小判断哪个表是小表
默认 20971520 = 20M
20 * 1024 * 1024调整为 512M
set hive.auto.convert.join.noconditionaltask.size=512000000 ;
问题:如果小表总大小超过了 最小大小 的限制, 就不会走 map join, 怎么办?
如果小表总大小超过了 Map 阶段的限制,即默认设置的 "hive.auto.convert.join.noconditionaltask.size" 参数(256 MB),就不会自动走 Map Join 了。此时可以考虑使用 Sort Merge Join 或者 Bucketized Join 来代替 Map Join。
执行计划
1.不开map join
set hive.auto.convert.join=false; explain select * from tb_story_bucket_sort t1 inner join db_2.tb_person_bucket_sort t2 on t1.id=t2.story_id;
2.开 map join
set hive.auto.convert.join=true; explain select * from tb_story_bucket_sort t1 inner join db_2.tb_person_bucket_sort t2 on t1.id=t2.story_id;
3 bucket map join
中表 对 中表 的原理图
1 第一步 小表一分为二
2 大表 一分为四
3 关联
将小表放对应机器的内存中, 在进行 map join的操作
使用条件
1.表必须是分桶表
2.必须是用在 map join 的场景中
3.bucket列 跟 join列 一样:bucket列 == join列
4.一个表的bucket数 是另一个表bucket数的整数倍
5.必须开启 map join
5.11 是否自动尝试map join
set hive.auto.convert.join=true;
5.122 根据大小判断哪个表是小表
默认 20971520 = 20M
20 * 1024 * 1024set hive.auto.convert.join.noconditionaltask.size=512000000 ;
4 sort merge bucket map join (SMB map join)
原理
第一步 小说表 分2桶
第二步 人物表 分2桶
第三步 关联
使用条件
1 必须保证开启分桶map join
2 一个表的bucket数 必须等于 另一个表bucket数
3 bucket列 == join 列 == sort列4 必须是用在 map join 的场景中5 两个表必须是桶表配置1 开启强制排序set hive.enforce.sorting=true;
2 开启 SMB map join
set hive.auto.convert.sortmerge.join=true; set hive.auto.convert.sortmerge.join.noconditionaltask=true;
3 是否开启自动尝试 SMB
set hive.optimize.bucketmapjoin.sortedmerge = true; # 建议直接配置到 CM中 # 说明 # 一般在生产中, 建议大家开启 # 这样hive执行过程中 # 如果发现表 可以走SMB join, 就走 SMB join # 如果发现表 可以走 bucket join, 就会走 bucket join # 如果发表 可以走 map join, 就会走 map join
效果
set hive.auto.convert.join=true; set hive.enforce.sorting=true; set hive.auto.convert.sortmerge.join=true; set hive.auto.convert.sortmerge.join.noconditionaltask=true; set hive.optimize.bucketmapjoin.sortedmerge = true; explain select * from tb_story_bucket_sort t1 inner join db_2.tb_person_bucket_sort t2 on t1.id=t2.story_id;