数据转换工具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 |
三、开源社区支持的数据源
四、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
本次台式机Windows10环境已部署一套,访问地址为:http://10.63.205.53:8080
7、自定义模型开发
本次数据建模从数据导入、数据计算、模型生成三个步骤进行,模拟真实建模过程。
7.1、通过DBT导入数据到数据库
创建两个个csv文件fund_name_em.csv,放入项目下的seeds目录,然后seeds目录下再创建fund_data.yml,一次建模所需的文件准备就已经完成,可以看到,仅需一个数据文件,一个yml格式的配置文件。
fund_name_em.csv
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文件
最新文章
- 如何通过CRM评估客户价值和提高客户忠诚度?
- Sharepoint学习笔记—习题系列--70-573习题解析 -(Q125-Q126)
- C# 文件的读取、写入和删除
- HDOJ-ACM1015(JAVA) 运用 组合、全排列实现
- Multiplepack coming~^.^
- Online网站集
- 图片上传之FileAPI与NodeJs
- MYSQL 中 LIMIT 用法
- ABP入门系列(1)——通过模板创建MAP版本项目
- SUCTF 2016 : dMd
- L332 NBA: Dwyane Wade and Dirk Nowitzki Say Emotional Goodbyes
- ZKWeb网页框架1.9正式发布
- 经典 mysql 28道题
- onsrcoll和scrollTop兼容与实现
- 【CF 585E】 E. Present for Vitalik the Philatelist
- python网络编程知识体系
- Vue.js小案例(2)
- 数论3——gcd&;&;lcm
- Zeppelin interperter 模式设置总结图解2
- 分享知识-快乐自己:Liunx-大数据(Hadoop)初始化环境搭建
热门文章
- 第三方模块 request openpyxl
- 软件开发架构、构架趋势、OSI七层协议
- 【RocketMQ】主从模式下的消费进度管理
- Python从入门到精通(第2版)——pyuic5: error: no such option: -m的问题解决
- [python]《Python编程快速上手:让繁琐工作自动化》学习笔记7
- Spark详解(05) - Spark核心编程SparkCore
- 「Python实用秘技12」像导入模块一样导入ipynb文件
- 算法之SPFA的前置:Bellman-Ford算法
- Python3+Selenium3自动化测试-(六)
- 【原创】项目六 Load Of The Root