Интеграция dbt и ClickHouse

ClickHouse Supported

dbt (инструмент сборки данных) позволяет аналитическим инженерам преобразовывать данные в своих хранилищах, просто написав операторы select. dbt обрабатывает материализацию этих операторов select в объекты в базе данных в виде таблиц и представлений - выполняя T из Извлечения, Загрузки и Преобразования (ELT). Пользователи могут создавать модель, определенную оператором SELECT.

В dbt эти модели могут ссылаться друг на друга и быть layered, чтобы позволить построение более высокоуровневых концепций. Стандартный SQL, необходимый для подключения моделей, автоматически генерируется. Более того, dbt идентифицирует зависимости между моделями и гарантирует, что они создаются в соответствующем порядке с помощью направленного ациклического графа (DAG).

Dbt совместим с ClickHouse через поддерживаемый плагин для ClickHouse. Мы описываем процесс подключения ClickHouse с простым примером на основе общедоступного набора данных IMDB. Мы также подчеркиваем некоторые ограничения текущего соединителя.

• Концепции
• Установка dbt и плагина ClickHouse
• Подключение к ClickHouse
• Создание простой материализации представления
• Создание материализации таблицы
• Создание инкрементного материализованного представления
• Создание снимка
• Использование Seeds
• Ограничения
• Fivetran
• Связанное содержимое

Концепции

dbt вводит концепцию модели. Это определяется как оператор SQL, потенциально объединяющий многие таблицы. Модель может быть "материализована" различными способами. Материализация представляет собой стратегию сборки для запроса select модели. Код, стоящий за материализацией, - это стандартный SQL, который оборачивает ваш запрос SELECT в оператор, чтобы создать новое или обновить существующее отношение.

dbt предоставляет 4 типа материализации:

• view (по умолчанию): Модель строится как представление в базе данных.
• table: Модель строится как таблица в базе данных.
• ephemeral: Модель не строится напрямую в базе данных, а вместо этого интегрируется в зависимые модели в виде общих табличных выражений.
• incremental: Модель изначально материализуется как таблица, а в последующих запусках dbt вставляет новые строки и обновляет измененные строки в таблице.

Дополнительный синтаксис и конструкции определяют, как эти модели должны обновляться, если их исходные данные изменяются. dbt обычно рекомендует начинать с материализации в виде представления, пока производительность не станет проблемой. Материализация в виде таблицы обеспечивает улучшение производительности времени выполнения запросов, захватывая результаты запроса модели в виде таблицы с увеличением объема хранения. Инкрементальный подход дополнительно развивает это, позволяя последующим обновлениям исходных данных быть зафиксированными в целевой таблице.

Текущий плагин для ClickHouse поддерживает view, table, ephemeral и incremental материализации. Плагин также поддерживает снимки и посевы, которые мы рассмотрим в этом руководстве.

Для следующих руководств мы предполагаем, что у вас есть доступный экземпляр ClickHouse.

Установка dbt и плагина ClickHouse

dbt

Мы предполагаем использование dbt CLI для следующих примеров. Пользователям также может быть интересно рассмотреть возможность использования dbt Cloud, который предлагает веб-интерфейс для интегрированной среды разработки (IDE), позволяющий пользователям редактировать и запускать проекты.

dbt предлагает множество вариантов установки CLI. Следуйте инструкциям, описанным здесь. На данном этапе установите только dbt-core. Мы рекомендуем использовать pip.

Важно: Следующее протестировано на python 3.9.

Плагин ClickHouse

Установите плагин dbt ClickHouse:

Подготовка ClickHouse

dbt отлично подходит для моделирования высоко реляционных данных. В качестве примера мы предоставляем небольшой набор данных IMDB со следующим реляционным схематическим описанием. Этот набор данных происходит из репозитория реляционных наборов данных. Это тривиально по сравнению с общими схемами, используемыми с dbt, но представляет собой управляемую выборку:

Мы используем подмножество этих таблиц, как показано.

Создайте следующие таблицы:

примечание

В колонке created_at таблицы roles устанавливается значение по умолчанию now(). Мы используем это позже, чтобы определить инкрементные обновления для наших моделей - см. Инкрементные модели.

Мы используем функцию s3 для чтения исходных данных с общедоступных конечных точек для вставки данных. Выполните следующие команды для заполнения таблиц:

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

Ответ должен выглядеть следующим образом:

В следующих руководствах мы преобразуем этот запрос в модель - материализуем его в ClickHouse как представление и таблицу dbt.

Подключение к ClickHouse

1. Создайте проект dbt. В данном случае мы назовем его по нашему источнику imdb. Когда будет предложено, выберите clickhouse в качестве источника базы данных.

2. cd в папку вашего проекта:

3. На этом этапе вам понадобится текстовый редактор на ваш выбор. В приведенных ниже примерах мы используем популярный VS Code. Открыв директорию IMDB, вы должны увидеть набор файлов yml и sql:

   

4. Обновите ваш файл dbt_project.yml, чтобы указать нашу первую модель - actor_summary и установить профиль на clickhouse_imdb.

   

5. Затем мы должны предоставить dbt информацию для подключения к нашему экземпляру ClickHouse. Добавьте следующее в ваш файл ~/.dbt/profiles.yml.

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

6. Из директории IMDB выполните команду dbt debug, чтобы подтвердить, что dbt может подключиться к ClickHouse.

   Подтвердите, что ответ включает Тест подключения: [ОК, соединение установлено], что указывает на успешное подключение.

Создание простой материализации представления

При использовании материализации представления модель пересоздается как представление при каждом выполнении через оператор CREATE VIEW AS в ClickHouse. Это не требует дополнительных затрат на хранение данных, но будет медленнее по сравнению с материализациями таблиц.

1. Из папки imdb удалите директорию models/example:

2. Создайте новый файл в actors внутри папки models. Здесь мы создаем файлы, которые каждый представляют модель актера:

3. Создайте файлы schema.yml и actor_summary.sql в папке models/actors.

   Файл schema.yml определяет наши таблицы. Эти таблицы будут впоследствии доступны для использования в макросах. Отредактируйте models/actors/schema.yml, чтобы он содержал этот контент:

   Файл actors_summary.sql определяет нашу фактическую модель. Обратите внимание, что в конфигурационной функции мы также запрашиваем, чтобы модель была материализирована как представление в ClickHouse. Наши таблицы ссылаются на файл schema.yml через функцию source, например, source('imdb', 'movies') ссылается на таблицу movies в базе данных imdb. Отредактируйте models/actors/actors_summary.sql, чтобы он содержал этот контент:

   Обратите внимание, как мы включаем колонку updated_at в нашем окончательном actor_summary. Мы используем это позже для инкрементных материализаций.

4. Из директории imdb выполните команду dbt run.

5. dbt будет представлять модель как представление в ClickHouse, как было запрошено. Теперь мы можем запрашивать это представление напрямую. Это представление будет создано в базе данных imdb_dbt - это определяется параметром schema в файле ~/.dbt/profiles.yml под профилем clickhouse_imdb.

   Запрашивая это представление, мы можем воспроизвести результаты нашего предыдущего запроса с более простой синтаксисом:

Создание материализации таблицы

В предыдущем примере наша модель была материализирована как представление. Хотя это может обеспечить достаточную производительность для некоторых запросов, более сложные SELECT или часто выполняемые запросы могут быть лучше материализированы как таблица. Эта материализация полезна для моделей, которые будут запрашиваться BI-инструментами, чтобы обеспечить пользователям более быстрый опыт. Это фактически приводит к тому, что результаты запросов сохраняются как новая таблица с сопутствующими затратами на хранение - фактически выполняется INSERT TO SELECT. Обратите внимание, что эта таблица будет перестраиваться каждый раз, т.е. она не инкрементная. Большие наборы результатов могут, следовательно, привести к долгим временам выполнения - см. Ограничения dbt.

1. Измените файл actors_summary.sql, чтобы параметр materialized был установлен в table. Обратите внимание, как определяется ORDER BY и как мы используем движок таблиц MergeTree:

2. Из директории imdb выполните команду dbt run. Это выполнение может занять немного больше времени - около 10 секунд на большинстве машин.

3. Подтвердите создание таблицы imdb_dbt.actor_summary:

   Вы должны увидеть таблицу с соответствующими типами данных:

4. Подтвердите, что результаты из этой таблицы совпадают с предыдущими ответами. Обратите внимание на заметное улучшение времени ответа теперь, когда модель является таблицей:

   Не стесняйтесь выполнять другие запросы к этой модели. Например, какие актеры имеют фильмы с самым высоким рейтингом более чем с 5 появлениями?

Создание инкрементного материализованного представления

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

Чтобы преодолеть эти ограничения для больших наборов, плагин поддерживает режим 'inserts_only', где все обновления вставляются непосредственно в целевую таблицу без создания временной таблицы (подробнее об этом ниже).

Чтобы проиллюстрировать этот пример, мы добавим актера "Clicky McClickHouse", который появится в невероятных 910 фильмах - тем самым он станет более популярным, чем даже Mel Blanc.

1. Сначала мы модифицируем нашу модель так, чтобы она была инкрементной. Это требует:

   1. unique_key - Чтобы гарантировать, что плагин может уникально идентифицировать строки, мы должны предоставить unique_key - в данном случае поле id из нашего запроса подойдет. Это обеспечит отсутствие дубликатов строк в нашей материализованной таблице. Для получения дополнительной информации о ограничениях уникальности, смотрите здесь.
   2. Инкрементный фильтр - Мы также должны указать dbt, как оно должно определять, какие строки изменились при инкрементном выполнении. Это достигается за счет предоставления выражения дельты. Обычно это включает временную метку для событийных данных; следовательно, наше поле временной метки updated_at. Этот столбец, который по умолчанию принимает значение now() при вставке строк, позволяет идентифицировать новые роли. Кроме того, нам нужно указать альтернативный случай, где добавляются новые актеры. Используя переменную {{this}}, которая обозначает существующую материализованную таблицу, мы получаем выражение where id > (select max(id) from {{ this }}) or updated_at > (select max(updated_at) from {{this}}). Мы помещаем это внутри условия {% if is_incremental() %}, чтобы убедиться, что оно используется только при инкрементных запусках, а не когда таблица впервые создается. Для получения дополнительной информации о фильтрации строк для инкрементных моделей, смотрите это обсуждение в документации dbt.

   Обновите файл actor_summary.sql следующим образом:

   Обратите внимание, что наша модель будет реагировать только на обновления и добавления в таблицы roles и actors. Чтобы реагировать на все таблицы, пользователям будет предложено разделить эту модель на несколько подмоделей - каждая из которых с собственными инкрементными критериями. Эти модели, в свою очередь, могут быть соотнесены и связаны. Для получения дополнительной информации о перекрестных ссылках моделей смотрите здесь.

2. Выполните dbt run и подтвердите результаты итоговой таблицы:

3. Теперь мы добавим данные в нашу модель, чтобы проиллюстрировать инкрементное обновление. Добавьте нашего актера "Clicky McClickHouse" в таблицу actors:

4. Пусть "Clicky" снимется в 910 случайных фильмах:

5. Убедитесь, что он действительно стал актером с наибольшим количеством появлений, запросив исходную таблицу и обойдя любые модели dbt:

6. Выполните dbt run и подтвердите, что наша модель обновилась и соответствует вышеприведенным результатам:

Внутренности

Мы можем определить операторы, выполненные для выполнения указанного выше инкрементного обновления, запросив журнал запросов ClickHouse.

Отрегулируйте вышеуказанный запрос в соответствии с периодом выполнения. Мы оставляем проверку результатов пользователю, но подчеркиваем общую стратегию, использованную плагином для выполнения инкрементных обновлений:

1. Плагин создает временную таблицу actor_sumary__dbt_tmp. Измененные строки передаются в эту таблицу.
2. Создается новая таблица actor_summary_new. Строки из старой таблицы передаются старой и новой, с проверкой, чтобы гарантировать, что идентификаторы строк не существуют во временной таблице. Это эффективно обрабатывает обновления и дубликаты.
3. Результаты из временной таблицы передаются в новую таблицу actor_summary.
4. Наконец, новая таблица атомарно заменяется старой версией с помощью оператора EXCHANGE TABLES. В свою очередь, старая и временная таблицы удаляются.

Это визуализировано ниже:

Эта стратегия может столкнуться с проблемами на очень больших моделях. Для получения дополнительной информации смотрите Ограничения.

Стратегия добавления (режим inserts-only)

Чтобы преодолеть ограничения больших наборов данных в инкрементных моделях, плагин использует параметр конфигурации dbt incremental_strategy. Это может быть установлено на значение append. При установленном значении обновленные строки вставляются непосредственно в целевую таблицу (также известную как imdb_dbt.actor_summary), и временная таблица не создается. Примечание: Режим добавления требует, чтобы ваши данные были неизменяемыми или чтобы дубликаты были приемлемыми. Если вам нужна инкрементная табличная модель, поддерживающая измененные строки, не используйте этот режим!

Чтобы проиллюстрировать этот режим, мы добавим еще одного нового актера и повторно выполним dbt run с параметром incremental_strategy='append'.

1. Настройте режим добавления только в actor_summary.sql:

2. Давайте добавим еще одного известного актера - Дэни ДеВито

3. Давайте снимем Дэни в 920 случайных фильмах.

4. Выполните dbt run и подтвердите, что Дэни был добавлен в таблицу actor-summary

Обратите внимание, насколько быстрее было это инкрементное обновление по сравнению с вставкой "Clicky".

Проверка снова таблицы query_log показывает различия между двумя инкрементными запусками:

В этом запуске только новые строки добавляются прямо в таблицу imdb_dbt.actor_summary, и создание таблицы не требуется.

Режим Delete+Insert (Экспериментальный)

Исторически в ClickHouse было только ограниченное количество обновлений и удалений в виде асинхронных мутаций. Эти операции могут потребовать значительных затрат по вводу-выведению и должны в целом избегаться.

ClickHouse 22.8 представил Легковесные удаления. Эти функции в настоящее время являются экспериментальными, но предлагают более производительный способ удаления данных.

Этот режим можно настроить для модели через параметр incremental_strategy, т.е.

Эта стратегия работает непосредственно с таблицей целевой модели, поэтому, если возникнет проблема во время операции, данные в инкрементной модели, вероятно, окажутся в недействительном состоянии - атомарного обновления нет.

Вкратце, этот подход:

1. Плагин создает временную таблицу actor_sumary__dbt_tmp. Измененные строки передаются в эту таблицу.
2. Выдается команда DELETE для текущей таблицы actor_summary. Строки удаляются по id из actor_sumary__dbt_tmp.
3. Строки из actor_sumary__dbt_tmp вставляются в actor_summary с помощью команды INSERT INTO actor_summary SELECT * FROM actor_sumary__dbt_tmp.

Этот процесс показан ниже:

Режим insert_overwrite (Экспериментальный)

Выполняет следующие шаги:

1. Создайте промежуточную (временную) таблицу с той же структурой, что и инкрементная модель: CREATE TABLE {staging} AS {target}.
2. Вставьте только новые записи (сгенерированные с помощью SELECT) в промежуточную таблицу.
3. Замените только новые партиции (представленные в промежуточной таблице) в целевой таблице.

Этот подход имеет следующие преимущества:

• Он быстрее, чем стратегия по умолчанию, потому что не копирует всю таблицу.
• Он безопаснее, чем другие стратегии, поскольку не изменяет оригинальную таблицу, пока операция INSERT не завершится успешно: в случае промежуточной ошибки оригинальная таблица не будет изменена.
• Он реализует лучшие практики инженерии данных по "неизменяемости партиций", что упрощает инкрементную и параллельную обработку данных, откаты и т.д.

Создание снимка

Снимки dbt позволяют зафиксировать изменения в изменяемой модели с течением времени. Это, в свою очередь, позволяет выполнять запросы на модели с точки зрения времени, где аналитики могут "оглядываться назад" на предыдущее состояние модели. Это достигается с помощью типа 2 Постепенно Изменяющиеся Размеры, где столбцы дата начала и дата окончания фиксируют, когда строка была актуальна. Эта функция поддерживается плагином ClickHouse и продемонстрирована ниже.

Этот пример предполагает, что вы завершили Создание инкрементной табличной модели. Убедитесь, что ваш actor_summary.sql не устанавливает inserts_only=True. Ваши models/actor_summary.sql должны выглядеть следующим образом:

1. Создайте файл actor_summary в каталоге снимков.

2. Обновите содержимое файла actor_summary.sql следующим образом:

Несколько наблюдений по этому содержимому:

• SQL-запрос определяет результаты, которые вы хотите сохранить во времени. Функция ref используется для ссылки на ранее созданную модель actor_summary.
• Нам требуется столбец временной метки для указания изменений записей. Наша колонка updated_at (см. Создание инкрементной табличной модели) может быть использована здесь. Параметр strategy указывает на использование временной метки для обозначения обновлений, а параметр updated_at определяет столбец для использования. Если его нет в вашей модели, вы можете альтернативно использовать check strategy. Это значительно менее эффективно и требует от пользователя указать список столбцов для сравнения. dbt сравнивает текущие и исторические значения этих столбцов, фиксируя любые изменения (или ничего не делает, если они идентичны).

3. Запустите команду dbt snapshot.

Обратите внимание, что таблица actor_summary_snapshot была создана в базе данных снимков (определяемая параметром target_schema).

4. При выборке этих данных вы увидите, что dbt включило столбцы dbt_valid_from и dbt_valid_to. Последний имеет значения, установленные в null. Последующие запуски обновят это.

5. Сделайте так, чтобы наш любимый актер Clicky McClickHouse снялся еще в 10 фильмах.

6. Повторно выполните команду dbt run из каталога imdb. Это обновит инкрементную модель. После завершения запустите dbt snapshot, чтобы зафиксировать изменения.

7. Если мы теперь запрашиваем наш снимок, обратите внимание, что у Clicky McClickHouse теперь есть 2 строки. Наша предыдущая запись сейчас имеет значение dbt_valid_to. Наша новая запись фиксируется с тем же значением в столбце dbt_valid_from и значением dbt_valid_to, равным null. Если бы у нас были новые строки, они также были бы добавлены к снимку.

Для получения дополнительной информации о снимках dbt смотрите здесь.

Использование Seeds

dbt предоставляет возможность загружать данные из CSV файлов. Эта функция не предназначена для загрузки больших экспортов базы данных и больше подходит для небольших файлов, которые обычно используются для кодовых таблиц и словарей, например, для сопоставления кодов стран с названиями стран. В качестве простого примера мы сгенерируем и затем загрузим список кодов жанров, используя функциональность seed.

1. Мы генерируем список кодов жанров из нашего существующего набора данных. Из директории dbt используйте clickhouse-client, чтобы создать файл seeds/genre_codes.csv:

2. Выполните команду dbt seed. Это создаст новую таблицу genre_codes в нашей базе данных imdb_dbt (как определено в нашей конфигурации схемы) с записями из нашего CSV файла.

3. Подтвердите, что данные были загружены:

Ограничения

Текущий плагин ClickHouse для dbt имеет несколько ограничений, о которых пользователи должны быть осведомлены:

1. Плагин в настоящее время материализует модели как таблицы, используя INSERT TO SELECT. Это фактически приводит к дублированию данных. Очень большие наборы данных (PB) могут приводить к чрезвычайно долгому времени выполнения, что делает некоторые модели непрактичными. Стремитесь минимизировать количество строк, возвращаемых любым запросом, используя GROUP BY, где это возможно. Предпочитайте модели, которые обобщают данные, над теми, которые просто выполняют преобразование, сохраняя количество строк в источнике.
2. Для использования распределенных таблиц для представления модели пользователи должны создать основные реплицируемые таблицы на каждом узле вручную. Распределенная таблица, в свою очередь, может быть создана поверх этих таблиц. Плагин не управляет созданием кластера.
3. Когда dbt создает отношение (таблицу/представление) в базе данных, оно обычно создает его как: {{ database }}.{{ schema }}.{{ table/view id }}. ClickHouse не имеет понятия схем. Поэтому плагин использует {{schema}}.{{ table/view id }}, где schema — это база данных ClickHouse.

Дополнительная информация

Предыдущие руководства только касаются функциональности dbt. Пользователям рекомендуется ознакомиться с отличной документацией dbt.

Дополнительная конфигурация для плагина описана здесь.

Fivetran

Коннектор dbt-clickhouse также доступен для использования в преобразованиях Fivetran, что позволяет легко интегрировать и преобразовывать данные непосредственно в платформе Fivetran с помощью dbt.

Связанное содержимое

• Блог и вебинар: ClickHouse и dbt - Подарок от сообщества