Hive HQL 详解：大数据 SQL 实战指南

一、HQL 基础架构与执行流程

1.1 HQL 处理流程

1.2 HQL 与标准 SQL 差异

特性	HQL	标准 SQL
数据更新	有限支持（需ACID表）	完整支持
事务	可选支持	默认支持
索引	有限使用	广泛使用
执行引擎	MapReduce/Tez/Spark	数据库内置引擎
延迟	高（分钟级）	低（毫秒级）

二、数据定义语言（DDL）

2.1 数据库操作

-- 创建数据库
CREATE DATABASE IF NOT EXISTS sales_db
COMMENT '销售数据存储'
LOCATION '/hive/sales_db';

-- 切换数据库
USE sales_db;

-- 删除数据库（级联删除）
DROP DATABASE IF EXISTS sales_db CASCADE;

2.2 表管理

内部表 vs 外部表

-- 内部表（托管表）
CREATE TABLE managed_table (
    id INT,
    name STRING
) STORED AS ORC;

-- 外部表（仅管理元数据）
CREATE EXTERNAL TABLE external_table (
    id INT,
    name STRING
) STORED AS ORC
LOCATION '/data/external_table';

分区表

-- 创建分区表
CREATE TABLE logs (
    user_id STRING,
    action STRING
) PARTITIONED BY (dt STRING, country STRING)
STORED AS PARQUET;

-- 添加分区
ALTER TABLE logs ADD PARTITION (dt='2023-01-01', country='US');

-- 修复分区元数据
MSCK REPAIR TABLE logs;

分桶表

CREATE TABLE bucketed_users (
    id INT,
    name STRING,
    age INT
) CLUSTERED BY (id) INTO 8 BUCKETS
STORED AS ORC;

三、数据操作语言（DML）

3.1 数据加载

-- 从本地加载
LOAD DATA LOCAL INPATH '/path/to/data.csv' 
INTO TABLE employees;

-- 从HDFS加载
LOAD DATA INPATH '/hdfs/data/transactions.orc' 
INTO TABLE transactions PARTITION (dt='2023-01-01');

-- CTAS（创建表并加载）
CREATE TABLE new_table AS
SELECT * FROM old_table WHERE age > 30;

3.2 数据插入

-- 静态分区插入
INSERT INTO TABLE logs PARTITION (dt='2023-01-01')
SELECT user_id, action FROM raw_events;

-- 动态分区插入
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE logs PARTITION (dt, country)
SELECT user_id, action, event_date, country FROM raw_events;

3.3 数据更新与删除

-- 启用ACID支持
SET hive.support.concurrency=true;
SET hive.txn.manager=org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;

-- 更新数据
UPDATE employees
SET salary = salary * 1.1
WHERE dept = 'Engineering';

-- 删除数据
DELETE FROM employees
WHERE status = 'inactive';

-- 合并操作
MERGE INTO target_table AS T
USING source_table AS S
ON T.id = S.id
WHEN MATCHED AND S.status='deleted' THEN DELETE
WHEN MATCHED THEN UPDATE SET T.value = S.value
WHEN NOT MATCHED THEN INSERT VALUES(S.id, S.value);

四、数据查询语言（DQL）

4.1 基础查询

-- 条件过滤
SELECT * FROM employees 
WHERE department = 'Sales' AND salary > 5000;

-- 聚合函数
SELECT department, 
       AVG(salary) AS avg_salary,
       COUNT(*) AS emp_count
FROM employees
GROUP BY department
HAVING AVG(salary) > 6000;

4.2 高级查询

窗口函数

SELECT 
    employee_id,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

复杂Join

-- MapJoin优化小表
SELECT /*+ MAPJOIN(d) */ 
    e.name, d.dept_name, e.salary
FROM employees e
JOIN departments d ON e.dept_id = d.id;

-- 分桶Join优化
SELECT e.name, s.bonus
FROM bucketed_employees e
JOIN bucketed_salaries s 
ON e.id = s.emp_id;

4.3 子查询

-- 标量子查询
SELECT name, salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

-- IN子查询
SELECT * FROM orders
WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'US'
);

-- EXISTS子查询
SELECT * FROM products p
WHERE EXISTS (
    SELECT 1 FROM sales s 
    WHERE s.product_id = p.id AND s.quantity > 100
);

五、HQL 高级特性

5.1 视图与物化视图

-- 创建视图
CREATE VIEW high_paid_employees AS
SELECT * FROM employees WHERE salary > 10000;

-- 物化视图（需Hive 3.0+）
CREATE MATERIALIZED VIEW mv_sales_summary
AS
SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

-- 自动刷新物化视图
ALTER MATERIALIZED VIEW mv_sales_summary REBUILD;

5.2 自定义函数

-- UDF（标量函数）
CREATE TEMPORARY FUNCTION my_upper AS 'com.example.MyUpperUDF';

SELECT my_upper(name) FROM employees;

-- UDAF（聚合函数）
CREATE FUNCTION my_avg AS 'com.example.MyAvgUDAF';

SELECT department, my_avg(salary) FROM employees GROUP BY department;

-- UDTF（表生成函数）
SELECT user_id, page_url
FROM clicks
LATERAL VIEW explode(split(pages, ',')) t AS page_url;

5.3 复杂数据类型操作

-- 结构体
CREATE TABLE users (
    id INT,
    name STRING,
    address STRUCT<street:STRING, city:STRING, zip:INT>
);

SELECT address.city FROM users;

-- 数组
SELECT id, hobbies[0] AS primary_hobby FROM profiles;

-- Map
SELECT id, preferences['theme'] AS theme FROM user_settings;

-- JSON处理
SELECT 
    get_json_object(json_col, '$.user.name') AS user_name,
    json_tuple(json_col, 'age', 'city') AS (age, city)
FROM json_table;

六、性能优化技巧

6.1 执行计划分析

-- 查看执行计划
EXPLAIN
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- 详细执行计划
EXPLAIN FORMATTED
SELECT * FROM logs WHERE dt = '2023-01-01';

-- 依赖分析
EXPLAIN DEPENDENCY
SELECT e.name, d.dept_name 
FROM employees e 
JOIN departments d ON e.dept_id = d.id;

6.2 优化配置参数

-- 启用向量化
SET hive.vectorized.execution.enabled=true;
SET hive.vectorized.execution.reduce.enabled=true;

-- 优化Join
SET hive.auto.convert.join=true;
SET hive.auto.convert.join.noconditionaltask.size=10000000; -- 10MB

-- 动态分区优化
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
SET hive.exec.max.dynamic.partitions=10000;

-- 压缩设置
SET hive.exec.compress.intermediate=true;
SET hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;

6.3 数据倾斜处理

-- 随机数分散Key
SELECT * 
FROM (
  SELECT *, rand() as rnd 
  FROM skewed_table
) t 
DISTRIBUTE BY key, rnd;

-- MapJoin处理倾斜
SET hive.optimize.skewjoin=true;
SET hive.skewjoin.key=100000; -- 超过100000条记录视为倾斜

-- 分组聚合优化
SET hive.groupby.skewindata=true;

-- 倾斜值单独处理
SELECT 
  key, 
  SUM(value) AS total
FROM (
  SELECT key, value
  FROM table
  WHERE key NOT IN ('skewed_value')
  
  UNION ALL
  
  SELECT key, SUM(value) AS value
  FROM table
  WHERE key = 'skewed_value'
  GROUP BY key
) combined
GROUP BY key;

七、实战案例：电商数据分析

7.1 用户行为分析

WITH user_activity AS (
  SELECT
    user_id,
    COUNT(DISTINCT session_id) AS session_count,
    SUM(CASE WHEN action='purchase' THEN 1 ELSE 0 END) AS purchases
  FROM user_logs
  WHERE dt BETWEEN '2023-01-01' AND '2023-01-31'
  GROUP BY user_id
)

SELECT
  CASE
    WHEN session_count BETWEEN 1 AND 3 THEN '低频'
    WHEN session_count BETWEEN 4 AND 10 THEN '中频'
    ELSE '高频'
  END AS frequency_segment,
  AVG(purchases) AS avg_purchases,
  COUNT(*) AS users
FROM user_activity
GROUP BY 
  CASE
    WHEN session_count BETWEEN 1 AND 3 THEN '低频'
    WHEN session_count BETWEEN 4 AND 10 THEN '中频'
    ELSE '高频'
  END;

7.2 销售漏斗分析

SELECT
  product_category,
  COUNT(DISTINCT view_user) AS viewers,
  COUNT(DISTINCT cart_user) AS cart_users,
  COUNT(DISTINCT purchase_user) AS buyers,
  ROUND(COUNT(DISTINCT purchase_user) / COUNT(DISTINCT view_user), 2) AS conversion_rate
FROM (
  SELECT
    product_category,
    user_id AS view_user,
    NULL AS cart_user,
    NULL AS purchase_user
  FROM events WHERE event_type = 'view'
  
  UNION ALL
  
  SELECT
    product_category,
    NULL,
    user_id,
    NULL
  FROM events WHERE event_type = 'cart'
  
  UNION ALL
  
  SELECT
    product_category,
    NULL,
    NULL,
    user_id
  FROM events WHERE event_type = 'purchase'
) funnel
GROUP BY product_category;

八、HQL 最佳实践

8.1 编码规范

1. 命名规范：

   -- 使用蛇形命名法
   CREATE TABLE user_activity_logs (...);
   

2. 代码格式化：

   SELECT
     a.id,
     b.name,
     COUNT(*) AS total
   FROM table_a a
   JOIN table_b b ON a.id = b.id
   WHERE a.date > '2023-01-01'
   GROUP BY a.id, b.name;
   

3. 注释规范：

   -- 计算部门平均薪资（排除实习生）
   SELECT 
     department,
     AVG(salary) AS avg_salary -- 部门平均薪资
   FROM employees
   WHERE employee_type != 'intern' -- 排除实习生
   GROUP BY department;
   

8.2 性能守则

1. 分区过滤前置：

   -- 正确做法
   SELECT * FROM logs WHERE dt='2023-01-01' AND country='US';
   
   -- 错误做法
   SELECT * FROM logs WHERE country='US' AND dt='2023-01-01';
   

2. 避免全局排序：

   -- 使用DISTRIBUTE BY + SORT BY代替ORDER BY
   SELECT * FROM employees
   DISTRIBUTE BY department
   SORT BY salary DESC;
   

3. 合理选择文件格式：

   • 分析型查询：ORC (Optimized Row Columnar) / Parquet
   • 数据交换：JSON/CSV
   • 临时数据：TextFile

8.3 安全实践

-- 视图权限控制
CREATE VIEW finance_limited AS
SELECT id, name, department FROM employees
WHERE department = 'Finance';

GRANT SELECT ON finance_limited TO user_analyst;

-- 列级加密
CREATE TABLE sensitive_data (
    id INT,
    name STRING,
    ssn STRING ENCRYPTED WITH 'AES'
);

架构师建议：

1. 数据分层：建立raw(原始)、cleaned(清洗)、aggregated(聚合)三层数据模型
2. 统一格式：全链路使用ORC/Parquet格式 + Snappy压缩
3. 分区策略：时间分区(日/月) + 业务分区(类别/地区)
4. 监控指标：查询完成率、平均执行时间、数据倾斜比例
5. 迁移路径：逐步将复杂HQL迁移到Spark SQL获取性能提升