当 TiDB 遇见 dbt丨让数据价值清晰可见

dbt （data build tool）是一款流行的开源数据转换工具，能够通过 SQL 实现数据转化，将命令转化为表或者视图，提升数据分析师的工作效率。TiDB 社区在近日推出了dbt-tidb插件，实现了 TiDB 和dbt的兼容适配。本文将通过一个简单的案例介绍如何通过 dbt 实现 TiDB 中数据的简单分析。

dbt 主要功能在于转换数据库或数据仓库中的数据，在 E（Extract）、L（Load）、T（Transform） 的流程中，仅负责转换（transform）的过程。 通过 dbt-tidb 插件，数据分析师在使用 TiDB 的过程中，能够通过 SQL 直接建立表单并匹配数据，而无需关注创建 table 或 view 的过程，并且可以直观地看到数据的流动；同时能够运用 dbt 的 Jinja 编写 SQL、测试、包管理等功能，大大提升工作效率。

（图片来源：https://blog.getdbt.com/what-exactly-is-dbt/）

接下来，我将以dbt 官方教程为例，给大家介绍下 TiDB 与 dbt 的结合使用。

本例用到的相关软件及其版本要求：

• TiDB 5.3 或更高版本
• dbt 1.0.1 或更高版本
• dbt-tidb 1.0.0

安装

dbt 除了本地 CLI 工具外，还支持dbt Cloud(目前，dbt Cloud 只支持 dbt-lab 官方维护的 adapter)，其中本地 CLI 工具有多种安装方式。我们这里直接使用 pypi 安装 dbt 和 dbt-tidb 插件。

安装 dbt 和 dbt-tidb，只需要一条命令，因为 dbt 会作为依赖在安装 dbt-tidb 的时候顺便安装。

$ pip install dbt-tidb

dbt 也可自行安装，安装方式参考官方安装教程。

创建项目：jaffle_shop

jaffle_shop 是 dbt-lab 提供的用于演示 dbt 功能的工程项目，你可以直接从 GitHub 上获取它。

$ gitclonehttps://github.com/dbt-labs/jaffle_shop $cdjaffle_shop

这里展开 jaffle_shop 工程目录下所有文件。

• dbt_project.yml是 dbt 项目的配置文件，其中保存着项目名称、数据库配置文件的路径信息等。
• models目录下存放该项目的 SQL 模型和 table 约束，注意这部分是数据分析师自行编写的。
• seed目录存放 CSV 文件。此类文件可以来源于数据库导出工具，例如TiDB 可以通过Dumpling把 table 中的数据导出为 CSV 文件。jaffle_shop 工程中，这些 CSV 文件用来作为待处理的原始数据。

关于它们更加具体的内容，在用到上面的某个文件或目录后，我会再次进行更详细的说明。

ubuntu@ubuntu:~/jaffle_shop$ tree . ├── dbt_project.yml ├── etc │ ├── dbdiagram_definition.txt │ └── jaffle_shop_erd.png ├── LICENSE ├── models │ ├── customers.sql │ ├── docs.md │ ├── orders.sql │ ├── overview.md │ ├── schema.yml │ └── staging │ ├── schema.yml │ ├── stg_customers.sql │ ├── stg_orders.sql │ └── stg_payments.sql ├── README.md └── seeds ├── raw_customers.csv ├── raw_orders.csv └── raw_payments.csv

配置项目

1.全局配置

dbt 有一个默认的全局配置文件：~/.dbt/profiles.yml，我们首先在用户目录下建立该文件，并配置 TiDB 数据库的连接信息。

$ vi ~/.dbt/profiles.yml jaffle_shop_tidb:# 工程名称target: dev# 目标outputs: dev:type: tidb# 适配器类型server: 127.0.0.1# 地址port: 4000# 端口号schema: analytics# 数据库名称username: root# 用户名password:""# 密码

2.项目配置

jaffle_shop 工程目录下，有此项目的配置文件，名为dbt_project.yml。把profile配置项改为jaffle_shop_tidb，即profiles.yml文件中的工程名称。这样此工程在会到~/.dbt/profiles.yml文件中查询数据库连接配置。

$ cat dbt_project.yml name:'jaffle_shop'config-version: 2 version:'0.1'profile:“jaffle_shop_tidb”# 注意此处修改model-paths: ["models"]# model 路径seed-paths: ["seeds"]# seed 路径test-paths: ["tests"] analysis-paths: ["analysis"] macro-paths: ["macros"] target-path:"target"clean-targets: -"target"-“dbt_modules”-"logs"require-dbt-version: [">=1.0.0","<2.0.0"] models: jaffle_shop: materialized: table# models/ 中的 *.sql 物化为表staging: materialized: view# models/staging/ 中的 *.sql 物化为视图

3.验证配置

可以通过以下命令，检测数据库和项目配置是否正确。

$ dbt debug06:59:18Runningwithdbt=1.0.1dbt version:1.0.1python version:3.8.10python path:/usr/bin/python3 os info: Linux-5.4.0-97-generic-x86_64-with-glibc2.29Usingprofiles.yml fileat/home/ubuntu/.dbt/profiles.ymlUsingdbt_project.yml fileat/home/ubuntu/jaffle_shop/dbt_project.yml Configuration: profiles.yml file [OK foundandvalid] dbt_project.yml file [OK foundandvalid] Configuration: profiles.yml file [OK foundandvalid] dbt_project.yml file [OK foundandvalid] Required dependencies:-git [OK found] Connection: server:127.0.0.1port:4000database:Noneschema: analyticsuser: root Connection test: [OK connection ok]Allchecks passed!

加载 CSV

加载 CSV 数据，把 CSV 具体化为目标数据库中的表。注意：一般来说，dbt 项目不需要这个步骤，因为你的待处理项目的数据都在数据库中。

$dbtseed07:03:24Running with dbt=1.0.107:03:24Partial parse save file not found. Starting full parse.07:03:25Found5models,20tests,0snapshots,0analyses,172macros,0operations,3seed files,0sources,0exposures,0metrics07:03:2507:03:25Concurrency:1threads (target='dev')07:03:2507:03:251of3START seed file analytics.raw_customers..................................[RUN]07:03:251of3OK loaded seed file analytics.raw_customers..............................[INSERT 100 in 0.19s]07:03:252of3START seed file analytics.raw_orders.....................................[RUN]07:03:252of3OK loaded seed file analytics.raw_orders.................................[INSERT 99 in 0.14s]07:03:253of3START seed file analytics.raw_payments...................................[RUN]07:03:263of3OK loaded seed file analytics.raw_payments...............................[INSERT 113 in 0.24s]07:03:2607:03:26Finished running3seeds in0.71s.07:03:2607:03:26Completed successfully07:03:2607:03:26Done. PASS=3WARN=0ERROR=0SKIP=0TOTAL=3

上述结果中，可以清楚的看到共执行了三个任务，分别加载了analytics.raw_customers、analytics.raw_orders、analytics.raw_payments三张表。

接着，去 TiDB 数据库中看看发生了什么。

发现多出了analytics数据库，这是 dbt 为我们创建的工程数据库。

mysql>showdatabases;+--------------------+|Database|+--------------------+|INFORMATION_SCHEMA||METRICS_SCHEMA||PERFORMANCE_SCHEMA||analytics||mysql||test|+--------------------+6rowsinset(0.00sec)

analytics数据库中有三张表，分别对应着上述三个任务结果。

mysql> show tables; +---------------------+ |Tables_in_analytics| +---------------------+ |raw_customers| |raw_orders| |raw_payments| +---------------------+ 3 rows in set (0.00 sec)

model 是什么？

在进行下一个步骤之前，我们有必要先了解下 dbt 中的 model 扮演着什么角色？

dbt 中使用 model 来描述一组数据表或视图的结构，其中主要有两类文件：SQL 和 YML。还需要注意到的是：在 jaffle_shop 这个项目中，根据物化配置，models/目录下保存的是表结构，而models/staging/目录下保存的是视图结构。

以models/orders.sql为例，它是一句 SQL 查询语句，支持jinja语法，接下来的命令中，会根据这条 SQL 创建出orders表。

$ cat models/orders.sql {% set payment_methods = ['credit_card','coupon','bank_transfer','gift_card'] %} with ordersas(select*from{{ ref('stg_orders') }} ), paymentsas(select*from{{ ref('stg_payments') }} ), order_paymentsas(selectorder_id, {%forpayment_methodinpayment_methods -%}sum(casewhen payment_method ='{{ payment_method }}'然后量else0end)as{{ payment_method }}_amount, {% endfor -%}sum(amount)astotal_amountfrompaymentsgroupbyorder_id ),finalas(selectorders.order_id, orders.customer_id, orders.order_date, orders.status, {%forpayment_methodinpayment_methods -%} order_payments.{{ payment_method }}_amount, {% endfor -%} order_payments.total_amountasamountfromorders leftjoinorder_payments on orders.order_id = order_payments.order_id )select*fromfinal

并且，与这条 SQL 配套的约束信息在models/schema.yml文件中。

schema.yml是当前目录下所有模型的注册表，所有的模型都被组织成一个树形结构，描述了每条字段的说明和属性。其中tests条目表示这个字段的一些约束项，可以通过dbt test命令来检测，更多信息请查阅官网文档。

catmodels/schema.ymlversion:2...-name:ordersdescription:Thistablehasbasicinformationaboutorders,aswellassomederivedfactsbasedonpaymentscolumns:-name:order_idtests:-unique-not_nulldescription:Thisisauniqueidentifierforanorder-name:customer_iddescription:Foreignkeytothecustomerstabletests:-not_null-relationships:to:ref('customers')field:customer_id-name:order_datedescription:Date(UTC)thattheorderwasplaced-name:statusdescription:'{{ doc("orders_status") }}'tests:-accepted_values:values:['placed','shipped','completed','return_pending',“返回”]-name:amountdescription:Totalamount(AUD)oftheordertests:-not_null-name:credit_card_amountdescription:Amountoftheorder(AUD)paidforbycreditcardtests:-not_null-name:coupon_amountdescription:Amountoftheorder(AUD)paidforbycoupontests:-not_null-name:bank_transfer_amountdescription:Amountoftheorder(AUD)paidforbybanktransfertests:-not_null-name:gift_card_amountdescription:Amountoftheorder(AUD)paidforbygiftcardtests:-not_null

运行

结果中显示成功创建了三张视图（analytics.stg_customers、analytics.stg_orders、analytics.stg_payments）和两张表（analytics.customers、analytics.orders）。

$dbtrun07:28:43Running with dbt=1.0.107:28:43Unable to do partial parsing because profile has changed07:28:43Unable to do partial parsing because a project dependency has been added07:28:44Found5models,20tests,0snapshots,0analyses,172macros,0operations,3seed files,0sources,0exposures,0metrics07:28:4407:28:44Concurrency:1threads (target='dev')07:28:4407:28:441of5START view model analytics.stg_customers.................................[RUN]07:28:441of5OK created view model analytics.stg_customers............................[SUCCESS 0 in 0.12s]07:28:442of5START view model analytics.stg_orders....................................[RUN]07:28:442of5OK created view model analytics.stg_orders...............................[SUCCESS 0 in 0.08s]07:28:443of5START view model analytics.stg_payments..................................[RUN]07:28:443of5OK created view model analytics.stg_payments.............................[SUCCESS 0 in 0.07s]07:28:444of5START table model analytics.customers....................................[RUN]07:28:444of5OK created table model analytics.customers...............................[SUCCESS 0 in 0.16s]07:28:445of5START table model analytics.orders.......................................[RUN]07:28:455of5OK created table model analytics.orders..................................[SUCCESS 0 in 0.12s]07:28:4507:28:45Finished running3view models,2table models in0.64s.07:28:4507:28:45Completed successfully07:28:4507:28:45Done. PASS=5WARN=0ERROR=0SKIP=0TOTAL=5

去 TiDB 数据库中验证下，是否真的创建成功。

结果显示多出了customers等五张表格或视图，并且表或视图中的数据也都转换完成。这里只展示customers的部分数据。

mysql>showtables;+---------------------+|Tables_in_analytics|+---------------------+|customers||orders||raw_customers||raw_orders||raw_payments||stg_customers||stg_orders||stg_payments|+---------------------+8rowsinset(0.00sec) mysql>select*fromcustomers;+-------------+------------+-----------+-------------+-------------------+------------------+-------------------------+|customer_id|first_name|last_name|first_order|most_recent_order|number_of_orders|customer_lifetime_value|+-------------+------------+-----------+-------------+-------------------+------------------+-------------------------+|1|Michael|P.|2018-01-01|2018-02-10|2|33.0000||2|Shawn|M.|2018-01-11|2018-01-11|1|23.0000||3|Kathleen|P.|2018-01-02|2018-03-11|3|65.0000||4|Jimmy|C.|NULL|NULL|NULL|NULL||5|Katherine|R.|NULL|NULL|NULL|NULL||6|Sarah|R.|2018-02-19|2018-02-19|1|8.0000||7|Martin|M.|2018-01-14|2018-01-14|1|26.0000||8|Frank|R.|2018-01-29|2018-03-12|2|45.0000|....

生成文档

dbt 还支持生成可视化的文档，命令如下。

1.生成文档

$dbtdocs generate07:33:59Running with dbt=1.0.107:33:59Found5models,20tests,0snapshots,0analyses,172macros,0operations,3seed files,0sources,0exposures,0metrics07:33:5907:33:59Concurrency:1threads (target='dev')07:33:5907:33:59Done.07:33:59Building catalog07:33:59Catalog written to /home/ubuntu/jaffle_shop/target/catalog.json

2.开启服务

$dbtdocs serve07:43:01Running with dbt=1.0.107:43:01Serving docs at0.0.0.0:808007:43:01To access from your browser, navigate to: http://localhost:808007:43:0107:43:0107:43:01Press Ctrl+C to exit.

可以通过浏览器查看文档，其中包含 jaffle_shop 项目的整体结构以及所有表和视图的描述说明。

总结

TiDB 在 dbt 中的使用主要有以下几步：

1. 安装 dbt 和 dbt-tidb
2. 配置项目
3. 编写 SQL 和 YML 文件
4. 运行项目

目前，TiDB 支持 dbt 的版本在 4.0 以上，但根据 dbt-tidb项目文档描述，低版本的 TiDB 在和 dbt 结合使用中还存在一些问题，例如：不支持临时表和临时视图、不支持 WITH 语法等。想要痛快的使用 dbt ，建议使用 TiDB 5.3 以上版本，此版本支持 dbt 的全部功能。