Integración entre dbt y ClickHouse

Puedes transformar y modelar tus datos en ClickHouse con dbt

​

El adaptador dbt-clickhouse

dbt (data build tool) permite a los ingenieros de analítica transformar datos en sus almacenes de datos simplemente escribiendo sentencias select. dbt se encarga de materializar estas sentencias select como objetos en la base de datos, en forma de tablas y vistas, realizando la T de Extract Load and Transform (ELT). Puede crear un modelo definido por una sentencia SELECT.

Dentro de dbt, estos modelos pueden referenciarse entre sí y organizarse en capas para construir conceptos de nivel superior. El código SQL repetitivo necesario para conectar modelos se genera automáticamente. Además, dbt identifica las dependencias entre modelos y garantiza que se creen en el orden adecuado mediante un grafo acíclico dirigido (DAG).

dbt es compatible con ClickHouse mediante un adaptador con soporte para ClickHouse.

dbt OSS, dbt v2 y la plataforma dbt. ClickHouse ahora funciona con dbt OSS (Beta), dbt v2 (Beta) y la plataforma dbt (Private Beta; consulte cómo solicitar acceso). La integración todavía no está lista para producción. Consulte la página de dbt OSS, dbt v2 y la plataforma dbt para conocer el estado actual y las limitaciones conocidas. El resto de esta documentación también se aplica a todos ellos, sujeto a las limitaciones indicadas en la tabla de paridad de esa página.

​

Páginas relacionadas

Página	Descripción
Funcionalidades y configuraciones	Descripción de las funcionalidades y configuraciones generales disponibles
Materializaciones	Materializaciones disponibles y sus configuraciones
Vistas materializadas	Documentación específica de la materialización materialized_view
Guías	Guías para usar dbt con ClickHouse

​

Funcionalidades compatibles

Lista de funcionalidades compatibles:

• Materialización de tabla
• Materialización de vista
• Materialización incremental
• Materialización incremental de Microbatch
• Materializaciones de vista materializada (usa la forma TO de MATERIALIZED VIEW, experimental)
• Seeds
• Sources
• Generación de documentación
• Pruebas
• Snapshots
• La mayoría de las macros de dbt-utils (ahora incluidas en dbt-core)
• Materialización efímera
• Materialización de tabla distribuida (experimental)
• Materialización incremental distribuida (experimental)
• Materialización de diccionario (experimental)
• Contratos
• Configuraciones de columna específicas de ClickHouse (Codec, TTL…)
• Configuración de tablas específica de ClickHouse (índices, proyecciones…)

Se admiten las funcionalidades hasta dbt-core 1.12, incluida la opción --sample, y se han corregido todas las advertencias de desuso de cara a futuras versiones. Las integraciones de catálogo (por ejemplo, Iceberg) introducidas en dbt 1.10 aún no son compatibles de forma nativa en el adaptador, pero hay soluciones alternativas disponibles. Consulta la sección Compatibilidad con catálogos para obtener más información.

​

Conceptos de dbt y materializaciones compatibles

dbt introduce el concepto de modelo. Este se define como una sentencia SQL que potencialmente combina muchas tablas. Un modelo puede “materializarse” de varias maneras. Una materialización representa una estrategia de construcción para la consulta SELECT del modelo. El código detrás de una materialización es SQL repetitivo que envuelve tu consulta SELECT en una sentencia para crear una nueva relación o actualizar una existente.

dbt proporciona 5 tipos de materialización. Todos ellos son compatibles con dbt-clickhouse:

• view (predeterminado): El modelo se construye como una vista en la base de datos. En ClickHouse, esto se construye como una vista.
• table: El modelo se construye como una tabla en la base de datos. En ClickHouse, esto se construye como una tabla.
• ephemeral: El modelo no se construye directamente en la base de datos, sino que se incorpora en los modelos dependientes como CTE (expresiones de tabla comunes).
• incremental: El modelo se materializa inicialmente como una tabla y, en ejecuciones posteriores, dbt inserta filas nuevas y actualiza las filas modificadas en la tabla.
• materialized view: El modelo se construye como una vista materializada en la base de datos. En ClickHouse, esto se construye como una vista materializada.

La sintaxis y las cláusulas adicionales definen cómo deben actualizarse estos modelos si cambian sus datos subyacentes. Por lo general, dbt recomienda comenzar con la materialización view hasta que el rendimiento se convierta en una preocupación. La materialización table ofrece una mejora del rendimiento en tiempo de consulta al capturar los resultados de la consulta del modelo como una tabla, a costa de un mayor almacenamiento. El enfoque incremental desarrolla aún más esta idea para permitir que las actualizaciones posteriores de los datos subyacentes se capturen en la tabla de destino.

Las siguientes son características experimentales en dbt-clickhouse:

Tipo	¿Compatible?	Detalles
Materialización de vista materializada	Sí. La creación con explicit target está en Beta	Crea una vista materializada.
Materialización de tabla distribuida	Sí, Experimental	Crea una tabla distribuida.
Materialización incremental distribuida	Sí, Experimental	Modelo incremental basado en la misma idea que la tabla distribuida. Ten en cuenta que no todas las estrategias son compatibles; visita su sección de documentación para obtener más información.
Materialización de Diccionario	Sí, Experimental	Crea un Diccionario.

​

Configuración de dbt y del adaptador de ClickHouse

Si prefiere partir de un proyecto ya funcional, nuestro fork del proyecto de ejemplo de dbt, Jaffle Shop para ClickHouse, incluye perfiles para un ClickHouse local y para ClickHouse Cloud, datos de ejemplo y un README que cubre dbt Core 1.x, dbt OSS, dbt v2 y la plataforma dbt. La página de guías se basa en él.

​

Instalar dbt-core y dbt-clickhouse

dbt ofrece varias opciones para instalar la interfaz de línea de comandos (CLI), que se detallan aquí. Recomendamos utilizar pip para instalar tanto dbt como dbt-clickhouse.

pip install dbt-core dbt-clickhouse

​

Proporcione a dbt los datos de conexión de nuestra instancia de ClickHouse.

Configure el perfil clickhouse-service en el archivo ~/.dbt/profiles.yml y proporcione las propiedades de esquema, host, puerto, usuario y contraseña. La lista completa de opciones de configuración de la conexión está disponible en la página funcionalidad y configuración:

clickhouse-service:
  target: dev
  outputs:
    dev:
      type: clickhouse
      schema: [ default ] # ClickHouse database for dbt models

      # Optional
      host: [ localhost ]
      port: [ 8123 ]  # Defaults to 8123, 8443, 9000, 9440 depending on the secure and driver settings
      user: [ default ] # User for all database operations
      password: [ <empty string> ] # Password for the user
      secure: True  # Use TLS (native protocol) or HTTPS (http protocol)

​

Crear un proyecto de dbt

Ahora puedes usar este perfil en uno de tus proyectos existentes o crear uno nuevo mediante:

dbt init project_name

Dentro del directorio project_name, actualiza el archivo dbt_project.yml para especificar un nombre de perfil para conectarte al servidor de ClickHouse.

profile: 'clickhouse-service'

​

Probar la conexión

Ejecuta dbt debug con la herramienta de línea de comandos para confirmar si dbt puede conectarse a ClickHouse. Verifica que la respuesta incluya Connection test: [OK connection ok], lo que indica que la conexión se ha realizado correctamente.

Ve a la página de guías para obtener más información sobre cómo usar dbt con ClickHouse.

​

Probar y desplegar tus modelos (CI/CD)

Hay muchas formas de probar y desplegar tu proyecto de dbt. dbt ofrece algunas recomendaciones sobre flujos de trabajo recomendados y trabajos de CI. Vamos a analizar varias estrategias, pero ten en cuenta que puede ser necesario ajustarlas en profundidad para adaptarlas a tu caso de uso específico.

​

CI/CD con pruebas de datos simples y pruebas unitarias

Una forma sencilla de poner en marcha tu pipeline de CI es ejecutar un clúster de ClickHouse dentro de tu job y luego ejecutar tus modelos en él. Puedes insertar datos de demostración en este clúster antes de ejecutar tus modelos. También puedes usar un seed para poblar el entorno de staging con un subconjunto de tus datos de producción.

Una vez insertados los datos, puedes ejecutar tus pruebas de datos y tus pruebas unitarias.

Tu step de CD puede ser tan simple como ejecutar dbt build contra tu clúster de ClickHouse de producción.

​

Etapa de CI/CD más completa: usar datos recientes y probar solo los modelos afectados

Una estrategia habitual consiste en usar trabajos de Slim CI, en los que solo se vuelven a desplegar los modelos modificados (y sus dependencias ascendentes y descendentes). Este enfoque utiliza artefactos de tus ejecuciones de producción (es decir, el manifiesto de dbt) para reducir el tiempo de ejecución de tu proyecto y garantizar que no haya divergencias de esquema entre entornos.

Para mantener tus entornos de desarrollo sincronizados y evitar ejecutar tus modelos sobre despliegues obsoletos, puedes usar clone o incluso defer. En ClickHouse, dbt clone copia tablas MergeTree mediante una instrucción CLONE de copia cero; consulta Clonar modelos con dbt clone a continuación para obtener más detalles.

Recomendamos usar un clúster o servicio de ClickHouse dedicado para el entorno de pruebas (es decir, un entorno de staging) para evitar afectar al funcionamiento de tu entorno de producción. Para garantizar que el entorno de pruebas sea representativo, es importante que uses un subconjunto de tus datos de producción y que ejecutes dbt de una forma que evite divergencias de esquema entre entornos.

• Si no necesitas datos recientes para las pruebas, puedes restaurar una copia de seguridad de tus datos de producción en el entorno de staging.
• Si necesitas datos recientes para las pruebas, puedes usar una combinación de la función de tabla remoteSecure() y vistas materializadas actualizables para insertar con la frecuencia deseada. Otra opción es usar almacenamiento de objetos como intermediario y escribir datos periódicamente desde tu servicio de producción para luego importarlos al entorno de staging mediante las funciones de tabla de almacenamiento de objetos o ClickPipes (para la ingestión continua).

Usar un entorno dedicado para las pruebas de CI también te permite realizar pruebas manuales sin afectar a tu entorno de producción. Por ejemplo, puede que quieras apuntar una herramienta de BI a este entorno para hacer pruebas.

Para el despliegue (es decir, el paso de CD), recomendamos usar los artefactos de tus despliegues de producción para actualizar solo los modelos que hayan cambiado. Esto requiere configurar almacenamiento de objetos (por ejemplo, S3) como almacenamiento intermedio para tus artefactos de dbt. Una vez configurado, puedes ejecutar un comando como dbt build --select state:modified+ --state path/to/last/deploy/state.json para reconstruir selectivamente la cantidad mínima de modelos necesaria en función de lo que haya cambiado desde la última ejecución en producción.

​

Clonación de modelos con dbt clone

A partir de dbt-clickhouse 1.10.1, el comando dbt clone utiliza la sentencia de copia cero CREATE OR REPLACE TABLE ... CLONE AS ... de ClickHouse para clonar modelos materializados como tablas que usan un motor de la familia MergeTree. Esto crea una copia de la tabla sin duplicar las partes de datos subyacentes, lo que permite sincronizar entornos de forma rápida y económica; por ejemplo, al configurar un entorno de desarrollo o Slim CI a partir del estado de producción.

Los modelos que no se pueden clonar de esta forma recurren al comportamiento predeterminado de dbt, que consiste en crear una vista que apunta a la relación de origen:

• Tablas que usan motores distintos de MergeTree
• Materializaciones Distributed

En los modelos de vista materializada, solo se clona la tabla de destino; la vista materializada se crea sobre la tabla clonada.

​

Solución de problemas frecuentes

​

Conexiones

Si tienes problemas para conectarte a ClickHouse desde dbt, asegúrate de que se cumplan los siguientes criterios:

• El motor debe ser uno de los motores compatibles.
• Debes tener los permisos adecuados para acceder a la base de datos.
• Si no usas el motor de tabla predeterminado de la base de datos, debes especificar un motor de tabla en la configuración de tu modelo.

​

Comprender las operaciones de larga duración

Algunas operaciones pueden tardar más de lo esperado debido a consultas específicas de ClickHouse. Para obtener más información sobre qué consultas tardan más, aumente el nivel de registro a debug; esto mostrará el tiempo empleado por cada consulta. Por ejemplo, puede lograrse añadiendo --log-level debug a los comandos de dbt.

​

Correlación de ejecuciones de dbt con consultas de ClickHouse

Para obtener información detallada del lado del servidor, a partir de dbt-clickhouse 1.10.1, a cada sentencia ejecutada por el adaptador se le asigna su propio ID de consulta (un UUID4), que se reenvía a ClickHouse. El ID de la sentencia principal de un modelo se devuelve en la adapter_response de su resultado de dbt, por lo que está disponible en artefactos de dbt como run_results.json. Puede buscarlo en la tabla system.query_log para inspeccionar los tiempos de ejecución y el uso de recursos de esa sentencia:

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'

Ten en cuenta que una materialización suele ejecutar varias sentencias por modelo (DDL, inserts, etc.), cada una con su propio ID de consulta; el de run_results.json identifica únicamente la sentencia principal del modelo. Para encontrar todas las sentencias de una ejecución, filtra system.query_log por el comentario de consulta de dbt incluido en el texto de cada consulta.

El ID de consulta también permite que las herramientas de observabilidad que consumen artefactos de dbt (por ejemplo, Elementary) vinculen automáticamente las ejecuciones de modelos de dbt con las entradas de system.query_log.

​

Limitaciones

El adaptador actual de ClickHouse para dbt tiene varias limitaciones que debe tener en cuenta:

• El plugin usa una sintaxis que requiere ClickHouse versión 25.3 o posterior. No probamos versiones anteriores de ClickHouse. Actualmente tampoco probamos tablas Replicated.
• Distintas ejecuciones de dbt-adapter pueden entrar en conflicto si se ejecutan al mismo tiempo, ya que internamente pueden usar los mismos nombres de tabla para las mismas operaciones. Para más información, consulte el issue #420.
• Actualmente, el adaptador materializa los modelos como tablas mediante INSERT INTO SELECT. En la práctica, esto implica duplicación de datos si la ejecución vuelve a realizarse. Los datasets muy grandes (PB) pueden dar lugar a tiempos de ejecución extremadamente largos, lo que hace inviables algunos modelos. Para mejorar el rendimiento, use vistas materializadas de ClickHouse implementando la vista como materialized: materialization_view. Además, procure minimizar el número de filas que devuelve cualquier consulta usando GROUP BY siempre que sea posible. Priorice modelos que resuman los datos frente a los que simplemente los transforman manteniendo el mismo número de filas del source.
• Para usar tablas distribuidas para representar un modelo, debe crear manualmente las tablas replicadas subyacentes en cada nodo. La tabla distribuida puede, a su vez, crearse sobre ellas. El adaptador no gestiona la creación del clúster.
• Cuando dbt crea una relación (table/view) en una database, normalmente la crea como: {{ database }}.{{ schema }}.{{ table/view id }}. ClickHouse no tiene el concepto de esquemas. Por lo tanto, el adaptador usa {{schema}}.{{ table/view id }}, donde schema es la database de ClickHouse.

​

Fivetran

El conector dbt-clickhouse también está disponible para su uso en las transformaciones de Fivetran, lo que permite integrar y transformar datos sin problemas directamente en la plataforma de Fivetran mediante dbt.