Hadoop生态 -- Hive HQL 详解：大数据 SQL 实战指南

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获取性能提升