数据转换工具DBT介绍及实操

一、什么是DBT

dbt （data build tool）是一款流行的开源数据转换工具，能够通过 SQL 实现数据转化，将命令转化为表或者视图，提升数据分析师的工作效率。dbt 主要功能在于转换数据库或数据仓库中的数据，在 E（Extract）、L（Load）、T（Transform） 的流程中，仅负责转换（transform）的过程。 通过 dbt插件，数据分析师能够通过 SQL 直接建立表单并匹配数据，而无需关注创建 table 或 view 的过程，并且可以直观地看到数据的流动；同时能够运用 dbt 编写 SQL、进行测试、以及包管理等功能。

二、DBT官方支持的数据源

Data Platform	latest verified version
AlloyDB	(same as dbt-postgres)
Azure Synapse	1.3.0
BigQuery	1.2.0
Databricks	1.3.0 ??
Dremio	1.3.0 ??
Postgres	1.2.0
Redshift	1.2.0
Snowflake	1.2.0
Spark	1.2.0
Starburst & Trino	1.2.0 ??

三、开源社区支持的数据源

Athena	Greenplum	Oracle
Clickhouse	Hive	Rockset
IBM DB2	Impala	SingleStore
Doris & SelectDB	Infer	SQLite
DuckDB	iomete	SQL Server & Azure SQL
Dremio	Layer	Teradata
Exasol Analytics	Materialize	TiDB
Firebolt	MindsDB	Vertica
AWS Glue	MySQL
Databend Cloud	fal - Python models

四、DBT关于建模的两个特点

1、DBT 能避免编写DDL语句

仅使用 查询SQL 语句或 Python dataframe编写业务逻辑，并返回所需的数据集，dbt 负责这些数据集的物化，从而构建可重用或模块化的数据模型，这些数据模型可以在后续工作中引用，而不是从每次分析的原始数据开始。这显著减少了数据查询的运行时间，dbt还能通过函数、工具包等构建和管理模型的ER图。

2、DBT 提供更可靠的分析

不再复制和粘贴 SQL，因为这可能会导致逻辑更改时出错。dbt可以构建可重用的数据模型，并将其提取到后续模型和分析中。更改一次模型，该更改将传播到其所有依赖项。发布特定数据模型的规范版本，封装所有复杂的业务逻辑。此模型之上的所有分析都将包含相同的业务逻辑，而无需重新实现它。使用类似git一样成熟的源代码管理流程，如分支、拉取请求和代码评审。在基础数据上快速轻松地编写数据质量测试。

五、DBT建模功能总结

特征	描述
将SQL查询语句转化为关系	数据模型通过编写固定格式的标准select语句来实现，DBT能够识别一个模型配置文件内SQL语句之间的血缘关系，在将SQL语句执行为表或视图的同时，提取表或视图之间的关系进行展示。
固化模型的执行顺序	通过DBT做复杂的数据转换，将分多个步骤，关于每个步骤的执行顺序，DBT 通过REF函数分阶段实现转换。
类似git的版本控制	DBT将模型编写、版本控制和每个模型和字段编写描述等，通过类似git的模式进行管控。
数据模型管理功能	DBT 附带了一个包管理器，使得模型代码的公共和私有存储库能够被有权限的其他人直接调用。
本地文件导入	如一些码表信息、CSV文件等，能够直接加载为数据库中的表。
数据快照	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文件