Hive 分桶表及作用

我就是我 2024-04-07 14:40 168阅读 0赞

#### Hive 分桶表 ####

*  一.概述
 *   *  分桶和分区的区别 ？
     *  如何创建分桶表 ？
     *  分桶表的作用 ？
 *  二. 补充：通用的 join 优化
 *   *   *  （1）空key过滤
         *  （2）空key转换
 *  三. 分桶表的作用
 *   *  2.1 数据的采样
     *  2.2 提升查询效率（多表join优化）
     *   *  （一）小表 join 大表
         *  （二）中表 join 大表
         *  （三）大表 join 大表

## 一.概述 ##

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

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

### 分桶和分区的区别 ？ ###

1.  分桶针对的是数据文件，分区针对的是数据的存储路径；
2.  分桶根据哈希函数进行分割，比较平均；分区按照字段的值进行分割，容易造成数据倾斜；
3.  分桶分区互不干扰，可以把分区表进一步分桶；

### 如何创建分桶表 ？ ###

![在这里插入图片描述][478953e57afc4786a60999a75fc790c7.png]

### 分桶表的作用 ？ ###

**1）数据采样**  
案例1：如数据质量校验工作（一般会判断各个字段的结构信息是否完整）；  
案例2：在进行数据分析时，一天需要编写很多SQL，但是每编写一条SQL，都需要对SQL进行校验是否正确，  
如果面对完整的数据集合做校验，会导致校验时间过长，影响进度，所以可以采样出一部分；

**2）提升查询的效率**（多表 join 优化）

## 二. 补充：通用的 join 优化 ##

#### （1）空key过滤 ####

**使用场景**：  
①【外连接时】才用，内连接已经默认过滤null了）  
②不需null字段的情况下

有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同 的 reducer 上，从而导致内存不够。因此先干掉异常的key！

**例**：在 join 之前就用 `where is not null` 过滤掉 ！ 以免join之后再过滤；

select n.* from  (select * from nullidtable where id is not null)  n
       left join bigtable o on n.id = o.id;

#### （2）空key转换 ####

有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在 join 的结果中  
而同一个key的数据太大，同时进入一个reduce可能会导致数据倾斜；  
此时可以表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地 分不到不同的 reducer 上；

**例**：用 `nvl(id,rand())` 将为null 的字段赋为随机值；

select n.* from nullidtable n full join bigtable o on nvl ( n.id,rand() ) = o.id;

## 三. 分桶表的作用 ##

### 2.1 数据的采样 ###

tablesample (bucket x out of y columnnanme)

x： 表示从第几个桶开始抽取，桶号是从1开始  
y：抽取桶的比例，（总的桶数/2）=抽取桶的个数， 如10个桶，y=2即10/2=5个桶；  
（y 必须是桶个数的因子或者倍数，如10个桶，y可以取2、5不能取3）  
（x 一定要小于等于 y ！）  
columnname：表示按照哪个字段进行抽样（分桶字段）

`tablesample` 的位置：**一定在from 表名的后面**， 就算表有别名也要在别名的前面！

**例**： select \* from table `tablesample` (bucket x out of y on column) as q;

**例**：  
![在这里插入图片描述][baf8c0ece8ff4e6f9bc2d00e5a4542d4.png]  
抽的桶个数： 20/5=4

### 2.2 提升查询效率（多表join优化） ###

#### （一）小表 join 大表 ####

使用**map join**（`Block Cache`缓存技术，和环形缓冲区无关），在进行join时，将**小表放在每个读取大表的MapTask的内存上**，让mapTask每读取一次大表都和内存中小表的数据进行join，将join的结果输出到reduce端，从而实现在map端即reduce之前完成join的操作！

如何开启：  
①开启map -join ②设置小表的阀值 ，默认25mb  
![在这里插入图片描述][d4a17742a0034c08b701b4b2722de138.png]

#### （二）中表 join 大表 ####

**解决方案**：  
① **空key过滤**，过滤成小表就能用map-join了  
如：select n.\* from (select \* from nullidtable where id is not null) n left join bigtable o on n.id = o.id;

② **空key转换**，可以尝试添加随机数（保证各个reduce接收的数据量差不多，减少数据倾斜）  
如：select n.\* from nullidtable n full join bigtable o on nvl ( n.id,rand() ) = o.id;

③基于**分桶表**的 **bucket map join** ，即两个表都以桶为单位拆分，让每个桶的数据分别去进行两个表的map-join，即中型表的桶就是map-join的小表，大表的桶和小表的桶对应进行join，比普通mapjoin效率更高；

**bucket map join的条件**：  
![在这里插入图片描述][61be6115b21d4e76bad53035eb4f6eff.png]  
![在这里插入图片描述][b96ef215c7bb40039411d180ee970516.png]  
![在这里插入图片描述][23aca0e9ca32400985b8271b6b0600d8.png]  
![在这里插入图片描述][011328b12d8d412aa3475356b0826c77.png]

#### （三）大表 join 大表 ####

整体思想和普通的bucket map join类似，将两个表按照桶进行拆分，不过此时两个表的数量相同，所以一个桶对应一个桶，每个桶达到map join的条件，一个桶放在一个maptask的内存中；  
① 空key过滤，减少 join 的数量，提升效率；  
② 空key转换，可以尝试添加随机数（保证各reduce接收的数据量接近，从而减少数据倾斜）；  
③ **SMB**（sort merge Buckect Map join）；

**SMB map join 条件**：  
①开启参数  
![在这里插入图片描述][277c0ec1bacc4dfa918caf03f3f4cb63.png]  
②两个表的bucket桶数量要**相等**！ 而不是整数倍！  
③**bucket列=join列=sort列**

[478953e57afc4786a60999a75fc790c7.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/17ef0332788f4545bca1b2a5158a3b88.png
[baf8c0ece8ff4e6f9bc2d00e5a4542d4.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/28aafa923889477bb228cea60828f035.png
[d4a17742a0034c08b701b4b2722de138.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/c6bdd31485a5415985cfa6fb820d2eef.png
[61be6115b21d4e76bad53035eb4f6eff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/747fc4e77e0545dc877271bb083ec0f9.png
[b96ef215c7bb40039411d180ee970516.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/bd806f6a5fc4411a9438aae532340109.png
[23aca0e9ca32400985b8271b6b0600d8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/155f306cd2cc4647af10c1684cc105e4.png
[011328b12d8d412aa3475356b0826c77.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/2d3c1121753e4e3da0176debe3d9f311.png
[277c0ec1bacc4dfa918caf03f3f4cb63.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/07/d888060ed2ff4d45afb1fe06f37df0be.png