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

dbt (data build tool) позволяет аналитикам данных преобразовывать данные в своих хранилищах, просто записывая запросы SELECT. dbt материализует эти запросы SELECT в объекты базы данных в виде таблиц и представлений, то есть выполняет T в Extract Load and Transform (ELT). Вы можете создать модель, определённую оператором SELECT.

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

dbt совместим с ClickHouse через адаптер с поддержкой ClickHouse.

Список поддерживаемых возможностей:

• Материализация таблиц
• Материализация представлений
• Инкрементальная материализация
• Инкрементальная материализация Microbatch
• Materialized View materializations (использует форму TO для MATERIALIZED VIEW, экспериментально)
• Seeds
• Источники
• Генерация документации
• Тесты
• Снимки
• Большинство макросов dbt-utils (теперь входят в dbt-core)
• Эфемерная материализация
• Материализация distributed таблиц (экспериментально)
• Инкрементальная материализация distributed таблиц (экспериментально)
• Материализация словарей (экспериментально)
• Контракты
• Специфичные для ClickHouse конфигурации столбцов (кодек, TTL…)
• Специфичные для ClickHouse настройки таблиц (индексы, проекции…)

Поддерживаются все возможности вплоть до dbt-core 1.10, включая флаг --sample; также устранены все предупреждения об устаревании для будущих версий. Интеграции с каталогами (например, Iceberg), появившиеся в dbt 1.10, пока не поддерживаются адаптером нативно, но доступны обходные решения. Подробности см. в разделе Catalog Support.

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

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

dbt предоставляет 5 типов материализации. Все они поддерживаются dbt-clickhouse:

• view (по умолчанию): Модель создаётся как представление в базе данных. В ClickHouse это создаётся как view.
• table: Модель создаётся как таблица в базе данных. В ClickHouse это создаётся как table.
• ephemeral: Модель не создаётся напрямую в базе данных, а подставляется в зависимые модели как CTE (Common Table Expressions, общие табличные выражения).
• incremental: Изначально модель материализуется как таблица, а при последующих запусках dbt выполняет вставку новых строк и обновляет изменённые строки в таблице.
• materialized view: Модель создаётся как materialized view в базе данных. В ClickHouse это создаётся как materialized view.

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

Ниже перечислены экспериментальные возможности в dbt-clickhouse:

dbt предлагает несколько способов установки интерфейса командной строки (CLI); они подробно описаны здесь. Мы рекомендуем устанавливать и dbt, и dbt-clickhouse с помощью pip.

pip install dbt-core dbt-clickhouse

Настройте профиль clickhouse-service в файле ~/.dbt/profiles.yml и задайте свойства schema, host, port, user и password. Полный список параметров конфигурации подключения доступен на странице Возможности и конфигурации:

clickhouse-service:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: [ default ] # База данных ClickHouse для dbt-моделей

      # Необязательно
      host: [ localhost ]
      port: [ 8123 ]  # По умолчанию 8123, 8443, 9000, 9440 в зависимости от настроек secure и driver 
      user: [ default ] # Пользователь для всех операций с базой данных
      password: [ <empty string> ] # Пароль пользователя
      secure: True  # Использовать TLS (собственный протокол) или HTTPS (HTTP-протокол)

Теперь вы можете использовать этот профиль в одном из существующих проектов или создать новый с помощью:

dbt init project_name

В каталоге project_name обновите файл dbt_project.yml, указав имя профиля для подключения к серверу ClickHouse.

profile: 'clickhouse-service'

Выполните команду dbt debug в CLI, чтобы проверить, может ли dbt подключиться к ClickHouse. Убедитесь, что в ответе есть строка Connection test: [OK connection ok], которая указывает на успешное подключение.

Перейдите на страницу руководств, чтобы узнать больше об использовании dbt с ClickHouse.

Существует множество способов тестирования и развертывания вашего dbt-проекта. dbt предлагает рекомендации по лучшим практикам организации рабочих процессов и CI-задачам. Мы рассмотрим несколько стратегий, но имейте в виду, что их, возможно, потребуется существенно адаптировать под ваш конкретный сценарий использования.

Один из простых способов быстро запустить CI-конвейер — поднять кластер ClickHouse в рамках задачи, а затем запустить на нём ваши модели. Перед запуском моделей в этот кластер можно вставить тестовые данные. Для заполнения промежуточного окружения частью данных из продакшн-окружения можно просто использовать seed.

После вставки данных вы можете запустить тесты данных и модульные тесты.

Шаг CD может быть таким же простым, как запуск dbt build на продакшн-кластере ClickHouse.

Одна из распространённых стратегий — использовать задачи Slim CI, при которых повторно развертываются только изменённые модели (и их зависимости выше и ниже по графу). Этот подход использует артефакты из запусков в продакшн (то есть манифест dbt), чтобы сократить время выполнения проекта и избежать расхождения схем между средами.

Чтобы среды разработки оставались синхронизированными и модели не запускались на устаревших развертываниях, можно использовать clone или даже defer. В ClickHouse dbt clone копирует таблицы семейства MergeTree с помощью zero-copy оператора CLONE — подробности см. ниже в разделе Клонирование моделей с помощью dbt clone.

Мы рекомендуем использовать выделенный кластер или сервис ClickHouse для тестовой среды (то есть staging-среды), чтобы не влиять на работу вашей среды продакшн. Чтобы тестовая среда была репрезентативной, важно использовать подмножество данных из продакшн, а также запускать dbt так, чтобы не допускать расхождения схем между средами.

• Если вам не нужны свежие данные для тестирования, можно восстановить резервную копию данных из продакшн в staging-среду.
• Если вам нужны свежие данные для тестирования, можно использовать сочетание table function remoteSecure() и refreshable materialized views, чтобы выполнять вставку с нужной частотой. Другой вариант — использовать Объектное хранилище как промежуточное хранилище, периодически записывать в него данные из вашего продакшн-сервиса, а затем импортировать их в staging-среду с помощью table functions для Объектного хранилища или ClickPipes (для непрерывной ингестии).

Использование выделенной среды для CI-тестирования также позволяет проводить ручное тестирование, не затрагивая среду продакшн. Например, для тестирования можно подключить BI-инструмент к этой среде.

Для развертывания (то есть шага CD) мы рекомендуем использовать артефакты из ваших развертываний в продакшн, чтобы обновлять только те модели, которые изменились. Для этого нужно настроить Объектное хранилище (например, S3) как промежуточное хранилище для артефактов dbt. После этого можно запускать команду вида dbt build --select state:modified+ --state path/to/last/deploy/state.json, чтобы выборочно пересобирать минимально необходимое количество моделей на основе изменений с момента последнего запуска в продакшн.

Начиная с версии dbt-clickhouse 1.10.1, команда dbt clone использует оператор ClickHouse CREATE OR REPLACE TABLE ... CLONE AS ... с zero-copy для клонирования моделей, материализованных в виде таблиц на движках семейства MergeTree. При этом создаётся копия таблицы без дублирования базовых частей данных, что делает этот способ быстрым и недорогим для синхронизации окружений — например, при настройке окружения разработки или Slim CI на основе состояния продакшна.

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

• Таблицы на движках, не относящихся к MergeTree
• Распределённые материализации

Для моделей materialized view клонируется только целевая таблица; сама materialized view создаётся поверх клонированной таблицы.

Если у вас возникают проблемы с подключением к ClickHouse из dbt, убедитесь, что выполнены следующие условия:

• Движок должен быть одним из поддерживаемых движков.
• У вас должны быть достаточные разрешения на доступ к базе данных.
• Если вы не используете движок таблицы по умолчанию для базы данных, необходимо указать движок таблицы в конфигурации модели.

Некоторые операции могут занимать больше времени, чем ожидается, из-за отдельных запросов ClickHouse. Чтобы лучше понять, какие запросы выполняются дольше, повысьте уровень логирования до debug — тогда будет выводиться время выполнения каждого запроса. Например, для этого можно добавить --log-level debug к командам dbt.

Начиная с dbt-clickhouse 1.10.1 для получения подробной информации на стороне сервера каждому оператору, выполняемому адаптером, присваивается собственный ID запроса (UUID4), который передаётся в ClickHouse. ID основного оператора модели возвращается в adapter_response результата dbt, поэтому он доступен в артефактах dbt, таких как run_results.json. Его можно найти в таблице system.query_log, чтобы изучить время выполнения и использование ресурсов этого оператора:

SELECT query_id, query, query_duration_ms, read_rows, memory_usage
FROM system.query_log
WHERE query_id = '<query_id from run_results.json>'
  AND type = 'QueryFinish'

Учтите, что материализация обычно выполняет несколько операторов для каждой модели (DDL, вставки и т. д.), и у каждого из них свой идентификатор запроса; идентификатор в run_results.json относится только к основному оператору модели. Чтобы найти все операторы, выполненные в рамках запуска, отфильтруйте system.query_log по комментарию к запросу dbt, встроенному в текст каждого запроса.

Идентификатор запроса также позволяет инструментам обсервабилити, использующим артефакты dbt (например, Elementary), автоматически связывать запуски моделей dbt с записями в system.query_log.

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

• Плагин использует синтаксис, требующий ClickHouse версии 25.3 или новее. Более старые версии ClickHouse мы не тестируем. Также в настоящее время мы не тестируем таблицы Replicated.
• Разные запуски dbt-adapter могут конфликтовать, если выполняются одновременно, поскольку внутри они могут использовать одинаковые имена таблиц для одних и тех же операций. Подробнее см. issue #420.
• Сейчас адаптер материализует модели в виде таблиц с использованием INSERT INTO SELECT. На практике это означает дублирование данных при повторном запуске. Очень большие датасеты (PB) могут приводить к крайне долгому времени выполнения, из-за чего некоторые модели становятся непрактичными. Чтобы повысить производительность, используйте materialized views ClickHouse, реализуя представление как materialized: materialization_view. Кроме того, старайтесь по возможности уменьшать количество строк, возвращаемых любым запросом, используя GROUP BY. Предпочтительнее модели, которые агрегируют данные, а не просто преобразуют их, сохраняя количество строк источника.
• Чтобы использовать distributed таблицы для представления модели, необходимо вручную создать базовые реплицируемые таблицы на каждом узле. Затем поверх них можно создать distributed таблицу. Адаптер не управляет созданием кластера.
• Когда dbt создает отношение (table/view) в базе данных, оно обычно создается в виде: {{ database }}.{{ schema }}.{{ table/view id }}. В ClickHouse нет понятия схем. Поэтому адаптер использует {{schema}}.{{ table/view id }}, где schema — это база данных ClickHouse.
• Эфемерные модели/CTE не работают, если размещены перед INSERT INTO в операторе вставки ClickHouse, см. https://github.com/ClickHouse/ClickHouse/issues/30323. Это не должно затрагивать большинство моделей, но следует учитывать, где именно эфемерная модель размещается в определениях моделей и других SQL-командах.

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