分桶表——头歌

任务描述

本关任务：根据相关知识内容实现 Hive 分桶表的操作。

相关知识

为了完成本关任务，你需要掌握： 1.分桶表的创建 2.分桶表的数据加载 3.抽样查询 4.相关表的操作

分桶表概述

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。 分区针对的是数据的存储路径；分桶针对的是数据文件。

创建分桶表

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

1. CREATE TABLE if not exists stu_buck(id int, name string)
2. clustered by(id) into 4 buckets ##分成4个桶
3. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; ##列分隔符

加载数据到分桶表中

由于 Hive-2.x 版本直接通过load加载不会有分桶的效果，所有需要借助一张中间表(该表需要与原表字段一致)，先将数据load到中间表再通过insert插入到分桶表中。

通过load加载将本地上的/bucket_stu.txt文本数据加载到temp_stu_buck表中。

1. ## 创建中间表：temp_stu_buck
2. CREATE TABLE if not exists temp_stu_buck(id int, name string)
3. ROW FORMAT DELIMITED FIELDS TERMINATED BY ','; ##列分隔符
4. ## 将数据导入到temp_stu_buck表中
5. load data local inpath '/root/bucket_stu.txt' into table temp_stu_buck;

将中间表temp_stu_buck的数据通过insert方式插入到分桶表stu_buck中。

1. ## 将中间表数据插入到分桶表中
2. insert into table stu_buck select * from temp_stu_buck;

抽样查询

对于非常大的数据集，有时用户需要使用的是一个具有代表性的查询结果而不是全部结果。Hive 可以通过对表进行抽样来满足这个需求。

1. ## 注意：x 的值必须小于等于 y 的值
2. ## X表示从哪个桶中开始抽取，Y表示相隔多少个桶再次抽取，colname 表示抽样的列。
3. TABLESAMPLE (BUCKET x OUT OF y [ON colname])

y必须是table总bucket数的倍数或者因子。hive根据y的大小，决定抽样的比例。例如，table总共分了4份，当y=4时，抽取（4/4）= 1 个bucket的数据，x表示从哪个bucket开始抽取。

查询表stu_buck中的数据：

1. ## 表示从桶中抽取1（4/4）个bucket数据，从第1个bucket开始抽取，抽取的个数由每个桶中的数据量决定。相隔4个桶再次抽取，因此，依次抽取的桶为：1，5，9，13，17
2. select * from stu_buck TABLESAMPLE(BUCKET 1 OUT OF 4 ON id);

查询结果如下所示：

编程要求

请根据右侧命令行内的提示，在Begin - End区域内进行sql语句代码补充，具体任务如下：

• 创建分桶表dept_buck：根据deptno分成4个桶

• 创建中间表temp_dept_buck：与dept_buck表结构一致

• load加载数据到temp_dept_buck中间表

• insert插入数据至dept_buck分桶表

• 抽样查询dept_buck表数据：从第1个桶中开始抽取，相隔4个桶再次抽取，抽样的列为deptno

dept表结构：

INFO	TYPE
deptno	int
dname	string

部分数据如下：

1. 10,staff1
2. 20,staff2
3. 30,staff3

数据切分方式：逗号（,）

数据所在目录：/root/dept.txt

测试说明

平台会对你编写的代码进行测试：

预期输出：

1. 16 staff16
2. 12 staff12
3. 8 staff8
4. 4 staff4

---

开始你的任务吧，祝你成功！

代码如下 

---创建mydb数据库
create database if not exists mydb;
---使用mydb数据库
use mydb;
---------- Begin ----------
---创建分桶表dept_buck：根据deptno分成4个桶
CREATE TABLE if not exists dept_buck(deptno int, dname string)
clustered by(deptno) into 4 buckets 
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
----创建中间表temp_dept_buck：与dept_buck表结构一致
CREATE TABLE if not exists temp_dept_buck(deptno int, dname string)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
---通过load加载数据到temp_dept_buck中间表
load data local inpath '/root/dept.txt' into table temp_dept_buck;  
---通过insert插入数据至dept_buck分桶表
insert into table dept_buck select * from temp_dept_buck;  
---抽样查询`dept_buck`表数据：从第`1`个桶中开始抽取，相隔`4`个桶再次抽取，抽样的列为`deptno`
select * from dept_buck TABLESAMPLE(BUCKET 1 OUT OF 4 ON deptno);  
---------- End ----------
---清空表
truncate table dept_buck;
truncate table temp_dept_buck;
---删除dept_buck表
drop table dept_buck;
drop table temp_dept_buck;