SQLMesh 系列教程9- 宏变量及内置宏变量

SQLMesh 的宏变量是一个强大的工具，能够显著提高 SQL 模型的动态化能力和可维护性。通过合理使用宏变量，可以实现动态时间范围、多环境配置、参数化查询等功能，从而简化数据模型的开发和维护流程。随着数据团队的规模扩大和业务复杂度的增加，宏变量的重要性将愈发凸显。

介绍宏变量

SQLMesh 是一个开源的数据转换框架，旨在简化 SQL 数据模型的设计、维护和部署流程。它通过引入 DevOps 最佳实践，支持多引擎、虚拟环境和数据血缘分析等功能，帮助数据团队高效管理数据模型。在 SQLMesh 中，宏变量（Macro Variables）是一个重要的特性，用于在 SQL 模型中实现动态化和可重用性。以下将详细介绍 SQLMesh 的宏变量，并结合示例说明其用法和优势。

宏变量的定义与作用

宏变量是 SQLMesh 中用于动态替换值的占位符，允许用户在 SQL 模型中定义可重用的逻辑。它们的主要作用包括：

• 动态化 SQL 逻辑：通过宏变量，可以在运行时动态替换 SQL 中的值，避免硬编码。
• 提高代码复用性：将常用的逻辑封装为宏变量，减少重复代码。
• 简化维护：通过集中管理宏变量，降低维护成本。

SQLMesh 的宏变量通常以 @ 或 $ 开头，并在 SQL 模型中被引用。宏变量的值可以在模型定义、运行时或配置文件中指定14。

宏变量的分类

在 SQLMesh 中，宏变量可以分为以下几类：

• 内置宏变量：SQLMesh 提供了一些预定义的宏变量，例如 @start_date 和 @end_date，用于处理时间范围相关的逻辑。
• 用户自定义宏变量：用户可以根据需求定义自己的宏变量，并在 SQL 模型中引用。
• 环境变量：SQLMesh 支持从环境变量中读取值，并将其作为宏变量使用。

宏变量的使用场景

宏变量在 SQLMesh 中的应用场景非常广泛，以下是一些典型用例：

• 动态时间范围：在增量模型中，使用 @start_date 和 @end_date 动态指定时间范围。
• 多环境配置：通过宏变量区分开发、测试和生产环境，例如动态切换数据库连接。
• 参数化查询：在查询中使用宏变量实现参数化，避免 SQL 注入风险。
• 代码复用：将复杂的逻辑封装为宏变量，供多个模型调用。

举例说明

宏变量是占位符，其值在呈现宏时被替换。它们支持动态宏行为——例如，日期参数的值可能基于宏运行的时间。

考虑一个在WHERE子句中按日期过滤的SQL查询。与每次运行模型时手动更改日期不同，您可以使用宏变量使日期动态。使用动态方法，日期会根据查询的运行时间自动更改。

这个查询过滤列my_date在‘2023-01-01’之后的行：

SELECT *
FROM table
WHERE my_date > '2023-01-01'

要使这个查询的日期动态，你可以使用预定义的SQLMesh宏变量@execution_ds：

SELECT *
FROM table
WHERE my_date > @execution_ds

@符号告诉SQLMesh， @execution_ds是一个宏变量，需要在执行SQL之前进行替换。

宏变量@execution_ds是预定义的，因此它的值将由SQLMesh根据执行开始的时间自动设置。如果模型在2023年2月1日执行，呈现的查询将是：

SELECT *
FROM table
WHERE my_date > '2023-02-01'

这个例子使用了SQLMesh的一个预定义变量，但是你也可以定义自己的宏变量。

我们将在下面描述SQLMesh的预定义变量；在SQLMesh宏和Jinja宏页面中讨论了用户定义的宏变量。

内置宏变量

SQLMesh附带了可以在查询中使用的预定义变量。它们由SQLMesh运行时自动设置。

大多数预定义变量都与时间相关，并使用前缀（start、end等）和后缀（date、ds、ts等）的组合。它们将在下一节中描述；下一节将讨论其他预定义变量。

时间变量

SQLMesh使用python datetime模块来处理日期和时间。它使用标准的Unix纪元开始符1970-01-01。

前缀:

• start - 包括模型运行的启动区间
• end - 包括模型运行的结束区间
• execution - 执行开始的时间戳

后缀：

• dt - Python 的 datetime 对象，可转换为原生的 SQL TIMESTAMP（或等效的 SQL 引擎类型）
• date - Python 的 date 对象，可转换为原生的 SQL DATE
• ds - 格式为 ‘%Y-%m-%d’ 的日期字符串
• ts - 格式为 ‘%Y-%m-%d %H:%M:%S’ 的 ISO 8601 日期时间格式字符串
• tstz - 格式为 ‘%Y-%m-%d %H:%M:%S%z’ 的带有时区的 ISO 8601 日期时间格式字符串
• hour - 表示一天中的小时数的整数，取值范围为 0 到 23
• epoch - 表示自 Unix 纪元以来的秒数的整数
• millis - 表示自 Unix 纪元以来的毫秒数的整数

所有预定义的时间宏变量：

• dt
  • @start_dt
  • @end_dt
  • @execution_dt
• date
  • @start_date
  • @end_date
  • @execution_date
• ds
  • @start_ds
  • @end_ds
  • @execution_ds
• ts
  • @start_ts
  • @end_ts
  • @execution_ts
• tstz
  • @start_tstz
  • @end_tstz
  • @execution_tstz
• hour
  • @start_hour
  • @end_hour
  • @execution_hour
• epoch
  • @start_epoch
  • @end_epoch
  • @execution_epoch
• millis
  • @start_millis
  • @end_millis
  • @execution_millis

运行时变量

SQLMesh提供了另外两个预定义变量，用于根据运行时可用的信息修改模型行为。

• @runtime_stage — 字符串值，表示SQLMesh运行时的当前阶段。通常在模型中用于有条件地执行pre/post语句（在这里了解更多）。它返回以下值之一：

  • loading - 项目正在加载到 SQLMesh 的运行时上下文中。

  • creating - 模型表正在创建。

  • evaluating - 模型查询逻辑正在评估。

  • promoting - 模型正在目标环境中推广（虚拟层更新）。

  • auditing - 正在运行审计。

  • testing - 模型查询逻辑正在单元测试的上下文中评估。

• @gateway — 包含当前网关名称的字符串值。

• @this_model — 字符串值，包含模型视图选择的物理表的名称。通常用于创建通用审核。在on_virtual_update语句的情况下，它包含限定视图名称。

  • 当SQLGlot不能完全解析语句，需要直接引用模型的底层物理表时，可以在模型定义中使用。

  • 可以作为参数传递给访问或与底层物理表交互的宏。

实战案例

以下是一个完整的示例，展示如何在 SQLMesh 中使用宏变量实现动态时间范围和多环境配置。

1. 项目配置

gateways:prod_gateway:connection:type: duckdbdatabase: prod.dbdev_gateway:connection:type: duckdbdatabase: dev.dbdefault_gateway: dev_gateway

2. 定义模型

MODEL (name example.incremental_model,owner Yuki,kind INCREMENTAL_BY_TIME_RANGE (time_column (updated_date, '%Y-%m-%d'),lookback 5,),start '2025-01-01',cron '@daily',grain id,column_descriptions (id = 'primary key',letter = 'alphabet letter',updated_date = 'updated date',)
);

3. 引用宏变量

SELECTid,letter,updated_date
FROMexample.base_model
WHEREupdated_date BETWEEN @start_date AND @end_dateAND @gateway = 'prod_gateway';

4. 运行计划

sqlmesh plan --gateway prod_gateway

最后总结

SQLMesh 的宏变量是用于动态替换值的占位符，允许用户在 SQL 模型中定义可重用的逻辑。它们的主要作用包括动态化 SQL 逻辑、提高代码复用性和简化维护。SQLMesh 的宏变量可以分为内置宏变量、用户自定义宏变量和环境变量。宏变量在 SQLMesh 中的应用场景非常广泛，包括动态时间范围、多环境配置、参数化查询和代码复用。通过合理使用宏变量，可以显著提高 SQL 模型的动态化能力和可维护性。