查询数据

hive查询数据详解：从基础语法到分布式特性

Hive 查询语法与传统 SQL 高度兼容，但作为分布式数据仓库，其查询执行依赖 MapReduce/Spark 等引擎，因此衍生出适合分布式场景的特性（如 SORT BY、DISTRIBUTE BY 等）。本文重点讲解 Hive 查询的基础语法、分布式排序与分区特性，帮助开发者高效编写 Hive SQL。

Hive 查询基础语法

Hive 查询核心语法与 SQL 一致，支持 SELECT、WHERE、GROUP BY、JOIN 等常见操作，适用于结构化数据的筛选、聚合和关联分析。

基础查询操作

操作类型	语法示例	说明
全表扫描	SELECT * FROM dept;	查询表中所有列和行
特定列查询	SELECT deptno, dname FROM dept;	仅查询指定列，减少数据传输
条件过滤	SELECT * FROM emp WHERE sal > 2000;	通过 WHERE 筛选符合条件的行
别名设置	SELECT deptno AS no, dname AS name FROM dept;	用 AS 为列或表设置别名，简化查询
去重查询	SELECT DISTINCT deptno FROM emp;	用 DISTINCT 去除重复值

范围与模糊查询

Hive 支持 SQL 中的范围判断和模糊匹配，满足复杂筛选需求：

范围查询

-- 数值范围（BETWEEN ... AND）  
SELECT * FROM emp WHERE sal BETWEEN 1000 AND 3000;  

-- 枚举范围（IN）  
SELECT * FROM emp WHERE deptno IN (10, 20);  -- 查询部门 10 和 20 的数据  

模糊查询（LIKE）

-- % 匹配任意个字符（包括 0 个），_ 匹配单个字符  
SELECT * FROM emp WHERE ename LIKE 'A%';  -- 匹配名字以 A 开头的行  
SELECT * FROM emp WHERE ename LIKE '_M%';  -- 匹配名字第二个字符为 M 的行  

聚合与分组查询

Hive 支持 GROUP BY 分组和聚合函数（如 AVG、SUM），并通过 HAVING 过滤聚合结果：

基础聚合

-- 按部门分组，计算平均薪资  
SELECT deptno, AVG(sal) AS avg_sal, SUM(sal) AS total_sal  
FROM emp  
GROUP BY deptno;  

聚合过滤（HAVING）

-- 筛选平均薪资大于 2000 的部门  
SELECT deptno, AVG(sal) AS avg_sal  
FROM emp  
GROUP BY deptno  
HAVING avg_sal > 2000;  -- 注意：HAVING 用于过滤聚合结果，WHERE 用于过滤原始数据  

多表关联（JOIN）

Hive 支持 INNER JOIN、LEFT JOIN、RIGHT JOIN 和 FULL JOIN，语法与 SQL 一致，用于多表数据关联分析：

-- 内连接：仅保留两表匹配的行  
SELECT e.empno, e.ename, d.dname  
FROM emp e  
INNER JOIN dept d ON e.deptno = d.deptno;  

-- 左外连接：保留左表所有行，右表无匹配则为 NULL  
SELECT e.empno, e.ename, d.dname  
FROM emp e  
LEFT JOIN dept d ON e.deptno = d.deptno;  

-- 全外连接：保留两表所有行，无匹配则为 NULL  
SELECT e.empno, e.ename, d.dname  
FROM emp e  
FULL JOIN dept d ON e.deptno = d.deptno;  

注意：Hive 对大表 JOIN 有优化（如 MapJoin），小表可广播到所有节点，减少数据传输。

Hive 分布式查询特性

Hive 作为分布式系统，查询执行依赖多个节点的并行计算，因此设计了适合分布式场景的排序和分区机制，区别于传统 SQL 的单节点执行逻辑。

全局排序（ORDER BY）

ORDER BY 与 SQL 功能一致，对查询结果进行全局排序，但在分布式环境中存在性能限制：

语法与特性

-- 按薪资升序排序（默认 ASC，可指定 DESC 降序）  
SELECT ename, sal FROM emp ORDER BY sal;  

底层执行机制

• ORDER BY 会将所有数据发送到 单个 Reducer 处理，确保全局有序；
• 日志中可见 number of reducers: 1，表明仅用一个 Reducer；
• 缺点：大数据量时单个 Reducer 可能成为瓶颈，导致查询超时。

局部排序（SORT BY）

SORT BY 为每个 Reducer 生成一个局部有序的文件，适合大数据量的初步排序，提升查询效率：

语法与特性

-- 每个 Reducer 内部按薪资降序排序  
SELECT ename, deptno, sal FROM emp SORT BY sal DESC;  

底层执行机制

• 数据按 Reducer 数量拆分，每个 Reducer 对分配到的数据单独排序；
• 全局结果 不是整体有序，但每个 Reducer 的输出文件内部有序；
• 可通过 set mapreduce.job.reduces=N 指定 Reducer 数量（如 set mapreduce.job.reduces=3）。

数据分区（DISTRIBUTE BY）

DISTRIBUTE BY 用于控制数据在 Reducer 之间的分配规则，确保相同 key 的数据进入同一个 Reducer，常与 SORT BY 结合使用：

语法与场景

-- 按部门分区（相同部门进入同一 Reducer），再按薪资降序排序  
SELECT deptno, ename, sal  
FROM emp  
DISTRIBUTE BY deptno  -- 分区规则：deptno 的 hash 值 % Reducer 数量  
SORT BY sal DESC;     -- 每个 Reducer 内按薪资排序  

作用

• 解决 SORT BY 数据分配随机的问题，确保同类数据（如同一部门）集中处理；
• 分区字段通常为聚合或排序的关键列（如 deptno、user_id）。

分区排序合并（CLUSTER BY）

若 DISTRIBUTE BY 和 SORT BY 的字段相同，可简化为 CLUSTER BY，实现 “分区 + 排序” 一体化：

语法等价性

-- 以下两种写法等价（分区和排序字段均为 deptno）  
SELECT deptno, ename FROM emp DISTRIBUTE BY deptno SORT BY deptno;  
SELECT deptno, ename FROM emp CLUSTER BY deptno;  -- 简化写法  

注意事项

• CLUSTER BY 仅支持升序排序，若需降序需显式使用 DISTRIBUTE BY + SORT BY；
• 适用于按单一字段分区并排序的场景（如按用户 ID 分区并排序）。

Hive 查询高级特性

限制返回行数（LIMIT）

-- 从第 2 行开始，返回 2 行数据（offset 从 0 开始）  
SELECT * FROM emp LIMIT 2, 2;  

空值处理

Hive 中 NULL 表示缺失值，查询时需用 IS NULL 或 IS NOT NULL 判断：

-- 查询薪资为空的员工  
SELECT ename FROM emp WHERE sal IS NULL;  

模糊查询增强

支持通配符 %（任意字符）和 _（单个字符），结合 ESCAPE 处理特殊字符：

-- 查询名字含 "A_" 的员工（用 ESCAPE 指定转义符）  
SELECT ename FROM emp WHERE ename LIKE '%A\_%' ESCAPE '\';  

分布式查询最佳实践

1. 避免大表 ORDER BY：全局排序依赖单 Reducer，大数据量下改用 DISTRIBUTE BY + SORT BY 实现局部有序；
2. 合理设置 Reducer 数量：通过 set mapreduce.job.reduces=5 调整，数量过多会增加 overhead，过少则可能倾斜；
3. JOIN 优化：小表在前（如 SELECT ... FROM small_table JOIN big_table ON ...），触发 MapJoin 优化；
4. 分区表优先过滤分区列：查询分区表时先通过 WHERE dt='2023-10-01' 过滤分区，减少扫描数据量。