Crea tu primera proyección

Aprende a usar las proyecciones en ClickHouse para almacenar una copia ordenada adicional de tus datos en la misma tabla, lo que permite búsquedas rápidas en columnas no incluidas en tu clave primaria.

Todas las guías de inicio rápido

Analítica en tiempo realData warehousingObservabilidadIA/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 deberías haber completado las siguientes guías de inicio rápido, ya que esta guía se basa directamente en la tabla uk_price_paid y en los conceptos que se introducen allí:

• Crea tu primera tabla MergeTree
• Crea tu primera vista materializada

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 proyección: una representación ordenada adicional de tus datos almacenada dentro de la misma tabla. A diferencia de las vistas materializadas, las proyecciones no requieren una tabla de destino independiente, se mantienen sincronizadas con las mutaciones (eliminaciones y actualizaciones) y el optimizador de consultas las usa de forma transparente; sigues consultando la misma tabla. Al final, entenderás cómo añadir y materializar una proyección, cómo ClickHouse la selecciona automáticamente y cuándo elegir proyecciones en lugar de vistas materializadas.

1. Entiende por qué necesitas una proyección

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

   Una proyección almacena una copia adicional ordenada de (algunas o todas las) columnas dentro de la misma tabla. Cuando consultas la tabla, el optimizador de consultas comprueba automáticamente si leer desde la proyección implicaría menos gránulos que los datos base y, si es así, la utiliza de forma transparente.

   Diferencias clave con las vistas materializadas:

   • Sin tabla independiente: la proyección reside dentro de uk_price_paid
   • Optimización transparente de consultas: consultas uk_price_paid con normalidad; ClickHouse elige la proyección automáticamente
   • Se mantiene sincronizada con las mutaciones: las eliminaciones y actualizaciones aplicadas a la tabla se reflejan en la proyección

   Más información en la documentación de referencia sobre proyecciones.

2. Agrega una proyección a tu tabla

   Define una proyección sobre uk_price_paid que almacene town, date, price y type, ordenados por (town, date):

   ALTER TABLE uk_price_paid
       ADD PROJECTION uk_price_paid_by_town
       (
           SELECT town, date, price, type
           ORDER BY (town, date)
       );
   

   Esto registra la proyección en los metadatos de la tabla, pero no la materializa para los datos existentes; solo las inserciones futuras la poblarán.

   Comprueba que la proyección aparezca en la definición de la tabla:

   SHOW CREATE TABLE uk_price_paid;
   

   Deberías ver el bloque PROJECTION uk_price_paid_by_town en la salida.

3. Materializar la proyección para los datos existentes

   Al igual que las vistas materializadas, una proyección recién añadida solo se aplica a las inserciones futuras. Para rellenarla con los 30 millones de filas que ya están en la tabla, materialícela explícitamente:

   ALTER TABLE uk_price_paid
       MATERIALIZE PROJECTION uk_price_paid_by_town;
   

   Esto se ejecuta como una mutación en segundo plano. Puede consultar su progreso:

   SELECT
       mutation_id,
       command,
       is_done
   FROM system.mutations
   WHERE table = 'uk_price_paid'
   ORDER BY create_time DESC
   LIMIT 5;
   

   Una vez que is_done = 1, la proyección está completamente materializada. También puede comprobarlo consultando system.projection_parts:

   SELECT
       name,
       count() AS parts,
       sum(rows) AS total_rows,
       formatReadableSize(sum(bytes_on_disk)) AS size
   FROM system.projection_parts
   WHERE table = 'uk_price_paid'
     AND active = true
   GROUP BY name;
   
4. Consulta la tabla y observa el uso automático de la proyección

   Ahora ejecuta una consulta con filtro por town, en la misma tabla que has consultado hasta ahora:

   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 muchas menos filas que antes de que existiera la proyección, porque ClickHouse ha elegido automáticamente leer de la proyección uk_price_paid_by_town en lugar de escanear los datos base.

   Puedes confirmar que se ha usado la proyección con EXPLAIN:

   EXPLAIN
   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;
   

   En la salida, busca ReadFromMergeTree con referencia al nombre de la proyección. Si quieres comparar el rendimiento explícitamente, puedes deshabilitar la optimización de proyecciones para una sola consulta:

   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
   SETTINGS optimize_use_projections = 0;
   

   Esto obliga a realizar un escaneo completo de la tabla para que puedas ver la diferencia en el número de filas leídas.

5. Comparación entre proyecciones y vistas materializadas

   Tanto las proyecciones como las vistas materializadas resuelven el mismo problema —lecturas más rápidas con patrones de acceso alternativos—, pero implican concesiones distintas. En resumen, las proyecciones son la mejor opción cuando solo necesitas un orden distinto para los mismos datos; las vistas materializadas son más flexibles cuando necesitas transformar, agregar o enrutar datos a un esquema diferente. Para una comparación detallada, consulta Vistas materializadas frente a proyecciones.

6. Observe la sobrecarga de almacenamiento

   Las proyecciones almacenan una segunda copia de las columnas seleccionadas dentro de la misma tabla, lo que incrementa el uso del disco. Consulta system.parts para ver el tamaño total de uk_price_paid (que ahora incluye los datos de la proyección):

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

   También puedes consultar el almacenamiento específico de las proyecciones:

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

   Esta es la misma disyuntiva fundamental que en las vistas materializadas: más espacio en disco a cambio de lecturas más rápidas. La proyección puede ser más pequeña que una copia completa porque solo incluye las cuatro columnas seleccionadas y la compresión varía según el orden de ordenación.

Siguientes pasos

En esta guía de inicio rápido añadiste una proyección a uk_price_paid que almacena los datos ordenados por (town, date), lo que permite búsquedas rápidas por localidad sin crear una tabla independiente. Aprendiste que el optimizador de consultas elige las proyecciones de forma transparente, que se mantienen sincronizadas con las mutaciones y que consumen espacio en disco a cambio de mejorar el rendimiento de lectura.

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

O profundiza con la documentación de referencia:

• Vistas materializadas frente a proyecciones
• Referencia de proyecciones
• Motor de tabla MergeTree

Todas las guías de inicio rápido