當 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-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 的全部功能。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/69994146/viewspace-2886801/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Flyway讓資料庫版本管理更簡單資料庫
- 資料遷移工具簡單分析
- 當 Kotlin 遇見 RxJava 多資料來源KotlinRxJava
- 5個常用的資料模型，讓資料分析更高效模型
- go-mongox：簡單高效，讓文件操作和 bson 資料構造更流暢Go
- 容災切換中的資料庫當機問題簡單分析（一）資料庫
- 更為簡單的JsonString資料與Map，List之間的轉換JSON
- 航空遇見大資料大資料
- Hiptype：讓出版商更懂讀者的大資料分析工具大資料
- Minitab 2021：讓資料分析變得更簡單，更直觀 win版
- NLA自然語言分析，讓資料分析更智慧！
- 資料庫轉換工具，不同資料庫之前任意轉換資料庫
- javascript資料型別轉換簡單介紹JavaScript資料型別
- ODC 3.4.0 現已上線，讓資料庫開發更簡單資料庫
- 5個免費工具，讓資料科學更加簡單資料科學
- 資料庫排行榜｜當 DB-Engines 遇見墨天輪國產資料庫排行資料庫
- 掌握這些技巧，讓Excel批次資料清洗變得簡單高效！Excel
- 讓 json 解析更簡單高效的 GJSONJSON
- 更簡單易用的資料倉儲，阿里雲重磅推出分析型資料庫3.0版阿里資料庫
- 簡單分析oracle的資料儲存Oracle
- DataGrip 2023：讓資料庫開發變得更簡單、更高效 mac/win啟用版資料庫Mac
- 有了 ETL 資料神器 dbt，表資料秒變 NebulaGraph 中的圖資料
- Smartbi對話式分析，讓資料分析如同聊天一樣簡單！
- 讓資料傳輸更安全
- 4月22日丨【雲資料庫技術沙龍】技術進化，讓資料更智慧資料庫
- 高效採集資料業務更安心
- Python通用資料格式轉換工具Python
- 大資料常見的資料分析思維大資料
- 大資料直通盤更換大資料
- pip更換資料來源
- mysql 資料庫效能分析工具簡介MySql資料庫
- java簡單資料型別(轉)Java資料型別
- JavaScript資料型別分析及其轉換JavaScript資料型別
- 一個轉換資料屬性名的工具
- 遇見大資料視覺化 ： 那些 WOW 的資料視覺化案例大資料視覺化
- 匯入資料時遇見ORA-00054
- TiDB Operator，讓 TiDB 成為真正的 Cloud-Native 資料庫TiDBCloud資料庫
- Avdshare Video Converter，讓影片轉換變得更簡單！IDE