Hive 分区、分桶与存储

本章目标：能建分区表、写静态和动态分区、用 EXPLAIN 确认分区裁剪，理解分桶、采样、存储格式和小文件治理。 前置知识：Hive 基础、数据仓库入门 预计用时：90 分钟

离线数仓最大的性能优化不是调参数，而是少读数据。分区和分桶就是两个“少读”的手段：一个按目录跳过数据，一个按哈希定位文件。

1. 为什么需要分区

假设 dwd_order_detail 有 11 万行，一年后可能变成上千万行。业务每天只看当天数据，如果每次都全表扫描，99% 的 IO 都浪费了。

分区把表按日期拆成目录：

/warehouse/dw_demo.db/dwd_order_detail/
  dt=2018-08-01/part-00000.parquet
  dt=2018-08-02/part-00000.parquet
  dt=2018-08-03/part-00000.parquet

查 WHERE dt = '2018-08-01' 时，Hive 只读一个目录。这叫分区裁剪（Partition Pruning），是离线数仓最重要的优化，没有之一。

1.1 分区字段不是业务字段

dt 只表示数据放在哪个目录，不参与业务计算。建表时它不能写在普通字段列表里：

-- 正确
CREATE TABLE t (
    order_id STRING,
    amount   DECIMAL(14, 2)
) PARTITIONED BY (dt STRING);

-- 错误：dt 重复定义
CREATE TABLE t (
    order_id STRING,
    dt       STRING
) PARTITIONED BY (dt STRING);

查询时 dt 会像普通列一样出现在结果里，但它是从目录名解析出来的。

2. 分区表 DDL

基础建表语法、数据类型和分隔符见 Hive 基础。这里只讲分区相关的写法。

2.1 建表

CREATE TABLE IF NOT EXISTS dwd_order_detail (
    order_id                 STRING,
    item_seq                 INT,
    order_date               DATE,
    customer_id              STRING,
    product_id               STRING,
    category                 STRING,
    price                    DECIMAL(12, 2),
    quantity                 INT,
    freight                  DECIMAL(12, 2),
    item_amount              DECIMAL(14, 2),
    item_amount_with_freight DECIMAL(14, 2),
    order_status             STRING
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET;

项目演示脚本里的分区字段是 STRING，值是 yyyy-MM-dd 格式的日期字符串。生产上常用这种写法，因为目录名直观、跨引擎兼容性好。

2.2 静态分区写入

静态分区指分区值在 SQL 里写死：

INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt = '2018-08-01')
SELECT
    i.order_id,
    CAST(i.order_item_id AS INT) AS item_seq,
    CAST(o.order_purchase_timestamp AS DATE) AS order_date,
    o.customer_id,
    i.product_id,
    COALESCE(t.product_category_name_english, 'unknown') AS category,
    CAST(i.price AS DECIMAL(12, 2)) AS price,
    1 AS quantity,
    CAST(i.freight_value AS DECIMAL(12, 2)) AS freight,
    CAST(i.price AS DECIMAL(14, 2)) AS item_amount,
    CAST(CAST(i.price AS DECIMAL(14, 2))
         + CAST(i.freight_value AS DECIMAL(14, 2)) AS DECIMAL(14, 2))
        AS item_amount_with_freight,
    o.order_status
FROM ods_order_items i
JOIN ods_orders o ON i.order_id = o.order_id
LEFT JOIN ods_products p ON i.product_id = p.product_id
LEFT JOIN ods_category_translation t
       ON p.product_category_name = t.product_category_name
WHERE CAST(o.order_purchase_timestamp AS DATE) = DATE '2018-08-01'
  AND o.order_status NOT IN ('canceled', 'unavailable');

SELECT 里不需要 dt，它由 PARTITION (dt = '...') 指定。适合每天调度时传一个日期参数。

2.3 动态分区写入

动态分区指分区值来自 SELECT 的最后一列：

SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
SET hive.exec.max.dynamic.partitions = 1000;
SET hive.exec.max.dynamic.partitions.pernode = 100;

INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt)
SELECT
    i.order_id,
    CAST(i.order_item_id AS INT) AS item_seq,
    CAST(o.order_purchase_timestamp AS DATE) AS order_date,
    o.customer_id,
    i.product_id,
    COALESCE(t.product_category_name_english, 'unknown') AS category,
    CAST(i.price AS DECIMAL(12, 2)) AS price,
    1 AS quantity,
    CAST(i.freight_value AS DECIMAL(12, 2)) AS freight,
    CAST(i.price AS DECIMAL(14, 2)) AS item_amount,
    CAST(CAST(i.price AS DECIMAL(14, 2))
         + CAST(i.freight_value AS DECIMAL(14, 2)) AS DECIMAL(14, 2))
        AS item_amount_with_freight,
    o.order_status,
    date_format(CAST(o.order_purchase_timestamp AS DATE), 'yyyy-MM-dd') AS dt
FROM ods_order_items i
JOIN ods_orders o ON i.order_id = o.order_id
LEFT JOIN ods_products p ON i.product_id = p.product_id
LEFT JOIN ods_category_translation t
       ON p.product_category_name = t.product_category_name
WHERE o.order_status NOT IN ('canceled', 'unavailable')
  AND CAST(o.order_purchase_timestamp AS DATE) >= DATE '2016-09-04'
  AND CAST(o.order_purchase_timestamp AS DATE) < DATE '2018-10-18';

三个关键点：

1. 动态分区列必须放 SELECT 最后一列，顺序要和 PARTITION (dt) 对应。
2. nonstrict 模式允许所有分区都是动态的；默认 strict 要求至少有一个静态分区，防止误扫全表。
3. 限制最大分区数，防止一条 SQL 生成几十万个分区和小文件。

也可以混用：PARTITION (country = 'CN', dt) 表示静态分区在前、动态分区在后，动态列仍然放 SELECT 最后。

INSERT OVERWRITE 只覆盖命中的分区；不写分区条件就覆盖整张表，慎用。Spark 动态分区覆盖还要设置 spark.sql.sources.partitionOverwriteMode = dynamic，否则可能只留下本次写入的分区。

2.4 查看和修复分区

SHOW PARTITIONS dwd_order_detail;
DESC FORMATTED dwd_order_detail;

项目演示脚本预期生成 614 个分区，对应有效订单覆盖的 2016-09-04 到 2018-09-03 的 614 个下单日。如果文件是外部工具直接放进目录的，执行：

MSCK REPAIR TABLE dwd_order_detail;

3. 分区裁剪

3.1 用 EXPLAIN 看证据

EXPLAIN
SELECT COUNT(*)
FROM dwd_order_detail
WHERE dt = '2018-08-01';

执行计划里要找到 PartitionFilters、PartitionCount 或分区条件。看到只命中一个分区，说明裁剪生效。

如果计划里显示扫描了所有分区，说明条件没被识别。

3.2 什么写法会让裁剪失效

-- 不裁剪：对分区列做函数运算
SELECT * FROM dwd_order_detail
WHERE substr(dt, 1, 7) = '2018-08';

-- 裁剪：改成范围条件
SELECT * FROM dwd_order_detail
WHERE dt >= '2018-08-01' AND dt < '2018-09-01';

同理，CAST(dt AS DATE)、date_format(dt, ...)、concat(dt, ...) 都可能让分区裁剪失效。分区列要裸用，比较值放右边。

3.3 分区裁剪和列裁剪要一起用

-- 只读两列，不读整行
SELECT dt, SUM(item_amount_with_freight)
FROM dwd_order_detail
WHERE dt = '2018-08-01'
GROUP BY dt;

列存格式（Parquet/ORC）只读命中的列，叫列裁剪。分区裁剪减少文件，列裁剪减少每行读取的字节，两者叠加效果最好。多表 JOIN 时，优化器还可能把左表的分区条件下推到右表，这叫动态分区裁剪（DPP），知道概念即可。

4. 分桶

4.1 分桶是什么

分桶按哈希把数据分到固定数量的文件里：

CREATE TABLE fact_order_bucket (
    order_id     STRING,
    customer_id  STRING,
    order_amount DECIMAL(14, 2)
)
CLUSTERED BY (customer_id) INTO 8 BUCKETS
STORED AS ORC;

写入时，Hive 计算 hash(customer_id) % 8，把行放到对应的桶文件。桶是文件，分区是目录，两者可以叠加：

CREATE TABLE fact_order_part_bucket (
    ...
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (customer_id) INTO 8 BUCKETS
STORED AS ORC;

每个分区下会有 8 个桶文件。

4.2 分桶的用途

采样：

SELECT *
FROM fact_order_bucket
TABLESAMPLE (BUCKET 1 OUT OF 8);

只读 1/8 的桶，适合大数据量探查和测试。采样是按桶取的，不是随机行采样，但同一客户的数据会落在同一桶里，适合按用户分析。

Map 端 JOIN：

• 小表广播：/*+ MAPJOIN(dim) */，小表进内存，避免 shuffle。
• 桶 Map JOIN：两张表都按 JOIN 键分桶，且桶数成倍数关系。
• SMB JOIN：两张表都按 JOIN 键分桶且排序，桶数相同，直接归并。

相关参数：

SET hive.auto.convert.join = true;
SET hive.mapjoin.smalltable.filesize = 25000000;
SET hive.optimize.bucketmapjoin = true;

注意：分桶只保证哈希分文件，不保证文件内有序。要做 SMB JOIN，建表时还要加 SORTED BY (customer_id) ASC，并开启 hive.optimize.bucketmapjoin.sortedmerge。

4.3 分桶的约束

• 桶数一旦确定，改桶数要重建表。
• 两表桶数要相同或成整数倍，才能做桶 JOIN。
• 桶数太多会产生大量小文件。
• 分桶对 JOIN 和采样有用，对单纯的范围查询帮助不大。

5. 存储格式：TextFile、Parquet、ORC

5.1 列存为什么快

行存按整行读，查一列也要读完整行；列存只读需要的列。分析查询通常只碰少数几列、扫描很多行，列存还能按列压缩、把过滤条件下推到文件层（谓词下推），优势明显。

5.2 建表指定格式和压缩

CREATE TABLE dws_category_1d (
    category  STRING,
    order_cnt BIGINT,
    gmv       DECIMAL(16, 2)
)
PARTITIONED BY (dt STRING)
STORED AS PARQUET
TBLPROPERTIES ('parquet.compression' = 'SNAPPY');

ORC 只需把 STORED AS 换成 ORC，属性换成 'orc.compress' = 'SNAPPY'。ODS 常用 TextFile 保留原始格式；DWD 以后转 Parquet 或 ORC，转换后要核对行数和金额。

6. 小文件问题

6.1 产生与危害

动态分区一次写很多分区、并行度太高、频繁小批量 INSERT，都会产生小文件。危害有三层：NameNode 元数据压力大、每个文件都要单独打开和调度、查询扫几千个小文件远慢于扫几个大文件。

6.2 治理手段

控制写入文件数：

SET hive.merge.mapfiles = true;
SET hive.merge.mapredfiles = true;
SET hive.merge.size.per.task = 256000000;
SET hive.merge.smallfiles.avgsize = 16000000;

参数含义是：任务结束时如果小文件平均大小低于阈值，再启动一轮合并任务。

按分区重新分布：

INSERT OVERWRITE TABLE dwd_order_detail PARTITION (dt = '2018-08-01')
SELECT ... FROM dwd_order_detail
WHERE dt = '2018-08-01'
DISTRIBUTE BY floor(rand() * 4);

DISTRIBUTE BY 控制 reducer 分布，能把输出控制在固定文件数。Spark 里用 repartition(n) 或 coalesce(n)。

合并已有文件：

ALTER TABLE dwd_order_detail PARTITION (dt = '2018-08-01') CONCATENATE;

CONCATENATE 适合 ORC/RCFile，能把小文件合并成大文件。Parquet 表通常用重写分区的方式合并。

减少分区粒度：不要按小时、按用户、按订单分区，分区数会爆炸。日期分区通常够用，更细的分析放 DWD 明细。

提示：小文件治理的目标不是“一个文件”，而是“每个文件接近 HDFS 块大小（常见 128 MB 或 256 MB）”。文件太大也不好，任务并行度上不去。

7. 常见坑

1. 动态分区列没放最后：报错或数据写错分区。
2. 忘记开动态分区：默认 strict，报错 Dynamic partition strict mode requires at least one static partition column。
3. 分区数爆炸：按小时或按用户分区，产生几十万小文件。
4. 对分区列做函数：分区裁剪失效，全表扫描。
5. INSERT INTO 每日追加：重跑数据重复，应该用覆盖写。
6. 桶数不一致：桶 JOIN 退化，甚至结果错误。
7. 只做分区不做文件合并：查询依旧被小文件拖慢。

8. 在线练习

自测：

• 能为订单明细写出按 dt 分区的 DDL，并说明分区列为什么不能重复写在普通字段里。
• 能区分静态分区和动态分区，写出各自的 INSERT OVERWRITE 语句。
• 能解释分桶对 JOIN 和采样有什么用。
• 能说出小文件问题的成因和至少两种治理办法。

9. 面试延伸

面试题：分区和分桶的区别？

是什么：分区按字段值把数据拆成目录，分桶按哈希把数据拆成固定数量的文件。

解决什么问题：分区用于裁剪扫描范围，适合日期、地区等低基数、查询常用的字段；分桶用于采样、Map 端 JOIN 和均匀分布，适合用户 ID 等高基数字段。分区数可以动态增长，桶数建表时固定。

我的实操边界：项目里建过按天分区的明细表和动态分区写入，跑过 SHOW PARTITIONS 和 EXPLAIN；分桶只在本地建过示例表，没有在生产环境做过分桶 JOIN 调优。

面试题：动态分区怎么用？为什么默认是 strict？

是什么：动态分区让分区值来自 SELECT 最后一列，一次写入多个分区。

解决什么问题：适合批量回刷多天数据。默认 strict 要求至少一个静态分区，是为了防止用户漏写分区条件，一条 SQL 生成海量分区或覆盖整表。使用时设置 hive.exec.dynamic.partition=true 和 mode=nonstrict，并限制最大分区数。

我的实操边界：我在本地用 INSERT OVERWRITE ... PARTITION (dt) 一次写入 614 个分区，设置过 spark.sql.sources.partitionOverwriteMode=dynamic；没有在生产环境处理过分区数超限的故障。

面试题：小文件有什么问题，怎么解决？

是什么：小文件是远小于 HDFS 块大小的文件，通常几十 KB 到几 MB。

解决什么问题：小文件过多会增加 NameNode 元数据压力，增加任务启动和文件打开开销。治理手段包括合并任务、控制并行度、重写分区、使用 CONCATENATE、减少分区粒度。

我的实操边界：项目数据量小，没有真正遇到小文件故障；我理解合并参数和重写分区的原理，但没有在生产集群上做过 NameNode 级别的调优。

10. 本章验收

• 能解释“分区 = 目录”，并写出按天分区的 DDL
• 能写静态分区和动态分区写入 SQL，知道动态分区列必须放最后
• 能说出 hive.exec.dynamic.partition.mode=nonstrict 的作用
• 能用 EXPLAIN 找到分区裁剪证据，并改写让裁剪失效的 SQL
• 能解释分桶、采样、桶 Map JOIN 的原理和限制
• 能对比 TextFile、Parquet、ORC，并说出至少三种小文件治理手段