在TiDB上使数据转换变得更容易的方法教程

方石剑
269 阅读2分钟

TiDB上的数据转换变得更容易了

通过dbt-tidb插件，使用TiDB的分析工程师可以直接创建表单并通过SQL进行数据匹配。

数据构建工具（dbt）是一个流行的开源数据转换工具，使分析工程师能够通过SQL语句转换仓库中的数据。TiDB社区最近发布了dbt-tidb适配器，使TiDB成为分布式SQL数据库，与dbt协同工作。通过dbt-tidb插件，使用TiDB的分析工程师可以直接创建表单，通过SQL匹配数据，而不必考虑创建表或视图的过程。他们还可以使用Jinja这个dbt模板语言来编写SQL、测试、包管理和其他功能，这大大提高了效率。

在本教程中，我将向你展示如何在TiDB中使用dbt。在你尝试下面的任何步骤之前，请确保已经安装了以下项目：

  • TiDB 5.3或更高版本
  • dbt 1.01或更高版本
  • dbt-tidb 1.0.0

安装

有几种方法可以安装dbt和dbt-tidb，在本教程中，我们将使用pypi。当你安装dbt-tidb时，dbt会作为一个依赖项被安装。所以你只需要一个命令就可以安装这两个东西：

$ pip install dbt-tidb

你也可以单独安装dbt。请参考dbt文档中的How to install dbt。

创建项目：jaffle shop

dbt-lab提供了一个项目，jaffle_shop，来演示dbt的功能。你可以直接从GitHub获得该项目。

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

jaffle_shop项目目录下的所有文件的结构如下：

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
  • dbt_project.yml是dbt项目的配置文件，它持有项目名称和数据库配置文件信息。
  • models目录包含项目的SQL模型和表模式。注意，你公司的数据分析师会写这一部分。要了解更多关于模型的信息，请看dbt文档。
  • seed目录存储了从数据库导出工具转出的CSV文件。例如，TiDB可以通过Dumpling将表的数据导出为CSV文件。在jaffle shop项目中，这些CSV文件被用作要处理的原始数据。

配置项目

要配置该项目：

  1. 完成全局配置。在用户目录中，编辑默认的全局配置文件，~/.dbt/profiles.yml，配置与TiDB的连接。

    $ vi ~/.dbt/profiles.yml
jaffle_shop_tidb:           # project name
target: dev                # target
outputs:
   dev:
      type: tidb           # adapter type
      server: 127.0.0.1
      port: 4000
      schema: analytics    # database name
      username: root
      password: ""

  2. 完成项目配置： 在jaffle_shop项目目录下，进入项目配置文件dbt_project.yml ，并将配置文件字段改为jaffle_shop_tidb 。该配置允许项目从~/.dbt/profiles.yml文件中指定的数据库查询。

    $ cat dbt_project.yml
name: 'jaffle_shop'

config-version: 2
version: '0.1'

profile: 'jaffle_shop_tidb'      # note the modification here

model-paths: ["models"]          # model path
seed-paths: ["seeds"]            # seed path
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 # *.sql which in models/ would be materialized to table
staging: 
materialized: view # *.sql which in models/staging/ would bt materialized to view

  3. 验证该配置： 运行下面的命令来检查数据库和项目配置是否正确。

    $ dbt debug

加载CSV文件

现在你已经成功地创建和配置了项目，是时候加载CSV数据并将CSV作为目标数据库中的一个表来实现。注意，对于dbt项目来说，一般不需要这个步骤，因为要处理的数据项已经在数据库中了。

  1. 通过运行下面的命令加载CSV文件。

    $ dbt seed

    这时显示如下：

    Running with dbt=1.0.1
Partial parse save file not found. Starting full parse.
Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics

Concurrency: 1 threads (target='dev')

1 of 3 START seed file analytics.raw_customers.................................. [RUN]
1 of 3 OK loaded seed file analytics.raw_customers.............................. [INSERT 100 in 0.19s]
2 of 3 START seed file analytics.raw_orders..................................... [RUN]
2 of 3 OK loaded seed file analytics.raw_orders................................. [INSERT 99 in 0.14s]
3 of 3 START seed file analytics.raw_payments................................... [RUN]
3 of 3 OK loaded seed file analytics.raw_payments............................... [INSERT 113 in 0.24s]

    正如你在结果中看到的，种子文件被启动并加载到三个表中：analytics.raw_customers,analytics.raw_orders, 和analytics.raw_payments 。

  2. 验证TiDB中的结果。showdatabases命令列出了dbt创建的新的分析数据库。show tables命令显示，在分析数据库中有三个表，与我们上面创建的表相对应。

    mysql> show databases;
+--------------------+
| Database |
+--------------------+
| INFORMATION_SCHEMA |
| METRICS_SCHEMA |
| PERFORMANCE_SCHEMA |
| analytics |
| mysql |
| test |
+--------------------+
6 rows in set (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_analytics |
+---------------------+
| raw_customers |
| raw_orders |
| raw_payments |
+---------------------+
3 rows in set (0.00 sec)

运行dbt项目

现在你已经准备好运行配置好的项目并完成 数据转换。

  1. 运行dbt项目，完成数据转换。

    $ dbt run

    Running with dbt=1.0.1
Unable to do partial parsing because profile has changed
Unable to do partial parsing because a project dependency has been added
Found 5 models, 20 tests, 0 snapshots, 0 analyses, 172 macros, 0 operations, 3 seed files, 0 sources, 0 exposures, 0 metrics
Concurrency: 1 threads (target='dev')
1 of 5 START view model analytics.stg_customers................................. [RUN]
1 of 5 OK created view model analytics.stg_customers............................ [SUCCESS 0 in 0.12s]
2 of 5 START view model analytics.stg_orders.................................... [RUN]
2 of 5 OK created view model analytics.stg_orders............................... [SUCCESS 0 in 0.08s]
3 of 5 START view model analytics.stg_payments.................................. [RUN]
3 of 5 OK created view model analytics.stg_payments............................. [SUCCESS 0 in 0.07s]
4 of 5 START table model analytics.customers.................................... [RUN]
4 of 5 OK created table model analytics.customers............................... [SUCCESS 0 in 0.16s]
5 of 5 START table model analytics.orders....................................... [RUN]
5 of 5 OK created table model analytics.orders.................................. [SUCCESS 0 in 0.12s]

    结果显示三个视图（analytics.stg_customers,analytics.stg_orders, 和analytics.stg_payments ）和两个表（analytics.customers 和analytics.orders ）被成功创建。

  2. 转到TiDB数据库，验证操作是否成功。

    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 |

    输出显示还有5个表或视图被添加，并且表或视图中的数据已经被转换。注意，这里只显示了客户表的部分数据。

生成可视化文档

dbt可以让你生成可视化文档，显示项目的整体结构并描述所有的表和视图。要生成可视化文档。

  1. 生成文件。

    $ dbt docs generate

  2. 启动服务器。

    $ dbt docs serve 
Running with dbt=1.0.1
Serving docs at 0.0.0.0:8080

  3. 要从浏览器中访问文档视图，请导航到http://localhost:8080。

目前，TiDB在TiDB 4.0及以后的版本中支持dbt。早期版本的TiDB在使用dbt的时候可能会遇到问题。详情请访问GitHub上的tidb-dbt项目。为了充分利用dbt，我们建议你运行TiDB 5.3或更高版本。这些版本支持dbt的所有功能。

avatar
文章
阅读
粉丝