乐趣区

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

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-…)

接下来,我将以 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/j…

$ 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 的全副性能。

退出移动版