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 模型的動態化能力和可維護性。