dbt-models

dbt-models

内容概览：

  • SQL模型
    • 创建模型
    • 模型配置
    • 问题整理
  • Python模型（待编写）

一些案例项目：

1. SQL模型

1.1 创建模型

关于模型的创建，主要包括两个步骤：

  1. 配置 schema.yml文件：用于定义数据源【sources】和表资源【models】
  2. 编写SQL脚本： 基于SQL开发业务逻辑。

使用dbt init 创建项目，models生成的样例项目的配置是schema.yml，但是官方的文档里，写的是properties.yml，这个名称似乎无所谓的。

schema.yml 配置样例：

version: 2

sources:
  - name : udo
    description : "test database"
    tables:
      - name : t_auther
      - name : t_bookstore 

models:
  - name: t_auther
    description: "作者信息"
    columns:
      - name: u_id
        description: "primary key"
        tests:
          - unique
          - not_null
      - name: u_auther_name
      - name: u_desc
  - name: t_bookstore
    description: "书籍信息"
    columns:
      - name: u_id
        description: "primary key"
        tests:
          - unique
          - not_null
      - name: u_bookname
      - name: u_bookdesc

SQL脚本样例 ：

SQL语法这里不再多说了，比较有意思的，SQL脚本里是支持jinja2语法的。这样我们可以通过模版语法开发一些特殊的东西。

需要注意的一点是，如果模型需要输出，模型其实是存储到某个文章或者视图了，模型的名字就是表名或者视图名。所以最好选择英文文件名，防止某些时候中文名称带来的困扰。

从例子里看，好像SQL里都用到了“公共表达式（Common Table Expressions，CTE）”，之前在阅读mysql的使用教程中看到了这个定义，简单的说，在一些场景下，我们在一个sql中需要重复使用到一个查询结果，而且这个查询结果，每次都需要在查询中重新计算结果，但是呢，我们手里的权限又不太方便建中间表或者视图，这时候，就可以用CTE，在查询的一开始就定义需要查询结果集，mysql会在查询开始前，执行一次查询，（就好像建了一个视图，但实际上没有），然后在查询中使用CTE的名称就可以反复引用。【说实话，一开始并没有理解这个CTE的优势，但在dbt的例子里，这个方式，好像真的很不错】

models/customers.sql

with customer_orders as (
    select
        customer_id,
        min(order_date) as first_order_date,
        max(order_date) as most_recent_order_date,
        count(order_id) as number_of_orders

    from jaffle_shop.orders

    group by 1
)

select
    customers.customer_id,
    customers.first_name,
    customers.last_name,
    customer_orders.first_order_date,
    customer_orders.most_recent_order_date,
    coalesce(customer_orders.number_of_orders, 0) as number_of_orders

from jaffle_shop.customers

left join customer_orders using (customer_id)

完成模型和配置、模型脚本撰写后，就可以执行dbt run来运行编写好的模型。

运行单个SQL模型，测试模型，参考下面的“注意事项”。

1.2 模型配置

模型配置的内容比较多，所以另外进行整理

对于model的配置，包括了三个层级的配置：

  • 项目级别：项目下的dbt_project.yml。可以指定models文件夹下每个子目录的物化方式、标签等
  • 模型级别：模型文件夹中的schema.yml。可以在models文件夹对应的目录里，为我们的模型做配置，名称并不进行特殊要求，只要是yml格式就可以。
  • 文件级别：单个模型脚本内的配置。可以在这里单独指定模型物化的方式，sql-header等。

配置是有自己范围的，虽然在全局定义了一些东西，但不是每个模型都必须继承这个配置，最底一层的配置会覆盖上级的。（类似css的配置）

模型配置包含的内容：

  • materializations（物化方式）： view，table,incremental and ephemeral.
  • 物化配置的介绍：https://docs.getdbt.com/docs/build/materializations
  • 为模型添加标签，以便于分类
  • 为表或者视图创建别名
  • 配置一些SQL代码，在模型运行前后做一些操作，如自定义函数，行列权限，清理表等。
  • 将模型拆分为多个模式
  • 性能方面的配置。

1.3 问题整理

这里整理了开发中遇到的问题：

1、如何查看正在执行的sql？

可以看命令行界面，或者在target目录下的compiled和run目录下查看对应编译转换后的脚本。

2、是否需要预先在数据库中建立对应的结果表？

不需要，程序会检测是否存在，如果不存在就创建。

3、如果SQL或数据库有问题，会发生什么情况？

CLI中会返回错误，根据提示进行解决即可。模型下游任务也会因此挂了，程序会跳过这部分分受影响的程序。

4、 使用哪个SQL规范开发模型？

嗯，dbt没有提供各种规范的自动转换，也就是说，需要针对对应的平台，编写不同的脚本。

5、 为啥示例模型里都是一个select查询，而不是相关的建表语句？

嗯，可能我的理解还不够完善，不过可以看这个官方提供的解释：https://docs.getdbt.com/faqs/project/why-not-write-dml

这里整理了一下我的理解：

  • 不同数据库平台的DML语句是不一样的，适配起来比较麻烦，但是select语句，基本上大部分数据库是通用的，适配更加容易。
  • select不会更改数据库的数据，这样可以避免对数据库的影响。（也便于对数据资源的安全管理）
  • 便于迁移复用，dbt会实现对接平台的建表工作，会自动进行检测。
  • 便于数据的血缘管理，搞定执行顺序。项目变的复杂后，我们就需要开始需要关注数据是怎么来的，如果是从一堆表里去分析数据的来源，其实挺困难的，但是基于select查询语句的话，就很容易了。通过关键内容的抽取，借助相关图算法，就可以快速的分析出来血缘关系。（统计高频用表、数据血缘回溯，字段血缘回溯等等）。

6、 如何测试模型和执行单个模型？

可以查看命令行相关信息：

  • 测试模型：

    • dbt test : 测试所有的模型
    • dbt test -select xxx ： 测试某个模型

  • 执行模型：

    • dbt run
    • dbt run -select xxx

7、 模型名称是否需要唯一？

是的，为了依赖关系的创建，这里使用ref函数，名称因而需要唯一。（即使是不同目录，也需要唯一）

8、如何删除创建的模型？

dbt不会自动删除，需要手工，一个删除模型文件，一个是删除数据库表或视图

9、 业务模型需要设计数据的插入，如何操作？

在一些ETL模式中，其实很多时候会把一些逻辑最后变成insert和update语句，在dbt里，这里是将其包装为一个create table as 语句中。但一些业务确实是“增量的”，所以这里提供了两种方式。

10、 如何修改列的类型？

最简单的方式就是在select 语句里写好转换，其他的方式，都会比较折腾。

暂时未找到答案的问题：

  1. 如果模型很复杂，而且其中某一个挂了，如何拉起当前及下游的模型？
  2. 平台某几天挂了，如何补历史数据？
  3. 如何对某个或某几个模型组做补数据、测试模型的操作？
  4. 任务参数如何定义，补数据的时候，是否会动态的变化？

2. python 模型

python模型是SQL模型的一个扩展，官方推荐pyspark和Snowpark 之类的方式。（如果数据量很大，其实不太适合单机处理，所以需要分布式的平台来处理），由于暂时不太方便创建pyspark环境，这里就先跳过，后面在进行补充。

posted @ 2023-06-11 18:54  人人从众  阅读(194)  评论(0)  编辑  收藏  举报