处理大量数据时,需要一个管道,该管道只能处理新的和已更改的记录,而不是重新处理整个数据集。 这称为增量 ETL。 在 Databricks SQL 中,可以使用流式处理表和具体化视图生成增量 ETL 管道,而无需编写过程代码或计划手动刷新。
本教程将引导你完成一种常见模式:跟踪一段时间内的产品更改。 你创建源表,捕获变更事件,构建一个维度表以保留每个产品的完整历史,维护一个逐步覆盖近期记录的事实表,并在上面添加汇总报告层。 你用 AUTO CDC 作为维度表,用 REPLACE WHERE 流程用于事实表。
本教程的主要功能是 AUTO CDC。 在传统的仓库中,你将编写复杂的 MERGE INTO 语句来协调目标表中的插入、更新和删除事件。 此方法容易出错,尤其是在事件无序到达时。
AUTO CDC 为你处理此问题。 声明业务密钥、排序列以及是否希望 SCD 类型 1(仅最新值)或 SCD 类型 2(完整历史记录),Azure Databricks自动应用正确的合并逻辑。 有关 CDC 的概述,请参阅 AUTO CDC API:使用管道简化更改数据捕获。
在本教程结束时,你将拥有:
- 创建了一个源表,用于跟踪更改数据馈送的更改。
- 检查了原始更改数据以了解 CDC 事件流。
- 用于
AUTO CDC从这些事件生成 SCD 类型 2 维度表。 - 通过管道以增量方式处理删除事件。
- 构建了一个事实表,通过 REPLACE WHERE 流程逐步覆盖最近的行。
- 创建了以增量方式维护聚合报表的具体化视图。
- 配置
SCHEDULE REFRESH EVERY 1 DAY后,更改会自动通过管道传播。
要求
若要完成此教程,必须满足以下要求:
- 启用了 Unity Catalog 的 Azure Databricks 工作区。
- SQL 仓库(无服务器或专业人员)。
- 有权 创建计算资源 或 访问计算资源。
- 为帐户启用无服务器计算。 请参阅区域可用性受限的功能。
步骤 1:设置目录和架构
打开 Databricks SQL 编辑器 并设置工作目录和架构。 必须拥有对所选目录和架构的 USE 权限:
USE CATALOG <your-catalog>;
USE SCHEMA <your-schema>;
步骤 2:创建源表并加载数据
创建一个 products 表,启用在 Azure Databricks (CDF) 上使用更改数据馈送。 CDF 是一项 Delta Lake 功能,以可查询的更改日志的形式记录每个插入、更新和删除。 这类似于事务源系统中的 CDC 流,只是更改直接在 Delta 表中捕获,而不是从外部日志捕获。 在此处使用 CDF 生成下游管道将使用的更改事件。
创建表并加载初始记录:
CREATE OR REPLACE TABLE products ( product_id INT, product_name STRING, category STRING, warehouse STRING ) TBLPROPERTIES (delta.enableChangeDataFeed = true); INSERT INTO products VALUES (1, 'Spoon', 'Cutlery', 'Seattle'), (2, 'Fork', 'Cutlery', 'Portland'), (3, 'Knife', 'Cutlery', 'Denver'), (4, 'Chair', 'Furniture', 'Austin'), (5, 'Table', 'Furniture', 'Chicago'), (6, 'Lamp', 'Lighting', 'Boston'), (7, 'Mug', 'Kitchenware', 'Seattle'), (8, 'Plate', 'Kitchenware', 'Atlanta'), (9, 'Bowl', 'Kitchenware', 'Dallas'), (10, 'Glass', 'Kitchenware', 'Phoenix');模拟上游更改,包括新产品、仓库移动和类别重新分配:
INSERT INTO products VALUES (11, 'Napkin', 'Dining', 'San Francisco'), (12, 'Coaster', 'Dining', 'New York'); UPDATE products SET warehouse = 'Los Angeles' WHERE product_id = 1; UPDATE products SET category = 'Dining' WHERE product_id = 2;
步骤 3:查询变更数据流
在生成下游管道之前,有助于查看原始更改事件,以便您了解AUTO CDC将处理的内容。 该 table_changes() 函数读取 CDF 日志,并返回已捕获的每个操作及其对应的元数据列:
SELECT
product_id, product_name, warehouse,
_change_type, _commit_version
FROM table_changes('products', 1)
ORDER BY _commit_version, product_id;
例如,Spoon 有三个事件:一个 insert(西雅图)、一个 update_preimage(西雅图)和一个 update_postimage(洛杉矶)。
请注意,单个逻辑更改(例如,将 Spoon 移动到其他仓库)会产生多个事件:预映像和后映像。 在传统的仓库中,你将编写一个 MERGE 语句,将所有这些事件协调到目标表中,使用单独的逻辑处理插入、更新和删除,并确保按正确的顺序应用事件。 这正是 AUTO CDC 在下一步中消除的复杂性。
步骤 4:使用 AUTO CDC 构建 SCD 类型 2 维度
流式处理表以增量方式处理数据。 每次刷新时,它只读取自上次运行以来的新行,因此不需要重新处理完整的数据集。 这使得它特别适合大容量或频繁更改的数据源。
AUTO CDC 在流式表上添加更改数据捕获处理。 与其编写手动处理插入、更新和删除的 MERGE INTO 语句,不如声明业务密钥和排序列,并让 Azure Databricks 应用正确的逻辑。
AUTO CDC 还自动处理无序事件,这是处理 MERGE INTO 从分布式系统到达的事件或具有重叠时间戳的批处理加载时常见的问题。
以下语句创建一个 SCD 类型 2 表,该表保留每个产品的完整版本历史记录。 每个版本获取 __START_AT 和 __END_AT 时间戳。
NULL 中的 __END_AT 标记当前版本。
CREATE OR REFRESH STREAMING TABLE products_history
SCHEDULE REFRESH EVERY 1 DAY
FLOW AUTO CDC
FROM (
SELECT * FROM STREAM products WITH (readChangeFeed = true)
WHERE _change_type != 'update_preimage'
)
KEYS (product_id)
APPLY AS DELETE WHEN _change_type = 'delete'
SEQUENCE BY _commit_timestamp
COLUMNS * EXCEPT (_change_type, _commit_version, _commit_timestamp)
STORED AS SCD TYPE 2;
-
SCHEDULE REFRESH EVERY 1 DAY:按每日计划刷新表。 -
FLOW AUTO CDC:将此声明为 CDC 流。 Azure Databricks自动应用插入、更新和删除语义。 -
WHERE _change_type != 'update_preimage':跳过 preimage。 后像已经包含了更新的行。 -
KEYS (product_id):业务密钥。 具有相同键的事件合并到版本化行中。 -
APPLY AS DELETE WHEN _change_type = 'delete':在删除事件到达时关闭当前版本。 这样就可以定义标识删除事件的条件。 -
SEQUENCE BY _commit_timestamp:建立事件排序。 正确处理无序到达。 -
STORED AS SCD TYPE 2:保留完整历史记录。AUTO CDC同时支持 SCD 类型 1 和 SCD 类型 2。
查询维度表:
SELECT product_id, product_name, warehouse, __START_AT, __END_AT
FROM products_history
ORDER BY product_id, __START_AT;
- 勺子:两个版本。 西雅图(关闭,
__END_AT设置)和洛杉矶(当前,__END_AT = NULL)。 - 分支:两个版本。 餐具类(已关闭)和餐饮类(当前)。
- 餐巾纸和杯垫:各一个版本(新插入,
__END_AT = NULL)。 - 其他所有产品:各一个版本(
__END_AT = NULL)。
步骤 5:处理通过管道删除
现在,通过从源表中删除两个已停用的产品来模拟它们:
DELETE FROM products WHERE product_id = 9;
DELETE FROM products WHERE product_id = 10;
这些删除事件记录在 CDF 日志中,但流式处理表尚未看到它们。 刷新流表以处理新事件。
REFRESH STREAMING TABLE products_history;
查询维度表以验证是否已应用删除:
SELECT product_id, product_name, warehouse, __START_AT, __END_AT
FROM products_history
ORDER BY product_id, __START_AT;
碗和玻璃现在通过 __END_AT 设置已停产,被标记为终止。 所有其他当前产品保持不变。 流式处理表仅处理新的删除事件,而无需重新处理上一次刷新中的插入和更新。
步骤6:构建带有 REPLACE WHERE 流程的事实表
前几步的维度表之所以使用 AUTO CDC ,是因为它追踪每个产品的当前和历史状态。 事实表有不同的需求。 事实表记录事件,比如销售,通常只重新处理最近的窗口,以纠正晚到的记录。 对于此模式,使用增量 REPLACE WHERE flow,只覆盖与谓词匹配的行,其他行保持不动。 有关此构造与具体化视图的完整对比,请参阅何时使用 REPLACE WHERE 与具体化视图。
创建一个
product_sales表格,加载几个销售事件,有些是近期的,有些是较早的:CREATE OR REPLACE TABLE product_sales ( sale_id INT, product_id INT, quantity INT, sale_date DATE ); INSERT INTO product_sales VALUES (101, 1, 3, current_date()), (102, 2, 1, current_date()), (103, 4, 5, date_add(current_date(), -2)), (104, 6, 2, date_add(current_date(), -10));创建一个事实表,每次刷新时只重新计算最近7天的销售情况:
CREATE OR REFRESH STREAMING TABLE product_sales_recent SCHEDULE REFRESH EVERY 1 DAY FLOW REPLACE WHERE sale_date >= date_add(current_date(), -7) BY NAME SELECT sale_id, product_id, quantity, sale_date FROM product_sales;-
FLOW REPLACE WHERE sale_date >= date_add(current_date(), -7)每次刷新时,删除目标中与谓词匹配的行,重新计算同一区间的源查询,并插入结果。 7天窗口期外的行保持原样。 -
SCHEDULE REFRESH EVERY 1 DAY: 以与维度表相同的每日计划刷新表。 -
BY NAME:必须。 按列名而非位置匹配。
这笔 10 天前的销售记录不在谓词范围内,因此流不会加载该记录。 只保留过去7天的销售记录。
-
查询事实表:
SELECT sale_id, product_id, quantity, sale_date FROM product_sales_recent ORDER BY sale_date DESC, sale_id;结果包含了过去7天内的三笔销售。
sale_date这些数值取决于你运行教程的日期:sale_id product_id 数量 销售日期 101 1 3 今天 102 2 1 今天 103 4 5 2 天前 104号销售未被计入结果,因为其日期超出了7天窗口。
步骤7:创建一个聚合物化视图
现在,你有了一个能够随源变化及时更新的维度表,可以在其上添加一个报告层。
具体化视图将预先计算的查询结果存储为物理表。 与常规视图每次读取时重新执行查询不同,物化视图会持久化结果,只在每次刷新时重新计算受上游更改影响的行。 这使得它特别适合用于对查询性能要求较高的仪表板和报表。
CREATE OR REPLACE MATERIALIZED VIEW products_by_category
SCHEDULE REFRESH EVERY 1 DAY
AS
SELECT
category,
COUNT(*) AS active_products
FROM products_history
WHERE __END_AT IS NULL
GROUP BY category;
SCHEDULE REFRESH EVERY 1 DAY 表示此视图按每日计划刷新。 与流式处理表上的相同计划相结合,现在有一个三阶段管道,其中源表的更改会通过维度级联,并在每个刷新周期中聚合。 你不需要手动刷新维度或聚合。
SELECT * FROM products_by_category ORDER BY active_products DESC;
第八步:验证端到端级联
要验证完整的管道级联,请更改源表并记录昨天的晚到销售:
UPDATE products SET warehouse = 'Seattle' WHERE product_id = 3;
INSERT INTO product_sales VALUES (105, 3, 4, date_add(current_date(), -1));
刀从丹佛移动到西雅图。 该 UPDATE 触发了完整的管道级联,展示了这三个阶段如何协同工作:
-
products在变更数据流中记录变更事件。 -
products_history处理事件并为 Knife 添加新版本。 -
products_by_category仅重新计算受影响的餐具行。
事实表会按自己的时间表更新。 要立即提取延迟到达的销售数据,而不是等待下一次预定刷新,请手动刷新事实表:
REFRESH STREAMING TABLE product_sales_recent;
该流程从 product_sales 重新计算了过去 7 天的数据,因此销售 105 现在出现在 product_sales_recent。
验证:
SELECT product_id, product_name, warehouse, __START_AT, __END_AT
FROM products_history
WHERE product_id = 3
ORDER BY __START_AT;
SELECT * FROM products_by_category ORDER BY active_products DESC;
SELECT sale_id, product_id, quantity, sale_date
FROM product_sales_recent
ORDER BY sale_date DESC, sale_id;
清理
若要清理本教程创建的资源,请使用以下 SQL:
DROP MATERIALIZED VIEW IF EXISTS products_by_category;
DROP TABLE IF EXISTS product_sales_recent;
DROP TABLE IF EXISTS product_sales;
DROP TABLE IF EXISTS products_history;
DROP TABLE IF EXISTS products;