【大数据技术详解】——Hive 离线数仓分层（学习笔记）

张

张建站

2026/7/22 6:46:48

10分钟阅读

目录 Hive 离线数仓分层操作规范一、各层定义与职责二、示例业务用户行为日志分析三、分层建表与 ETL 实现✅ 步骤 1ODS 层 —— 原始日志表1.1 创建 ODS 表外部表1.2 加载数据每日分区1.3 添加分区✅ 步骤 2DWD 层 —— 清洗后的明细表2.1 创建 DWD 表2.2 ETL从 ODS 解析并清洗✅ 步骤 3DWS 层 —— 轻度汇总表3.1 创建 DWS 表按天、按事件聚合3.2 ETL从 DWD 聚合✅ 步骤 4ADS 层 —— 应用指标表面向报表4.1 创建 ADS 表最终展示字段4.2 ETL多指标整合可来自多个 DWS 表四、导出 ADS 到 MySQL方式使用 Spark推荐或 SqoopSpark 导出示例PySpark五、调度与运维建议1. 任务调度Airflow 示例 DAG2. 监控项六、附录目录结构建议七、总结 Hive 离线数仓分层操作规范目标构建清晰、可维护、高性能的离线数据仓库支撑报表与 BI 可视化适用场景日志分析、用户行为、业务指标统计等批处理任务一、各层定义与职责表格层级全称职责特点ODSOperational Data Store存放原始数据不做清洗或仅做轻度清洗与源系统结构一致保留全量历史DWDData Warehouse Detail清洗、脱敏、标准化、维度退化后的明细事实表统一命名、统一编码、统一单位DWSData Warehouse Summary按主题/维度聚合的轻度汇总表如日活、订单总额宽表设计减少后续 JOINADSApplication Data Service面向具体业务场景的最终指标表直接对接报表、API、MySQL二、示例业务用户行为日志分析原始日志格式JSON{user_id:U1001,event:click,page:home,ts:1710489600}三、分层建表与 ETL 实现✅ 步骤 1ODS 层 —— 原始日志表1.1 创建 ODS 表外部表-- 数据库 CREATE DATABASE IF NOT EXISTS ods; USE ods; -- 外部表指向 HDFS 路径 CREATE EXTERNAL TABLE ods.user_log_ods ( raw_data STRING COMMENT 原始 JSON 字符串 ) PARTITIONED BY (dt STRING) STORED AS TEXTFILE LOCATION /data/ods/user_log;1.2 加载数据每日分区# 模拟 HDFS 写入 hdfs dfs -put /local/logs/2026-03-15.log /data/ods/user_log/dt2026-03-15/1.3 添加分区ALTER TABLE ods.user_log_ods ADD PARTITION (dt2026-03-15);✅ 步骤 2DWD 层 —— 清洗后的明细表2.1 创建 DWD 表CREATE DATABASE IF NOT EXISTS dwd; USE dwd; CREATE TABLE dwd.user_log_dwd ( user_id STRING COMMENT 用户ID, event STRING COMMENT 事件类型, page STRING COMMENT 页面, ts BIGINT COMMENT 时间戳, dt STRING COMMENT 日期分区 ) PARTITIONED BY (dt STRING) STORED AS PARQUET TBLPROPERTIES (parquet.compressionSNAPPY);2.2 ETL从 ODS 解析并清洗INSERT OVERWRITE TABLE dwd.user_log_dwd PARTITION (dt2026-03-15) SELECT get_json_object(raw_data, $.user_id) AS user_id, get_json_object(raw_data, $.event) AS event, get_json_object(raw_data, $.page) AS page, CAST(get_json_object(raw_data, $.ts) AS BIGINT) AS ts, 2026-03-15 AS dt FROM ods.user_log_ods WHERE dt 2026-03-15 AND get_json_object(raw_data, $.user_id) IS NOT NULL;✅清洗规则过滤空 user_id、转换时间格式、统一事件命名如 click → CLICK✅ 步骤 3DWS 层 —— 轻度汇总表3.1 创建 DWS 表按天、按事件聚合CREATE DATABASE IF NOT EXISTS dws; USE dws; CREATE TABLE dws.user_event_daily ( event_type STRING COMMENT 事件类型, active_users BIGINT COMMENT 活跃用户数, event_count BIGINT COMMENT 事件次数 ) PARTITIONED BY (dt STRING) STORED AS PARQUET;3.2 ETL从 DWD 聚合INSERT OVERWRITE TABLE dws.user_event_daily PARTITION (dt2026-03-15) SELECT event AS event_type, COUNT(DISTINCT user_id) AS active_users, COUNT(*) AS event_count FROM dwd.user_log_dwd WHERE dt 2026-03-15 GROUP BY event;✅ 步骤 4ADS 层 —— 应用指标表面向报表4.1 创建 ADS 表最终展示字段CREATE DATABASE IF NOT EXISTS ads; USE ads; CREATE TABLE ads.daily_report ( stat_date STRING COMMENT 统计日期, total_users BIGINT COMMENT 总活跃用户, click_count BIGINT COMMENT 点击次数, home_uv BIGINT COMMENT 首页UV ) STORED AS PARQUET;4.2 ETL多指标整合可来自多个 DWS 表INSERT OVERWRITE TABLE ads.daily_report SELECT 2026-03-15 AS stat_date, SUM(active_users) AS total_users, SUM(CASE WHEN event_type click THEN event_count ELSE 0 END) AS click_count, SUM(CASE WHEN event_type click AND page home THEN 1 ELSE 0 END) AS home_uv FROM ( SELECT event_type, active_users, event_count FROM dws.user_event_daily WHERE dt 2026-03-15 ) t; 实际中可 JOIN 多个 DWS 表如订单、支付生成综合日报表。四、导出 ADS 到 MySQL方式使用 Spark推荐或 SqoopSpark 导出示例PySparkfrom pyspark.sql import SparkSession spark SparkSession.builder.appName(ads_to_mysql).getOrCreate() df spark.sql(SELECT * FROM ads.daily_report) df.write \ .format(jdbc) \ .option(url, jdbc:mysql://mysql-host:3306/report_db?useSSLfalse) \ .option(dbtable, daily_report) \ .option(user, report_user) \ .option(password, secure_password) \ .mode(overwrite) \ .save()⚠️注意确保 MySQL 表结构与 Hive ADS 表字段兼容如 STRING → VARCHAR(255)。五、调度与运维建议1.任务调度Airflow 示例 DAGfrom airflow import DAG from airflow.providers.apache.hive.operators.hive import HiveOperator from airflow.operators.bash import BashOperator with DAG(user_log_etl, schedule_intervaldaily) as dag: ods_to_dwd HiveOperator( task_idods_to_dwd, hqlINSERT OVERWRITE ... FROM ods.user_log_ods WHERE dt{{ ds }} ) dwd_to_dws HiveOperator(task_iddwd_to_dws, hql...) dws_to_ads HiveOperator(task_iddws_to_ads, hql...) export_to_mysql BashOperator(task_idexport_to_mysql, bash_commandspark-submit ...) ods_to_dwd dwd_to_dws dws_to_ads export_to_mysql2.监控项各层数据量环比波动±20% 告警任务执行时长超阈值告警MySQL 导出成功状态六、附录目录结构建议/data/ ├── ods/ │ └── user_log/dt2026-03-15/ ├── dwd/ │ └── user_log_dwd/dt2026-03-15/ ├── dws/ │ └── user_event_daily/dt2026-03-15/ └── ads/ └── daily_report/七、总结层级关键动作输出ODS原始数据接入1:1 原始日志DWD清洗、解析、标准化高质量明细DWS聚合、宽表主题汇总ADS指标对齐业务报表就绪数据✅优势解耦原始数据与业务逻辑提升查询性能避免重复 JOIN便于数据治理与血缘追踪提示在生产环境中建议结合Apache Atlas做元数据管理DataX / Seatunnel做异构数据同步Prometheus Grafana做任务监控。