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

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-...）

接下來，我將以 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 https://github.com/dbt-labs/j...

$ 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 debug

06:59:18 Running with dbt=1.0.1

dbt version: 1.0.1

python version: 3.8.10

python path: /usr/bin/python3

os info: Linux-5.4.0-97-generic-x86_64-with-glibc2.29

Using profiles.yml file at /home/ubuntu/.dbt/profiles.yml

Using 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 seed

07:03:24 Running with dbt=1.0.1

07: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 metrics

07:03:25

07:03:25 Concurrency: 1 threads (target='dev')

07:03:25

07: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:26

07:03:26 Finished running 3 seeds in 0.71s.

07:03:26

07:03:26 Completed successfully

07:03:26

07: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.yml

version: 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 run

07:28:43 Running with dbt=1.0.1

07:28:43 Unable to do partial parsing because profile has changed

07:28:43 Unable to do partial parsing because a project dependency has been added

07:28:44 Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics

07:28:44

07:28:44 Concurrency: 1 threads (target='dev')

07:28:44

07: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:45

07:28:45 Finished running 3 view models, 2 table models in 0.64s.

07:28:45

07:28:45 Completed successfully

07:28:45

07: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;

| 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 |

| 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 generate

07:33:59 Running with dbt=1.0.1

07:33:59 Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics

07:33:59

07:33:59 Concurrency: 1 threads (target='dev')

07:33:59

07:33:59 Done.

07:33:59 Building catalog

07:33:59 Catalog written to /home/ubuntu/jaffle_shop/target/catalog.json
2.開啟服務

$ dbt docs serve

07:43:01 Running with dbt=1.0.1

07:43:01 Serving docs at 0.0.0.0:8080

07:43:01 To access from your browser, navigate to: http://localhost:8080

07:43:01

07:43:01 Press Ctrl+C to exit.
可以通過瀏覽器檢視文件，其中包含 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丨高效的資料轉換工具讓資料分析更簡單

相關文章