当 dbt 遇见 TiDB丨高效的数据转换工具让数据分析更简单-低调大师

当 dbt 遇见 TiDB丨高效的数据转换工具让数据分析更简单

2022-04-13 556

dbt （data build tool）是一款流行的开源数据转换工具，能够通过 SQL 实现数据转化，将命令转化为表或者视图，提升数据分析师的工作效率。TiDB 社区在近日推出了 dbt-tidb 插件，实现了 TiDB 和 dbt 的兼容适配。本文将通过一个简单的案例介绍如何通过 dbt 实现 TiDB 中数据的简单分析。

dbt 主要功能在于转换数据库或数据仓库中的数据，在 E（Extract）、L（Load）、T（Transform）的流程中，仅负责转换（transform）的过程。通过 dbt-tidb 插件，数据分析师在使用 TiDB 的过程中，能够通过 SQL 直接建立表单并匹配数据，而无需关注创建 table 或 view 的过程，并且可以直观地看到数据的流动；同时能够运用 dbt 的 Jinja 编写 SQL、测试、包管理等功能，大大提升工作效率。

（图片来源： https://blog.getdbt.com/what-exactly-is-dbt/）

接下来，我将以 dbt 官方教程为例，给大家介绍下 TiDB 与 dbt 的结合使用。

本例用到的相关软件及其版本要求：

TiDB 5.3 或更高版本
dbt 1.0.1 或更高版本
dbt-tidb 1.0.0

安装

dbt 除了本地 CLI 工具外，还支持 dbt Cloud (目前，dbt Cloud 只支持 dbt-lab 官方维护的 adapter)，其中本地 CLI 工具有多种安装方式。我们这里直接使用 pypi 安装 dbt 和 dbt-tidb 插件。

安装 dbt 和 dbt-tidb，只需要一条命令，因为 dbt 会作为依赖在安装 dbt-tidb 的时候顺便安装。

$ pip install dbt-tidb

dbt 也可自行安装，安装方式参考官方安装教程。

创建项目：jaffle_shop

jaffle_shop 是 dbt-lab 提供的用于演示 dbt 功能的工程项目，你可以直接从 GitHub 上获取它。

$ git clone https://github.com/dbt-labs/jaffle_shop

$ cd jaffle_shop

这里展开 jaffle_shop 工程目录下所有文件。

dbt_project.yml是 dbt 项目的配置文件，其中保存着项目名称、数据库配置文件的路径信息等。
models目录下存放该项目的 SQL 模型和 table 约束，注意这部分是数据分析师自行编写的。
seed目录存放 CSV 文件。此类文件可以来源于数据库导出工具，例如TiDB 可以通过 Dumpling 把 table 中的数据导出为 CSV 文件。jaffle_shop 工程中，这些 CSV 文件用来作为待处理的原始数据。

关于它们更加具体的内容，在用到上面的某个文件或目录后，我会再次进行更详细的说明。

ubuntu@ubuntu:~/jaffle_shop$ tree
.

├── dbt_project.yml

├── etc

│   ├── dbdiagram_definition.txt

│   └── jaffle_shop_erd.png

├── LICENSE

├── models

│   ├── customers.sql

│   ├── docs.md

│   ├── orders.sql

│   ├── overview.md

│   ├── schema.yml

│   └── staging

│       ├── schema.yml

│       ├── stg_customers.sql

│       ├── stg_orders.sql

│       └── stg_payments.sql

├── README.md

└── seeds

    ├── raw_customers.csv

    ├── raw_orders.csv

    └── raw_payments.csv

配置项目

1.全局配置

dbt 有一个默认的全局配置文件：~/.dbt/profiles.yml，我们首先在用户目录下建立该文件，并配置 TiDB 数据库的连接信息。

 $ vi ~/.dbt/profiles.yml

 jaffle_shop_tidb:                        # 工程名称

  target: dev                             # 目标

  outputs:

    dev:

      type: tidb                         # 适配器类型

      server: 127.0.0.1                  # 地址

      port: 4000                         # 端口号

      schema: analytics                  # 数据库名称

      username: root                     # 用户名

      password: ""                       # 密码

2.项目配置

jaffle_shop 工程目录下，有此项目的配置文件，名为dbt_project.yml。把profile配置项改为jaffle_shop_tidb，即profiles.yml文件中的工程名称。这样此工程在会到 ~/.dbt/profiles.yml文件中查询数据库连接配置。

$ cat dbt_project.yml 

name: 'jaffle_shop'



config-version: 2

version: '0.1'



profile: 'jaffle_shop_tidb'                   # 注意此处修改



model-paths: ["models"]                       # model 路径

seed-paths: ["seeds"]                         # seed 路径

test-paths: ["tests"]                         

analysis-paths: ["analysis"]

macro-paths: ["macros"]



target-path: "target"

clean-targets:

    - "target"

    - "dbt_modules"

    - "logs"



require-dbt-version: [">=1.0.0", "<2.0.0"]



models:

  jaffle_shop:

      materialized: table            # models/ 中的 *.sql 物化为表

      staging:           

        materialized: view           # models/staging/ 中的 *.sql 物化为视图

3.验证配置

可以通过以下命令，检测数据库和项目配置是否正确。

$ dbt debug

06:59:18  Running with dbt=1.0.1

dbt version: 1.0.1

python version: 3.8.10

python path: /usr/bin/python3

os info: Linux-5.4.0-97-generic-x86_64-with-glibc2.29

Using profiles.yml file at /home/ubuntu/.dbt/profiles.yml

Using dbt_project.yml file at /home/ubuntu/jaffle_shop/dbt_project.yml



Configuration:

  profiles.yml file [OK found and valid]

  dbt_project.yml file [OK found and valid]



Configuration:

  profiles.yml file [OK found and valid]

  dbt_project.yml file [OK found and valid]



Required dependencies:

 - git [OK found]



Connection:

  server: 127.0.0.1

  port: 4000

  database: None

  schema: analytics

  user: root

  Connection test: [OK connection ok]



All checks passed!

加载 CSV

加载 CSV 数据，把 CSV 具体化为目标数据库中的表。注意：一般来说，dbt 项目不需要这个步骤，因为你的待处理项目的数据都在数据库中。

$ dbt seed

07:03:24  Running with dbt=1.0.1

07:03:24  Partial parse save file not found. Starting full parse.

07:03:25  Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics

07:03:25

07:03:25  Concurrency: 1 threads (target='dev')

07:03:25

07:03:25  1 of 3 START seed file analytics.raw_customers.................................. [RUN]

07:03:25  1 of 3 OK loaded seed file analytics.raw_customers.............................. [INSERT 100 in 0.19s]

07:03:25  2 of 3 START seed file analytics.raw_orders..................................... [RUN]

07:03:25  2 of 3 OK loaded seed file analytics.raw_orders................................. [INSERT 99 in 0.14s]

07:03:25  3 of 3 START seed file analytics.raw_payments................................... [RUN]

07:03:26  3 of 3 OK loaded seed file analytics.raw_payments............................... [INSERT 113 in 0.24s]

07:03:26

07:03:26  Finished running 3 seeds in 0.71s.

07:03:26

07:03:26  Completed successfully

07:03:26

07:03:26  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

上述结果中，可以清楚的看到共执行了三个任务，分别加载了 analytics.raw_customers、analytics.raw_orders、analytics.raw_payments三张表。

接着，去 TiDB 数据库中看看发生了什么。

发现多出了 analytics数据库，这是 dbt 为我们创建的工程数据库。

mysql> show databases;

+--------------------+

| Database           |

+--------------------+

| INFORMATION_SCHEMA |

| METRICS_SCHEMA     |

| PERFORMANCE_SCHEMA |

| analytics          |

| mysql              |

| test               |

+--------------------+

6 rows in set (0.00 sec)

analytics数据库中有三张表，分别对应着上述三个任务结果。

mysql> show tables;

+---------------------+

| Tables_in_analytics |

+---------------------+

| raw_customers       |

| raw_orders          |

| raw_payments        |

+---------------------+

3 rows in set (0.00 sec)

model 是什么？

在进行下一个步骤之前，我们有必要先了解下 dbt 中的 model 扮演着什么角色？

dbt 中使用 model 来描述一组数据表或视图的结构，其中主要有两类文件：SQL 和 YML。还需要注意到的是：在 jaffle_shop 这个项目中，根据物化配置，models/目录下保存的是表结构，而 models/staging/目录下保存的是视图结构。

以 models/orders.sql为例，它是一句 SQL 查询语句，支持 jinja 语法，接下来的命令中，会根据这条 SQL 创建出 orders表。

$ cat models/orders.sql

{% set payment_methods = ['credit_card', 'coupon', 'bank_transfer', 'gift_card'] %}



with orders as (



    select * from {{ ref('stg_orders') }}



),



payments as (



    select * from {{ ref('stg_payments') }}



),



order_payments as (



    select

        order_id,



        {% for payment_method in payment_methods -%}

        sum(case when payment_method = '{{ payment_method }}' then amount else 0 end) as {{ payment_method }}_amount,

        {% endfor -%}



        sum(amount) as total_amount



    from payments



    group by order_id



),



final as (



    select

        orders.order_id,

        orders.customer_id,

        orders.order_date,

        orders.status,



        {% for payment_method in payment_methods -%}



        order_payments.{{ payment_method }}_amount,



        {% endfor -%}



        order_payments.total_amount as amount



    from orders





    left join order_payments

        on orders.order_id = order_payments.order_id



)



select * from final

并且，与这条 SQL 配套的约束信息在 models/schema.yml文件中。

schema.yml是当前目录下所有模型的注册表，所有的模型都被组织成一个树形结构，描述了每条字段的说明和属性。其中 tests条目表示这个字段的一些约束项，可以通过 dbt test命令来检测，更多信息请查阅官网文档。

cat models/schema.yml

version: 2

...

  - name: orders

    description: This table has basic information about orders, as well as some derived facts based on payments



    columns:

      - name: order_id

        tests:

          - unique

          - not_null

        description: This is a unique identifier for an order



      - name: customer_id

        description: Foreign key to the customers table

        tests:

          - not_null

          - relationships:

              to: ref('customers')

              field: customer_id



      - name: order_date

        description: Date (UTC) that the order was placed



      - name: status

        description: '{{ doc("orders_status") }}'

        tests:

          - accepted_values:

              values: ['placed', 'shipped', 'completed', 'return_pending', 'returned']



      - name: amount

        description: Total amount (AUD) of the order

        tests:

          - not_null



      - name: credit_card_amount

        description: Amount of the order (AUD) paid for by credit card

        tests:

          - not_null



      - name: coupon_amount

        description: Amount of the order (AUD) paid for by coupon

        tests:

          - not_null



      - name: bank_transfer_amount

        description: Amount of the order (AUD) paid for by bank transfer

        tests:

          - not_null



      - name: gift_card_amount

        description: Amount of the order (AUD) paid for by gift card

        tests:

          - not_null

运行

结果中显示成功创建了三张视图（analytics.stg_customers、analytics.stg_orders、analytics.stg_payments）和两张表（analytics.customers、analytics.orders）。

$ dbt run

07:28:43  Running with dbt=1.0.1

07:28:43  Unable to do partial parsing because profile has changed

07:28:43  Unable to do partial parsing because a project dependency has been added

07:28:44  Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics

07:28:44

07:28:44  Concurrency: 1 threads (target='dev')

07:28:44

07:28:44  1 of 5 START view model analytics.stg_customers................................. [RUN]

07:28:44  1 of 5 OK created view model analytics.stg_customers............................ [SUCCESS 0 in 0.12s]

07:28:44  2 of 5 START view model analytics.stg_orders.................................... [RUN]

07:28:44  2 of 5 OK created view model analytics.stg_orders............................... [SUCCESS 0 in 0.08s]

07:28:44  3 of 5 START view model analytics.stg_payments.................................. [RUN]

07:28:44  3 of 5 OK created view model analytics.stg_payments............................. [SUCCESS 0 in 0.07s]

07:28:44  4 of 5 START table model analytics.customers.................................... [RUN]

07:28:44  4 of 5 OK created table model analytics.customers............................... [SUCCESS 0 in 0.16s]

07:28:44  5 of 5 START table model analytics.orders....................................... [RUN]

07:28:45  5 of 5 OK created table model analytics.orders.................................. [SUCCESS 0 in 0.12s]

07:28:45

07:28:45  Finished running 3 view models, 2 table models in 0.64s.

07:28:45

07:28:45  Completed successfully

07:28:45

07:28:45  Done. PASS=5 WARN=0 ERROR=0 SKIP=0 TOTAL=5

去 TiDB 数据库中验证下，是否真的创建成功。

结果显示多出了 customers等五张表格或视图，并且表或视图中的数据也都转换完成。这里只展示 customers的部分数据。

mysql> show tables;

+---------------------+

| Tables_in_analytics |

+---------------------+

| customers           |

| orders              |

| raw_customers       |

| raw_orders          |

| raw_payments        |

| stg_customers       |

| stg_orders          |

| stg_payments        |

+---------------------+

8 rows in set (0.00 sec)



mysql> select * from customers;

+-------------+------------+-----------+-------------+-------------------+------------------+-------------------------+

| customer_id | first_name | last_name | first_order | most_recent_order | number_of_orders | customer_lifetime_value |

+-------------+------------+-----------+-------------+-------------------+------------------+-------------------------+

|           1 | Michael    | P.        | 2018-01-01  | 2018-02-10        |                2 |                 33.0000 |

|           2 | Shawn      | M.        | 2018-01-11  | 2018-01-11        |                1 |                 23.0000 |

|           3 | Kathleen   | P.        | 2018-01-02  | 2018-03-11        |                3 |                 65.0000 |

|           4 | Jimmy      | C.        | NULL        | NULL              |             NULL |                    NULL |

|           5 | Katherine  | R.        | NULL        | NULL              |             NULL |                    NULL |

|           6 | Sarah      | R.        | 2018-02-19  | 2018-02-19        |                1 |                  8.0000 |

|           7 | Martin     | M.        | 2018-01-14  | 2018-01-14        |                1 |                 26.0000 |

|           8 | Frank      | R.        | 2018-01-29  | 2018-03-12        |                2 |                 45.0000 |

....

生成文档

dbt 还支持生成可视化的文档，命令如下。

1.生成文档

$ dbt docs generate

07:33:59  Running with dbt=1.0.1

07:33:59  Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics

07:33:59

07:33:59  Concurrency: 1 threads (target='dev')

07:33:59

07:33:59  Done.

07:33:59  Building catalog

07:33:59  Catalog written to /home/ubuntu/jaffle_shop/target/catalog.json

2.开启服务

$ dbt docs serve

07:43:01  Running with dbt=1.0.1

07:43:01  Serving docs at 0.0.0.0:8080

07:43:01  To access from your browser, navigate to:  http://localhost:8080

07:43:01

07:43:01

07:43:01  Press Ctrl+C to exit.

可以通过浏览器查看文档，其中包含 jaffle_shop 项目的整体结构以及所有表和视图的描述说明。

总结

TiDB 在 dbt 中的使用主要有以下几步：

安装 dbt 和 dbt-tidb
配置项目
编写 SQL 和 YML 文件
运行项目

目前，TiDB 支持 dbt 的版本在 4.0 以上，但根据 dbt-tidb 项目文档描述，低版本的 TiDB 在和 dbt 结合使用中还存在一些问题，例如：不支持临时表和临时视图、不支持 WITH 语法等。想要痛快的使用 dbt ，建议使用 TiDB 5.3 以上版本，此版本支持 dbt 的全部功能。

微信关注我们

原文链接：https://my.oschina.net/zhaiyuan/blog/5513416

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

刘天栋：开源是打破内卷的最好方式

| 转载自：腾源会 |编辑：金心悦 | 设计：朱亿钦 | 责编：王玥敏熵，Entropy，一个描述系统状态的函数，最初诞生于热力学，用来测量不能做功的能量总数。之后被引申到信息论、生物学、控制论、概率论等各个学科，成为其中重要的参考值和变化量。在去年的一次讲演中，刘天栋借用「熵」的概念，阐述了他对开源的理解。最近，我们有幸邀请到他，再次从「熵」出发，交流分享在开源与生活、个人与社区、创新与商业等方面的思考。本期 WeOpen Talk 栏目嘉宾——开源社联合创始人、Apache 软件基金会 Member、腾讯云 TVP、腾源会导师刘天栋。以下为他的分享：时间之矢：个人开源成长热力学第二定律指出，热量自发地从高温热源流向低温热源，过程中损耗的能量即为熵。因为其过程不可逆，可作为时间流向的依据，英国天体物理学家 A.Eddington 把熵喻为「时间之矢」。我从事软件行业有将近 30 多年的时间，曾经任职过 Computer Associates、甲骨文和微软。2000 年初，我从甲骨文加入了全球最早的做 Linux 发行版的公司 TurboLinux，开始接触到开源的世界，...

2022-04-11

533

摘要：在电商平台，一个好的推荐系统能帮助用户发现好商品，也能使高质量的商品触达精准受众。本文分享自华为云社区《推荐--用图分析！》，作者：你好_TT。导读推荐系统被认为是一种用于提供给用户感兴趣的商品、内容或者服务的技术。图技术作为刻画事物之间深度链路关系的技术，常常被用来进行推荐系统的建模。本文首先介绍推荐系统以及推荐系统和图技术之间的关系，而后通过举例说明图技术如何赋能推荐系统。什么是推荐系统当用户需要连接到互联网以满足其需求时，他经常会遇到大量的相关信息。推荐系统是一种用于过滤信息并提供给用户尽量使他们满意和感兴趣的商品、内容或者服务的技术。随着电子商务和社交媒体平台的快速发展，为了防止用户在数据海中迷失自我，推荐系统应运而生。推荐系统作为AI最成功和最重要的应用之一，帮助消费者更容易地找到相关的或感兴趣的内容、商品或者服务。推荐系统的重要性当今互联网承载着多种信息资源，数据量呈指数级增长。推荐系统在缓解信息过载这一问题上发挥着重要作用。在电商平台，一个好的推荐系统能帮助用户发现好商品，也能使高质量的商品触达精准受众。从用户角度来看，推荐系统可以缓解信息过载带...

2022-04-13

540

资源下载

更多资源

优质分享App

近一个月的开发和优化，本站点的第一个app全新上线。该app采用极致压缩，本体才4.36MB。系统里面做了大量数据访问、缓存优化。方便用户在手机上查看文章。后续会推出HarmonyOS的适配版本。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。