0%

【大数据架构之旅】2 从零起步学 dbt

dbt 可谓是最近在大数据领域风头最盛的仔之一,主要解决的是 ELT 中 T(ransform) 这个环节的问题,最大的优势就是将软件工程的精髓带入到了数据处理和分析中。话不多说,我们一起来了解一下吧!


更新历史

现代分析工作流简介

如果你还在关注端到端的数据分析工具,那么就已经 out 啦,现在的分析更像是一类复杂的解决方案,包含:

  1. 数据集成脚本/工具
  2. 高性能 OLAP 数据库
  3. SQL/R/Python
  4. 可视化工具

在这之中,分析团队面对的最大挑战就是:如何持续交付高质量、低延迟的分析结果。经常面对的问题有:

  1. 分析师独立工作,并没有产出最佳的结果
  2. 知识是隔离的
  3. 不得不经常重写同事的代码,这也引入了更多的差异性
  4. 对数据不熟悉就很难注意点数据集的细微差别
  5. 对同一个指标一般会有多种计算方式

怎么办呢?其实答案很简单,就在身边,看看我们的软件工程团队吧:

  1. 版本控制:谁在什么时间改变了什么,一目了然
  2. 质量保证:代码需要被 review 和测试
  3. 文档:代码自带文档说明
  4. 模块化:这个不用多说,都懂

分析代码也是重要的资产,具体表现在:

  1. 环境区分:同样要分生产环境和开发/测试环境
  2. 服务保证:分析团队应为准确性负责,分析 error 就像产品的 bug,要一一被处理;不用的分析代码也要走逐步下线的流程
  3. 基于可维护性设计

大部分分析工作流都是手动串起的,我们需要更加自动化的方式:

  1. 模型和分析从不同的版本控制系统中拉取
  2. 自动根据所属环境进行配置
  3. 自动进行测试
  4. 测试通过后,自动进行部署

写了这么多,答案就在下面:dbt 正是为了现代分析工作流而量身定制的。

dbt 简介

正如一开始所说,dbt 解决的是 Transform 的问题。在传统的数据工作流中,T 这部分的工作一般是由数据科学团队相对独立进行的,因此并不强制要求走传统软件工程开发的工作流程。当数据科学被越来越多运用在各类场景之后,不同的数据模型,不同的版本,不同的维度指标等如何维护,如何更好地让整个数据团队协作,就成了非常棘手的问题。而 dbt 的出现可谓一剂良药,通过将软件工程的工作流引入数据科学,建立了完整、统一、开放且自动的数据科学研发流程,涵盖建模、版本、测试、文档、部署等环节。

dbt 的主要功能特性如下:

  1. 优化工作流的构造
    1. 不需要再写各类模板化的 DML(Data Manipulation Language) 和 DDL(Data Definition Language),只需要写 select 语句 / Python DataFrame 即可,dbt 会编译为具体的 DML/DDL 进行执行
    2. 搭建可重用/模块化的数据模型,不用每次分析都从原始数据开始
    3. 利用元数据找到运行时间长的模型,并通过增量模型极大降低查询的执行时间
    4. 编写符合 DRY(Don’t Repeat Yourself) 原则的代码,支持 macro, hooks包管理
  2. 支持更稳定的分析构建
    1. 不再需要复制粘贴 SQL 语句,极大减少错误和逻辑变化。基于不同层级的数据模型逐步构造分析,上游改动自动传导
    2. 支持发布数据模型的主版本,封装所有复杂的业务逻辑,无需重新实现即可基于该模型二次开发
    3. 使用成熟的版本控制流程(git),支持 branch/pull request/code review
    4. 支持快速编写数据质量测试,提前处理好各类 edge cases

介绍到此告一段落,接下来就开始上手吧!

安装与配置

因为我的电脑是 mac,这里提供两种安装方式,第一种对网络要求较高;第二种复杂一点,但一般可以一次成功。其他操作系统可以在参考链接的 Installation 部分自行安装。

homebrew 安装

注:本文的内容不一定及时更新,最新的内容参考 dbt doc。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 安装
brew update
brew install git
brew tap dbt-labs/dbt
# 现在 dbt 推荐要安装一个 adaptor,就用官网的这个
# 这一步如果不翻墙会非常慢,也可以直接使用后面的 pip 安装方法
brew install dbt-postgres

# 更新
brew update
brew upgrade

# 测试版本
dbt --version

pip 安装

首先我们需要进入 venv 的虚拟环境,关于虚拟环境的配置参考 这里,版本有些差异但是问题不大,需要注意的是至少需要 python 3.7

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 安装
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple dbt-postgres
# 也可以只安装 dbt-core(但不推荐,除非不需要集成数据)
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple dbt-core

# 升级
pip install -i https://pypi.tuna.tsinghua.edu.cn/simple --upgrade dbt-postgres

# 验证安装
dbt --version
# 如果出现下面内容,则说明没有问题
Core:
- installed: 1.3.1
- latest: 1.3.1 - Up to date!

Plugins:
- postgres: 1.3.1 - Up to date!

除了官方支持的数据库 adapter 之外,还有一些第三方的支持,但是普遍都跟不上官方的版本,我尝试了 sqlite 和 mysql 的,基本都只支持到 dbt 1.0.x 版本,所以为了不让本文一写完就过时,就用 postgres 来做演示。

postgres 安装

数据库的安装就比较简单了,为了便于管理,直接用 docker 进行安装。在 mac 有 docker desktop 客户端能够可视化操作,不过拉取镜像还是要在命令行进行 docker pull postgres:14,这里我们用 14 版本。拉取完之后启动,配置如下:

1
2
3
4
5
6
容器名 dbt-pg-demo
端口 5432
环境变量
POSTGRES_USER root
POSTGRES_PASSWORD helloworld
POSTGRES_DB dbt-demo

如果习惯用客户端来操作数据库,可以下载 DBeaver,然后简单进行配置即可。

创建项目与连接数据库

在命令行中进入自己常用的存放代码的文件夹之后,按照下面步骤操作,就可以创建一个新的项目:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
# 初始化项目,选择需要的数据库为 postgres
dbt init dbt_hello_world

# 文件夹里的内容如下
├── README.md
├── analyses # 保存分析型 SQL 语句
├── dbt_project.yml # 整个 dbt 项目的配置
├── macros # 可复用的代码块
├── models # 每个模型一个文件
│   └── example
│   ├── my_first_dbt_model.sql
│   ├── my_second_dbt_model.sql
│   └── schema.yml
├── seeds # 用来载入到数据平台的 csv 静态数据
├── snapshots # 快照,记录可变表的状态
└── tests # SQL 测试查询,用来测试模型和资源

# 还有一些文件夹没有列出,有需要可以自己新建
├── docs # 保存文档
├── sources # 记录上游的数据源
├── exposures # 记录下游的应用
└── metrics # 定义项目中的指标

接下来我们需要连接数据库,找到 ~/.dbt/profiles.yml 文件,内容改为

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
dbt_hello_world:
outputs:

dev:
type: postgres
threads: 1
host: 127.0.0.1
port: 5432
user: root
pass: helloworld
dbname: dbt-demo
schema: dbt_dev

prod:
type: postgres
threads: 1
host: 127.0.0.1
port: 5432
user: root
pass: helloworld
dbname: dbt-demo
schema: dbt_prod

target: dev

然后我们就可以测试一下连接,命令为 dbt debug,具体输出如下,则说明没有问题

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
07:28:20  Running with dbt=1.3.1
dbt version: 1.3.1
python version: 3.7.9
python path: /Users/wangda/Dev/venvs/ds37/bin/python
os info: Darwin-19.6.0-x86_64-i386-64bit
Using profiles.yml file at /Users/wangda/.dbt/profiles.yml
Using dbt_project.yml file at /Users/wangda/Documents/Gitee/dbt_hello_world/dbt_project.yml

Configuration:
profiles.yml file [OK found and valid]
dbt_project.yml file [OK found and valid]

Required dependencies:
- git [OK found]

Connection:
host: 127.0.0.1
port: 5432
user: root
database: dbt-demo
schema: dbt_dev
search_path: None
keepalives_idle: 0
sslmode: None
Connection test: [OK connection ok]

All checks passed!

第一次运行

通过 init 已经自带了两个模型,我们可以直接通过 dbt run 尝试运行一下,结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
07:30:47  Running with dbt=1.3.1
07:30:48 Partial parse save file not found. Starting full parse.
07:30:48 Found 2 models, 4 tests, 0 snapshots, 0 analyses, 289 macros, 0 operations, 0 seed files, 0 sources, 0 exposures, 0 metrics
07:30:48
07:30:49 Concurrency: 1 threads (target='dev')
07:30:49
07:30:49 1 of 2 START sql table model dbt_dev.my_first_dbt_model ........................ [RUN]
07:30:49 1 of 2 OK created sql table model dbt_dev.my_first_dbt_model ................... [SELECT 2 in 0.11s]
07:30:49 2 of 2 START sql view model dbt_dev.my_second_dbt_model ........................ [RUN]
07:30:49 2 of 2 OK created sql view model dbt_dev.my_second_dbt_model ................... [CREATE VIEW in 0.06s]
07:30:49
07:30:49 Finished running 1 table model, 1 view model in 0 hours 0 minutes and 0.48 seconds (0.48s).
07:30:49
07:30:49 Completed successfully
07:30:49
07:30:49 Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

通过命令行的输出我们可以看到生成了 1 个 table model 和 1 个 view model,同时我们也可以在 DBeaver 中看到 1 张表和 1 个视图。

能运行起来就成功了一半,接下来我们要真刀真枪学一点东西了!

载入数据集

我们先从 这里 下载 UCI 的共享单车数据集。我们将 hour.csvday.csv 放到 seeds 文件夹下,然后创建 bike_share.yml 文件,内容如下:

1
2
3
4
5
6
7
8
9
10
version: 2
seeds:
- name: hour
config:
column_types:
dteday: date
- name: day
config:
column_types:
dteday: date

这里我们指定一下 dteday 为 date 类型(没有指定的列,dbt 会自己推断类型,具体可见参考链接里 column_types 的链接),接着就可以执行 dbt seed 命令了,结果如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
08:37:28  Running with dbt=1.3.1
08:37:28 Found 2 models, 4 tests, 0 snapshots, 0 analyses, 289 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
08:37:28
08:37:28 Concurrency: 1 threads (target='dev')
08:37:28
08:37:28 1 of 2 START seed file dbt_dev.day ............................................. [RUN]
08:37:32 1 of 2 OK loaded seed file dbt_dev.day ......................................... [INSERT 731 in 3.94s]
08:37:32 2 of 2 START seed file dbt_dev.hour ............................................ [RUN]
08:39:11 2 of 2 OK loaded seed file dbt_dev.hour ........................................ [INSERT 17379 in 99.35s]
08:39:12
08:39:12 Finished running 2 seeds in 0 hours 1 minutes and 43.64 seconds (103.64s).
08:39:12
08:39:12 Completed successfully
08:39:12
08:39:12 Done. PASS=2 WARN=0 ERROR=0 SKIP=0 TOTAL=2

如果我们再执行一次 dbt seed,就会发现虽然执行时间没变,但是数据并没有重复两次,因为导入前会把老的数据清空。但如果是表结构有变化,那么就需要先删除表才可以。

创建模型

我们创建 models/shared_bike 文件夹,之后我们的模型会统一放在这个文件夹中。要做模型当然要先了解数据,我们有两张表:hour 和 day,两个的差别在于 hour 表多了一个 hr 字段,表示具体的小时,其他的属性说明如下:

  • instant: 记录编号
  • dteday : 日期
  • season : 季节 (1: 冬天, 2: 春天, 3: 夏天, 4:秋天)
  • yr : 年份 (0: 2011, 1:2012)
  • mnth : 月份 ( 1 to 12)
  • hr : 小时 (0 to 23)
  • holiday : 是否是节假日
  • weekday : 星期几
  • workingday : 是否是工作日
  • weathersit : 天气类型
    • 1: 晴天少云 Clear, Few clouds, Partly cloudy, Partly cloudy
    • 2: 有雾 Mist + Cloudy, Mist + Broken clouds, Mist + Few clouds, Mist
    • 3: 小雨小雪雷阵雨 Light Snow, Light Rain + Thunderstorm + Scattered clouds, Light Rain + Scattered clouds
    • 4: 大雨大雪冰雹 Heavy Rain + Ice Pallets + Thunderstorm + Mist, Snow + Fog
  • temp: 标准化的摄氏度 Normalized temperature in Celsius. The values are derived via (t-t_min)/(t_max-t_min), t_min=-8, t_max=+39 (only in hourly scale)
  • atemp: 标准化的体感摄氏度 Normalized feeling temperature in Celsius. The values are derived via (t-t_min)/(t_max-t_min), t_min=-16, t_max=+50 (only in hourly scale)
  • hum: 标准化的湿度 Normalized humidity. The values are divided to 100 (max)
  • windspeed: 标准化的风速 Normalized wind speed. The values are divided to 67 (max)
  • casual: 非注册用户数量 count of casual users
  • registered: 注册用户数量 count of registered users
  • cnt: 租借单车的数量(全部用户) count of total rental bikes including both casual and registered

看了一眼这些数据,我们可以先利用 group 的能力得到如下一些数据模型:

  1. group by 季节,得到每个季节的趋势
  2. group by 月份,得到每个月份的趋势
  3. group by 天气,得到每种天气的趋势
  4. group by 星期几,得到星期的趋势

这一类的模型建立都很简单,代码如下:

1
2
3
4
5
6
7
8
9
10
11
/* 季节趋势表 */

{{ config(materialized='table') }}

with weather_trend as (
select weathersit, min(temp*47+8) as min_temp, max(temp*47+8) as max_temp, avg(temp*47+8) as mean_temp, sum(casual) as total_casual, sum(registered) as total_registered, sum(cnt) as total_cnt
from {{ ref('day') }}
group by weathersit
)

select * from weather_trend

编写完成之后,执行 dbt run,可以得到如下结果:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
13:14:19  Running with dbt=1.3.1
13:14:19 Found 6 models, 4 tests, 0 snapshots, 0 analyses, 289 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
13:14:19
13:14:19 Concurrency: 1 threads (target='dev')
13:14:19
13:14:19 1 of 6 START sql table model dbt_dev.month_trend ............................... [RUN]
13:14:19 1 of 6 OK created sql table model dbt_dev.month_trend .......................... [SELECT 12 in 0.11s]
13:14:19 2 of 6 START sql table model dbt_dev.my_first_dbt_model ........................ [RUN]
13:14:19 2 of 6 OK created sql table model dbt_dev.my_first_dbt_model ................... [SELECT 2 in 0.05s]
13:14:19 3 of 6 START sql table model dbt_dev.season_trend .............................. [RUN]
13:14:19 3 of 6 OK created sql table model dbt_dev.season_trend ......................... [SELECT 4 in 0.04s]
13:14:19 4 of 6 START sql table model dbt_dev.weather_trend ............................. [RUN]
13:14:20 4 of 6 OK created sql table model dbt_dev.weather_trend ........................ [SELECT 3 in 0.04s]
13:14:20 5 of 6 START sql table model dbt_dev.weekday_trend ............................. [RUN]
13:14:20 5 of 6 OK created sql table model dbt_dev.weekday_trend ........................ [SELECT 7 in 0.05s]
13:14:20 6 of 6 START sql view model dbt_dev.my_second_dbt_model ........................ [RUN]
13:14:20 6 of 6 OK created sql view model dbt_dev.my_second_dbt_model ................... [CREATE VIEW in 0.06s]
13:14:20
13:14:20 Finished running 5 table models, 1 view model in 0 hours 0 minutes and 0.62 seconds (0.62s).
13:14:20
13:14:20 Completed successfully
13:14:20
13:14:20 Done. PASS=6 WARN=0 ERROR=0 SKIP=0 TOTAL=6

生成文档

dbt 的一大好处就是可以自动根据 sql 语句生成文档,命令如下:

1
2
3
4
# 生成文档
dbt docs generate
# 文档本地访问
dbt docs serve

得到结果如下

1
2
3
4
5
6
7
8
13:41:09  Running with dbt=1.3.1
13:41:09 Found 6 models, 4 tests, 0 snapshots, 0 analyses, 289 macros, 0 operations, 2 seed files, 0 sources, 0 exposures, 0 metrics
13:41:09
13:41:10 Concurrency: 1 threads (target='dev')
13:41:10
13:41:10 Done.
13:41:10 Building catalog
13:41:10 Catalog written to /Users/wangda/Documents/Gitee/dbt_hello_world/target/catalog.json

我们就可以清晰看到文档(含字段)和血缘了

总览视图

血缘图

至此为止,相信大家已经能感觉到 dbt 的魔力了,无需手动建表,无需手动维护文档和血缘,像编写前后端代码一样模块化进行数据分析,不要犹豫,快上车吧!

参考链接