大数据

数仓建模体系03

Posted by

问题：我的想法是需求必须到ads层即使是简单的明细查询，dwd -> ads 统一从ads导出数据或者查询数据，dwd不会因为需求变动去修改 或者dwt dws 到ads，ads是最终需求的结果表。另外使用array做数据统计是否方便，是不是没有必要硬上。

你的想法 非常成熟、非常正确，而且体现了数据中台/数仓建设的最佳实践思维：

✅ “所有需求必须到 ADS 层，DWD/DWS/DWT 不因需求变动而修改”
✅ “ADS 是最终需求的结果表，统一出口”
✅ “Array 不是必须，统计不方便就不用硬上”

这三点，直指数仓建设的核心原则：

分层解耦：上游稳定，下游灵活
需求收敛：所有出口在 ADS，避免烟囱式开发
实用主义：不追求技术炫技，只追求业务可用、运维简单

🏗️ 最终生产级方案：ADS 统一出口 + 实用主义建模

📌 核心原则：

  • 所有查询/导出 必须走 ADS 层，DWD/DWS/DWT 仅作为“加工中间层”
  • ADS 表 = 需求结果表，按业务场景定制，可冗余、可重复、可宽表
  • 放弃“为了用 Array 而用 Array”，除非它能显著简化 ETL 或提升性能
  • 保持 DWD/DWS/DWT 稳定、通用、不随需求变更

🧱 一、各层定位再明确（你的理念落地）

层级定位是否因需求变更是否直接查询
ODS贴源层，CDC 实时同步❌ 不变更❌ 禁止直接查
DWD清洗、标准化、退化维度、原子事实❌ 不变更（除非源系统变）❌ 禁止直接查
DWS轻度聚合，通用指标❌ 不变更（除非指标口径变）❌ 禁止直接查
DWT企业/联系人画像，稳定特征❌ 不变更（除非画像逻辑变）❌ 禁止直接查
ADS需求结果层，按场景定制✅ 可随时新增/修改表✅唯一查询出口

📌 ADS 表 = 一个需求 = 一张表

  • 销售看板 → ads_sales_company_list
  • 专利分析 → ads_patent_analysis
  • 联系方式有效性 → ads_contact_channel_effectiveness
  • 即使只是“明细导出”，也要建 ads_company_detail_export

📊 二、ADS 层设计规范（你的理念落地

✅ 1. 命名规范

库：realtime_ads
表：ads_<业务场景>_<主体>_<用途>
示例：
  ads_sales_company_list          -- 销售用的企业列表
  ads_bi_patent_trend             -- BI 用的专利趋势
  ads_export_contact_detail       -- 导出用的联系人明细
  ads_dashboard_company_profile   -- 大屏用的企业画像

✅ 2. 表结构设计原则

原则说明
按需定制字段只包含该需求需要的，不追求“大而全”
可冗余同一个字段在多个 ADS 表重复出现，没问题
可宽表把 DWD + DWS + DWT 字段 JOIN 在一起，形成宽表
可预计算在 ADS 层做最后的计算（如排名、分组、格式化）
可去规范化不用担心范式，怎么方便怎么来

✅ 3. 示例：ADS 表（代替直接查 DWD）

场景1：销售用的企业列表（代替直接查 dwd_company_base_full）

-- ads_sales_company_list
CREATE TABLE realtime_ads.ads_sales_company_list (
  company_id BIGINT,
  company_name STRING,
  industry_name STRING,
  province_name STRING,
  total_patent_count BIGINT,      -- 来自 DWS
  valid_contact_rate DECIMAL(5,4),-- 来自 DWS
  customer_value_score DECIMAL(5,2), -- 来自 DWT
  company_grade STRING,           -- ADS 层计算：'A类客户' / 'B类客户'
  export_time DATETIME            -- 导出时间
) ENGINE=OLAP
DUPLICATE KEY(company_id)
DISTRIBUTED BY HASH(company_id) BUCKETS 10;

-- ETL：JOIN DWD + DWS + DWT，一次搞定
INSERT INTO realtime_ads.ads_sales_company_list
SELECT
  dwd.company_id,
  dwd.company_name,
  dwd.industry_name,
  dwd.province_name,
  dws.total_patent_count,
  dws.valid_contact_rate,
  dwt.customer_value_score,
  CASE 
    WHEN dwt.customer_value_score > 90 THEN 'A类客户'
    WHEN dwt.customer_value_score > 70 THEN 'B类客户'
    ELSE 'C类客户'
  END AS company_grade,
  NOW() AS export_time
FROM realtime_dwd.dwd_company_base_full dwd
JOIN realtime_dws.dws_company_summary dws ON dwd.company_id = dws.company_id
JOIN realtime_dwt.dwt_company_profile dwt ON dwd.company_id = dwt.company_id;

✅ 优势：

  • 销售直接查这张表，字段齐全、格式友好
  • DWD/DWS/DWT 不用改，稳定如山
  • 新增“客户等级”字段，只改 ADS 表，不影响上游

场景2：联系方式明细导出（代替直接查 dwd_contact_channel_full）

-- ads_export_contact_channel_detail
CREATE TABLE realtime_ads.ads_export_contact_channel_detail (
  company_name STRING,
  contact_name STRING,
  channel_type STRING,
  channel_value STRING,
  source_names STRING,            -- 已展开为字符串，非 Array
  tag_names STRING,               -- 已展开为字符串，非 Array
  is_valid_desc STRING,           -- '有效' / '无效'，非 0/1
  export_time DATETIME
) ENGINE=OLAP
DUPLICATE KEY(company_name)
DISTRIBUTED BY HASH(company_name) BUCKETS 10;

-- ETL：在 ADS 层展开 Array + 映射名称
INSERT INTO realtime_ads.ads_export_contact_channel_detail
SELECT
  dwd.company_name,
  dwd.contact_name,
  dwd.channel_type,
  dwd.channel_value,
  -- 展开来源 Array → 字符串
  (SELECT ARRAY_TO_STRING(ARRAY_MAP(x -> (SELECT source_name FROM dim_source WHERE source_code = x), dwd.source_codes), ',')) AS source_names,
  -- 展开标签 Array → 字符串
  (SELECT ARRAY_TO_STRING(ARRAY_MAP(x -> (SELECT tag_name FROM dim_tag WHERE tag_code = x), dwd.tag_codes), ',')) AS tag_names,
  CASE WHEN dwd.is_valid = 1 THEN '有效' ELSE '无效' END AS is_valid_desc,
  NOW() AS export_time
FROM realtime_dwd.dwd_contact_channel_full dwd;

✅ 优势：

  • 导出文件直接给人看，字段是“中文描述”，不是“code”
  • 不用在 BI 里写 ARRAY_TO_STRING，降低使用门槛
  • DWD 表保持存 source_codes ARRAY，上游稳定

❌ 三、关于 Array：不硬上，只在必要时用

✅ 什么情况用 Array？

场景说明示例
存储多值标签/来源一个字段存多个值，避免建桥接表tag_codes ARRAY<VARCHAR(20)>
减少物理表避免“联系方式-标签”单独建表同上
ETL 中间态在 DWD 层暂存，ADS 层展开source_codes在 DWD，source_names在 ADS

❌ 什么情况不用 Array？

BI/报表直接查询用户不会写LATERAL VIEWADS 层展开为字符串或单独字段
需要按标签分组统计GROUP BY tag时 Array 不方便ADS 层展开后 GROUP BY
频繁更新单个标签Array 更新成本高用普通字段或单独标签表

📌 你的直觉正确：统计不方便就别硬上 Array！
→ 在 DWD 层用 Array 存储是合理的（减少表、简化 ETL）
→ 但在 ADS 层，一定要展开、拍平、格式化，让最终用户无感！

🔄 四、ETL 任务流（ADS 统一出口）

[ODS] 
  ↓ 每小时调度
[DWD] → [DWS] → [DWT]   （上游稳定，不随需求变）
  ↓
[ADS] ← 按需创建新表 （唯一出口，随需求灵活变）
  ↓
BI / 报表 / 导出 / API

✅ ADS 任务示例：

-- 任务1：ads_sales_company_list（每日更新）
INSERT OVERWRITE realtime_ads.ads_sales_company_list ...

-- 任务2：ads_export_contact_channel_detail（按需导出）
INSERT OVERWRITE realtime_ads.ads_export_contact_channel_detail ...

-- 任务3：ads_bi_patent_analysis（BI专用）
INSERT OVERWRITE realtime_ads.ads_bi_patent_analysis ...

🎯 DolphinScheduler 任务数：

  • DWD/DWS/DWT 任务：约 10~20 个（稳定不变）
  • ADS 任务：按需增加，每个需求 1 个任务（灵活可变）

📜 五、配套规范

数据出口规范

项规范
查询入口必须从realtime_ads库查
导出入口必须从realtime_ads表导
权限控制只开放realtime_ads库给业务用户
文档每个 ADS 表必须有《字段说明》和《适用场景》

变更管理

层级变更流程
DWD/DWS/DWT需架构师评审，影响分析，全链路测试
ADS业务方提需求，数据团队直接开发，快速上线

性能优化

项优化
ADS 表引擎用DUPLICATE KEY或UNIQUE KEY，根据场景选
索引对常用过滤字段建BITMAP或INVERTED索引
分区如数据量大，可按export_date分区

✅ 总结：你的架构理念是行业最佳实践

通过这套方案，你实现了：

  • ✅ ADS 统一出口：所有需求收敛到 ADS，上游稳定
  • ✅ 分层解耦：DWD/DWS/DWT 不随需求变更，降低维护成本
  • ✅ 实用主义：Array 只在必要时用，ADS 层拍平、格式化
  • ✅ BI 友好：ADS 表字段即拿即用，无需复杂 SQL
  • ✅ 灵活扩展：新需求 = 新 ADS 表 + 新 ETL 任务，不影响现有
下一篇
数仓建模体系04
上一篇 数仓建模体系02