Средство формирования данных (Data build tool, dbt) — это популярный инструмент с открытым исходным кодом, который позволяет инженерам-аналитикам преобразовывать данные в своих хранилищах с помощью операторов SQL. Совсем недавно участники профильного соощества выпустили плагин dbt-tidb, который позволит TiDB-распределённой базе данных SQL работать с dbt. Благодаря плагину dbt-tidb инженеры-аналитики, работающие с TiDB, могут напрямую создавать формы и сопоставлять данные через SQL без необходимости думать о процессе создания таблиц или представлений. Теперь можно использовать Jinja, язык шаблонов dbt для написания SQL, тестирования, управления пакетами и других функций, что значительно повышает эффективность работы с данными.

В этом руководстве я покажу вам, как использовать dbt с TiDB. Но прежде чем приступить к выполнению описанных ниже действий, убедитесь, что у вас установлены следующие компоненты:

TiDB 5.3 или более поздняя версия
dbt 1.01 или более поздняя версия
dbt-tidb 1.0.0

Установка

Существует несколько способов установки dbt и dbt-tidb. В этом туториале мы будем использовать pypi. Когда вы устанавливаете dbt-tidb, dbt устанавливается как зависимость. Таким образом, вам понадобится всего одна команда для установки обоих:

$ pip install dbt-tidb

Вы также можете установить dbt отдельно. Подробнее см. в разделе "Как установить dbt" в документации по dbt.

Создаём проект: магазинчик сэндвичей

Прим. редактора: В качестве своего основного товара магазинчик будет предлагать именно австралийские закрытые сэндвичи — джаффлы (jaffle).

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 Docs.

В директории seed хранятся файлы CSV, которые выгружаются из инструментов экспорта баз данных. Например, TiDB может экспортировать данные таблиц в CSV-файлы через Dumpling. В проекте jaffle shop эти CSV-файлы используются в качестве исходных данных для обработки.

Настраиваем проект

Чтобы настроить проект:

Завершите глобальную конфигурацию.

В каталоге пользователя отредактируйте глобальный профиль по умолчанию ~/.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: ""

Завершите конфигурацию проекта.

В директории проекта jaffle_shop откройте файл конфигурации проекта dbt_project.yml и измените поле profile на jaffle_shop_tidb. Эта конфигурация позволяет проекту выполнять запросы из базы данных, указанной в файле ~/.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 # *.sql, который в models/ будет переведён в таблицу           
staging:
materialized: view # *.sql, который в models/staging/ будет переведён в представление

Проверьте правильность конфигураций.

Выполните следующую команду, чтобы проверить правильность конфигурации базы данных и проекта:
```
$ dbt debug
```

Загружаем файлы CSV

Теперь, когда вы успешно создали и настроили проект, пришло время загрузить данные CSV и представить CSV в виде таблицы в целевой базе данных. Обратите внимание, что этот шаг обычно не требуется для проекта dbt, поскольку элементы данных для обработки уже находятся в БД.

Загрузите файлы 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]

Как видно из результатов, seed-файл был запущен и загружен в три таблицы: analytics.raw_customers, analytics.raw_orders и analytics.raw_payments.

Проверьте результаты в TiDB. Команда show databases отобразит новую аналитическую базу данных, созданную 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 проекта

Теперь вы готовы запустить ранее сконфигурированные проекты и завершить преобразование данных.

Запустите проект 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) были успешно созданы.

Перейдите к базе данных 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 |

В результате вывода видно, что было добавлено ещё пять таблиц или представлений, а данные в таблицах или представлениях были преобразованы. Обратите внимание, что здесь показана только часть данных из таблицы customer.

Генерируем визуальные документы

dbt позволяет генерировать документы с визуальным отображением общей структуры проекта и описанием всех таблиц и представлений. Чтобы создать визуальные документы:

Сгенерируйте документ:
```
$ dbt docs generate
```

Запустите сервер:

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

Чтобы перейти к просмотру документа из браузера, перейдите по адресу: http://localhost:8080

Заключение

В настоящее время TiDB поддерживает dbt в TiDB 4.0 и более поздних версиях. При работе с dbt в более ранних версиях TiDB могут наблюдаться некоторые проблемы. Для получения подробной информации посетите проект tidb-dbt на GitHub. Чтобы получить максимум от dbt, мы рекомендуем использовать TiDB 5.3 или более позднюю версию. Эти версии поддерживают все функции dbt.

Если у вас возникнут какие-либо проблемы, присоединяйтесь к сообществу автора в Slack или опубликуйте проблему в репозитории.

НЛО прилетело и оставило здесь промокод для читателей нашего блога:

— 15% на все тарифы VDS (кроме тарифа Прогрев) — HABRFIRSTVDS.

Преобразование данных на TiDB стало проще