网站做关键词首页,什么是网络营销?如何理解它的产生和把握它的特点?,天津建设工程招标信息网官网,网站建设与维护的重要性在数据仓库和商业智能领域#xff0c;处理随时间变化的数据是一个常见且具有挑战性的任务。缓慢变化维度(Slowly Changing Dimensions, SCD)是解决这一问题的经典模式。本文将深入探讨SQLMesh中SCD Type 2的实现方式、配置选项以及实际应用场景。 什么是SCD Type 2#xff1f… 在数据仓库和商业智能领域处理随时间变化的数据是一个常见且具有挑战性的任务。缓慢变化维度(Slowly Changing Dimensions, SCD)是解决这一问题的经典模式。本文将深入探讨SQLMesh中SCD Type 2的实现方式、配置选项以及实际应用场景。 什么是SCD Type 2
SCD Type 2是一种用于跟踪维度表中记录历史变化的模型。它通过为每条记录添加有效时间范围(valid_from和valid_to)来实现这一点
valid_from: 记录生效的起始时间(包含)valid_to: 记录失效的结束时间(不包含)最新记录的valid_to设为NULL
这种设计允许我们不仅了解当前的数据状态还能追溯任何时间点的历史数据。
SQLMesh中的SCD Type 2实现
SQLMesh提供了两种实现SCD Type 2的方式基于时间戳和基于列值比较。
SCD Type 2 By Time(基于时间戳)
这是SQLMesh推荐的方式适用于源表包含更新时间(updated_at)字段的情况。
模型定义示例
MODEL (name db.menu_items,kind SCD_TYPE_2_BY_TIME (unique_key id,)
);SELECTid::INT,name::STRING,price::DOUBLE,updated_at::TIMESTAMP
FROMstg.current_menu_items;特点
使用updated_at字段精确确定记录变更时间提高SCD Type 2表的准确性需要源表包含时间戳字段
SCD Type 2 By Column(基于列值比较)
适用于源表不包含时间戳字段的情况通过比较指定列的值变化来检测变更。
模型定义示例
MODEL (name db.menu_items,kind SCD_TYPE_2_BY_COLUMN (unique_key id,columns [name, price])
);SELECTid::INT,name::STRING,price::DOUBLE,
FROMstg.current_menu_items;特点
不需要updated_at字段通过比较指定列的值变化来检测变更变更时间基于SQLMesh执行时间
高级配置选项
SQLMesh提供了丰富的配置选项来定制SCD Type 2行为
共享配置
unique_key: 用于标识源表和目标表之间行的唯一键valid_from_name/valid_to_name: 自定义有效时间列名invalidate_hard_deletes: 控制硬删除记录的处理方式batch_size: 批处理大小用于历史数据处理
SCD Type 2 By Time特有配置
updated_at_name: 指定包含时间戳的列名updated_at_as_valid_from: 控制新记录valid_from的设置方式
SCD Type 2 By Column特有配置
columns: 指定需要检查变化的列(使用*表示所有列)execution_time_as_valid_from: 控制新记录valid_from的设置方式updated_at_name: 如果源表包含可用作valid_from的时间戳列
数据变更处理机制
记录更新
当检测到记录变更时SQLMesh会
将旧记录的valid_to设置为变更时间插入新记录valid_from设置为变更时间valid_to为NULL
记录删除
删除处理取决于invalidate_hard_deletes设置
默认(false): 删除记录的valid_to保持NULL如果记录重新添加valid_to设置为新记录的valid_from设置为true: 删除记录的valid_to设置为SQLMesh运行开始时间重新添加记录不会改变valid_to
查询SCD Type 2模型
SQLMesh提供了多种查询SCD Type 2模型的方法 查询当前版本记录: SELECT * FROM menu_items WHERE valid_to IS NULL;查询特定时间点的记录: SELECT * FROM menu_items
WHERE id 1
AND 2020-01-02 01:00:00 valid_from
AND 2020-01-02 01:00:00 COALESCE(valid_to, CAST(2199-12-31 23:59:5900:00 AS TIMESTAMP));查询已删除记录: SELECT id, MAX(CASE WHEN valid_to IS NULL THEN 0 ELSE 1 END) AS is_deleted
FROM menu_items
GROUP BY id;历史数据处理与重置
SQLMesh支持处理包含历史数据的源表通过设置batch_size为1可以按时间顺序处理每个间隔的数据。
重要提示SCD Type 2模型设计上保护已捕获的历史数据但可以通过设置disable_restatement为false来清除历史并重新开始。这是一个危险操作可能导致数据不可恢复。
实际应用示例
SCD Type 2按时间实现示例。假设您的源表初始有以下数据并且invalidate_hard_deletes设置为true
ID名称价格更新时间1鸡肉三明治10.992020-01-01 00:00:002芝士汉堡8.992020-01-01 00:00:003薯条4.992020-01-01 00:00:00
目标表当前为空将会被物化为以下数据
ID名称价格更新时间有效起始时间有效结束时间1鸡肉三明治10.992020-01-01 00:00:001970-01-01 00:00:00NULL2芝士汉堡8.992020-01-01 00:00:001970-01-01 00:00:00NULL3薯条4.992020-01-01 00:00:001970-01-01 00:00:00NULL
现在假设您更新源表为以下数据
ID名称价格更新时间1鸡肉三明治12.992020-01-02 00:00:003薯条4.992020-01-01 00:00:004奶昔3.992020-01-02 00:00:00
变更摘要
鸡肉三明治的价格从10.99美元上涨到12.99美元芝士汉堡从菜单中移除奶昔被添加到菜单中
假设您的管道在2020-01-02 11:00:00运行目标表将被更新为以下数据
ID名称价格更新时间有效起始时间有效结束时间1鸡肉三明治10.992020-01-01 00:00:001970-01-01 00:00:002020-01-02 00:00:001鸡肉三明治12.992020-01-02 00:00:002020-01-02 00:00:00NULL2芝士汉堡8.992020-01-01 00:00:001970-01-01 00:00:002020-01-02 11:00:003薯条4.992020-01-01 00:00:001970-01-01 00:00:00NULL4奶昔3.992020-01-02 00:00:002020-01-02 00:00:00NULL
在我们的最后一次更新中假设您将源表更新为以下数据
ID名称价格更新时间1鸡肉三明治14.992020-01-03 00:00:002芝士汉堡8.992020-01-03 00:00:003薯条4.992020-01-01 00:00:004巧克力奶昔3.992020-01-02 00:00:00
变更摘要
鸡肉三明治的价格从12.99美元上涨到14.99美元(很不错)芝士汉堡被重新添加到菜单中使用原始名称和价格奶昔名称更新为巧克力奶昔
目标表将被更新为以下数据
ID名称价格更新时间有效起始时间有效结束时间1鸡肉三明治10.992020-01-01 00:00:001970-01-01 00:00:002020-01-02 00:00:001鸡肉三明治12.992020-01-02 00:00:002020-01-02 00:00:002020-01-03 00:00:001鸡肉三明治14.992020-01-03 00:00:002020-01-03 00:00:00NULL2芝士汉堡8.992020-01-01 00:00:001970-01-01 00:00:002020-01-02 11:00:002芝士汉堡8.992020-01-03 00:00:002020-01-03 00:00:00NULL3薯条4.992020-01-01 00:00:001970-01-01 00:00:00NULL4奶昔3.992020-01-02 00:00:002020-01-02 00:00:002020-01-03 00:00:004巧克力奶昔3.992020-01-03 00:00:002020-01-03 00:00:00NULL
注意芝士汉堡从2020-01-02 11:00:00到2020-01-03 00:00:00期间被删除这意味着如果您在那个时间范围内查询表将看不到芝士汉堡。这是基于源数据提供的菜单最准确的表示。如果芝士汉堡以原始更新时间戳2020-01-01 00:00:00被重新添加到菜单中那么新记录的valid_from时间戳将是2020-01-02 11:00:00导致没有时间段显示该项目被删除。由于在这种情况下更新时间戳没有变化很可能该项目是错误删除的这再次最准确地反映了基于源数据的菜单情况。
最后总结
SQLMesh中的SCD Type 2实现提供了灵活且强大的方式来处理缓慢变化维度。通过基于时间戳或列值比较的两种方法以及丰富的配置选项可以满足各种业务场景的需求。理解其工作原理和配置选项对于设计高效、准确的数据仓库至关重要。
无论是需要精确跟踪变更时间的场景还是源表缺乏时间戳信息的情况SQLMesh都提供了相应的解决方案。合理配置SCD Type 2模型可以确保数据仓库既能反映当前状态又能保留完整的历史变更记录为业务分析提供坚实的数据基础。
