另辟蹊径: 在云端使用SQL语言实现数据转化，测试和文档维护

前言

这是系列文章的第一篇，在这篇文章中主要介绍ELT的模式，开源工具DBT，以及DBT的简要用法；在第二篇文章中，将以此为基础，使用Amazon Web Services CDK构建一个Data OPS方案。

引子

全球化经济放缓带来的需求乏力、竞争过度、产能过剩等问题日益突出，传统企业面临着比以往更加激烈的市场竞争，迫切需要探寻新的增长机会和发展模式。企业如何利用数字化技术，实施以创新为核心的数字化转型，就变得尤为重要。

作为一名解决方案架构师，按我的理解，从技术角度去看，数字化转型的本质就是利用大数据的技术来提升企业的运营效率。

在当前这个数据量，数据来源，数据类型爆炸的时代，企业做数据分析也面临着新的挑战, 如何能够更高效的去做数据准备，从而缩短整个数据分析的周期，让数据更有实效性，增加数据的价值，就变得尤为重要。数据量巨大，数据来源众多，数据类型多样也对数据分析人员提出了挑战，有客户讲，在“数据准备”这个环节所占用的时间，很多情况下会超过整个项目时间的80%，这其中又包含了开发，测试和文档维护等多个环节。

有没有什么办法可以提高数据准备的效率呢？

本篇文章将为读者介绍一新的开源工具: DBT(Data Build Tool), 并详细演示，如何使用DBT，基于Amazon Redshift 使用SQL语言来做数据转换，测试用例编写和文档维护。

在开始介绍DBT之前，我们先来回顾下数据准备的两个常见模式： ELT 和 ETL.

数据准备模式: ETL 还是 ELT，如何选择？

在 ETL and ELT design patterns for lake house architecture using Amazon Redshift 中，详细分析对比了ETL 和 ELT两种模式。在本篇文章中，不再详细去分析对比，只是选取其中关键部分加上笔者理解，目的是帮助读者能够更全面的理解本文中基于DBT做数据准备的方案。

ETL(Extract –> Transform –> Load) 和 ELT(Extract –> Load –> Transform) 的概要对比图如下:

图片出自Amazon Web Services Blog: ETL and ELT design patterns for lake house architecture using Amazon Redshift

ETL 和 ELT两种模式的区别在于，先做数据转换(Transform) 还是先做数据加载(Load)。

在ETL的模式中，先做数据转换，这个过程是在数据仓库之外完成的，做数据转换的工具可以选择 Amazon EMR 或者 Glue。

在ELT的模式中，先把数据加载到数据仓库中，然后再做数据转换。

在亚马逊云科技上，就可以选择Amazon Redshift，充分利用Redshift MPP的架构优势来做数据转换。同时也得益于Redshift Spectrum的功能，可以简单的使用CTAS(Create Table as )命令来做数据转换, 整个流程图如下:

图片出自Amazon Web Services Blog: ETL and ELT design patterns for lake house architecture using Amazon Redshift

可以看出，使用ELT模式，技术栈也相对简单，开发人员只需要使用SQL就可以完成数据转换。而在ETL的模式中，做数据转换，开发人员则需要掌握Spark等技能，使用的技术语言则是Scala或者Python。所以，如果读者已经非常熟悉SQL，而面对学习Scala/Python又有些不知所措时，ELT模式会是一个好的选择。

那么在选择ELT的模式之后，如何高效开发，如何进行测试，如何维护项目文档？这些问题可以尝试下DBT，接下来将带读者认识DBT，学会如何使用DBT。

DBT 简介

DBT(data build tool) 是一个开源的工具，利用DBT，数据分析人员可以使用SQL来做数据转换，DBT会负责将命令转化为表或者视图。并且DBT支持多种数仓产品(Redshift, Snowflake, BigQuery, Presto 等），DBT会根据用户的配置，自动将用户编写的代码转换为特定数据仓库支持的命令。因此，DBT广受欢迎，目前以后850+ 企业在他们的生产环境使用DBT做数据转换，其中包括Casper, Seatgeek, Wistia等知名公司。

图片来自于 DBT Blog: what exactly is dbt

DBT 可以大幅度的提高数据分析人员的效率：

DBT 集成了 package manager的功能。

数据分析人员可以把常用的包做成Package，发布到私有或者公开的仓库，方便重用；

DBT支持开发人员定义测试用例，可以方便的对编写的代码进行测试，保障代码质量；
DBT支持根据开发人员编写代码中的元数据生成对应文档（类似于数据分析领域的Swagger), 降低文档维护工作量。

使用DBT在Redshift中做数据转换(Data Transformation)

本文接下来为读者详细演示，如何配置使用DBT在Redshift中做数据转换。

演示所使用的示例代码，读者可以通过https://github.com/readybuilderone/elt-with-dbt-demo 下载运行，示例代码仅供参考。

环境准备

创建演示用的Redshift集群，为了演示方便，在本文中打开了Redshift集群的公网访问权限。

笔者封装成了Amazon Web Services CDK的Project，可以使用如下方法创建 (不习惯Amazon Web Services CDK的读者，可以参考Redshift官方文档手工创建集群):

Amazon Web Services CDK 开发环境搭建

开发Amazon Web Services CDK需要先安装Amazon Web Services CDK CLI，利用 Amazon Web Services CDK CLI可以生成对应的CDK 的Project。

Amazon Web Services CDK CLI的安装依赖于Node.js, 所以在您的开发环境需要先安装node.js。node.js 的安装可参看官方教程: https://nodejs.org/en/download/package-manager/。

安装好 node.js 之后，可以直接使用如下命令安装 Amazon Web Services CDK CLI：

npm install -g aws-cdk #安装cdk cli
cdk --version #查看版本

安装 CDK CLI后，需要通过aws configure 命令配置开发环境的用户权限，详情参考: https://docs.thinkwithwp.com/cli/latest/userguide/cli-configure-files.html

搭建测试Redshift集群

配置好之后，在命令行使用如下命令，创建Redshift集群:

git clone https://github.com/readybuilderone/elt-with-dbt-demo.git
cd elt-with-dbt-demo
npx projen
npx cdk bootstrap
npx cdk deploy --profile <YOUR-PROFILE>

注意，请将上述代码中的替换为您配置的Amazon Web Services Credential的Profile。

执行完成之后，会创建一具有公网访问权限的S3 Bucket 和一个Redshift集群。

创建成功后，会在命令行打印出：

用于存放需要加载到Redshift数据的S3的BucketName；
Redshift的Host地址；
Redshift的Execute Role的ARN

Redshift的链接信息如下

Redshift DB Name: redshift-db
Redshift User: redshift-user
Redshift Port: 5439
Redshift Host: 参考CDK运行截图

上述CDK 代码会在Secrets Manager中创建名为”redshift-credentials” 的secrets，您可以通过Retrieve secret value 查看。

可以使用如下命令，测试Redshift连接 (参考: https://docs.thinkwithwp.com/redshift/latest/mgmt/connecting-from-psql.html):

psql -h <endpoint> -U <userid> -d <databasename> -p <port>
安装DBT

DBT 的安装非常简单并且有多种选择, 参考: https://docs.getdbt.com/dbt-cli/installation

安装完成之后，运行如下命令检查DBT安装是否成功:

dbt --version

#正确安装应该会输出如下内容
installed version: 0.20.2
latest version: 0.20.2

Up to date!

Plugins:
- bigquery: 0.20.2
- snowflake: 0.20.2
- redshift: 0.20.2
- postgres: 0.20.2

新建DBT Project

DBT安装好之后，使用如下命令创建 Project:

dbt init dbt-demo

这个命令将会创建 profiles.yml 和一个 dbt-demo 的文件夹, profiles.yml 存放RedShift的Credentials。dbt-demo 文件夹为DBT的项目文件夹。

根据创建的Redshift的值，修改profiles.yml 中 default –> outputs –> dev 的配置项：

之后，在dbt-demo 中执行如下命令，验证dbt可以连接到新建的Redshift集群：

dbt debug

数据准备

测试数据说明

本文中使用Jaffle Shop Sample 数据来做演示。

Jaffle Shop 中共有customer, order, payments 三张表，schema如下:

加载测试数据到Redshift

Jaffle Shop 的原始数据存在sample_db/jaffle_shop/ 文件夹下，使用如下命令将CSV文件同步到S3:

cd ./sample_db/jaffle_shop
aws s3 sync . s3://<YOUR-BUCKET>/jaffle_shop/ --profile <YOUR-PROFILE>

注意: 读者请替换和为自己环境中的值；

数据同步到S3之后，读者可以选择使用自己喜欢的IDE或者在Amazon Web Services控制台中，使用如下语句创建Table:

create table raw_customers
(
    id         integer,
    first_name varchar(10),
    last_name  varchar(2)
);

create table raw_orders
(
    id         integer,
    user_id    integer,
    order_date date,
    status     varchar(14)
);

create table raw_payments
(
    id             integer,
    order_id       integer,
    payment_method varchar(13),
    amount         integer
);

使用如下语句把数据从s3加载到redshift:

copy raw_customers from 's3://<myBucket>/jaffle_shop/raw_customers.csv' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter ',' region '<aws-region>'
IGNOREHEADER 1;

copy raw_orders from 's3://<myBucket>/jaffle_shop/raw_orders.csv' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter ',' region '<aws-region>'
IGNOREHEADER 1;

copy raw_payments from 's3://<myBucket>/jaffle_shop/raw_payments.csv' 
credentials 'aws_iam_role=<iam-role-arn>' 
delimiter ',' region '<aws-region>'
IGNOREHEADER 1;

注意：需要替换上述代码中的 , , , 其中, 参考创建Redshift集群时CDK的Output。

到此，我们已经准备好了DBT的开发环境，也准备好了Redshift中的数据，接下来为读者们演示如何使用DBT来做Data Transform，Test 和Generate Doc。

使用DBT做Data Transform， Test 和 Generate Doc:

使用DBT做Data Transform

如上文所讲，在ELT的模式中，使用create table as 的方法来做Data Transform。

在DBT的Project中，先定义对应的model，再利用dbt run命令可以方便的来实现。

在数据准备阶段，创建了raw_customers, raw_orders, raw_payments三张表，现在模拟数仓分层，生成对应的stg 数据, 在DBT中操作如下:

创建对应的model, 内容如下:

stg_customers.sql
with source as (
    select * from raw_customers
),

renamed as (
    select
        id as customer_id,
        first_name,
        last_name
    from source
)

select * from renamed

stg_orders.sql
with source as (
    select * from raw_orders
),

renamed as (
    select
        id as order_id,
        user_id as customer_id,
        order_date,
        status
    from source
)

select * from renamed

stg_payments.sql
with source as (
    select * from raw_payments
),

renamed as (
    select
        id as payment_id,
        order_id,
        payment_method,

        -- `amount` is currently stored in cents, so we convert it to dollars
        amount / 100 as amount
    from source
)

select * from renamed

创建完成之后，文档的目录应如下图所示:

/models/example文件夹为DBT生成的示例model，这里可忽略。

允许 dbt run命令，执行对应的数据转换:

执行完成之后，在Redshift进行验证:

可以看到，对应的stg tables 都已创建完成，操作成功。

使用DBT做Test

在做数据准备的时候，测试是提高效率，保证数据质量，降低Bug的有效手段。

在上述做stg的时候，如果对于数据有如下要求:

customer_id, order_id, payment_id 要求唯一切不能为null
order的status只能为’placed’, ‘shipped’, ‘completed’, ‘return_pending’, ‘returned’ 之一；
payment method 只可以为 ‘credit_card’, ‘coupon’, ‘bank_transfer’, ‘gift_card’ 之一；

这样的条件该如何满足呢？

在DBT中，可以利用yml文件定义规则，然后使用dbt run来进行检查。

具体操作如下：

创建schema.yml 文件，放在 models/stg 文件夹下:

version: 2

models:
  - name: stg_customers
    columns:
      - name: customer_id
        tests:
          - unique
          - not_null

  - name: stg_orders
    columns:
      - name: order_id
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

  - name: stg_payments
    columns:
      - name: payment_id
        tests:
          - unique
          - not_null
      - name: payment_method
        tests:
          - accepted_values:
              values: ['credit_card', 'coupon', 'bank_transfer', 'gift_card']

在命令行来进行测试:

dbt test --models stg_customers, stg_orders, stg_payments

测试成功。

使用DBT Generate Doc

文档维护也是数据分析项目中极其重要又极其耗时的工作。使用DBT，可以利用yml 对数据格式进行描述，并自动生成文档：

更新 schema.yml, 添加 description 字段:

version: 2

models:
  - name: customers
    description: One record per customer
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: first_order_date
        description: NULL when a customer has not yet placed an order.

  - name: stg_customers
    description: This model cleans up customer data
    columns:
      - name: customer_id
        description: Primary key
        tests:
          - unique
          - not_null

  - name: stg_orders
    description: This model cleans up order data
    columns:
      - name: order_id
        description: Primary key
        tests:
          - unique
          - not_null
      - name: status
        tests:
          - accepted_values:
              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']

在命令行，使用命令生成文档，查看文档

dbt docs generate
dbt docs serve

文档生成成功。

总结

在本文中，介绍了数据准备中的ELT的模式和 DBT，基于这个方案，可以让数据分析人员使用SQL来做数据转换，测试和维护文档。这个是系列文档的第一篇，读者将在第二篇文章中为读者演示如何使用Amazon Web Services CDK利用DBT来构建一个DataOPS的方案，希望对您有所帮助。

参考文档

Medium:Data Engineering — ETL or ELT
Amazon Web Services Blog: ETL and ELT design patterns for lake house architecture using Amazon Redshift: Part 1
Amazon Web Services Blog: https://thinkwithwp.com/blogs/big-data/etl-and-elt-design-patterns-for-lake-house-architecture-using-amazon-redshift-part-2/
Towards Data Science Blog:A Data Warehouse Implementation on Amazon Web Services
DBT Offical Doc

亚马逊AWS官方博客