Crea tu primera vista materializada

Aprende a usar las vistas materializadas en ClickHouse para precalcular y almacenar resultados de consultas con un orden diferente, lo que permite búsquedas rápidas en columnas que no forman parte de tu clave primaria.

Todas las guías de inicio rápido

Analítica en tiempo realData warehousingObservabilidadAI/MLCloudOss

Requisitos previos

To successfully follow this guide, you’ll need the following:

• A running ClickHouse Cloud service. If you don’t have one yet, complete the Create your first Cloud service quickstart first.

También debes haber completado la guía de inicio rápido Crea tu primera tabla MergeTree, ya que esta guía se basa directamente en la tabla uk_price_paid creada en ella.

Lo que crearás

En la guía de inicio rápido de MergeTree viste que consultar uk_price_paid por town o county requiere un escaneo completo de la tabla porque está ordenada por (postcode, addr1, addr2). En esta guía de inicio rápido resolverás ese problema creando una vista materializada que almacena los mismos datos ordenados por (town, date), lo que permite búsquedas rápidas por localidad sin modificar la tabla original. Al final, entenderás cómo funcionan las vistas materializadas como desencadenador de inserción, cómo cargar datos históricos ya existentes y la compensación en espacio de disco que implica almacenar los datos dos veces.

1. Comprenda por qué necesita una vista materializada

   Su tabla uk_price_paid está ordenada por (postcode, addr1, addr2). Esto significa que ClickHouse puede omitir grandes bloques de datos cuando filtra por postcode, addr1 o addr2, pero las consultas que filtran por town deben examinar todas las filas: los 30 millones.

   Podría crear una segunda tabla con un ORDER BY distinto, pero entonces tendría que acordarse de insertar en ambas tablas cada vez que lleguen datos nuevos. Una vista materializada automatiza esto: detecta las inserciones en una tabla de origen, transforma las filas y las escribe automáticamente en una tabla de destino.

   Piense en una vista materializada como un trigger de inserción: cada vez que se insertan filas en la tabla de origen, la consulta SELECT de la MV se ejecuta sobre el nuevo bloque de filas y el resultado se inserta en la tabla de destino.

2. Crear la tabla de destino

   Una vista materializada necesita algún lugar donde almacenar su resultado. Se trata simplemente de una tabla MergeTree normal; tienes control total sobre su esquema, ORDER BY y PARTITION BY.

   Crea una tabla ordenada por (town, date) con solo las columnas que necesitas para las consultas por ciudad:

   CREATE TABLE uk_price_paid_by_town
   (
       town       LowCardinality(String),
       date       Date,
       price      UInt32,
       type       Enum8('terraced' = 1, 'semi-detached' = 2, 'detached' = 3, 'flat' = 4, 'other' = 0)
   )
   ENGINE = MergeTree
   PARTITION BY toYYYYMM(date)
   ORDER BY (town, date);
   

   No hay nada especial en esta tabla: es una tabla MergeTree estándar. La vista materializada que crearás a continuación simplemente enruta los datos hacia ella.

   Verifica que la tabla se haya creado:

   SHOW CREATE TABLE uk_price_paid_by_town;
   
3. Cree la vista materializada

   Ahora, cree la vista materializada que conecta la tabla de origen (uk_price_paid) con la tabla de destino (uk_price_paid_by_town):

   CREATE MATERIALIZED VIEW uk_price_paid_by_town_mv
   TO uk_price_paid_by_town
   AS SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   La cláusula TO uk_price_paid_by_town le indica a ClickHouse que escriba el resultado de SELECT en la tabla de destino. A partir de ahora, cada vez que se insertan filas en uk_price_paid, esta vista materializada se activa e inserta las filas transformadas en uk_price_paid_by_town.

   Hay una salvedad importante: las vistas materializadas solo se activan con inserciones. Si eliminas o actualizas filas en la tabla de origen, la tabla de destino no tiene forma de saberlo; las vistas materializadas no se mantienen sincronizadas con eliminaciones ni actualizaciones. Si necesitas ese tipo de sincronización, considera usar proyecciones en su lugar.

4. Rellenar los datos históricos existentes

   La vista materializada solo procesa inserciones futuras. Los 30 millones de filas que ya hay en uk_price_paid se insertaron antes de que existiera la MV, por lo que la tabla de destino está vacía en este momento.

   Rellénela manualmente:

   INSERT INTO uk_price_paid_by_town
   SELECT
       town,
       date,
       price,
       type
   FROM uk_price_paid;
   

   Esto inserta directamente en la tabla de destino; la MV no interviene en este paso. Una vez completado el proceso, verifica que el número de filas coincida:

   SELECT
       'uk_price_paid' AS table,
       count() AS rows
   FROM uk_price_paid
   UNION ALL
   SELECT
       'uk_price_paid_by_town' AS table,
       count() AS rows
   FROM uk_price_paid_by_town;
   

   Ambas tablas deben tener el mismo número de filas.

5. Consultar la tabla de destino de la vista materializada

   Ahora ejecuta una consulta con un filtro por town en la tabla de destino y compárala con una consulta directa a la tabla de origen.

   Primero, consulta la tabla de origen:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   Comprueba las estadísticas de la consulta: se leen los 30 millones de filas porque town no está en el ORDER BY de la tabla de origen.

   Ahora ejecuta la misma consulta en la tabla de destino de la vista materializada:

   SELECT
       toYear(date) AS year,
       round(avg(price)) AS avg_price,
       count() AS sales
   FROM uk_price_paid_by_town
   WHERE town = 'LONDON'
   GROUP BY year
   ORDER BY year DESC;
   

   Vuelve a comprobar las estadísticas de la consulta: se leen muchas menos filas porque la tabla de destino está ordenada por (town, date) y ClickHouse puede omitir todos los datos que no coinciden con LONDON.

   Ejecuta SHOW TABLES para ver qué se ha creado:

   SHOW TABLES;
   

   Verás tanto uk_price_paid_by_town (la tabla de destino) como uk_price_paid_by_town_mv (la vista). Como usaste CREATE MATERIALIZED VIEW ... TO, puedes controlar el nombre de la tabla de destino. Si omites la cláusula TO, ClickHouse crea una tabla de destino con un nombre implícito (.inner.xxx), con la que es más difícil trabajar directamente. Por ello, se recomienda crear vistas materializadas con la cláusula TO.

6. Observa que los datos se almacenan por duplicado

   Las vistas materializadas te ofrecen lecturas más rápidas a costa de ocupar más espacio en disco. Consulta system.parts para ver cuánto espacio usa cada tabla:

   SELECT
       table,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS compressed_size
   FROM system.parts
   WHERE table IN ('uk_price_paid', 'uk_price_paid_by_town')
     AND active = true
   GROUP BY table;
   

   Los datos se almacenan físicamente dos veces: una vez en uk_price_paid, ordenados por (postcode, addr1, addr2), y otra en uk_price_paid_by_town, ordenados por (town, date). Esta es la compensación fundamental: se utiliza más espacio en disco a cambio de lecturas más rápidas para distintos patrones de acceso.

   La tabla de destino puede ocupar menos espacio en disco porque contiene menos columnas y el orden de clasificación (town, date) puede comprimirse de forma distinta al original.

Siguientes pasos

En esta guía de inicio rápido creaste una vista materializada para almacenar datos de ventas de propiedades del Reino Unido con un orden distinto, lo que permite búsquedas rápidas por localidad sin modificar la tabla original. Aprendiste que las vistas materializadas actúan como desencadenadores de inserción, que los datos existentes deben rellenarse manualmente con datos históricos y que, a cambio, se requiere espacio adicional en disco.

A continuación, consulta estas guías de inicio rápido:

• Crea tu primera proyección

O profundiza con la documentación de referencia:

• Referencia de vista materializada
• Vistas materializadas incrementales
• Proyecciones

Todas las guías de inicio rápido