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 模型可以用于：

1. 每日销售报告：每天自动生成销售数据，供业务团队分析。
2. 增量更新：只处理当天的订单数据，避免全量计算，提高效率。
3. 虚拟更新测试：在部署前测试模型的更改，确保不会破坏现有数据管道。

以下是一个完整的 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;
};

详细说明

1. MODEL DDL：
   • name：定义模型的名称和目标表（db.daily_sales_report）。
   • kind：指定模型的类型。这里使用 INCREMENTAL_BY_TIME_RANGE，表示这是按时间范围更新的增量模型。
   • time_column：指定时间列（order_date），用于增量更新。
   • cron：定义模型的调度频率（每天执行一次）。
   • grain：定义数据的粒度（按 order_date 聚合）。
2. 预处理语句：
   • 创建了一个临时表 temp_daily_orders，用于存储当天的订单数据。
   • @start_ds 是 SQLMesh 提供的宏，表示当前处理的时间范围起点。
3. 主查询：
   • 从临时表 temp_daily_orders 中查询数据，按 order_date 聚合计算总销售额、总订单数和平均订单价值。
4. 后处理语句：
   • 清理临时表 temp_daily_orders，避免占用资源。
5. ON VIRTUAL UPDATE：
   • 在虚拟更新时，返回一个示例结果集，用于测试模型的输出结构。

通过这种方式，SQLMesh 的 SQL 模型能够清晰地定义数据转换逻辑，同时支持增量更新和虚拟更新，非常适合复杂的数据工程场景。

最后总结

本文深入探讨了 SQLMesh 的 SQL 模型组成要素，包括 MODEL DDL、预处理语句、主查询、后处理语句以及 ON VIRTUAL UPDATE 语句。通过一个电商平台每日销售报告的实例，我们展示了如何利用 SQLMesh 构建高效、可维护的数据管道。SQLMesh 的增量更新机制和虚拟更新测试功能，极大地提升了数据工程的灵活性和可靠性。无论是处理大规模数据还是优化数据工作流，SQLMesh 都提供了强大的工具和方法，帮助团队实现数据驱动决策。希望本文能为读者在实际项目中应用 SQLMesh 提供有价值的参考。