2019 小土刀再启程

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.csv 和 day.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 的魔力了，无需手动建表，无需手动维护文档和血缘，像编写前后端代码一样模块化进行数据分析，不要犹豫，快上车吧！

参考链接

相关文章