Vistas materializadas

La materialización materialized_view debe ser un SELECT sobre una tabla de origen existente. A diferencia de PostgreSQL, una vista materializada de ClickHouse no es "estática" (y no tiene una operación REFRESH equivalente). En cambio, actúa como un desencadenador de inserción: inserta nuevas filas en una tabla de destino aplicando la transformación SELECT definida a las filas que se insertan en la tabla de origen. Consulta la documentación sobre vistas materializadas de ClickHouse para obtener más información sobre cómo funcionan las vistas materializadas en ClickHouse.

Cuando usas la materialización materialized_view, dbt-clickhouse necesita crear tanto una vista materializada como una tabla de destino donde se insertan las filas transformadas. Hay dos formas de gestionar la tabla de destino:

El enfoque que elijas afecta a cómo se gestionan los cambios de esquema, los full refresh y las configuraciones con varias MV. Las siguientes secciones describen cada enfoque en detalle.

Este es el comportamiento predeterminado. Cuando se define un modelo materialized_view, el adaptador hará lo siguiente:

1. Crear una tabla de destino con el nombre del modelo
2. Crear una vista materializada de ClickHouse con el nombre <model_name>_mv

El esquema de la tabla de destino se infiere a partir de las columnas de la instrucción SELECT de la vista materializada. Todos los recursos (tabla de destino + MVs) comparten la misma configuración del modelo.

-- models/events_mv.sql
{{
    config(
        materialized='materialized_view',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)'
    )
}}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date, event_type

Consulta el archivo de pruebas para ver más ejemplos.

ClickHouse permite que más de una vista materializada escriba registros en la misma tabla de destino. Para admitirlo en dbt-clickhouse con el enfoque de destino implícito, puedes construir una UNION en tu archivo de modelo, delimitando el SQL de cada vista materializada con comentarios del tipo --my_mv_name:begin y --my_mv_name:end.

Por ejemplo, lo siguiente creará dos vistas materializadas, ambas escribiendo datos en la misma tabla de destino del modelo. Los nombres de las vistas materializadas tendrán la forma <model_name>_mv1 y <model_name>_mv2:

--mv1:begin
select a,b,c from {{ source('raw', 'table_1') }}
--mv1:end
union all
--mv2:begin
select a,b,c from {{ source('raw', 'table_2') }}
--mv2:end

A partir de la versión 1.9.8 de dbt-clickhouse, puede controlar cómo se actualiza el esquema de la tabla de destino cuando dbt run encuentra columnas distintas en el SQL de la MV.

{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    on_schema_change='fail'  # esta configuración
)}}

De forma predeterminada, dbt no aplicará ningún cambio a la tabla de destino (valor de la configuración ignore), pero puede cambiar esta configuración para que siga el mismo comportamiento que la configuración on_schema_change en los modelos incrementales.

Además, puede usar esta configuración como mecanismo de seguridad. Si la establece en fail, la compilación fallará si las columnas de la consulta SQL de la MV difieren de las de la tabla de destino que se creó con el primer dbt run.

De forma predeterminada, al crear o volver a crear una vista materializada (MV), la tabla de destino se rellena primero con datos históricos antes de crear la propia MV (catchup=True). Puede desactivar este comportamiento estableciendo la configuración catchup en False.

{{config(
    materialized='materialized_view',
    engine='MergeTree()',
    order_by='(id)',
    catchup=False  # esta configuración
)}}

De forma predeterminada, dbt-clickhouse crea y administra tanto la tabla de destino como las vistas materializadas dentro de un único modelo (el enfoque de destino implícito descrito anteriormente). Este enfoque tiene algunas limitaciones:

• Todos los recursos (tabla de destino + MVs) comparten la misma configuración. Si varias MVs apuntan a la misma tabla de destino, deben definirse juntas mediante la sintaxis UNION ALL.
• Ninguno de estos recursos puede procesarse por separado; todos deben administrarse mediante el mismo archivo de modelo.
• No es fácil controlar el nombre de cada MV.
• Toda la configuración se comparte entre la tabla de destino y las MVs, lo que dificulta configurar cada recurso individualmente y determinar qué configuración corresponde a cada uno.

La funcionalidad de destino explícito te permite definir la tabla de destino por separado como una materialización table normal y luego hacer referencia a ella desde tus modelos de vista materializada.

• Recursos totalmente separados: Ahora cada recurso puede definirse por separado, lo que mejora la legibilidad.
• Recursos 1:1 entre dbt y CH: Ahora puedes usar las herramientas de dbt para gestionarlos y modificarlos por separado.
• Ahora hay distintas configuraciones disponibles: Ahora se puede aplicar una configuración diferente a cada uno.
• Ya no es necesario mantener convenciones de nomenclatura: Ahora todos los recursos se crean con el nombre que les des, no con el nombre personalizado añadido con _mv para las MV.

• La definición de la tabla de destino no es natural en dbt: no es un SQL que lea de una tabla de origen, por lo que aquí se pierden las validaciones de dbt. El SQL de la MV se seguirá validando mediante las utilidades de dbt, y su compatibilidad con las columnas de la tabla de destino se validará a nivel de CH.
• Hemos encontrado algunos problemas relacionados con las limitaciones de la función ref(): Necesitamos usarla para referenciar modelos entre sí, pero solo puede usarse para referenciar modelos upstream, no downstream. Esto genera algunos problemas en esta implementación. Hemos creado una incidencia en el repositorio de dbt-core y actualmente estamos hablando con ellos para buscar posibles soluciones (dbt-labs/dbt-core#12319):
  • Cuando se llama a ref() desde dentro del bloque de configuración, devuelve el modelo actual, no el compartido. Esto nos impide definirlo en la sección config(), por lo que nos obliga a usar un comentario para añadir esta dependencia. Estamos siguiendo el mismo patrón definido en la documentación de dbt con el enfoque "–depends_on:".
  • ref() nos funciona porque fuerza que la tabla de destino se cree primero, pero en el gráfico de dependencias de la documentación generada, la tabla de destino aparecerá como otra dependencia upstream, no downstream, lo que hace que sea algo difícil de entender.
  • unit-test también nos obliga a definir algunos datos para la tabla de destino, incluso cuando la idea no es leer de ella. La solución alternativa es simplemente dejar vacíos los datos de esta tabla.

Paso 1: Defina la tabla de destino como un modelo de tabla estándar

Modelo events_daily.sql:

{{
    config(
        materialized='table',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)',
        partition_by='toYYYYMM(event_date)'
    )
}}

SELECT
    toDate(now()) AS event_date,
    '' AS event_type,
    toUInt64(0) AS total
WHERE 0  -- Crea una tabla vacía con el esquema correcto

Esta es la solución alternativa que mencionamos en la sección de limitaciones. Puede que aquí se pierdan algunas validaciones de dbt, pero el esquema se seguirá validando a nivel de ClickHouse.

Paso 2: Defina vistas materializadas que apunten a la tabla de destino

Por ejemplo, puede definir distintas MVs en diferentes modelos de esta forma, incluso apuntando a la misma tabla de destino. Tenga en cuenta la nueva llamada a la macro {{ materialization_target_table(ref('events_daily')) }}, que configura la tabla de destino de la MV.

Modelo page_events_aggregator.sql:

{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'page_events') }}
GROUP BY event_date, event_type

Modelo mobile_events_aggregator.sql:

{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'mobile_events') }}
GROUP BY event_date, event_type

Al usar tablas de destino explícitas, además de las configuraciones generales de materialización y las configuraciones específicas de la tabla, se aplican las siguientes configuraciones:

En la tabla de destino (materialized='table'):

En la vista materializada (materialized='materialized_view'):

Al usar --full-refresh, las tablas de destino explícitas se volverán a crear (por lo que podrías perder datos si se está ingestando información durante este proceso). Esto se comportará de distintas maneras según la configuración:

Opción 1: comportamiento predeterminado de --full-refresh. Todo se elimina y se vuelve a crear, pero durante la recreación de las MV, la tabla de destino estará vacía o cargada parcialmente.

Todo se elimina y se vuelve a crear. Si quieres volver a insertar los datos usando el SQL de las MV, mantén la configuración catchup=True:

-- models/page_events_aggregator.sql
{{ config(
    materialized='materialized_view',
    catchup=True  -- este es el valor predeterminado, por lo que no necesitas configurarlo explícitamente.
) }}
{{ materialization_target_table(ref('events_daily')) }}
...

Opción 2: Quiero volver a crear la tabla de destino y no quiero que quede vacía mientras se vuelven a crear las MV.

Si primero necesitas actualizar el SQL de las MV, puedes configurar en ellas catchup=False y luego ejecutar un dbt run o dbt run --full-refresh sobre las MV. Asegúrate de que las MV se creen antes de ejecutar --full-refresh en la tabla de destino, ya que este usa las definiciones de las MV de ClickHouse.

Establece repopulate_from_mvs_on_full_refresh=True en el modelo de la tabla de destino. En un dbt run --full-refresh, esto hará lo siguiente:

1. Crear una nueva tabla temporal
2. Ejecutar INSERT-SELECT usando el SQL de cada MV
3. Intercambiar atómicamente las tablas

Así, tu tabla no quedará vacía mientras se vuelven a crear las MV.

-- models/events_daily.sql
{{
    config(
        materialized='table',
        engine='SummingMergeTree()',
        order_by='(event_date, event_type)',
        repopulate_from_mvs_on_full_refresh=True
    )
}}
...

No puedes cambiar la tabla de destino de una MV sin un --full-refresh. Si intentas ejecutar un dbt run normal después de cambiar la referencia de materialization_target_table(), la compilación fallará con un mensaje de error que indica que el destino ha cambiado.

Para cambiar el destino:

1. Actualiza la llamada a materialization_target_table()
2. Ejecuta dbt run --full-refresh -s your_mv_model

Hay varias razones por las que esto puede ocurrir:

• Las vistas materializadas pueden estar configuradas con catchup=False o la tabla de destino puede estar configurada con repopulate_from_mvs_on_full_refresh=False, por lo que no se realizará ningún relleno de datos históricos cuando se creen las vistas materializadas o cuando se vuelva a crear la tabla de destino. Este es el comportamiento esperado, así que, si quiere volver a insertar los datos usando el SQL de las vistas materializadas, asegúrese de establecer catchup=True en la vista materializada (este es el valor predeterminado) o repopulate_from_mvs_on_full_refresh=True en la tabla de destino. Asegúrese de no activar ambas opciones al mismo tiempo para evitar duplicados. Consulte la sección de configuración para obtener más detalles.
• Mientras se ejecuta dbt run --full-refresh, si las vistas materializadas usan el valor predeterminado catchup=True, el destino se volverá a crear y las MV volverán a insertar los datos de forma secuencial. Para evitar esta situación, consulte Full refresh con destinos explícitos.

repopulate_from_mvs_on_full_refresh=True usa el SQL de la MV existente que ya está definida en ClickHouse. Para asegurarte de que se use la nueva definición de la vista materializada, ejecuta dbt run para cada vista materializada antes de ejecutar dbt run --full-refresh en la tabla de destino.

Posibles razones:

• Tanto catchup=True en las vistas materializadas como repopulate_from_mvs_on_full_refresh=True en la tabla de destino pueden estar habilitados: mantén solo uno de ellos, según las operaciones que quieras ejecutar. Consulta la sección de configuración para obtener más detalles.
• La tabla de destino no está definida con WHERE 0: la tabla de destino debe crearse vacía, pero la consulta interna puede insertar datos si no se incluye WHERE 0. Asegúrate de que la cláusula esté incluida.

Después de ejecutar dbt run --full-refresh, faltan algunas filas de la tabla de origen en la tabla de destino. Las vistas materializadas de ClickHouse actúan como disparadores de inserción: solo capturan datos mientras existen. Durante una actualización completa, hay un breve intervalo en el que la MV se elimina y se vuelve a crear (la "ventana ciega"). Las filas insertadas en la tabla de origen durante este intervalo no se capturan. Consulta la sección Comportamiento durante la ingestión activa para más detalles.

Consulta system.tables para ver dónde escribe una vista materializada:

SELECT
    name as mv_name,
    replaceRegexpOne(
        create_table_query,
        '.*TO\\s+`?([^`\\s(]+)`?\\.`?([^`\\s(]+)`?.*',
        '\\1.\\2'
    ) AS target_table
FROM system.tables
WHERE database = 'your_schema'
  AND engine = 'MaterializedView'

Durante una ejecución de dbt, busca este mensaje en el log:

La tabla <table_name> se usa como destino de una vista materializada gestionada por dbt. Se establece mv_on_schema_change en "fail" de forma predeterminada para evitar la pérdida de datos.

Si aparece este mensaje, dbt ha detectado que la tabla es el destino de al menos una vista materializada gestionada por dbt. Si esperas este mensaje pero no lo ves, verifica que:

• El modelo de vista materializada define {{ materialization_target_table(ref('your_target')) }} correctamente
• El modelo de vista materializada tiene materialized='materialized_view' en su configuración
• Tanto la vista materializada como la tabla de destino se han ejecutado al menos una vez

Si tiene modelos de vista materializada existentes que usan el enfoque de destino implícito y quiere migrar al enfoque de destino explícito, siga estos pasos:

1. Cree el modelo de la tabla de destino

Cree un nuevo archivo de modelo con materialized='table' que defina el mismo esquema que la tabla de destino de la MV actual. Use una cláusula WHERE 0 para crear una tabla vacía. Use el mismo nombre que el modelo actual de vista materializada implícita. Ahora podrá usar este modelo para iterar sobre la tabla de destino.

-- models/events_daily.sql
{{
    config(
        materialized='table',
        engine='MergeTree()',
        order_by='(event_date, event_type)'
    )
}}

SELECT
    toDate(now()) AS event_date,
    '' AS event_type,
    toUInt64(0) AS total
WHERE 0

2. Actualiza tus modelos de MV

Crea nuevos modelos, cada uno con el SQL de la MV y la llamada a la macro materialization_target_table() que apunte a la nueva tabla de destino. Si antes usabas UNION ALL, elimina esa parte y los comentarios.

Para los nombres de los modelos, tendrás que seguir esta convención:

• si solo se definió una MV, tendrá este nombre: <old_model_name>_mv
• si se definieron varias MV, cada una tendrá este nombre: <old_model_name>_mv_<name_in_comments>

Antes, en my_model.sql (destino implícito, modelo único con UNION ALL):

--mv1:begin
select a, b, c from {{ source('raw', 'table_1') }}
--mv1:end
union all
--mv2:begin
select a, b, c from {{ source('raw', 'table_2') }}
--mv2:end

Después (con destino explícito y archivos de modelo separados):

-- models/my_model_mv_mv1.sql
{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

select a, b, c from {{ source('raw', 'table_1') }}

-- models/my_model_mv_mv2.sql
{{ config(materialized='materialized_view') }}
{{ materialization_target_table(ref('events_daily')) }}

select a, b, c from {{ source('raw', 'table_2') }}

3. Ajústelos según sea necesario siguiendo las instrucciones de la sección destino explícito.

Al iterar los modelos, debe tener en cuenta cómo interactúan las distintas operaciones con los datos que se están insertando:

• Como las vistas materializadas de ClickHouse actúan como triggers de inserción, solo capturan datos mientras existen. Si una vista materializada se elimina y se vuelve a crear (p. ej., durante un --full-refresh), cualquier fila insertada en la tabla de origen durante ese intervalo no será procesada por la vista materializada. A esto se le llama que la vista materializada está "ciega".
• Los distintos procesos de catchup se basan en operaciones INSERT INTO ... SELECT que usan el SQL de las vistas materializadas y son independientes de cómo funcionan estas. Una vez que comienza el INSERT, los datos nuevos no se capturan por esa operación, pero sí los capturará la vista materializada adjunta.

La siguiente tabla resume la seguridad de cada operación cuando se están realizando inserciones activamente en la tabla de origen.

Modelos de vistas materializadas:

Modelo de tabla de destino:

Las vistas materializadas actualizables son un tipo especial de vista materializada en ClickHouse que vuelve a ejecutar periódicamente la consulta y almacena el resultado, de forma similar a como funcionan las vistas materializadas en otras bases de datos. Esto resulta útil en escenarios en los que se necesitan instantáneas periódicas o agregaciones, en lugar de desencadenadores de inserción en tiempo real.

Para usar una vista materializada actualizable, agrega un objeto de configuración refreshable a tu modelo de MV con las siguientes opciones:

{{
    config(
        materialized='materialized_view',
        engine='MergeTree()',
        order_by='(event_date)',
        refreshable={
            "interval": "EVERY 5 MINUTE",
            "randomize": "1 MINUTE",
            "append": True,
            "depends_on": ['schema.depend_on_model'],
            "depends_on_validation": True
        }
    )
}}

SELECT
    toStartOfDay(event_time) AS event_date,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date

{{
    config(
        materialized='materialized_view',
        refreshable={
            "interval": "EVERY 1 HOUR",
            "append": False
        }
    )
}}
{{ materialization_target_table(ref('events_daily')) }}

SELECT
    toStartOfDay(event_time) AS event_date,
    event_type,
    count() AS total
FROM {{ source('raw', 'events') }}
GROUP BY event_date, event_type

A partir de dbt-clickhouse 1.10.2, los cambios en los parámetros de refresco (interval, randomize y depends_on) de una vista materializada actualizable existente se aplican directamente durante una ejecución normal de dbt run, mediante ALTER TABLE ... MODIFY REFRESH. La vista materializada no se elimina ni se vuelve a crear, por lo que no se pierden datos ni hay una «ventana ciega» mientras se aplica el cambio.

Algunas transiciones no pueden aplicarse directamente. En lugar de ignorarlas silenciosamente, dbt hará fallar la ejecución con un error que le pedirá usar dbt run --full-refresh:

• Convertir una vista materializada normal en una actualizable (añadiendo la configuración refreshable), o viceversa (eliminándola).
• Cambiar la configuración append, ya que ClickHouse no permite añadir ni eliminar APPEND mediante MODIFY REFRESH.

Tenga en cuenta que --full-refresh elimina y vuelve a crear la vista materializada, con las implicaciones descritas en Comportamiento durante la ingestión activa.

• Al crear una vista materializada actualizable (MV) en ClickHouse que tiene una dependencia, ClickHouse no devuelve un error si la dependencia especificada no existe en el momento de la creación. En su lugar, la MV actualizable permanece en un estado inactivo, a la espera de que se cumpla la dependencia antes de empezar a procesar actualizaciones o a refrescarse. Este comportamiento es intencional, pero puede provocar retrasos en la disponibilidad de los datos si la dependencia requerida no se resuelve con prontitud. Debe asegurarse de que todas las dependencias estén correctamente definidas y existan antes de crear una vista materializada actualizable.
• A día de hoy, no existe una "vinculación con dbt" real entre la mv y sus dependencias, por lo que el orden de creación no está garantizado.
• La funcionalidad de actualización no se ha probado con varias mvs dirigidas al mismo modelo de destino.