147 lines
5.6 KiB
Markdown
147 lines
5.6 KiB
Markdown
# Databricks SQL 重构规范
|
||
|
||
## 1. 范围和原则
|
||
|
||
本规范适用于 CHPA Databricks 数仓脚本。第一阶段目标是在不改变业务口径的前提下提高可维护性。
|
||
|
||
- 先保持业务口径，再做性能优化。
|
||
- 每个脚本只负责一个主要目标表。
|
||
- 文件头声明源表、目标表、数据粒度、写入方式和迁移关系。
|
||
- 持久化写入必须显式列出目标列和查询列，禁止使用 `SELECT *`。
|
||
- 为行数、唯一性、非空约束和未匹配记录建立验证检查。
|
||
|
||
## 2. 数仓分层
|
||
|
||
数仓固定为三层，依赖方向为：
|
||
|
||
```text
|
||
DWD -> DWS -> DM
|
||
```
|
||
|
||
### DWD
|
||
|
||
- 保存清洗、标准化后的原子明细和基础主数据。
|
||
- 保持源数据可追溯性和稳定粒度。
|
||
- 不承载面向报表的层级拉宽、跨主题指标聚合。
|
||
|
||
### DWS
|
||
|
||
- 引用 DWD 构建公共维度、层级宽表和可复用事实表。
|
||
- 统一编码、公共口径和跨明细关联结果。
|
||
- 本批 ATC/NFC 层级宽维表属于 DWS，不再写回 DWD。
|
||
|
||
### DM
|
||
|
||
- 引用 DWS 构建具体业务主题、指标和报表数据集。
|
||
- 允许面向使用场景组织字段，但不得反向成为 DWD/DWS 的依赖。
|
||
|
||
读取关系保持清晰：DWS 读取 DWD，DM 读取 DWS。DWD 不读取 DWS/DM，DWS 不读取 DM；DWS/DM 的派生结果不得写回 DWD，DM 的派生结果不得写回 DWS。
|
||
|
||
## 3. 表命名
|
||
|
||
表名由“层级 + ext + 对象类型 + 业务实体”组成：
|
||
|
||
```text
|
||
[<catalog>.]<schema>.<layer>_ext_<object_type>_<business_entity>
|
||
```
|
||
|
||
固定前缀如下：
|
||
|
||
| 层级 | 对象 | 表名前缀 |
|
||
| --- | --- | --- |
|
||
| DWS | 维度/主数据 | `dws_ext_td_` |
|
||
| DWS | 事实数据 | `dws_ext_tf_` |
|
||
| DM | 维度/主数据 | `dm_ext_td_` |
|
||
| DM | 事实数据 | `dm_ext_tf_` |
|
||
|
||
命名规则：
|
||
|
||
- `<schema>` 与 `<layer>` 保持一致，例如 `dws.dws_ext_td_xxx`。
|
||
- `td` 表示维度或主数据，`tf` 表示事实数据。
|
||
- `<business_entity>` 使用小写 snake_case。
|
||
- 需要区分来源系统时，将来源放在业务实体开头，例如 `ims_atc_hierarchy`。
|
||
- `atc`、`nfc`、`ims` 等已形成业务共识的缩写可以保留。
|
||
|
||
本批核心映射：
|
||
|
||
| 旧表 | 新表 | 类型 |
|
||
| --- | --- | --- |
|
||
| `dwd.dwd_ims_atc_hierarchy` | `dws.dws_ext_td_ims_atc_hierarchy` | DWS 维度 |
|
||
| `dwd.dwd_ims_nfc_hierarchy` | `dws.dws_ext_td_ims_nfc_hierarchy` | DWS 维度 |
|
||
| `dwd.dwd_ims_td_manufacturer_corp` | `dws.dws_ext_td_ims_manufacturer_corporation` | DWS 维度 |
|
||
| `dwd.dwd_ims_td_pack_property` | `dws.dws_ext_td_ims_pack_property` | DWS 维度 |
|
||
| `tmp.tmp_ims_td_prod_tmp` | `dws.dws_ext_td_ims_product_multi_manufacturer` | DWS 维度 |
|
||
| `tmp.tmp_ims_tf_fact_sales` | `dws.dws_ext_tf_ims_chpa_sales` | DWS 事实 |
|
||
| `dws.dws_ims_td_market` | `dws.dws_ext_td_ims_market` | DWS 维度 |
|
||
|
||
`dwd.dwd_gnd_pharbers_prov_fact` 是省级原子事实，仍保留在 DWD；它与 IMS 全国事实组合后的可复用结果写入 `dws.dws_ext_tf_ims_chpa_sales`。全部 01/02 文件级映射见 `docs/chpa_01_02_migration.md`。
|
||
|
||
## 4. 文件和目录命名
|
||
|
||
目录结构统一为：
|
||
|
||
```text
|
||
sql/<业务域>/<阶段>/<序号>_<目标表>.sql
|
||
```
|
||
|
||
规则：
|
||
|
||
- 目录和文件名使用小写 snake_case，不使用空格。
|
||
- 目录阶段必须和输出层一致，例如 DWS 脚本放在 `02_dws`。
|
||
- 使用两位序号明确 notebook/job 执行顺序。
|
||
- 一个脚本只写入一个主要持久化目标。
|
||
- 多目标脚本按目标表和职责拆分。
|
||
|
||
示例：
|
||
|
||
```text
|
||
sql/chpa/02_dws/01_dws_ext_td_ims_atc_hierarchy.sql
|
||
```
|
||
|
||
## 5. 字段命名
|
||
|
||
- 新字段使用小写 snake_case。
|
||
- 标识使用 `_id`，业务编码使用 `_code`，名称或描述使用 `_name`、`_description`。
|
||
- 日期时间后缀按真实类型使用 `_date`、`_timestamp` 或 `_at`。
|
||
- 纯逻辑重构中不直接修改已有输出字段契约。
|
||
|
||
## 6. SQL 结构和格式
|
||
|
||
脚本统一按以下顺序组织：
|
||
|
||
1. Databricks notebook 标记和脚本契约头。
|
||
2. 带显式目标列的写入语句。
|
||
3. 源数据标准化 CTE。
|
||
4. 业务规则 CTE。
|
||
5. 按目标表字段顺序显式编写最终 `SELECT`。
|
||
|
||
格式规则：
|
||
|
||
- SQL 关键字使用大写。
|
||
- CTE 和别名使用小写 snake_case。
|
||
- 使用四个空格缩进，`SELECT` 每行一个字段。
|
||
- 使用有业务意义的别名，不使用 `t1`、`a` 等无语义别名。
|
||
- 同一作用域出现多个关系时，所有字段都带关系限定符。
|
||
|
||
## 7. 写入和数据质量
|
||
|
||
- 只有可确定性重跑的全量任务可以使用 `INSERT OVERWRITE`。
|
||
- 不在一个脚本中混合无关的 `UPDATE` 和目标表构建逻辑。
|
||
- 编码补零等标准化逻辑集中到一个明确阶段。
|
||
- 每个脚本必须声明预期目标粒度。
|
||
- 最少验证新旧行数、双向差集、业务键重复、必填键空值和层级未匹配数。
|
||
|
||
## 8. 兼容和验证说明
|
||
|
||
ATC 和 NFC 脚本保留旧逻辑中的父级编码规则：
|
||
|
||
- ATC 2 级关联 1 级：取前 1 位。
|
||
- ATC 3 级关联 2 级：取前 3 位。
|
||
- ATC 4 级关联 3 级：取前 4 位。
|
||
- NFC 2 级关联 1 级：取前 1 位。
|
||
- NFC 3 级关联 2 级：取前 2 位。
|
||
|
||
两个 hierarchy 脚本执行后运行 `validation/chpa/02_dws/validate_hierarchy_refactor.sql`。新 DWS 输出与旧 DWD 基线的行数必须一致，双向差集必须为 0；重复路径和未匹配层级作为业务 review 指标记录。
|
||
|
||
完整 01/02 批次执行后运行 `validation/chpa/02_dws/validate_01_02_refactor.sql`。比较时排除重新生成的 ETL 时间戳，并区分两类检查：逻辑等价迁移必须通过行数和双向 `EXCEPT ALL` 硬门禁；主动修复层级违规或已知故障模式的脚本记录质量指标和人工确认项，不伪造等价结论。
|