4.项目分桶表

最新推荐文章于 2024-06-11 18:40:56 发布

小布先生~噫嘘唏

最新推荐文章于 2024-06-11 18:40:56 发布

阅读量285

点赞数 2

分类专栏：大数据项目总结文章标签： mysql big data 数据库 sql

本文链接：https://blog.csdn.net/Clittle225/article/details/131046617

版权

大数据项目总结专栏收录该内容

5 篇文章 0 订阅

订阅专栏

为什么要用分桶表？

1 为了抽样：在处理大规模数据集时，在开发和修改查询的阶段，可以使用整个数据集的一部分进行抽样测试查询、修改。可以使得开发更高效。

2 为了提高查询：连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

什么是分桶表？

将指定列按照一定规则分文件存储数据。

分桶的规则

bucket_id = column.hashcode % bucket.num

按照分桶字段(列)的hash值除以分桶的个数进行取余

怎么分桶

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;

举例

select * from tb_story_bucket_sort_9
tablesample (bucket 1 out of 3 on id)

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 * 1024
set 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;