Hive 知识点八股文记录 ——（三）区别和原理

区别

order by , sort by , distribute by , cluster by 的区别

• order by：全局排序，只有一个reduce（多个reduce无法保证全局有序
• sort by：非全局排序，reduce前进行排序。
  • 因此 mapped. reduce. tasks >1， sort by只会保证每个 reducer的输出有序，并不保证全局有序
  • 实现全局排序： 先用 sortby保证每个 reducer输出有序，再order by归并reduce
• distribute by: 在map端拆分数据给 reduce端. hive 根据distribute by的列，对reduce个数分发，默认hash算法
  • （sort by为每个reduce产生排序文件，但有时候你需要空值某个行到特定的reduce）
  • hive规定distribute by 语句要写在sort by语句之前，两者配合使用
• cluster by：当distribute by 和 sort by 所指定的字段相同时，即可以使用cluster by（列只能是升序，不能指定asc和desc

hive分隔符

建表指定分隔符

CREATE TABLE mytable (col1 STRING, col2 STRING, col3 STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

加载数据指定分隔符

LOAD DATA LOCAL INPATH '/path/to/data.txt' INTO TABLE mytable
FIELDS TERMINATED BY ',';

分区分桶

概念

分区	分桶
大表存储的时候根据不同数据作为目录来存储。 一级目录：单分区表；多级目录：多分区表 非最终分区表不能存数据	hive表按hash值分为多个桶，让数据均匀分布到多个桶中提高查询性能。

他们并不互斥，存储格式有差别

作用？

分区	分桶
数据分为小块，提高查询性能	提高Join查询的效率，在一份数据会被经常用来做连接查询的时候建立分桶表，分桶字段就是连接字段

两者一起使用的结果

• 使取样（sampling）更高效，只取一部分数据集进行测试
• 分桶对比分区可粒度更细
• 分区不是按照真实数据的列，而是指定的伪列，分桶则是按照数据列

row_number 中 distribute by 和 partition by 的区别

• distribute by用于指定数据在Reducer上的分布方式，确保具有相同值的行被分发到同一个Reducer上。
• partition by用于指定在计算row_number()时要分区的列，将数据划分为更小的块，提高计算效率。 —— 按列分区后，每个列内再计算行号

partition by 只能和 order by 组合使用

distribute by 只能和 sort by 使用

left semi join 和 left join 区别

left join：展示左表和右表的所有字段，如果右表没有匹配的记录，右表的字段值为NULL。

left semi join：仅展示A表与B表匹配的记录，并且只展示A表字段，因为left semi join只传递表的join key给Map阶段（

A表：

id	name
1	Alice
2	Bob
3	Carol

B表：

id	age
2	25
3	30
4	35

1. 左连接（left join）示例：

SELECT A.id, A.name, B.age
FROM A
LEFT JOIN B ON A.id = B.id;

结果：

id	name	age
1	Alice	NULL
2	Bob	25
3	Carol	30

2. 左半连接（left semi join）示例：

SELECT A.id, A.name
FROM A
LEFT SEMI JOIN B ON A.id = B.id;

结果：

id	name
2	Bob
3	Carol

原理

group by 排序的原因

• 分组聚合：GROUP BY语句通常与聚合函数（如SUM、COUNT、AVG等）一起使用，用于对数据进行分组并计算聚合结果
• 归并操作：通过MapReduce或Tez任务来执行的。任务中，数据需要在不同的节点之间进行归并操作，以将相同键值的数据合并在一起。GROUP BY字段进行排序可以提高合并效率
• 优化查询性能：相同的键值的数据会被连续地存储在磁盘上，这样可以减少磁盘I/O的次数，提高查询的效率

group by 默认全局，可以用DISTRIBUTE BY和SORT BY语句来实现分布式排序，将排序的负载分散到不同的节点上

mapjoin

hint提示 /*+ mapjoin(table) */可以执行mapjoin

• 小表数据存入内存，生成hash表，key为hash值，value为数据
• mapper阶段，大表记录计算hash值匹配小表，匹配成功后输出到reduce
• 连接列的值，将所有匹配记录合并成一条记录输出结果

避免大表全盘扫描，磁盘读写，提高查询性能