Hive 数据仓库
什么是 Hive
Hive 是基于 Hadoop 的数据仓库工具，由 Facebook 开源。它将结构化数据文件映射为数据库表，并提供 HiveQL（类 SQL 查询语言），将 SQL 翻译成 MapReduce/Tez/Spark 任务执行。
核心价值：让不懂 MapReduce 的数据分析师也能处理海量数据。
适用场景：
- 离线数仓建设（T+1 报表）
- 大规模数据 ETL
- 数据探索与分析
- 不适合：实时查询、随机读写、OLTP
架构设计
┌─────────────────────────────────────────────────────────────┐
│ Hive 架构 │
│ │
│ ┌──────────────────────────────────────────────────────┐ │
│ │ 用户接口 │ │
│ │ CLI（hive shell） JDBC/ODBC HiveServer2 WebUI │ │
│ └──────────────────────────┬───────────────────────────┘ │
│ │ │
│ ┌──────────────────────────▼───────────────────────────┐ │
│ │ Driver（驱动层） │ │
│ │ 解析器（Parser）→ 编译器（Compiler）→ 优化器 │ │
│ │ → 执行器（Executor） │ │
│ └──────────────────────────┬───────────────────────────┘ │
│ │ │
│ ┌──────────────────────────▼───────────────────────────┐ │
│ │ Metastore（元数据） │ │
│ │ 表结构、分区信息、列信息 → 存储在 MySQL/Derby │ │
│ └──────────────────────────┬───────────────────────────┘ │
│ │ │
│ ┌──────────────────────────▼───────────────────────────┐ │
│ │ 执行引擎（MapReduce / Tez / Spark） │ │
│ └──────────────────────────┬───────────────────────────┘ │
│ │ │
│ ┌──────────────────────────▼───────────────────────────┐ │
│ │ HDFS │ │
│ └──────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘数据仓库分层
Hive 通常按照以下分层架构组织数据：
ODS（操作数据层）
原始数据，直接从业务系统同步，不做处理
保留原始格式，通常按天分区
DWD（数据明细层）
对 ODS 数据进行清洗、标准化
去除脏数据，统一字段格式
DWS（数据汇总层）
按主题对 DWD 数据进行轻度聚合
例：用户每日行为汇总、商品每日销售汇总
ADS（应用数据层）
面向具体业务需求的数据集市
直接供 BI 报表、API 使用HiveQL 核心语法
DDL 操作
sql
-- 创建数据库
CREATE DATABASE IF NOT EXISTS dw_db
COMMENT '数据仓库'
LOCATION '/user/hive/warehouse/dw_db.db';
-- 创建内部表（Managed Table）
-- 删除表时，数据也会被删除
CREATE TABLE IF NOT EXISTS ods_user_log (
user_id STRING COMMENT '用户ID',
event_type STRING COMMENT '事件类型',
event_time BIGINT COMMENT '事件时间戳',
page_url STRING COMMENT '页面URL',
ip STRING COMMENT 'IP地址'
)
COMMENT '用户行为日志原始表'
PARTITIONED BY (dt STRING COMMENT '日期分区')
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
STORED AS ORC
TBLPROPERTIES ('orc.compress' = 'SNAPPY');
-- 创建外部表（External Table）
-- 删除表时，数据不会被删除（推荐用于 ODS 层）
CREATE EXTERNAL TABLE ods_order (
order_id STRING,
user_id STRING,
amount DECIMAL(10,2),
status INT,
create_time STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/data/ods/order/';
-- 添加分区
ALTER TABLE ods_order ADD PARTITION (dt='2024-01-01')
LOCATION '/data/ods/order/dt=2024-01-01';
-- 修复分区（自动发现 HDFS 上的分区目录）
MSCK REPAIR TABLE ods_order;DML 操作
sql
-- 插入数据（覆盖写）
INSERT OVERWRITE TABLE dwd_user_log PARTITION (dt='2024-01-01')
SELECT
user_id,
event_type,
FROM_UNIXTIME(event_time, 'yyyy-MM-dd HH:mm:ss') AS event_time,
page_url,
ip
FROM ods_user_log
WHERE dt = '2024-01-01'
AND user_id IS NOT NULL
AND event_type IN ('click', 'view', 'purchase');
-- 动态分区插入
SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;
INSERT OVERWRITE TABLE dwd_user_log PARTITION (dt)
SELECT user_id, event_type, event_time, page_url, ip, dt
FROM ods_user_log
WHERE dt >= '2024-01-01';
-- 多表插入（一次扫描，写入多张表）
FROM ods_user_log
INSERT OVERWRITE TABLE dwd_click_log PARTITION (dt='2024-01-01')
SELECT user_id, page_url, event_time WHERE event_type = 'click'
INSERT OVERWRITE TABLE dwd_purchase_log PARTITION (dt='2024-01-01')
SELECT user_id, amount, event_time WHERE event_type = 'purchase';常用查询
sql
-- 窗口函数
SELECT
user_id,
event_time,
event_type,
-- 用户当天第几次行为
ROW_NUMBER() OVER (PARTITION BY user_id, dt ORDER BY event_time) AS rn,
-- 用户累计行为次数
COUNT(*) OVER (PARTITION BY user_id ORDER BY event_time
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_cnt,
-- 上一次行为时间
LAG(event_time, 1) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_time
FROM dwd_user_log
WHERE dt = '2024-01-01';
-- 用户留存率计算
SELECT
a.dt AS first_day,
COUNT(DISTINCT a.user_id) AS day0_users,
COUNT(DISTINCT b.user_id) AS day1_users,
ROUND(COUNT(DISTINCT b.user_id) / COUNT(DISTINCT a.user_id), 4) AS retention_rate
FROM (
SELECT user_id, dt FROM dwd_user_log GROUP BY user_id, dt
) a
LEFT JOIN (
SELECT user_id, dt FROM dwd_user_log GROUP BY user_id, dt
) b ON a.user_id = b.user_id
AND DATEDIFF(b.dt, a.dt) = 1
WHERE a.dt = '2024-01-01'
GROUP BY a.dt;存储格式
| 格式 | 特点 | 适用场景 |
|---|---|---|
| TextFile | 文本，可读，无压缩 | 原始数据导入（ODS） |
| SequenceFile | 二进制，支持压缩 | 中间结果 |
| ORC | 列式，高压缩，支持索引 | 数仓（推荐） |
| Parquet | 列式，与 Spark 生态兼容好 | 数仓、数据湖 |
| Avro | 行式，Schema 演进好 | 数据序列化 |
生产推荐：ORC + SNAPPY 压缩（Hive 生态）或 Parquet + SNAPPY（Spark 生态）
sql
-- 创建 ORC 表
CREATE TABLE dws_user_daily (
user_id STRING,
pv BIGINT,
uv BIGINT,
purchase_cnt BIGINT,
total_amount DECIMAL(12,2)
)
PARTITIONED BY (dt STRING)
STORED AS ORC
TBLPROPERTIES (
'orc.compress' = 'SNAPPY',
'orc.stripe.size' = '67108864',
'orc.row.index.stride' = '10000'
);性能调优
开启 Tez 引擎
bash
# hive-site.xml 或 session 级别设置
SET hive.execution.engine=tez; # 比 MapReduce 快 2-10x
# 或
SET hive.execution.engine=spark;分区裁剪
sql
-- 必须在 WHERE 中指定分区条件，否则全表扫描
SELECT * FROM ods_user_log WHERE dt = '2024-01-01'; -- ✅ 分区裁剪
SELECT * FROM ods_user_log WHERE event_type = 'click'; -- ❌ 全表扫描处理数据倾斜
sql
-- 开启 Map 端 Join（小表 < 25MB）
SET hive.auto.convert.join = true;
SET hive.mapjoin.smalltable.filesize = 26214400;
-- 开启倾斜 Join
SET hive.optimize.skewjoin = true;
SET hive.skewjoin.key = 100000;
-- 手动处理倾斜：给倾斜 key 加随机前缀
SELECT
REGEXP_REPLACE(a.user_id, '^[0-9]+_', '') AS user_id,
a.cnt
FROM (
SELECT
CONCAT(CAST(FLOOR(RAND() * 10) AS STRING), '_', user_id) AS user_id,
COUNT(*) AS cnt
FROM dwd_user_log
GROUP BY CONCAT(CAST(FLOOR(RAND() * 10) AS STRING), '_', user_id)
) a;合并小文件
sql
-- 输出时合并小文件
SET hive.merge.mapfiles = true;
SET hive.merge.mapredfiles = true;
SET hive.merge.size.per.task = 256000000; -- 256MB
SET hive.merge.smallfiles.avgsize = 16000000; -- 16MB关键配置
sql
-- 并行执行（无依赖的 Stage 并行跑）
SET hive.exec.parallel = true;
SET hive.exec.parallel.thread.number = 8;
-- 本地模式（数据量小时在本地执行，无需提交集群）
SET hive.exec.mode.local.auto = true;
-- 向量化查询（批量处理，提升 CPU 效率）
SET hive.vectorized.execution.enabled = true;
-- CBO 优化器（基于代价的优化）
SET hive.cbo.enable = true;
SET hive.compute.query.using.stats = true;