CREATE TABLE partitioned_table (col1 data_type,col2 data_type,...
) PARTITIONED BY (partition_col1 data_type, partition_col2 data_type, ...)
STORED AS file_format;

• 示例：

CREATE TABLE user_behavior (user_id BIGINT,item_id BIGINT,behavior_type INT
) PARTITIONED BY (dt STRING COMMENT '日期分區', country STRING COMMENT '國家代碼')
STORED AS ORC;

• 選擇高頻過濾條件：如時間、地區等常用查詢條件
• 避免過高基數：分區數過多會導致小文件問題
• 考慮未來擴展：預留必要的分區維度
• 命名規范化：采用key=value格式，如dt=20250101

• 適用于分區值已知且固定的場景：

-- 直接加載數據到指定分區
LOAD DATA INPATH '/input/path' 
INTO TABLE partitioned_table 
PARTITION (dt='2025-01-01', country='US');-- 從查詢結果加載
INSERT INTO TABLE partitioned_table 
PARTITION (dt='2025-01-01', country='US')
SELECT user_id, item_id, behavior_type 
FROM source_table;

• 適用于分區值不確定或變化頻繁的場景：

-- 啟用動態分區配置
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;-- 動態分區插入
INSERT INTO TABLE partitioned_table 
PARTITION (dt, country)
SELECT user_id, item_id, behavior_type, event_date as dt, country_code as country
FROM source_table;

• 動態分區調優參數：

SET hive.exec.max.dynamic.partitions=1000;  -- 單個MR作業最大分區數
SET hive.exec.max.dynamic.partitions.pernode=100; -- 單節點最大分區數

-- 查看分區
SHOW PARTITIONS partitioned_table;-- 添加分區（無數據）
ALTER TABLE partitioned_table ADD PARTITION (dt='2025-01-01');-- 刪除分區
ALTER TABLE partitioned_table DROP PARTITION (dt='2025-01-01');-- 修復分區元數據
MSCK REPAIR TABLE partitioned_table;

• 實現示例

CREATE TABLE sales (order_id STRING,amount DOUBLE
) PARTITIONED BY (year INT, month INT, day INT);

• 分區剪枝（Partition Pruning）是Hive查詢優化的關鍵技術：

-- 觸發分區剪枝的查詢
EXPLAIN EXTENDED
SELECT * FROM sales 
WHERE year=2025 AND month=1;  -- 只掃描2025年1月分區

• 執行計劃關鍵指標：

Number of partitions read: 1
Total filesystem I/O: 128MB

• 具體實現：

-- 創建臨時表存儲合并結果
CREATE TABLE temp_merge LIKE partitioned_table;-- 合并數據
INSERT INTO temp_merge PARTITION(dt='2025-01-01')
SELECT * FROM partitioned_table 
WHERE dt='2025-01-01';-- 替換原分區
ALTER TABLE partitioned_table DROP PARTITION (dt='2025-01-01');
ALTER TABLE partitioned_table ADD PARTITION (dt='2025-01-01');
LOAD DATA INPATH '/temp/merge/output' 
INTO TABLE partitioned_table PARTITION (dt='2025-01-01');

• Hive 2.0+支持基于虛擬列的分區：

CREATE TABLE log_data (ip STRING,request STRING,input__file__name STRING 
) PARTITIONED BY (file_date STRING AS (regexp_extract(input__file__name, '.*/([0-9]{8})/.*', 1))
STORED AS TEXTFILE;

• 自動化管理歷史分區：

-- 創建保留策略
CREATE TABLE partitioned_table (...
) PARTITIONED BY (dt STRING)
TBLPROPERTIES ('partition.retention.period'='90d','partition.retention.policy'='delete'
);

-- 收集分區統計信息
ANALYZE TABLE partitioned_table 
PARTITION(dt='2025-01-01') 
COMPUTE STATISTICS;-- 收集列級統計
ANALYZE TABLE partitioned_table 
PARTITION(dt='2025-01-01') 
COMPUTE STATISTICS FOR COLUMNS;

• 需求：分析每日活躍用戶行為，保留最近180天數據
• 解決方案：

-- 創建時間分區表
CREATE TABLE user_events (user_id BIGINT,event_time TIMESTAMP,event_type STRING
) PARTITIONED BY (event_date DATE)
STORED AS PARQUET
TBLPROPERTIES ('partition.retention.period'='180d'
);-- 每日增量加載
INSERT INTO TABLE user_events 
PARTITION (event_date='2025-01-01')
SELECT user_id, event_time, event_type
FROM raw_events
WHERE DATE(event_time)='2025-01-01';

• 場景：按月歸檔歷史交易數據，實現冷熱數據分離

• 實現代碼：

-- 創建歸檔表
CREATE TABLE trade_archive (trade_id STRING,amount DECIMAL(18,2),...
) PARTITIONED BY (year INT, month INT)
STORED AS ORC;-- 月度歸檔過程
SET hive.exec.dynamic.partition=true;
INSERT INTO TABLE trade_archive
PARTITION (year, month)
SELECT trade_id, amount, ..., YEAR(trade_date), MONTH(trade_date)
FROM current_trades
WHERE trade_date BETWEEN '2025-01-01' AND '2025-01-31';

• 診斷方法：

-- 查看分區大小分布
SELECT partition_col, COUNT(1) 
FROM partitioned_table
GROUP BY partition_col
ORDER BY COUNT(1) DESC LIMIT 10;

• 修復方案：

-- 修復元數據
MSCK REPAIR TABLE partitioned_table;-- 手動添加分區
ALTER TABLE partitioned_table ADD PARTITION (dt='2025-01-01') 
LOCATION '/user/hive/warehouse/db/table/dt=2025-01-01';-- 重建元數據
DROP TABLE partitioned_table;
CREATE TABLE partitioned_table ...;