企业官网建设流程全解析

一、项目

这次学习的内容是地铁业务数仓建模，主要完成三个层次的建设：

DWD 明细数据层

DIM 维度数据层

DWS 数据服务层

整个数据流程可以简单表示为：

业务系统 MySQL ↓ ODS 原始数据层 ↓ DWD 明细数据层 ↓ DIM 维度数据层 ↓ DWS 汇总宽表层

业务系统中保存的是原始业务数据，例如用户信息、地铁进出站记录等。数据进入数仓后，需要经过清洗、整理和关联，最后形成方便统计分析的宽表。

二、ODS 层：保存原始数据

ODS 层主要保存从业务系统采集过来的原始数据。

例如，业务系统中的 MySQL 里可能有以下两张表：

ods_szt_data ods_szt_user

其中：

ods_szt_data：地铁刷卡或乘坐记录；

ods_szt_user：用户基本信息。

数据采集工具会把 MySQL 中的数据同步到 Hive 的 ODS 层。

ODS 层的特点是：

1. 尽量保留原始数据；
2. 不做过多修改；
3. 方便后续数据清洗；
4. 出现问题时可以追溯源数据。

因此，ODS 层可以理解为数据仓库中的“原始资料库”。

三、DWD 层：保存清洗后的明细数据

DWD 是 Data Warehouse Detail 的缩写，中文可以理解为数据明细层。

这一层主要做数据清洗和规范化处理，例如：

• 去除重复数据；
• 过滤无效记录；
• 处理空值；
• 统一字段类型；
• 规范日期格式；
• 对进站和出站记录进行整理。

经过处理后，DWD 层保存的是比较干净的明细数据。

例如，地铁乘坐明细表中可能包含：

卡号 车牌号 进站线路 出站线路 进站站点 出站站点 进站时间 出站时间 交易金额 乘坐时长

DWD 层的数据仍然比较详细，一条记录通常代表一次地铁乘坐行为。

四、DIM 层：保存用户维度信息

DIM 是 Dimension 的缩写，中文叫维度层。

这次项目中的维度表主要是用户维度表：

dim.dim_szt_user

用户维度表中可以保存：

卡号 年龄 性别 用户类型 所在区域 其他用户属性

其中，card_no是连接地铁乘坐记录和用户信息的关键字段。

例如，DWD 层中的记录可能只有：

DIM 层中保存了这个用户的基本信息：

将两张表关联以后，就可以知道这个用户的年龄和性别。

五、DWS 层：构建地铁乘坐宽表

DWS 是 Data Warehouse Service 的缩写，可以理解为数据服务层。

DWS 层一般会把多个明细表和维度表进行关联，生成一张字段比较丰富的宽表，方便后续报表和分析使用。

本次创建的表是：

dws.dws_take_subway_detail

建表语句如下：

CREATE EXTERNAL TABLE dws.dws_take_subway_detail ( card_no STRING COMMENT '用户编号', car_no STRING COMMENT '车牌号', take_year STRING COMMENT '年', take_month STRING COMMENT '月', take_day STRING COMMENT '乘坐日期', in_company_name STRING COMMENT '进站线路', out_company_name STRING COMMENT '出站线路', in_station STRING COMMENT '进站站点', out_station STRING COMMENT '出站站点', in_time STRING COMMENT '进站时间', out_time STRING COMMENT '出站时间', in_equ_no STRING COMMENT '进站设备编码', out_equ_no STRING COMMENT '出站设备编码', age INT COMMENT '年龄', sex STRING COMMENT '性别', conn_mark INT COMMENT '联程标记', deal_money DOUBLE COMMENT '交易金额', take_time DOUBLE COMMENT '用时' ) PARTITIONED BY ( dt STRING COMMENT '按天分区' ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\u0001' STORED AS ORC LOCATION '/daas/dws/dws_take_subway_detail';

六、建表语句解析

1. CREATE EXTERNAL TABLE

CREATE EXTERNAL TABLE

表示创建一张外部表。

外部表和内部表的主要区别是：

删除内部表时，表结构和数据通常都会被删除；

删除外部表时，一般只删除 Hive 中的表定义，HDFS 中的数据仍然保留。

因此，外部表更加适合保存重要的业务数据。

2. 字段定义

例如：

card_no STRING COMMENT '用户编号'

表示创建一个叫作card_no的字段，数据类型是STRING，注释是“用户编号”。

字段后面的COMMENT主要是为了方便查看表结构和理解字段含义。

3. 分区字段

PARTITIONED BY ( dt STRING COMMENT '按天分区' )

表示按照dt字段进行分区。

例如：

dt=20180901 dt=20180902 dt=20180903

如果查询某一天的数据：

SELECT * FROM dws.dws_take_subway_detail WHERE dt = '20180901';

Hive 就可以只读取对应日期的分区，而不是扫描整张表。

分区的主要作用是减少数据扫描量，提高查询效率。

4. ORC 存储格式

STORED AS ORC

表示使用 ORC 格式保存数据。

ORC 是 Hive 中常用的列式存储格式，具有以下优点：

1.压缩率较高；

2.查询速度较快；

3.可以只读取需要的列；

4.支持统计信息；

5.适合大数据分析。

相比普通文本文件，ORC 更适合用于数仓中的明细表和汇总表。

5. LOCATION

LOCATION '/daas/dws/dws_take_subway_detail';

表示指定数据在 HDFS 中的保存路径。

Hive 的表结构保存在 Metastore 中，而真正的数据存放在 HDFS 的这个目录下。

七、使用 INSERT OVERWRITE 写入 DWS 表

完成建表以后，需要把 DWD 层和 DIM 层的数据写入 DWS 表。

核心 SQL 如下：

INSERT OVERWRITE TABLE dws.dws_take_subway_detail PARTITION (dt = '20180901') SELECT a.card_no, a.car_no, year(a.take_day) AS take_year, month(a.take_day) AS take_month, a.take_day, a.in_company_name, a.out_company_name, a.in_station, a.out_station, a.in_time, a.out_time, a.in_equ_no, a.out_equ_no, b.age, b.sex, a.conn_mark, a.deal_money, a.take_time FROM ( SELECT * FROM dwd.dwd_take_subway_detail WHERE dt = '20180901' ) AS a JOIN ( SELECT * FROM dim.dim_szt_user WHERE dt = '20180901' ) AS b ON a.card_no = b.card_no;

这条语句主要完成两件事：

1. 从 DWD 层读取地铁乘坐明细；
2. 通过卡号关联 DIM 层的用户信息。

最终生成一张包含乘坐信息和用户信息的宽表。

八、SQL 代码逐段解析

1. INSERT OVERWRITE

INSERT OVERWRITE TABLE dws.dws_take_subway_detail PARTITION (dt = '20180901')

表示将查询结果写入 DWS 表的20180901分区。

OVERWRITE表示覆盖原分区中的数据。

如果这一天的分区已经存在，执行后会用新的结果替换原来的数据。

2. 获取年份和月份

year(a.take_day) AS take_year, month(a.take_day) AS take_month,

这里从乘坐日期中提取年份和月份。

例如：

take_day = 2018-09-01 take_year = 2018 take_month = 9

这样后续进行按年、按月统计时会更加方便。

3. 读取 DWD 层数据

FROM ( SELECT * FROM dwd.dwd_take_subway_detail WHERE dt = '20180901' ) AS a

这里给 DWD 层数据起了一个别名a。

同时使用：

WHERE dt = '20180901'

只读取当天的数据，避免扫描其他日期的数据。

4. 读取 DIM 层数据

JOIN ( SELECT * FROM dim.dim_szt_user WHERE dt = '20180901' ) AS b

这里读取用户维度表，并给它起别名b。

同样只读取当天的分区数据。

5. 根据卡号关联两张表

ON a.card_no = b.card_no

这句话表示使用card_no作为关联条件。

DWD 层中的card_no是用户乘坐地铁时的卡号，DIM 层中的card_no是用户基本信息中的卡号。

两个字段相同，就可以把两张表拼接起来。

最终：

a表提供乘坐信息 b表提供年龄和性别

九、Map Join

代码中的注释提到了 Map Join：

-- map join：Hive在进行表关联时， -- 自动将小表加载到内存中， -- 在Map端进行关联，不产生Reduce

普通 Join 通常需要经过 Shuffle，把相同的关联键发送到同一个 Reduce 任务中。

Map Join 的思路是：

把较小的表加载到每个 Map 任务的内存中 Map 任务直接完成关联 不再进行 Reduce 阶段

这样可以减少网络传输和 Reduce 阶段，因此执行速度通常更快。

例如：

DWD 地铁乘坐明细：数据量很大 DIM 用户表：数据量相对较小

这种情况下，就可以考虑使用 Map Join。

不过，是否真正启用自动 Map Join，还与 Hive 的其他参数有关。例如：

set hive.auto.convert.join.noconditionaltask = false;

这条语句主要用于调整自动 Map Join 的条件任务行为，实际项目中还需要结合表大小和其他 Hive 参数一起判断。

十、 DWS 要做成宽表

如果不建立 DWS 宽表，每次分析时都需要重新关联 DWD 表和 DIM 表。

例如，每次查询地铁用户画像，都要重新执行：

DWD 地铁明细 JOIN DIM 用户信息

这样会增加查询时间和计算成本。

提前生成 DWS 宽表后，后续查询就会简单很多。

例如，统计不同性别用户的地铁乘坐次数：

SELECT sex, COUNT(*) AS take_num FROM dws.dws_take_subway_detail WHERE dt = '20180901' GROUP BY sex;

统计不同年龄段的乘坐金额：

SELECT age, SUM(deal_money) AS total_money FROM dws.dws_take_subway_detail WHERE dt = '20180901' GROUP BY age;

DWS 层的作用就是把常用的数据提前整理好，为报表、分析和数据应用提供方便。

十一、完整的数据流转过程

本次项目的数据流转过程可以总结为：

业务系统 MySQL
数据采集工具
ODS 原始数据层
数据清洗
DWD 地铁乘坐明细表
DIM 用户维度表
按 card_no 进行关联
DWS 地铁乘坐宽表
报表统计和数据分析

每一层都有不同的作用：

十二、总结

这次学习的重点不是单独记住某一条 SQL，而是理解数仓分层的整体思路。

DWD 层解决的是：

原始数据不规范，需要清洗

DIM 层解决的是：

用户、地区等基础信息需要统一管理

DWS 层解决的是：

多张表经常需要关联，查询比较麻烦

最终，地铁数仓的核心流程可以概括为：

原始数据进入 ODS
清洗后进入 DWD
用户信息进入 DIM
DWD 和 DIM 关联
生成 DWS 宽表
用于统计分析和报表展示

通过这种分层设计，数据结构会更加清晰，任务之间的职责也更加明确。后续无论是统计不同线路的客流量，还是分析不同年龄、性别用户的出行情况，都可以直接基于 DWS 宽表进行查询。