資料轉換工具DBT介紹及實操

一、什么是DBT

dbt （data build tool）是一款流行的開源資料轉換工具，能夠通過 SQL 實作資料轉化，將命令轉化為表或者視圖，提升資料分析師的作業效率，dbt 主要功能在于轉換資料庫或資料倉庫中的資料，在 E（Extract）、L（Load）、T（Transform） 的流程中，僅負責轉換（transform）的程序， 通過 dbt插件，資料分析師能夠通過 SQL 直接建立表單并匹配資料，而無需關注創建 table 或 view 的程序，并且可以直觀地看到資料的流動；同時能夠運用 dbt 撰寫 SQL、進行測驗、以及包管理等功能，

二、DBT官方支持的資料源

三、開源社區支持的資料源

四、DBT關于建模的兩個特點

1、DBT 能避免撰寫DDL陳述句

僅使用 查詢SQL 陳述句或 Python dataframe撰寫業務邏輯，并回傳所需的資料集，dbt 負責這些資料集的物化，從而構建可重用或模塊化的資料模型，這些資料模型可以在后續作業中參考，而不是從每次分析的原始資料開始，這顯著減少了資料查詢的運行時間，dbt還能通過函式、工具包等構建和管理模型的ER圖，

2、DBT 提供更可靠的分析

不再復制和粘貼 SQL，因為這可能會導致邏輯更改時出錯，dbt可以構建可重用的資料模型，并將其提取到后續模型和分析中，更改一次模型，該更改將傳播到其所有依賴項，發布特定資料模型的規范版本，封裝所有復雜的業務邏輯，此模型之上的所有分析都將包含相同的業務邏輯，而無需重新實作它，使用類似git一樣成熟的源代碼管理流程，如分支、拉取請求和代碼評審，在基礎資料上快速輕松地撰寫資料質量測驗，

五、DBT建模功能總結

六、DBT資料建模全流程實操

1、搭建DBT環境

DBT可在windows\mac\linux等系統獨立部署運行，也可通過Docker部署，官方還提供DBT Cloud，本檔案的演示環境為在windows系統下的linux環境，DBT的運行依賴Python環境，推薦Python3.9+版本，本檔案的演示環境為Python3.9.7，

#1、安裝必須的插件
###centos或者RedHat環境下：
sudo yum install redhat-rpm-config gcc libffi-devel  python-devel openssl-devel
pip install --upgrade pip wheel setuptools

###ubutun或者deb、deepin環境下
sudo apt-get install  redhat-rpm-config gcc libffi-devel  python-devel openssl-devel
pip install --upgrade pip wheel setuptools

#2、安裝需要的資料源插件（按需選擇，如本檔案演示環境為Kingbase8，選擇postgres即可）
pip install \
  dbt-core \
  dbt-postgres \
  dbt-redshift \
  dbt-snowflake \
  dbt-bigquery

#3、安裝完畢后，查看版本號
dbt --version

依次運行完上述命令后，正常情況下會輸出以下內容，，證明安裝完畢！

2、初始化DBT構建專案

dbt init dbt_project

運行完成后，會在當前用戶家目錄下生成.dbt目錄，目錄結構如下，一般訪問~/.dbt路徑即可到達該目錄，目錄下生成了以下子檔案夾及dbt_project.yml即可證明初始化成功，建議將該目錄遷移到某一個固定空間，如本文的D:/test/dbt_project：

3、修改資料庫連接資訊

修改profiles.yml檔案，添加資料庫連接的URL、USERNAME、PASSWORD等資訊，保存后運行命令：

dbt debug

當輸出如下內容，即證明DBT成功連接到配置好的資料庫（本文連接的是測驗環境KingbBase）

4、啟動DBT

dbt run

輸出以下資訊，即可證明啟動成功！

5、為專案生成檔案

dbt docs generate

DBT初始化后自動加載一個dbt.mysql_first_model、dbt.my_second_dbt_model的資料模型，除此之外的模型都是自定義模型，具體輸出如下日志即可證明成功生成檔案，

6、打開Web頁面查看模型

dbt docs serve

啟動后自動記錄訪問日志，輸出如下：

web頁面本地訪問地址：http://127.0.0.1:8080

7、自定義模型開發

本次資料建模從資料匯入、資料計算、模型生成三個步驟進行，模擬真實建模程序，

7.1、通過DBT匯入資料到資料庫

創建兩個個csv檔案fund_name_em.csv,放入專案下的seeds目錄，然后seeds目錄下再創建fund_data.yml，一次建模所需的檔案準備就已經完成，可以看到，僅需一個資料檔案，一個yml格式的組態檔，

fund_code,pinyin_abbr,fund_name,fund_type,pinyin_full
000001,HXCZHH,華夏成長混合,混合型-靈活,HUAXIACHENGZHANGHUNHE
000002,HXCZHH,華夏成長混合(后端),混合型-靈活,HUAXIACHENGZHANGHUNHE
000003,ZHKZZZQA,中海可轉債債券A,債券型-可轉債,ZHONGHAIKEZHUANZHAIZHAIQUANA
000004,ZHKZZZQC,中海可轉債債券C,債券型-可轉債,ZHONGHAIKEZHUANZHAIZHAIQUANC
000005,JSZQXYDQZQ,嘉實增強信用定期債券,債券型-長債,JIASHIZENGQIANGXINYONGDINGQIZHAIQUAN

fund_name_em.csv

seq,stock_code,stock_nme,percent,holding_number,holding_value,season,found_code
1,300438,鵬輝能源,6.4,587.41,36096.38,2022年2季度股票投資明細,001532
2,2340,格林美,3.73,2313.92,21056.69,2022年2季度股票投資明細,001532
3,601888,中國中免,3.22,78.01,18170.61,2022年2季度股票投資明細,001532
4,300568,星源材質,3.05,593.07,17222.69,2022年2季度股票投資明細,001532
5,300613,富瀚微,2.78,184.72,15664.18,2022年2季度股票投資明細,001532
6,2196,方正電機,2.59,1498.45,14624.82,2022年2季度股票投資明細,001532
7,601567,三星醫療,2.37,1007.9,12384.95,2022年2季度股票投資明細,001532
8,603259,蒟明康德,2.36,128.23,13335.06,2022年2季度股票投資明細,001532

fund_portfolio_hold_em.csv

其中fund_data.yml檔案的內容如下，解讀下關鍵資訊，name代表需要通過DBT匯入資料庫的模型名稱（就是表名稱），config代表自定義配置項（DBT會自動推斷欄位型別，如需指定可通過此方式完成）：

version: 2
seeds:
  - name: fund_name_em
    config:
      column_types: 
        fund_code: varchar(16)
        
  - name: fund_portfolio_hold_em
    config:
      column_types: 
        fund_code: varchar(16)

以上準備作業完成后，運行命令實作資料匯入：

dbt seed

正確執行完成輸出的日志為：

查看資料庫可以發現已經實作了資料自動建表

tips??：多次執行dbt seed不會產生多份資料, 匯入前會先truncate清空老的資料，如果表結構有變化, 則需要洗掉表在執行seed命令，

7.2、創建自定義的模型

在DBT安裝根目錄下的models檔案夾，新建一個子目錄，目錄名即為專案名，如本次命名為fund_status, 然后在該目錄下新增一個sql檔案fund_portfolio_stats.sql，注意這個sql檔案，就是本次模型構建的關鍵，

-- 統計3只基金等權重買入后的重倉股占比
with fund_stock_map AS (
  SELECT fund_code, stock_code, stock_name, cast(percent / 3 AS DECIMAL(18,2)) AS percent
  from {{ ref('fund_portfolio_hold_em') }}
  -- WHERE fund_code IN ('001716', '002446', '005094')
  -- AND season = '2022年2季度股票投資明細'
  -- AND percent > 0
),
fund_info AS (
  SELECT fund_name, cast(fund_code as int4) as fund_code
  FROM {{ ref('fund_name_em') }}
  -- WHERE fund_code IN ('001716', '002446', '005094')
),
stock_sum AS (
  SELECT stock_code, stock_name,
  cast(SUM(percent) AS DECIMAL(18,2)) pct
  from fund_stock_map
  GROUP BY stock_code, stock_name
  HAVING SUM(percent) > 0.5
)
SELECT stock_sum.stock_code, stock_sum.stock_name, stock_sum.pct AS total_pct, 
fund_info.fund_code, fund_info.fund_name, fund_stock_map.percent AS indiv_pct
FROM stock_sum,fund_stock_map ,fund_info 
--ON fund_stock_map.stock_code = stock_sum.stock_code
--JOIN fund_info 
WHERE fund_info.fund_code = fund_stock_map.fund_code and fund_stock_map.stock_code = stock_sum.stock_code
order by pct desc

tips??：這里我們只用到了dbt最基本的ref宏, 可以用宏名稱替代具體的表名. 因為前面我們已經通過seed命令匯入了2個表, 所以這兩個表直接可以通過名字來進行參考.dbt的宏極為強大，具體可關注官網

重點來了！sql檔案保存完畢后, 通過后臺命令就可以生成這個sql檔案代表的這個模型！！！

dbt run -s fund_portfolio_stats

注意如果語法不符合所選資料源的規范，或者欄位型別不匹配等，如果針對自己撰寫的sql檔案不確定對錯，可在相關資料源進行執行驗證，比如本次欄位不匹配造成的錯誤如下：

sql檔案被DBT正確執行后，輸入的日志為：

這種自定義的模型并不會將表實際落地到資料庫，而是保存為一個視圖，這一點需要注意，在執行完以上命令后，再次在命令列依次執行dbt docs generate和dbt docs serve, 會在瀏覽器里打開檔案頁面，回到Dbeaver查看效果如下：

在Web界面打開該資料模型的血緣關系圖，可以明顯看到最終模型所用到的表，效果如下：

七、DBT使用心得

7.1 缺點

7.1.1 一定的使用門檻

DBT作為一款資料建模工具，其用戶相對偏向資料工程師、資料分析師等有一定技術背景的人員，如一次自定義模型的建設，既要通過命令列操作（可交給運維人員），也要完成優秀且沒有錯誤的SQL腳本書寫，

7.1.2 無法一套SQL隨處運行

SQL腳本需要根據DBT連接的資料源進行適配，如連接postgres、hive、Oracle等資料源時，所需的SQL檔案需要改造為符合當前資料源的語法，無法做到一個SQL，隨處建模，

7.1.3 無法一套SQL隨處運行

國內技術檔案偏少，國內常用資料源支持不足，

7.1.4 無認證流程

當DBT的web服務啟動后，所有人能夠訪問，沒有權限管控或者賬號密碼體系，

7.2 優點

7.2.1 統一資料建模規范

維護統一的基礎資料源，資料建模做了扎口，當所有人通過DBT做資料模型建設時，資料模型不僅可復用，也包含資料血緣關系展示，并且當基礎資料源有變更時，已有資料模型將自動更新，規范資料模型建設，提高了資料模型的復用性、穩定性，有效降低重復建設等成本問題，

7.2.1 資料源支持豐富

除了業界常用的數倉組件外，也支持如Oracle、Postgres等關系型資料庫以及一些時序資料庫，能做到利用DBT統一連接公司資料源供下游資料建模團隊使用，

7.2.3 自動物化查詢

實操發現DBT能夠避免人工建表，無論是資料匯入，還是資料模型，都能自動實作建表或建視圖，避免了人工出現的失誤和誤刪表等不可控操作，從一定程度上保證了資料質量，統一了源資料的口徑等，

7.2.4 資料模型版本管理功能

官網提到了資料模型歷史版本的管理功能，類似git，生產環境下，比較方便資料模型變更、回滾、快照保存等，本次測驗也可以發現，新建模型的目錄下會生成.gitkeep檔案

標籤：大數據

上一篇：《資料治理行業實踐白皮書》正式發布，開辟資料治理新范式（附下載）

下一篇：推薦一款好用的資料一致性校驗工具