Materializaciones

Materializaciones disponibles y su configuración

Esta sección cubre todas las materializaciones disponibles en dbt-clickhouse, incluidas las características experimentales.

​

Configuraciones generales de materialización

La siguiente tabla muestra configuraciones compartidas por algunas de las materializaciones disponibles. Para obtener información más detallada sobre las configuraciones generales de los modelos de dbt, consulta la documentación de dbt:

Opción	Descripción	Valor predeterminado, si existe
engine	El motor de tabla (tipo de tabla) que se utilizará al crear tablas	MergeTree()
order_by	Una tupla de nombres de columna o expresiones arbitrarias. Esto permite crear un índice disperso pequeño que ayuda a encontrar los datos más rápido.	tuple()
partition_by	Una partición es una agrupación lógica de registros de una tabla según un criterio especificado. La clave de partición puede ser cualquier expresión de las columnas de la tabla.
primary_key	Al igual que order_by, es una expresión de primary key de ClickHouse. Si no se especifica, ClickHouse usará la expresión order_by como primary key.
settings	Un mapa/diccionario de configuraciones de “TABLE” que se usará en sentencias DDL como ‘CREATE TABLE’ con este modelo
query_settings	Un mapa/diccionario de configuraciones de ClickHouse a nivel de usuario que se usará con las sentencias INSERT o DELETE junto con este modelo
ttl	Una expresión TTL que se usará con la tabla. La expresión TTL es una cadena que puede usarse para especificar el TTL de la tabla.
sql_security	El usuario de ClickHouse que se utilizará al ejecutar la consulta subyacente de la vista. Valores aceptados: definer, invoker.
definer	Si sql_security se estableció en definer, debes especificar cualquier usuario existente o CURRENT_USER en la cláusula definer.

​

Motores de tabla compatibles

Tipo	Detalles
MergeTree (predeterminado)	documentación.
HDFS	documentación
MaterializedPostgreSQL	documentación
S3	documentación
EmbeddedRocksDB	documentación
Hive	documentación

Nota: para las vistas materializadas, se admiten todos los motores de la familia *MergeTree.

​

Motores de tabla compatibles experimentales

Tipo	Detalles
Tabla distribuida	docs.
Diccionario	docs

Si tienes problemas para conectarte a ClickHouse desde dbt con alguno de los motores anteriores, informa del problema aquí.

​

Una nota sobre la configuración del modelo

ClickHouse tiene varios tipos o niveles de “configuración”. En la configuración del modelo anterior, se pueden configurar dos de ellos. settings se refiere a la cláusula SETTINGS usada en sentencias DDL del tipo CREATE TABLE/VIEW, por lo que, en general, se trata de configuraciones específicas del motor de tabla concreto de ClickHouse. La nueva query_settings se usa para añadir una cláusula SETTINGS a las consultas INSERT y DELETE utilizadas para la materialización del modelo ( incluidas las materializaciones incrementales). Hay cientos de configuraciones en ClickHouse, y no siempre está claro cuál es una configuración de “tabla” y cuál es una configuración de “usuario” (aunque estas últimas suelen estar disponibles en la tabla system.settings). En general, se recomiendan los valores predeterminados, y cualquier uso de estas propiedades debe investigarse y probarse cuidadosamente.

​

Configuración de columna

NOTA: Las opciones de configuración de columna que se indican a continuación requieren que se apliquen los contratos de modelo.

Opción	Descripción	Valor predeterminado, si corresponde
codec	Una cadena formada por argumentos pasados a CODEC() en el DDL de la columna. Por ejemplo: codec: "Delta, ZSTD" se compilará como CODEC(Delta, ZSTD).
ttl	Una cadena formada por una expresión TTL (time-to-live) que define una regla TTL en el DDL de la columna. Por ejemplo: ttl: ts + INTERVAL 1 DAY se compilará como TTL ts + INTERVAL 1 DAY.

​

Ejemplo de configuración de esquema

models:
  - name: table_column_configs
    description: 'Testing column-level configurations'
    config:
      contract:
        enforced: true
    columns:
      - name: ts
        data_type: timestamp
        codec: ZSTD
      - name: x
        data_type: UInt8
        ttl: ts + INTERVAL 1 DAY

​

Añadir tipos complejos

dbt determina automáticamente el tipo de datos de cada columna analizando el SQL utilizado para crear el modelo. Sin embargo, en algunos casos, este proceso puede no determinar con precisión el tipo de datos, lo que genera conflictos con los tipos especificados en la propiedad data_type del contrato. Para solucionarlo, recomendamos usar la función CAST() en el SQL del modelo para definir explícitamente el tipo deseado. Por ejemplo:

{{
    config(
        materialized="materialized_view",
        engine="AggregatingMergeTree",
        order_by=["event_type"],
    )
}}

select
  -- event_type may be infered as a String but we may prefer LowCardinality(String):
  CAST(event_type, 'LowCardinality(String)') as event_type,
  -- countState() may be infered as `AggregateFunction(count)` but we may prefer to change the type of the argument used:
  CAST(countState(), 'AggregateFunction(count, UInt32)') as response_count,
  -- maxSimpleState() may be infered as `SimpleAggregateFunction(max, String)` but we may prefer to also change the type of the argument used:
  CAST(maxSimpleState(event_type), 'SimpleAggregateFunction(max, LowCardinality(String))') as max_event_type
from {{ ref('user_events') }}
group by event_type

​

Materialización: vista

Un modelo de dbt puede crearse como una vista de ClickHouse y configurarse con la siguiente sintaxis:

Archivo del proyecto (dbt_project.yml):

models:
  <resource-path>:
    +materialized: view

O el bloque de configuración (models/<model_name>.sql):

{{ config(materialized = "view") }}

​

Materialización: tabla

Un modelo de dbt puede crearse como una tabla de ClickHouse y configurarse con la siguiente sintaxis:

Archivo del proyecto (dbt_project.yml):

models:
  <resource-path>:
    +materialized: table
    +order_by: [ <column-name>, ... ]
    +engine: <engine-type>
    +partition_by: [ <column-name>, ... ]

O el bloque de configuración (models/<model_name>.sql):

{{ config(
    materialized = "table",
    engine = "<engine-type>",
    order_by = [ "<column-name>", ... ],
    partition_by = [ "<column-name>", ... ],
      ...
    ]
) }}

​

Índices de omisión de datos

Puede añadir índices de omisión de datos a las materializaciones table mediante la configuración indexes:

{{ config(
        materialized='table',
        indexes=[{
          'name': 'your_index_name',
          'definition': 'your_column TYPE minmax GRANULARITY 2'
        }]
) }}

​

Proyecciones

Puede añadir proyecciones a las materializaciones table y distributed_table mediante la configuración projections. Cada entrada de proyección requiere una clave query o una index (no ambas).

Nota: En las tablas distribuidas, la proyección se aplica a las tablas _local, no a la tabla proxy distribuida. Nota: Especificar tanto query como index en la misma entrada de proyección genera un error de compilación.

​

Proyecciones de consultas

Use query para definir una consulta de proyección completa:

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'your_projection_name',
               'query': 'SELECT department, avg(age) AS avg_age GROUP BY department'
           }
       ]
) }}

​

Proyecciones de índices

Use index como una abreviatura sintáctica para las proyecciones de índices ligeras que utilizan la columna virtual _part_offset. Pase un único nombre de columna o una lista de columnas según las que se desee ordenar:

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'proj_by_age',
               'index': 'age'
           }
       ]
) }}

{{ config(
       materialized='table',
       projections=[
           {
               'name': 'proj_by_dept_age',
               'index': ['department', 'age']
           }
       ]
) }}

dbt-clickhouse genera automáticamente DDL adecuado para cada versión:

Versión de ClickHouse	SQL generado
26.1+	ADD PROJECTION proj_by_age INDEX age TYPE basic
25.8 – 26.0	ADD PROJECTION proj_by_age (SELECT _part_offset ORDER BY age)

​

Materialización: incremental

El modelo de tabla se reconstruirá en cada ejecución de dbt. Esto puede ser inviable y extremadamente costoso para conjuntos de resultados de mayor tamaño o transformaciones complejas. Para resolver este problema y reducir el tiempo de compilación, un modelo de dbt puede crearse como una tabla incremental de ClickHouse y configurarse con la siguiente sintaxis:

Definición del modelo en dbt_project.yml:

models:
  <resource-path>:
    +materialized: incremental
    +order_by: [ <column-name>, ... ]
    +engine: <engine-type>
    +partition_by: [ <column-name>, ... ]
    +unique_key: [ <column-name>, ... ]
    +inserts_only: [ True|False ]

O bien en el bloque de configuración de models/<model_name>.sql:

{{ config(
    materialized = "incremental",
    engine = "<engine-type>",
    order_by = [ "<column-name>", ... ],
    partition_by = [ "<column-name>", ... ],
    unique_key = [ "<column-name>", ... ],
    inserts_only = [ True|False ],
      ...
    ]
) }}

​

Configuraciones

A continuación, se enumeran las configuraciones específicas de este tipo de materialización:

Opción	Descripción	¿Obligatorio?
unique_key	Una tupla de nombres de columna que identifica de forma única las filas. Para obtener más información sobre las restricciones de unicidad, consulta aquí.	Obligatorio. Si no se proporciona, las filas modificadas se añadirán dos veces a la tabla incremental.
inserts_only	Ha quedado obsoleto en favor de la strategy incremental append, que funciona de la misma manera. Si se establece en True para un modelo incremental, las actualizaciones incrementales se insertarán directamente en la tabla de destino sin crear una tabla intermedia. Si se establece inserts_only, se ignorará incremental_strategy.	Opcional (predeterminado: False)
incremental_strategy	La estrategia que se utilizará para la materialización incremental. Se admiten delete+insert, append, insert_overwrite o microbatch. Para obtener más información sobre las estrategias, consulta aquí	Opcional (predeterminado: ‘default’)
incremental_predicates	Condiciones adicionales que se aplicarán a la materialización incremental (solo se aplican a la estrategia delete+insert	Opcional

​

Estrategias para modelos incrementales

dbt-clickhouse admite las siguientes estrategias para modelos incrementales.

​

La estrategia predeterminada (heredada)

Históricamente, ClickHouse solo ha ofrecido compatibilidad limitada con actualizaciones y eliminaciones, en forma de “mutaciones” asíncronas. Para emular el comportamiento esperado de dbt, dbt-clickhouse crea de forma predeterminada una nueva tabla temporal que contiene todos los registros “antiguos” no afectados (no eliminados, no modificados), además de cualquier registro nuevo o actualizado, y luego intercambia esta tabla temporal con la relación del modelo incremental existente. Esta es la única estrategia que conserva la relación original si algo sale mal antes de que se complete la operación; sin embargo, como implica una copia completa de la tabla original, su ejecución puede resultar bastante costosa y lenta.

​

La estrategia Delete+Insert

La estrategia delete+insert utiliza eliminaciones ligeras para eliminar las filas afectadas y después inserta las nuevas. Como no copia toda la tabla, ofrece un rendimiento significativamente superior al de la estrategia «heredado». Al configurar use_lw_deletes: true en el perfil, delete+insert se convierte en la estrategia incremental predeterminada.

Hay consideraciones importantes al utilizar esta estrategia:

• Opera directamente sobre la tabla afectada sin crear tablas intermedias ni temporales, por lo que, si se produce un issue durante la operación, es probable que los datos del modelo incremental queden en un estado no válido.
• Requiere el ajuste de ClickHouse allow_nondeterministic_mutations. El adaptador lo habilita automáticamente para sus propias sesiones siempre que sea posible. Cuando no puede habilitarse (por ejemplo, porque es de solo lectura para tu usuario de dbt), el comportamiento depende de cómo se haya elegido la estrategia: los modelos que dependen de la estrategia predeterminada recurren silenciosamente a la estrategia heredado, los modelos que configuran explícitamente delete+insert o microbatch fallan en tiempo de ejecución y use_lw_deletes: true en el perfil falla al establecer la conexión.
• En casos muy poco frecuentes, el uso de incremental_predicates no deterministas podría provocar una condición de carrera en los elementos actualizados o eliminados. Para garantizar resultados coherentes, los predicados incrementales solo deben incluir subconsultas sobre datos que no se modificarán durante la materialización incremental.

​

La estrategia Microbatch (requiere dbt-core >= 1.9)

La estrategia incremental microbatch es una característica de dbt-core desde la versión 1.9, diseñada para gestionar de forma eficiente transformaciones de grandes volúmenes de datos de series temporales. En dbt-clickhouse, se basa en la estrategia incremental delete_insert existente y divide la carga incremental en lotes de series temporales predefinidos según las configuraciones del modelo event_time y batch_size.

Además de gestionar transformaciones de gran tamaño, microbatch permite:

• Reprocesar lotes fallidos.
• Detectar automáticamente la ejecución paralela de lotes.
• Eliminar la necesidad de lógica condicional compleja para la carga histórica.

Para obtener información detallada sobre el uso de microbatch, consulta la documentación oficial.

Opción	Descripción	Valor predeterminado, si aplica
event_time	La columna que indica “en qué momento ocurrió la fila”. Es obligatoria para tu modelo microbatch y para cualquier dependencia directa que deba filtrarse.
begin	El “inicio de los tiempos” para el modelo microbatch. Es el punto de partida para cualquier ejecución inicial o full-refresh. Por ejemplo, un modelo microbatch con granularidad diaria ejecutado el 2024-10-01 con begin = ‘2023-10-01 procesará 366 lotes (¡es un año bisiesto!) más el lote de “hoy”.
batch_size	La granularidad de tus lotes. Los valores admitidos son hour, day, month y year
lookback	Procesa X lotes anteriores al marcador más reciente para capturar registros que llegan con retraso.	1
concurrent_batches	Anula la detección automática de dbt para ejecutar lotes de forma concurrente (al mismo tiempo). Lee más sobre cómo configurar lotes concurrentes. Si se establece en true, los lotes se ejecutan de forma concurrente (en paralelo). false ejecuta los lotes de forma secuencial (uno tras otro).

​

La estrategia Append

Esta estrategia sustituye la configuración inserts_only en versiones anteriores de dbt-clickhouse. Este enfoque simplemente añade filas nuevas a la relación existente. Como resultado, las filas duplicadas no se eliminan y no hay ninguna tabla temporal ni intermedia. Es el enfoque más rápido si se permiten duplicados en los datos o si la consulta incremental los excluye mediante la cláusula/filtro WHERE.

​

La estrategia insert_overwrite (Experimental)

[IMPORTANT] Actualmente, la estrategia insert_overwrite no es totalmente funcional con materializaciones distribuidas.

Realiza los siguientes pasos:

1. Crea una tabla temporal de preparación con la misma estructura que la relación del modelo incremental: CREATE TABLE <staging> AS <target>.
2. Inserta únicamente los registros nuevos (generados por SELECT) en la tabla de preparación.
3. Reemplaza únicamente las particiones nuevas (presentes en la tabla de preparación) en la tabla de destino.

Este enfoque tiene las siguientes ventajas:

• Es más rápido que la estrategia predeterminada porque no copia la tabla completa.
• Es más seguro que otras estrategias porque no modifica la tabla original hasta que la operación INSERT se completa correctamente: en caso de fallo intermedio, la tabla original no se modifica.
• Aplica la buena práctica de ingeniería de datos de la “inmutabilidad de las particiones”, lo que simplifica el procesamiento incremental y paralelo de datos, las reversiones, etc.

La estrategia requiere que partition_by esté definido en la configuración del modelo. Ignora cualquier otro parámetro de la configuración del modelo específico de otras estrategias.

​

Materialización: materialized_view

La materialización materialized_view crea una vista materializada de ClickHouse que actúa como un disparador de inserción, transformando e insertando automáticamente nuevas filas de una tabla de origen en una tabla de destino. Esta es una de las materializaciones más potentes disponibles en dbt-clickhouse.

Dado su alcance, esta materialización tiene su propia página dedicada. Ve a la guía de vistas materializadas para consultar la documentación completa

​

Materialización: diccionario (experimental)

Un modelo de dbt puede crearse como un diccionario de ClickHouse. En cada dbt run, el diccionario se reemplaza por la definición actual del modelo mediante CREATE OR REPLACE DICTIONARY.

​

Configuraciones

Opción	Descripción	Obligatorio
fields	La estructura del diccionario, como una lista de pares (name, type).	Sí
primary_key	La clave primaria del diccionario. Debe coincidir con el tipo de clave que espera el diseño seleccionado (por ejemplo, una clave compleja para los diseños COMPLEX_KEY_*).	Sí
layout	El diseño utilizado para almacenar el diccionario en memoria, como HASHED(), COMPLEX_KEY_HASHED() o DIRECT().	Sí
source_type	De dónde obtiene el diccionario sus datos: clickhouse (predeterminado; usa el SQL del modelo o la opción table) o http.
lifetime	La cláusula LIFETIME que controla la frecuencia de actualización del diccionario, por ejemplo, MIN 0 MAX 300. Es opcional desde dbt-clickhouse 1.10.0; omítala para diseños que no la utilizan, como DIRECT().
table	Solo para la fuente clickhouse. Lee de una tabla existente en lugar del SQL del modelo.
update_field	Solo para la fuente clickhouse. Actualiza el diccionario de forma incremental obteniendo solo las filas cuyo valor en esta columna haya cambiado desde la actualización anterior. Consulte LIFETIME. Disponible desde dbt-clickhouse 1.10.0.
update_lag	Solo para la fuente clickhouse. Número de segundos que se restan de la hora de la actualización anterior al usar update_field, para tener en cuenta las actualizaciones tardías. Disponible desde dbt-clickhouse 1.10.0.
connection_overrides	Solo para la fuente clickhouse. Sobrescrituras de las credenciales utilizadas en la cláusula SOURCE del diccionario, por ejemplo, {'user': 'dictionary_reader'}.
url, format	Solo para la fuente http. La URL del archivo de origen y su formato de entrada.	Sí para http
range	La cláusula RANGE para diseños RANGE_HASHED(), por ejemplo, 'min start max stop'.

​

Ejemplo con una fuente de ClickHouse

El SQL del modelo se convierte en la consulta de la fuente del diccionario:

{{ config(
       materialized='dictionary',
       fields=[
           ('id', 'UInt64'),
           ('name', 'String'),
       ],
       primary_key='id',
       layout='HASHED()',
       lifetime='MIN 0 MAX 300'
) }}

select id, name from {{ source('raw', 'people') }}

​

Ejemplo con una fuente HTTP

Al usar source_type='http' (o la opción table), el SQL del modelo no se utiliza como fuente, pero dbt sigue requiriendo un cuerpo; use select 1 como marcador de posición:

{{ config(
       materialized='dictionary',
       fields=[
           ('LocationID', 'UInt16 DEFAULT 0'),
           ('Borough', 'String'),
           ('Zone', 'String'),
       ],
       primary_key='LocationID',
       layout='HASHED()',
       lifetime='MIN 0 MAX 0',
       source_type='http',
       url='https://datasets-documentation.s3.eu-west-3.amazonaws.com/nyc-taxi/taxi_zone_lookup.csv',
       format='CSVWithNames'
) }}

select 1

Consulta las pruebas de diccionarios para ver más ejemplos, incluidos los diccionarios de tipo range y direct.

​

Materialización: distributed_table (experimental)

La tabla distribuida se crea con los siguientes pasos:

1. Crea una vista temporal con una consulta SQL para obtener la estructura correcta
2. Crea tablas locales vacías a partir de la vista
3. Crea una tabla distribuida a partir de las tablas locales.
4. Los datos se insertan en la tabla distribuida, por lo que se distribuyen entre los segmentos sin duplicarse.

Notas:

• Las consultas de dbt-clickhouse ahora incluyen automáticamente la configuración insert_distributed_sync = 1 para garantizar que las operaciones posteriores de materialización incremental se ejecuten correctamente. Esto podría hacer que algunas inserciones en tablas distribuidas se ejecuten más lentamente de lo esperado.

​

Ejemplo de modelo de tabla distribuida

{{
    config(
        materialized='distributed_table',
        order_by='id, created_at',
        sharding_key='cityHash64(id)',
        engine='ReplacingMergeTree'
    )
}}

select id, created_at, item
from {{ source('db', 'table') }}

​

Migraciones generadas

CREATE TABLE db.table_local on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = ReplacingMergeTree
    ORDER BY (id, created_at);

CREATE TABLE db.table on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = Distributed ('cluster', 'db', 'table_local', cityHash64(id));

​

Configuraciones

A continuación se enumeran las configuraciones específicas de este tipo de materialización:

Opción	Descripción	Valor predeterminado, si existe
sharding_key	La clave de segmentación determina el servidor de destino al insertar en una tabla con motor Distributed. La clave de segmentación puede ser aleatoria o ser el resultado de una función hash	rand())

​

materialización: distributed_incremental (experimental)

Modelo incremental basado en la misma idea que una tabla distribuida; la principal dificultad es procesar correctamente todas las estrategias incrementales.

1. La estrategia Append solo inserta datos en la tabla distribuida.
2. La estrategia Delete+Insert crea una tabla temporal distribuida para trabajar con todos los datos en cada segmento.
3. La estrategia predeterminada (heredada) crea tablas temporales e intermedias distribuidas por la misma razón.

Solo se reemplazan las tablas de segmento, porque la tabla distribuida no almacena datos. La tabla distribuida se vuelve a cargar solo cuando el modo full_refresh está habilitado o cuando la estructura de la tabla puede haber cambiado.

​

Ejemplo de modelo incremental Distributed

{{
    config(
        materialized='distributed_incremental',
        engine='MergeTree',
        incremental_strategy='append',
        unique_key='id,created_at'
    )
}}

select id, created_at, item
from {{ source('db', 'table') }}

​

Migraciones generadas

CREATE TABLE db.table_local on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = MergeTree;

CREATE TABLE db.table on cluster cluster (
    `id` UInt64,
    `created_at` DateTime,
    `item` String
)
    ENGINE = Distributed ('cluster', 'db', 'table_local', cityHash64(id));

​

Snapshot

Los snapshots de dbt registran cómo cambian con el tiempo las filas de un modelo mutable, en forma de dimensiones de cambio lento de tipo 2, de modo que los analistas puedan “mirar hacia atrás en el tiempo” y consultar un estado anterior de un modelo. El adaptador de ClickHouse admite tanto la estrategia timestamp como la estrategia check. Cada nueva versión de la tabla de snapshot se construye en una tabla temporal de preparación y se intercambia con EXCHANGE TABLES (o mediante un drop y un rename cuando el servidor no puede intercambiar tablas), de manera que los lectores siempre vean una versión completa del snapshot.

Desde dbt 1.9, los snapshots se definen en YAML, en snapshots/<name>.yml:

snapshots:
  - name: <snapshot-name>
    relation: ref('<model-name>')
    config:
      unique_key: <column-name>
      strategy: timestamp             # or check
      updated_at: <column-name>       # timestamp strategy
      # check_cols: [<column-name>, ...]  # check strategy

La forma anterior con Jinja en snapshots/<name>.sql sigue funcionando:

{% snapshot <snapshot-name> %}
{{
   config(
     unique_key = "<column-name>",
     strategy = "<strategy>",
     updated_at = "<updated-at-column-name>",
   )
}}
select * from {{ ref('<model-name>') }}
{% endsnapshot %}

Para ver un ejemplo práctico con Jaffle Shop, consulta la sección de snapshots de las guías. Para conocer todas las opciones, consulta la página de referencia de snapshot configs.

​

Contratos y restricciones

Solo se admiten contratos de tipos de columna exactos. Por ejemplo, un contrato con una columna de tipo UInt32 fallará si el modelo devuelve un UInt64 u otro tipo entero. ClickHouse también admite solo restricciones CHECK sobre toda la tabla/modelo. No se admiten restricciones CHECK de clave primaria, clave foránea, únicas ni a nivel de columna. (Consulta la documentación de ClickHouse sobre las claves primarias/de ORDER BY.)