SQLMesh 系列教程5- 詳解SQL模型

本文將詳細介紹 SQLMesh 的 SQL 模型組成要素及其在實際項目中的應用。SQLMesh 是一個強大的數據工程工具，其 SQL 模型由 MODEL DDL、預處理語句、主查詢、后處理語句以及可選的 ON VIRTUAL UPDATE 語句組成。我們將通過一個電商平臺每日銷售報告的實例，展示如何利用這些要素構建高效的數據管道。文章將逐步解析每個部分的作用，并說明如何通過 SQLMesh 實現增量更新和虛擬更新測試，幫助讀者掌握 SQLMesh 的核心功能及其在實際場景中的最佳實踐。

SQL模型概述

SQL模型是SQLMesh使用的主要模型類型。這些模型可以使用SQL或生成SQL的Python來定義。
在這里插入圖片描述

基于sql的定義，基于SQL的SQL模型定義是最常見的定義，它由以下部分組成：

MODEL DDL：

使用 MODEL 關鍵字定義模型的基本信息，包括模型名稱、目標表、分區策略等。這是 SQL 模型的入口點，用于聲明模型的元數據。
可選的預處理語句（Pre-statements）：

在模型的主查詢之前執行的 SQL 語句。通常用于創建臨時表、設置變量或執行其他準備工作。
單個查詢（Main Query）：

模型的核心部分，定義數據轉換邏輯。必須是一個單獨的 SELECT 查詢，用于生成目標表的數據。
可選的后處理語句（Post-statements）：

在主查詢之后執行的 SQL 語句。通常用于清理臨時表、更新元數據或執行其他收尾工作。
可選的 ON VIRTUAL UPDATE 語句：

用于定義在虛擬更新（Virtual Update）時的行為。虛擬更新是 SQLMesh 的一種機制，允許在不實際修改數據的情況下測試模型的更改。

這些模型的設計目的是讓你看起來像是在簡單地使用SQL，但它們可以針對高級用例進行定制。

要創建基于sql的模型，請在SQLMesh項目中的models/目錄（或models/的子目錄）中添加一個后綴為.sql的新文件。雖然文件名并不重要，但是習慣上使用模型名（不帶模式名）作為文件名。例如，包含sqlmesh_example.seed_model的模型文件，將被命名為seed_model.sql。

舉例：

-- This is the MODEL DDL, where you specify model metadata and configuration information.
MODEL (name db.customers,kind FULL,
);/*Optional pre-statements that will run before the model's query.You should NOT do things that cause side effects that could error out whenexecuted concurrently with other statements, such as creating physical tables.
*/
CACHE TABLE countries AS SELECT * FROM raw.countries;/*This is the single query that defines the model's logic.Although it is not required, it is considered best practice to explicitlyspecify the type for each one of the model's columns through casting.
*/
SELECTr.id::INT,r.name::TEXT,c.country::TEXT
FROM raw.restaurants AS r
JOIN countries AS cON r.id = c.restaurant_id;/*Optional post-statements that will run after the model's query.You should NOT do things that cause side effects that could error out whenexecuted concurrently with other statements, such as creating physical tables.
*/
UNCACHE TABLE countries;

模型DDL

MODEL DDL用于指定關于模型的元數據，例如模型的名稱、類型、所有者、cron等。這應該是基于sql的模型文件中的首個語句。有關允許的屬性的完整列表，請參閱MODEL屬性。

可pre/post-statements

可選的pre/post語句允許你分別在模型運行之前和之后執行SQL命令。

例如，pre/post語句可能會修改設置或創建表索引。但是，如果并發運行，請注意不要運行任何可能與另一個模型的執行沖突的語句，例如創建物理表。

pre/post 語句只是位于模型查詢之前/之后的標準SQL命令。它們必須以分號結束，如果存在后置語句，則模型查詢必須以分號結束。上面的例子包含了前置語句和后置語句。

Pre/post語句被求值兩次：當創建模型的表時，以及當計算其查詢邏輯時。多次執行語句可能會產生意想不到的副作用，因此可以根據SQLMesh的運行時階段有條件地執行語句。

上面示例中的pre/post語句將運行兩次，因為它們不受運行時階段的限制。

我們可以使用@IF宏操作符和@runtime_stage宏變量對后置語句進行條件調整，使其僅在模型查詢被評估后運行，如下所示：

MODEL (name db.customers,kind FULL,
);[...same as example above...]@IF(@runtime_stage = 'evaluating',UNCACHE TABLE countries
);

注意，@IF（）宏中的SQL命令UNCACHE TABLE countries不以分號結束。相反，分號出現在@IF（）宏的右括號之后。

可選的on-virtual-update語句

可選的on-virtual-update語句允許你在虛擬更新完成后執行SQL命令。

例如，可以使用這些權限來授予虛擬層視圖的權限。這些SQL語句必須包含在ON_VIRTUAL_UPDATE_BEGIN；…;ON_VIRTUAL_UPDATE_END；

MODEL (name db.customers,kind FULL
);SELECTr.id::INT
FROM raw.restaurants AS r;ON_VIRTUAL_UPDATE_BEGIN; 
GRANT SELECT ON VIEW @this_model TO ROLE role_name;
JINJA_STATEMENT_BEGIN;     
GRANT SELECT ON VIEW {{ this_model }} TO ROLE admin;
JINJA_END;  
ON_VIRTUAL_UPDATE_END;

也可以在其中使用Jinja表達式，如上面的示例所示。這些表達式必須正確地嵌套在JINJA_STATEMENT_BEGIN；和JINJA_END;塊。

這些語句的表解析發生在虛擬層。這意味著表名，包括@this_model宏，被解析為它們的限定視圖名。例如，當在名為dev的環境中運行計劃時，db.customers和@this_model將解析為db__dev.customers而不是物理表名。

模型查詢

模型必須包含一個獨立的查詢，它可以是單個SELECT表達式，也可以是多個SELECT表達式與UNION、INTERSECT或EXCEPT操作符的組合。該查詢的結果將用于填充模型的表或視圖。

完整實例

實際應用場景

在一個電商平臺的數據分析項目中，該 SQL 模型可以用于：

每日銷售報告：每天自動生成銷售數據，供業務團隊分析。
增量更新：只處理當天的訂單數據，避免全量計算，提高效率。
虛擬更新測試：在部署前測試模型的更改，確保不會破壞現有數據管道。

以下是一個完整的 SQLMesh SQL 模型示例，結合上述實際應用場景：假設我們需要從原始訂單數據中生成每日銷售報告。

原始數據表：raw_orders，包含訂單的詳細信息。
目標數據表：daily_sales_report，按天匯總銷售數據。

SQL 模型腳本

-- MODEL DDL
MODEL (name db.daily_sales_report, -- 模型名稱和目標表kind INCREMENTAL_BY_TIME_RANGE ( -- 增量模型，按時間范圍更新time_column order_date),cron '@daily', -- 每天執行一次grain [order_date] -- 數據粒度
);-- 可選的預處理語句
-- 例如：創建一個臨時表來存儲當天的訂單數據
CREATE TEMPORARY TABLE temp_daily_orders AS
SELECT *
FROM raw_orders
WHERE order_date = @start_ds;-- 單個查詢（主查詢）
SELECTorder_date,SUM(quantity * price) AS total_sales, -- 計算總銷售額COUNT(DISTINCT order_id) AS total_orders, -- 計算總訂單數SUM(quantity * price) / COUNT(DISTINCT order_id) AS avg_order_value -- 計算平均訂單價值
FROM temp_daily_orders
GROUP BY order_date;-- 可選的后處理語句
-- 例如：刪除臨時表
DROP TABLE IF EXISTS temp_daily_orders;-- 可選的 ON VIRTUAL UPDATE 語句
ON VIRTUAL UPDATE {-- 在虛擬更新時，返回一個示例結果集SELECT'2023-10-01' AS order_date,1000.00 AS total_sales,10 AS total_orders,100.00 AS avg_order_value;
};

詳細說明

MODEL DDL：
- name：定義模型的名稱和目標表（db.daily_sales_report）。
- kind：指定模型的類型。這里使用 INCREMENTAL_BY_TIME_RANGE，表示這是按時間范圍更新的增量模型。
- time_column：指定時間列（order_date），用于增量更新。
- cron：定義模型的調度頻率（每天執行一次）。
- grain：定義數據的粒度（按 order_date 聚合）。
預處理語句：
- 創建了一個臨時表 temp_daily_orders，用于存儲當天的訂單數據。
- @start_ds 是 SQLMesh 提供的宏，表示當前處理的時間范圍起點。
主查詢：
- 從臨時表 temp_daily_orders 中查詢數據，按 order_date 聚合計算總銷售額、總訂單數和平均訂單價值。
后處理語句：
- 清理臨時表 temp_daily_orders，避免占用資源。
ON VIRTUAL UPDATE：
- 在虛擬更新時，返回一個示例結果集，用于測試模型的輸出結構。

通過這種方式，SQLMesh 的 SQL 模型能夠清晰地定義數據轉換邏輯，同時支持增量更新和虛擬更新，非常適合復雜的數據工程場景。
在這里插入圖片描述

最后總結

本文深入探討了 SQLMesh 的 SQL 模型組成要素，包括 MODEL DDL、預處理語句、主查詢、后處理語句以及 ON VIRTUAL UPDATE 語句。通過一個電商平臺每日銷售報告的實例，我們展示了如何利用 SQLMesh 構建高效、可維護的數據管道。SQLMesh 的增量更新機制和虛擬更新測試功能，極大地提升了數據工程的靈活性和可靠性。無論是處理大規模數據還是優化數據工作流，SQLMesh 都提供了強大的工具和方法，幫助團隊實現數據驅動決策。希望本文能為讀者在實際項目中應用 SQLMesh 提供有價值的參考。