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 上獲取它。

$ git clone 
$ cd jaffle_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:18  Running with dbt=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.29Using profiles.yml file at /home/ubuntu/.dbt/profiles.ymlUsing dbt_project.yml file at /home/ubuntu/jaffle_shop/dbt_project.yml
Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]
Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]
Required dependencies: - git [OK found]
Connection:
  server: 127.0.0.1
  port: 4000
  database: None
  schema: analytics  user: root
  Connection test: [OK connection ok]All checks passed!

載入 CSV

載入 CSV 資料，把 CSV 具體化為目標資料庫中的表。注意：一般來說，dbt 專案不需要這個步驟，因為你的待處理專案的資料都在資料庫中。

$ dbt seed07:03:24  Running with dbt=1.0.107:03:24  Partial parse save file not found. Starting full parse.07:03:25  Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics07:03:2507:03:25  Concurrency: 1 threads (target='dev')07:03:2507:03:25  1 of 3 START seed file analytics.raw_customers.................................. [RUN]07:03:25  1 of 3 OK loaded seed file analytics.raw_customers.............................. [INSERT 100 in 0.19s]07:03:25  2 of 3 START seed file analytics.raw_orders..................................... [RUN]07:03:25  2 of 3 OK loaded seed file analytics.raw_orders................................. [INSERT 99 in 0.14s]07:03:25  3 of 3 START seed file analytics.raw_payments................................... [RUN]07:03:26  3 of 3 OK loaded seed file analytics.raw_payments............................... [INSERT 113 in 0.24s]07:03:2607:03:26  Finished running 3 seeds in 0.71s.07:03:2607:03:26  Completed successfully07:03:2607:03:26  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

上述結果中，可以清楚的看到共執行了三個任務，分別載入了 analytics.raw_customers、 analytics.raw_orders、 analytics.raw_payments三張表。

接著，去 TiDB 資料庫中看看發生了什麼。

發現多出了 analytics資料庫，這是 dbt 為我們建立的工程資料庫。

mysql> show databases;+--------------------+| Database           |+--------------------+| INFORMATION_SCHEMA || METRICS_SCHEMA     || PERFORMANCE_SCHEMA || analytics          || mysql              || test               |+--------------------+6 rows in set (0.00 sec)

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 orders as (    select * from {{ ref('stg_orders') }}
),
payments as (    select * from {{ ref('stg_payments') }}
),
order_payments as (    select
        order_id,
        {% for payment_method in payment_methods -%}        sum(case when payment_method = '{{ payment_method }}' then amount else 0 end) as {{ payment_method }}_amount,
        {% endfor -%}        sum(amount) as total_amount    from payments    group by order_id
),final as (    select
        orders.order_id,
        orders.customer_id,
        orders.order_date,
        orders.status,
        {% for payment_method in payment_methods -%}
        order_payments.{{ payment_method }}_amount,
        {% endfor -%}
        order_payments.total_amount as amount    from orders
    left join order_payments
        on orders.order_id = order_payments.order_id
)select * from final

並且，與這條 SQL 配套的約束資訊在 models/schema.yml檔案中。

schema.yml是當前目錄下所有模型的登錄檔，所有的模型都被組織成一個樹形結構，描述了每條欄位的說明和屬性。其中 tests條目表示這個欄位的一些約束項，可以通過 dbt test命令來檢測，更多資訊請查閱官網文件。

cat models/schema.ymlversion: 2...
  - name: orders
    description: This table has basic information about orders, as well as some derived facts based on payments
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
        description: This is a unique identifier for an order
      - name: customer_id
        description: Foreign key to the customers table
        tests:
          - not_null
          - relationships:
              to: ref('customers')
              field: customer_id
      - name: order_date
        description: Date (UTC) that the order was placed
      - name: status
        description: '{{ doc("orders_status") }}'
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']      - name: amount
        description: Total amount (AUD) of the order
        tests:
          - not_null
      - name: credit_card_amount
        description: Amount of the order (AUD) paid for by credit card
        tests:
          - not_null
      - name: coupon_amount
        description: Amount of the order (AUD) paid for by coupon
        tests:
          - not_null
      - name: bank_transfer_amount
        description: Amount of the order (AUD) paid for by bank transfer
        tests:
          - not_null
      - name: gift_card_amount
        description: Amount of the order (AUD) paid for by gift card
        tests:
          - not_null

執行

結果中顯示成功建立了三張檢視（ analytics.stg_customers、 analytics.stg_orders、 analytics.stg_payments）和兩張表（ analytics.customers、 analytics.orders）。

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

去 TiDB 資料庫中驗證下，是否真的建立成功。

結果顯示多出了 customers等五張表格或檢視，並且表或檢視中的資料也都轉換完成。這裡只展示 customers的部分資料。

mysql> show tables;+---------------------+| Tables_in_analytics |+---------------------+| customers           || orders              || raw_customers       || raw_orders          || raw_payments        || stg_customers       || stg_orders          || stg_payments        |+---------------------+8 rows in set (0.00 sec)
mysql> select * from customers;+-------------+------------+-----------+-------------+-------------------+------------------+-------------------------+| 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.生成文件

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

2.開啟服務

$ dbt docs serve07:43:01  Running with dbt=1.0.107:43:01  Serving docs at 0.0.0.0:808007:43:01  To access from your browser, navigate to:

可以通過瀏覽器檢視文件，其中包含 jaffle_shop 專案的整體結構以及所有表和檢視的描述說明。

總結

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

安裝 dbt 和 dbt-tidb
配置專案
編寫 SQL 和 YML 檔案
執行專案

目前，TiDB 支援 dbt 的版本在 4.0 以上，但根據 dbt-tidb 專案文件描述，低版本的 TiDB 在和 dbt 結合使用中還存在一些問題，例如：不支援臨時表和臨時檢視、不支援 WITH 語法等。想要痛快的使用 dbt ，建議使用 TiDB 5.3 以上版本，此版本支援 dbt 的全部功能。

當 dbt 遇見 TiDB丨高效的資料轉換工具讓資料分析更簡單

安裝

建立專案：jaffle_shop

配置專案

載入 CSV

model 是什麼？

執行

生成文件

總結

相關文章