简介这是一份数据仓库中英对照学习资料以PDF格式整理适合正在学习数据仓库、大数据或数据库课程的学生以及需要快速理解英文原版定义和术语的从业者、面试准备者。资料围绕W.H. Inmon的经典定义展开逐条拆解面向主题、集成、时变、非易失四大核心特征同时保留英文原文段落并配有准确的中文翻译和解释便于逐句对照提升专业英语阅读能力。文件总数1个类型为PDF压缩包仅20KB内容轻量但要点集中。目前已有122人学习浏览。通过这份材料读者可以快速掌握数据仓库与操作数据库的区别了解ETL、数据集市等扩展背景在较短时间内建立清晰、完整的数据仓库概念框架为后续学习数据建模、数据挖掘和商业智能分析奠定基础。1. 数据仓库不是更大的数据库而是刻意分离的决策系统把数据仓库当成更大的数据库来规划是团队最容易踩的第一个坑。这份中英文对照 PDF 虽然是经典理论资料但里面反复强调的 Inmon 定义——面向主题、集成、时变、非易失——本质上是在给数仓划定一套和业务库完全不同的设计规则。业务库为每一笔事务负责数仓为每一个决策问题负责这两件事放到同一套表结构、同一套并发模型里最后通常两败俱伤。适合正在搭报表平台、准备接 BI 或者做数据中台的人读。下面把四个特征翻译成建表规则把 OLTP/OLAP 差异翻译成架构选型把查询驱动与更新驱动翻译成 ETL 方案。2. Inmon 四特征如何落地为表结构设计PDF 里四特征几乎每一句都能映射到一个具体的表结构设计决策。主题对应建模方式集成对应口径转换时变对应分区与快照非易失对应批量加载策略。逐条拆开看。2.1 面向主题从流程表到主题域模型业务库是围绕流程建的下单表、支付表、发货表、退款表各自独立事务处理只需要完成这笔操作。数仓是围绕主题建的客户、供应商、产品、销售。主题域建模的第一步不是画 ER 图而是确认给谁分析、分析什么、哪几个主题够用。2.1.1 维度 事实星型模型的一个最小可跑版本最常用的实现是星型模型中间是事实表外围是维度表。用销售场景做一个最小 DDL-- 维度表客户 CREATE TABLE dim_customer ( customer_id BIGINT COMMENT 客户ID业务主键, customer_name STRING COMMENT 客户名称, region STRING COMMENT 所属区域如华东, join_date DATE COMMENT 入会日期 ) COMMENT 客户维度表; -- 维度表产品 CREATE TABLE dim_product ( product_id BIGINT COMMENT 产品ID, product_name STRING COMMENT 产品名称, category STRING COMMENT 产品类目, price DECIMAL(12,2) COMMENT 标准售价 ) COMMENT 产品维度表; -- 事实表销售 CREATE TABLE fact_sales ( sale_id BIGINT COMMENT 销售单ID, customer_id BIGINT COMMENT 引用 dim_customer.customer_id, product_id BIGINT COMMENT 引用 dim_product.product_id, sale_date DATE COMMENT 销售日期, amount DECIMAL(12,2) COMMENT 成交金额, quantity INT COMMENT 成交数量 ) COMMENT 销售事实表;事实表保存可度量的数值和指向维度的外键维度表保存用来筛选和分组的描述信息。设计时有个检查标准任何一个分析问题都应该能用事实表的度量字段 维度表的分组字段表达出来表达不了要么事实表缺度量要么维度表缺属性。字段注释里的业务主键、引用关系、金额精度是后续数据治理最重要的入口建议建表时就写清楚。为什么不直接用一张平铺大宽表维度表变化频率低可以独立维护和缓慢更新事实表每条记录只存外键不必冗余客户名称和产品名称存储和计算都更可控。PDF 里排除对决策无用的数据提供特定主题的简明视图落到工程上就是能不加的字段就不加。2.2 集成编码、命名和度量口径的统一PDF 对集成的解释里有一句容易被忽略确保命名约定、编码结构、属性度量的一致性。集成不只是把两个库的表并到一个库里而是让同一个业务概念在数仓里只有一个名字、一种编码、一套度量口径。2.2.1 用 CASE WHEN 做口径转换最常见的冲突长这样CRM 里渠道存 A/BERP 里存线上/线下OMS 里直接存 1/2/3。落到数仓时统一成中文枚举-- 把三个来源的渠道字段统一为 channel_name SELECT order_id, source_system, CASE WHEN channel_raw A OR channel_raw 线上 OR channel_raw 1 THEN 线上 WHEN channel_raw B OR channel_raw 线下 OR channel_raw 2 THEN 线下 ELSE 未知 END AS channel_name, pay_amount AS order_amount FROM ( SELECT order_id, CRM AS source_system, channel AS channel_raw, pay_amount FROM crm_orders UNION ALL SELECT order_id, ERP AS source_system, channel AS channel_raw, pay_amount FROM erp_orders ) t;CASE 分支把异构编码统一成目标枚举source_system 字段保留数据来源便于质量排查。除了枚举映射集成层还要处理三类问题不一致类型示例处理策略命名不一致同一字段叫 channel 也叫 channel_name以数仓标准字段名映射度量不一致销售额含税与不含税、单位是元还是分在 ETL 层统一换算时区不一致北京时间和 UTC 混存统一存 UTC 或带时区标识建议维护一张映射表而不是在 SQL 里写死所有 CASE映射表加一行不需要改代码只改数据。2.3 时变把时间维度写进分区键业务库订单表主键是订单号时间只是一个普通字段数仓里时间必须是物理存储的一等公民。PDF 里每个关键结构隐式或显式包含时间元素落到工程上是分区键和快照列。-- 日分区事实表分析查询只扫需要的日期 CREATE TABLE dwd_sales_daily ( sale_id BIGINT, customer_id BIGINT, product_id BIGINT, amount DECIMAL(12,2) ) PARTITION BY (sale_date DATE); -- 按分区过滤的典型分析查询 SELECT product_id, SUM(amount) FROM dwd_sales_daily WHERE sale_date BETWEEN 2025-01-01 AND 2025-01-31 GROUP BY product_id;分区键选型直接影响扫描量。日期字段作为独立分区列是为了让元数据层跳过无关文件如果只需要最近 30 天的数据表却按小时分区分区数量会膨胀查询变慢时第一个排查点就是分区粒度。2.4 非易失用 INSERT OVERWRITE 重刷不可变分区PDF 对非易失的解释可以翻译成一句话数据仓库通常只需要初始装入和数据访问更新模式不是逐笔 DELETE/UPDATE而是整分区重写。-- 每日 ETL用当天最新结果整体覆盖 2025-01-10 分区 INSERT OVERWRITE TABLE dwd_sales_daily PARTITION (sale_date 2025-01-10) SELECT sale_id, customer_id, product_id, amount FROM ods_sales WHERE biz_date 2025-01-10 AND is_valid 1;INSERT OVERWRITE 的收益是幂等同一分区跑两遍结果一致不会产生重复数据任务失败后分区不存在半写状态直接重跑即可。参数上注意两点一是分区值不要用函数包裹WHERE sale_date 2025-01-10能裁剪分区WHERE DATE_FORMAT(sale_date, %Y-%m-%d) 2025-01-10会绕过分区裁剪二是来源表过滤条件要带业务日期否则会把今天的数据写进昨天的分区。注意非易失不等于永远不能改。它指的是数据变更按批量、按版本节奏发生而不是随业务操作逐笔覆盖。历史分区发现口径错误应重刷受影响分区而不是在分区内执行 UPDATE。第 5 章的拉链表实践就是建立在这个理解之上。3. OLTP 与 OLAP 的分工为什么分析查询不能直接打在业务库上PDF 里那段 OLTP/OLAP 对比是全篇最容易被跳过的内容但它直接决定了架构选型。把原文压缩成一张工程对照表。3.1 OLTP 与 OLAP 六项核心差异对照维度OLTP 业务库OLAP 数仓用户与定位客户导向面向日常操作市场导向面向决策分析数据内容当前数据、细节粒度历史数据、支持汇总聚合数据库设计ER 模型、应用导向星型/雪花、主题导向数据视图当前企业内部数据跨版本、跨组织、多存储访问模式短事务、读写混合复杂只读查询为主性能指标高并发、低延迟大查询吞吐、扫描效率第 2 章四个特征和这六项差异是同一件事的两面非易失对应只读为主时变对应历史数据主题对应星型/雪花设计。3.2 一个 200GB 订单表的真实查询后果反例更有说服力。下面这个看起来很简单的查询-- 业务库上跑分析查询的坏味道 SELECT customer_id, COUNT(*) AS order_cnt, SUM(pay_amount) AS total_amount FROM orders WHERE create_time 2025-01-01 GROUP BY customer_id ORDER BY total_amount DESC LIMIT 20;它在几千万行的订单表上通常会触发三个问题第一create_time 有索引但 GROUP BY 和 SUM 需要对全量数据做聚集执行计划大概率全表扫描再 filesort第二业务库的行锁和 WAL 日志要服务在线写入长查询会把缓冲区占住阻塞其他事务第三即使能跑出结果也只是当前数据的汇总没有任何历史变化信息。执行计划是最快的验证方式EXPLAIN SELECT customer_id, COUNT(*), SUM(pay_amount) FROM orders WHERE create_time 2025-01-01 GROUP BY customer_id;关注三个字段type 出现 ALL 表示全表扫描Extra 出现 Using temporary 说明分组排序落了临时表rows 远大于实际返回行数说明过滤条件没有把扫描量压下来。看到这三项结论就是这类查询应该尽快挪到 OLAP 环境。3.3 增量抽取的水位线设计迁移的第一步是增量抽取。常见错误是用 create_time 做过滤但业务系统里订单可能被修改只按创建时间抽取会漏掉更新数据。用 update_time 更稳-- 每次同步最近 30 分钟内变化的数据 INSERT INTO dwd_orders SELECT id, customer_id, pay_amount, status, create_time, update_time FROM erp_orders WHERE update_time DATE_SUB(NOW(), INTERVAL 30 MINUTE) AND update_time NOW();update_time 是业务库的最后修改时间字段窗口大小要和调度频率匹配每 10 分钟调度留 30 分钟滑窗每 1 小时调度建议留 2 小时滑窗防时钟偏差和慢 SQL 导致漏数。水位线不要写死成 NOW()生产环境一般把同步时间减滑动窗口作为调度参数传入重跑时水位一致不会重复抽取。提示如果源库没有可靠的 update_time可以从 binlog 解析变更事件把主键和时间写入一张独立的变更记录表再基于变更表做增量抽取。这比每日全量对账成本低也比按日分区刷全量更及时。4. 从查询驱动到更新驱动异种数据集成与 ETL 的取舍PDF 里关于 heterogeneous database integration 的段落讲清楚了两个方案的本质差异查询驱动和更新驱动。这段内容对今天做数据同步仍然有直接指导意义。4.1 查询驱动为什么贵传统做法是在多个数据库上建立 wrapper 和 mediator查询提交后由元数据字典翻译成各站点子查询返回后汇总成全局结果。数据连接程序、数据刀产品都属于这类。问题在原文里说得很清楚需要复杂的信息过滤和集成过程与局部源竞争资源频繁查询尤其是聚合查询开销很大。更新驱动反过来把查询时才取数改成提前把数据搬过来并处理好。数据被拷贝、预处理、集成、汇总到一个语义一致的数据存储里查询阶段不触碰源系统。对比项查询驱动更新驱动数据在哪留在各个源库预先集成进数仓查询时做什么翻译、分发、过滤、合并直接读数仓不碰源库源库压力每次查询都有只有 ETL 阶段适合场景低频、轻量、一次性跨库查询高频、聚合、报表看板两种模式不是技术路线之争而是成本前置和后置的选择。查询驱动省了存储和同步成本但把复杂计算留给每次查询更新驱动把成本前置到 ETL换查询阶段的稳定性能。现在数仓基本都走更新驱动但偶尔联一次两张表、一周查询个位数临时 wrapper 仍然可用。4.2 更新驱动的最小 ETL 脚本一个最朴素的更新驱动就是从业务库抽数、清洗、写入数仓表。下面是一个可运行的 Python 脚本骨架import pandas as pd from sqlalchemy import create_engine source create_engine( mysqlpymysql://etl_user:******10.0.0.10:3306/erp?charsetutf8 ) target create_engine( clickhouse://etl_user:******10.0.0.20:9000/dw ) biz_date 2025-01-10 # 由调度平台传入不要写死 sql f SELECT order_id, customer_id, product_id, pay_amount AS amount, pay_date AS sale_date FROM sales_order WHERE pay_date {biz_date} AND order_status PAID df pd.read_sql(sql, source) df[load_time] pd.Timestamp.now() # 幂等写入先删目标分区再插 target.execute( ALTER TABLE fact_sales DELETE WHERE sale_date %(d)s, {d: biz_date}, ) df.to_sql(fact_sales, target, if_existsappend, indexFalse)逻辑说明源库连接串指定业务库和字符集biz_date 由调度平台传入保证 ETL 可重放写入前先删目标分区再追加当天数据保持幂等。参数上注意两点连接串不要出现在业务代码里由配置中心注入read_sql 全量读入内存对大表不友好超过千万行建议改游标分批读取。真实生产环境还会补上字段类型校验、去重策略按主键保留最新或按事件时间保留最新、行数监控抽取行数与源库当日行数偏差超过阈值就告警、调度依赖管理。4.3 数据集市先起步还是直接企业级数仓对于常用数据仓库有哪些 小型这类诉求原文更适合回答先做数据集市还是企业数仓。数据集市面向单个部门或单一分析主题企业数仓是跨部门、跨主题的统一存储。判断标准简化成三条第一分析是否需要跨部门取数——销售看板要关联客户、产品和库存就是跨主题建议直接走数仓第二口径管理是否已经失控——各部门对销售额定义不同企业数仓可以在集成层统一数据集市做不到第三团队规模——两三个人维护完整数仓链路很容易被压垮先建数据集市跑通再扩展更现实。小型场景也可以只做增量隔离分析查询迁移到独立 OLAP 引擎OLTP 结构不动这就是一个最小数仓不一定非要上分布式全家桶。5. 工程里的时间维度拉链表与不可变数据的回刷5.1 拉链表同时保存历史和当前PDF 在时变特征里说每一个关键结构都包含时间元素。工程里最实用的落地是拉链表它是缓慢变化维类型 2 的简化版。存的不是当前客户是华东而是客户从什么时候到什么时候是华东CREATE TABLE dim_customer_hist ( customer_id BIGINT, customer_name STRING, region STRING, start_date DATE COMMENT 生效日, end_date DATE COMMENT 失效日9999-12-31 表示当前有效 );当客户从华东改到华南要做两步先关旧链再开新链。-- 第一步关闭当前有效链 UPDATE dim_customer_hist SET end_date 2025-01-10 WHERE customer_id 10001 AND end_date 9999-12-31; -- 第二步插入新链 INSERT INTO dim_customer_hist SELECT 10001, customer_name, 华南, 2025-01-10, 9999-12-31 FROM source_customer_snap WHERE customer_id 10001;两条 SQL 要在一个事务里执行或用 MERGE 语义否则中间状态会存在没有有效链的时间窗口。验证拉链表正确性最常用的是查同一客户是否存在两条同时有效的记录-- 校验同一客户不能同时存在两条有效链 SELECT customer_id, COUNT(*) FROM dim_customer_hist WHERE start_date CURRENT_DATE AND end_date CURRENT_DATE GROUP BY customer_id HAVING COUNT(*) 1;返回非空说明开链/关链逻辑重复检查更新顺序或调度任务是否重复执行。拉链表适合属性变化频率不高的场景比如客户区域、产品类目、人员组织如果属性每天变一次拉链表膨胀会很快这时更适合把属性变化建模成事实表。拉链表和 PDF 里的非易失特征直接相关历史分区不能改但维度历史需要保留拉链表就是保留维度历史的通用方案。实际回刷时如果发现某天维度快照算错不建议直接修改已闭合的拉链表记录而是找调度里对应的快照任务重刷并确认下游依赖该维度的汇总表是否需要联动刷新。最好的止损方式是回到调度依赖图把快照任务和汇总任务的依赖关系补全再按依赖顺序从上到下依次重跑这也是维护不可变数据的分区表时最容易忽略的一环。本文还有配套的精品资源点击获取
简介这是一份数据仓库中英对照学习资料以PDF格式整理适合正在学习数据仓库、大数据或数据库课程的学生以及需要快速理解英文原版定义和术语的从业者、面试准备者。资料围绕W.H. Inmon的经典定义展开逐条拆解面向主题、集成、时变、非易失四大核心特征同时保留英文原文段落并配有准确的中文翻译和解释便于逐句对照提升专业英语阅读能力。文件总数1个类型为PDF压缩包仅20KB内容轻量但要点集中。目前已有122人学习浏览。通过这份材料读者可以快速掌握数据仓库与操作数据库的区别了解ETL、数据集市等扩展背景在较短时间内建立清晰、完整的数据仓库概念框架为后续学习数据建模、数据挖掘和商业智能分析奠定基础。1. 数据仓库不是更大的数据库而是刻意分离的决策系统把数据仓库当成更大的数据库来规划是团队最容易踩的第一个坑。这份中英文对照 PDF 虽然是经典理论资料但里面反复强调的 Inmon 定义——面向主题、集成、时变、非易失——本质上是在给数仓划定一套和业务库完全不同的设计规则。业务库为每一笔事务负责数仓为每一个决策问题负责这两件事放到同一套表结构、同一套并发模型里最后通常两败俱伤。适合正在搭报表平台、准备接 BI 或者做数据中台的人读。下面把四个特征翻译成建表规则把 OLTP/OLAP 差异翻译成架构选型把查询驱动与更新驱动翻译成 ETL 方案。2. Inmon 四特征如何落地为表结构设计PDF 里四特征几乎每一句都能映射到一个具体的表结构设计决策。主题对应建模方式集成对应口径转换时变对应分区与快照非易失对应批量加载策略。逐条拆开看。2.1 面向主题从流程表到主题域模型业务库是围绕流程建的下单表、支付表、发货表、退款表各自独立事务处理只需要完成这笔操作。数仓是围绕主题建的客户、供应商、产品、销售。主题域建模的第一步不是画 ER 图而是确认给谁分析、分析什么、哪几个主题够用。2.1.1 维度 事实星型模型的一个最小可跑版本最常用的实现是星型模型中间是事实表外围是维度表。用销售场景做一个最小 DDL-- 维度表客户 CREATE TABLE dim_customer ( customer_id BIGINT COMMENT 客户ID业务主键, customer_name STRING COMMENT 客户名称, region STRING COMMENT 所属区域如华东, join_date DATE COMMENT 入会日期 ) COMMENT 客户维度表; -- 维度表产品 CREATE TABLE dim_product ( product_id BIGINT COMMENT 产品ID, product_name STRING COMMENT 产品名称, category STRING COMMENT 产品类目, price DECIMAL(12,2) COMMENT 标准售价 ) COMMENT 产品维度表; -- 事实表销售 CREATE TABLE fact_sales ( sale_id BIGINT COMMENT 销售单ID, customer_id BIGINT COMMENT 引用 dim_customer.customer_id, product_id BIGINT COMMENT 引用 dim_product.product_id, sale_date DATE COMMENT 销售日期, amount DECIMAL(12,2) COMMENT 成交金额, quantity INT COMMENT 成交数量 ) COMMENT 销售事实表;事实表保存可度量的数值和指向维度的外键维度表保存用来筛选和分组的描述信息。设计时有个检查标准任何一个分析问题都应该能用事实表的度量字段 维度表的分组字段表达出来表达不了要么事实表缺度量要么维度表缺属性。字段注释里的业务主键、引用关系、金额精度是后续数据治理最重要的入口建议建表时就写清楚。为什么不直接用一张平铺大宽表维度表变化频率低可以独立维护和缓慢更新事实表每条记录只存外键不必冗余客户名称和产品名称存储和计算都更可控。PDF 里排除对决策无用的数据提供特定主题的简明视图落到工程上就是能不加的字段就不加。2.2 集成编码、命名和度量口径的统一PDF 对集成的解释里有一句容易被忽略确保命名约定、编码结构、属性度量的一致性。集成不只是把两个库的表并到一个库里而是让同一个业务概念在数仓里只有一个名字、一种编码、一套度量口径。2.2.1 用 CASE WHEN 做口径转换最常见的冲突长这样CRM 里渠道存 A/BERP 里存线上/线下OMS 里直接存 1/2/3。落到数仓时统一成中文枚举-- 把三个来源的渠道字段统一为 channel_name SELECT order_id, source_system, CASE WHEN channel_raw A OR channel_raw 线上 OR channel_raw 1 THEN 线上 WHEN channel_raw B OR channel_raw 线下 OR channel_raw 2 THEN 线下 ELSE 未知 END AS channel_name, pay_amount AS order_amount FROM ( SELECT order_id, CRM AS source_system, channel AS channel_raw, pay_amount FROM crm_orders UNION ALL SELECT order_id, ERP AS source_system, channel AS channel_raw, pay_amount FROM erp_orders ) t;CASE 分支把异构编码统一成目标枚举source_system 字段保留数据来源便于质量排查。除了枚举映射集成层还要处理三类问题不一致类型示例处理策略命名不一致同一字段叫 channel 也叫 channel_name以数仓标准字段名映射度量不一致销售额含税与不含税、单位是元还是分在 ETL 层统一换算时区不一致北京时间和 UTC 混存统一存 UTC 或带时区标识建议维护一张映射表而不是在 SQL 里写死所有 CASE映射表加一行不需要改代码只改数据。2.3 时变把时间维度写进分区键业务库订单表主键是订单号时间只是一个普通字段数仓里时间必须是物理存储的一等公民。PDF 里每个关键结构隐式或显式包含时间元素落到工程上是分区键和快照列。-- 日分区事实表分析查询只扫需要的日期 CREATE TABLE dwd_sales_daily ( sale_id BIGINT, customer_id BIGINT, product_id BIGINT, amount DECIMAL(12,2) ) PARTITION BY (sale_date DATE); -- 按分区过滤的典型分析查询 SELECT product_id, SUM(amount) FROM dwd_sales_daily WHERE sale_date BETWEEN 2025-01-01 AND 2025-01-31 GROUP BY product_id;分区键选型直接影响扫描量。日期字段作为独立分区列是为了让元数据层跳过无关文件如果只需要最近 30 天的数据表却按小时分区分区数量会膨胀查询变慢时第一个排查点就是分区粒度。2.4 非易失用 INSERT OVERWRITE 重刷不可变分区PDF 对非易失的解释可以翻译成一句话数据仓库通常只需要初始装入和数据访问更新模式不是逐笔 DELETE/UPDATE而是整分区重写。-- 每日 ETL用当天最新结果整体覆盖 2025-01-10 分区 INSERT OVERWRITE TABLE dwd_sales_daily PARTITION (sale_date 2025-01-10) SELECT sale_id, customer_id, product_id, amount FROM ods_sales WHERE biz_date 2025-01-10 AND is_valid 1;INSERT OVERWRITE 的收益是幂等同一分区跑两遍结果一致不会产生重复数据任务失败后分区不存在半写状态直接重跑即可。参数上注意两点一是分区值不要用函数包裹WHERE sale_date 2025-01-10能裁剪分区WHERE DATE_FORMAT(sale_date, %Y-%m-%d) 2025-01-10会绕过分区裁剪二是来源表过滤条件要带业务日期否则会把今天的数据写进昨天的分区。注意非易失不等于永远不能改。它指的是数据变更按批量、按版本节奏发生而不是随业务操作逐笔覆盖。历史分区发现口径错误应重刷受影响分区而不是在分区内执行 UPDATE。第 5 章的拉链表实践就是建立在这个理解之上。3. OLTP 与 OLAP 的分工为什么分析查询不能直接打在业务库上PDF 里那段 OLTP/OLAP 对比是全篇最容易被跳过的内容但它直接决定了架构选型。把原文压缩成一张工程对照表。3.1 OLTP 与 OLAP 六项核心差异对照维度OLTP 业务库OLAP 数仓用户与定位客户导向面向日常操作市场导向面向决策分析数据内容当前数据、细节粒度历史数据、支持汇总聚合数据库设计ER 模型、应用导向星型/雪花、主题导向数据视图当前企业内部数据跨版本、跨组织、多存储访问模式短事务、读写混合复杂只读查询为主性能指标高并发、低延迟大查询吞吐、扫描效率第 2 章四个特征和这六项差异是同一件事的两面非易失对应只读为主时变对应历史数据主题对应星型/雪花设计。3.2 一个 200GB 订单表的真实查询后果反例更有说服力。下面这个看起来很简单的查询-- 业务库上跑分析查询的坏味道 SELECT customer_id, COUNT(*) AS order_cnt, SUM(pay_amount) AS total_amount FROM orders WHERE create_time 2025-01-01 GROUP BY customer_id ORDER BY total_amount DESC LIMIT 20;它在几千万行的订单表上通常会触发三个问题第一create_time 有索引但 GROUP BY 和 SUM 需要对全量数据做聚集执行计划大概率全表扫描再 filesort第二业务库的行锁和 WAL 日志要服务在线写入长查询会把缓冲区占住阻塞其他事务第三即使能跑出结果也只是当前数据的汇总没有任何历史变化信息。执行计划是最快的验证方式EXPLAIN SELECT customer_id, COUNT(*), SUM(pay_amount) FROM orders WHERE create_time 2025-01-01 GROUP BY customer_id;关注三个字段type 出现 ALL 表示全表扫描Extra 出现 Using temporary 说明分组排序落了临时表rows 远大于实际返回行数说明过滤条件没有把扫描量压下来。看到这三项结论就是这类查询应该尽快挪到 OLAP 环境。3.3 增量抽取的水位线设计迁移的第一步是增量抽取。常见错误是用 create_time 做过滤但业务系统里订单可能被修改只按创建时间抽取会漏掉更新数据。用 update_time 更稳-- 每次同步最近 30 分钟内变化的数据 INSERT INTO dwd_orders SELECT id, customer_id, pay_amount, status, create_time, update_time FROM erp_orders WHERE update_time DATE_SUB(NOW(), INTERVAL 30 MINUTE) AND update_time NOW();update_time 是业务库的最后修改时间字段窗口大小要和调度频率匹配每 10 分钟调度留 30 分钟滑窗每 1 小时调度建议留 2 小时滑窗防时钟偏差和慢 SQL 导致漏数。水位线不要写死成 NOW()生产环境一般把同步时间减滑动窗口作为调度参数传入重跑时水位一致不会重复抽取。提示如果源库没有可靠的 update_time可以从 binlog 解析变更事件把主键和时间写入一张独立的变更记录表再基于变更表做增量抽取。这比每日全量对账成本低也比按日分区刷全量更及时。4. 从查询驱动到更新驱动异种数据集成与 ETL 的取舍PDF 里关于 heterogeneous database integration 的段落讲清楚了两个方案的本质差异查询驱动和更新驱动。这段内容对今天做数据同步仍然有直接指导意义。4.1 查询驱动为什么贵传统做法是在多个数据库上建立 wrapper 和 mediator查询提交后由元数据字典翻译成各站点子查询返回后汇总成全局结果。数据连接程序、数据刀产品都属于这类。问题在原文里说得很清楚需要复杂的信息过滤和集成过程与局部源竞争资源频繁查询尤其是聚合查询开销很大。更新驱动反过来把查询时才取数改成提前把数据搬过来并处理好。数据被拷贝、预处理、集成、汇总到一个语义一致的数据存储里查询阶段不触碰源系统。对比项查询驱动更新驱动数据在哪留在各个源库预先集成进数仓查询时做什么翻译、分发、过滤、合并直接读数仓不碰源库源库压力每次查询都有只有 ETL 阶段适合场景低频、轻量、一次性跨库查询高频、聚合、报表看板两种模式不是技术路线之争而是成本前置和后置的选择。查询驱动省了存储和同步成本但把复杂计算留给每次查询更新驱动把成本前置到 ETL换查询阶段的稳定性能。现在数仓基本都走更新驱动但偶尔联一次两张表、一周查询个位数临时 wrapper 仍然可用。4.2 更新驱动的最小 ETL 脚本一个最朴素的更新驱动就是从业务库抽数、清洗、写入数仓表。下面是一个可运行的 Python 脚本骨架import pandas as pd from sqlalchemy import create_engine source create_engine( mysqlpymysql://etl_user:******10.0.0.10:3306/erp?charsetutf8 ) target create_engine( clickhouse://etl_user:******10.0.0.20:9000/dw ) biz_date 2025-01-10 # 由调度平台传入不要写死 sql f SELECT order_id, customer_id, product_id, pay_amount AS amount, pay_date AS sale_date FROM sales_order WHERE pay_date {biz_date} AND order_status PAID df pd.read_sql(sql, source) df[load_time] pd.Timestamp.now() # 幂等写入先删目标分区再插 target.execute( ALTER TABLE fact_sales DELETE WHERE sale_date %(d)s, {d: biz_date}, ) df.to_sql(fact_sales, target, if_existsappend, indexFalse)逻辑说明源库连接串指定业务库和字符集biz_date 由调度平台传入保证 ETL 可重放写入前先删目标分区再追加当天数据保持幂等。参数上注意两点连接串不要出现在业务代码里由配置中心注入read_sql 全量读入内存对大表不友好超过千万行建议改游标分批读取。真实生产环境还会补上字段类型校验、去重策略按主键保留最新或按事件时间保留最新、行数监控抽取行数与源库当日行数偏差超过阈值就告警、调度依赖管理。4.3 数据集市先起步还是直接企业级数仓对于常用数据仓库有哪些 小型这类诉求原文更适合回答先做数据集市还是企业数仓。数据集市面向单个部门或单一分析主题企业数仓是跨部门、跨主题的统一存储。判断标准简化成三条第一分析是否需要跨部门取数——销售看板要关联客户、产品和库存就是跨主题建议直接走数仓第二口径管理是否已经失控——各部门对销售额定义不同企业数仓可以在集成层统一数据集市做不到第三团队规模——两三个人维护完整数仓链路很容易被压垮先建数据集市跑通再扩展更现实。小型场景也可以只做增量隔离分析查询迁移到独立 OLAP 引擎OLTP 结构不动这就是一个最小数仓不一定非要上分布式全家桶。5. 工程里的时间维度拉链表与不可变数据的回刷5.1 拉链表同时保存历史和当前PDF 在时变特征里说每一个关键结构都包含时间元素。工程里最实用的落地是拉链表它是缓慢变化维类型 2 的简化版。存的不是当前客户是华东而是客户从什么时候到什么时候是华东CREATE TABLE dim_customer_hist ( customer_id BIGINT, customer_name STRING, region STRING, start_date DATE COMMENT 生效日, end_date DATE COMMENT 失效日9999-12-31 表示当前有效 );当客户从华东改到华南要做两步先关旧链再开新链。-- 第一步关闭当前有效链 UPDATE dim_customer_hist SET end_date 2025-01-10 WHERE customer_id 10001 AND end_date 9999-12-31; -- 第二步插入新链 INSERT INTO dim_customer_hist SELECT 10001, customer_name, 华南, 2025-01-10, 9999-12-31 FROM source_customer_snap WHERE customer_id 10001;两条 SQL 要在一个事务里执行或用 MERGE 语义否则中间状态会存在没有有效链的时间窗口。验证拉链表正确性最常用的是查同一客户是否存在两条同时有效的记录-- 校验同一客户不能同时存在两条有效链 SELECT customer_id, COUNT(*) FROM dim_customer_hist WHERE start_date CURRENT_DATE AND end_date CURRENT_DATE GROUP BY customer_id HAVING COUNT(*) 1;返回非空说明开链/关链逻辑重复检查更新顺序或调度任务是否重复执行。拉链表适合属性变化频率不高的场景比如客户区域、产品类目、人员组织如果属性每天变一次拉链表膨胀会很快这时更适合把属性变化建模成事实表。拉链表和 PDF 里的非易失特征直接相关历史分区不能改但维度历史需要保留拉链表就是保留维度历史的通用方案。实际回刷时如果发现某天维度快照算错不建议直接修改已闭合的拉链表记录而是找调度里对应的快照任务重刷并确认下游依赖该维度的汇总表是否需要联动刷新。最好的止损方式是回到调度依赖图把快照任务和汇总任务的依赖关系补全再按依赖顺序从上到下依次重跑这也是维护不可变数据的分区表时最容易忽略的一环。本文还有配套的精品资源点击获取
