hive的sql调优

### **Hive SQL 调优核心方法论** #### **1. 执行计划分析（关键第一步）** ```sql -- 查看执行计划（重点关注Stage划分） EXPLAIN EXTENDED SELECT user_id, count(*) FROM user_behavior WHERE dt='2023-12-01' GROUP BY user_id; -- 可视化依赖关系（Hive 4.0+） EXPLAIN DEPENDENCY SELECT a.* FROM table_a a JOIN table_b b ON a.id=b.id; ``` #### **2. 数据倾斜解决方案** ```sql -- 倾斜Key单独处理（经典方案） SELECT user_id, sum(amount) FROM ( SELECT user_id, amount FROM orders WHERE user_id NOT IN ('超长user_id列表') UNION ALL SELECT 'special_user' AS user_id, sum(amount) FROM orders WHERE user_id IN ('超长user_id列表') ) t GROUP BY user_id; -- 随机数打散（大表Join优化） SELECT /*+ MAPJOIN(b) */ a.* FROM big_table a JOIN small_table b ON concat(a.key, cast(rand()*10 as int)) = b.key; ``` #### **3. 资源控制参数** ```shell # 关键参数设置（应写在作业提交脚本中） set hive.exec.parallel=true; # 开启阶段并行 set hive.exec.parallel.thread.number=16; # 并行度 set mapreduce.map.memory.mb=4096; # Map内存 set mapreduce.reduce.memory.mb=8192; # Reduce内存 set hive.auto.convert.join=true; # 自动MapJoin ``` #### **4. 存储优化技巧** ```sql -- ORC高级特性（需Hive 3.0+） CREATE TABLE optimized_table ( id BIGINT, name STRING ) STORED AS ORC TBLPROPERTIES ( "orc.compress"="ZSTD", # 压缩算法 "orc.create.index"="true", # 建索引 "orc.bloom.filter.columns"="id" # 布隆过滤器 ); -- 分区裁剪优化（避免全表扫描） SELECT * FROM partitioned_table WHERE dt BETWEEN '2023-01-01' AND '2023-01-31' AND region IN ('华东','华北'); ``` #### **5. 执行引擎对比** | 引擎 | 适用场景 | 配置示例 | |------------|-------------------------|-----------------------------| | **MR** | 稳定可靠但慢 | set hive.execution.engine=mr; | | **Tez** | DAG优化（中等数据量） | set hive.execution.engine=tez;| | **Spark** | 大规模数据 | set hive.execution.engine=spark;| #### **6. 实战优化案例** ```sql -- 优化前（产生数据倾斜） SELECT a.user_id, b.product_name, count(*) FROM user_clicks a JOIN product_info b ON a.product_id = b.product_id GROUP BY a.user_id, b.product_name; -- 优化后（两阶段聚合） WITH stage1 AS ( SELECT product_id, user_id, count(*) as cnt FROM user_clicks GROUP BY product_id, user_id ) SELECT s.user_id, p.product_name, sum(s.cnt) as total FROM stage1 s JOIN product_info p ON s.product_id = p.product_id GROUP BY s.user_id, p.product_name; ```