ALTER TABLE ... PROJECTION

En esta página se explica qué son las proyecciones, cómo usarlas y las distintas opciones para gestionarlas.

Descripción general de las proyecciones

Las proyecciones almacenan los datos en un formato que optimiza la ejecución de consultas. Esta funcionalidad resulta útil para lo siguiente:

Ejecutar consultas sobre una columna que no forma parte de la clave primaria
Preagregar columnas, lo que reduce tanto el cómputo como la E/S

Puede definir una o más proyecciones para una tabla y, durante el análisis de la consulta, ClickHouse seleccionará la proyección con menos datos por escanear sin modificar la consulta proporcionada por el usuario.

Uso de discoLas proyecciones crean internamente una nueva tabla oculta, lo que implica más E/S y más espacio en disco. Por ejemplo, si la proyección define una clave primaria diferente, todos los datos de la tabla original se duplicarán.

Puede consultar más detalles técnicos sobre el funcionamiento interno de las proyecciones en esta página.

Uso de las proyecciones

Ejemplo de filtrado sin usar claves primarias

Creación de la tabla:

CREATE TABLE visits_order
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String
)
ENGINE = MergeTree()
PRIMARY KEY user_agent

Con ALTER TABLE, podemos añadir la proyección a una tabla existente:

ALTER TABLE visits_order ADD PROJECTION user_name_projection (
    SELECT *
    ORDER BY user_name
)

ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection

Inserción de los datos:

INSERT INTO visits_order SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

La proyección nos permitirá filtrar rápidamente por user_name, incluso si en la tabla original user_name no estaba definido como PRIMARY_KEY. En tiempo de consulta, ClickHouse determina que se procesarán menos datos si se utiliza la proyección, ya que los datos están ordenados por user_name.

SELECT
    *
FROM visits_order
WHERE user_name='test'
LIMIT 2

Para verificar que una consulta está usando la proyección, podemos revisar la tabla system.query_log. En el campo projections aparece el nombre de la proyección utilizada, o queda vacío si no se ha usado ninguna:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

Ejemplo de consulta de preagregación

Cree la tabla con la proyección projection_visits_by_user:

CREATE TABLE visits
(
   `user_id` UInt64,
   `user_name` String,
   `pages_visited` Nullable(Float64),
   `user_agent` String,
   PROJECTION projection_visits_by_user
   (
       SELECT
           user_agent,
           sum(pages_visited)
       GROUP BY user_id, user_agent
   )
)
ENGINE = MergeTree()
ORDER BY user_agent

Inserte los datos:

INSERT INTO visits SELECT
    number,
    'test',
    1.5 * (number / 2),
    'Android'
FROM numbers(1, 100);

INSERT INTO visits SELECT
    number,
    'test',
    1. * (number / 2),
   'IOS'
FROM numbers(100, 500);

Ejecute una primera consulta con GROUP BY usando el campo user_agent. Esta consulta no aprovechará la proyección definida, ya que la agregación previa no coincide.

SELECT
    user_agent,
    count(DISTINCT user_id)
FROM visits
GROUP BY user_agent

Para usar la proyección, puede ejecutar consultas que seleccionen parte o la totalidad de los campos de preagregación y de GROUP BY:

SELECT
    user_agent
FROM visits
WHERE user_id > 50 AND user_id < 150
GROUP BY user_agent

SELECT
    user_agent,
    sum(pages_visited)
FROM visits
GROUP BY user_agent

Como se mencionó anteriormente, puede revisar la tabla system.query_log para comprobar si se utilizó una proyección. El campo projections muestra el nombre de la proyección utilizada. Estará vacío si no se ha utilizado ninguna proyección:

SELECT query, projections FROM system.query_log WHERE query_id='<query_id>'

Creación y uso de índices de proyección

Para crear un índice de proyección:

CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id INDEX user_id TYPE basic
)
ENGINE = MergeTree()
ORDER BY (event_id);

Inserta algunos datos de ejemplo:

INSERT INTO events SELECT * FROM generateRandom() LIMIT 100000;

El campo _part_offset conserva su valor tras las fusiones y mutaciones, lo que lo hace útil para la indexación secundaria. Podemos aprovecharlo en las consultas:

SELECT
    count()
FROM events
WHERE _part_starting_offset + _part_offset IN (
    SELECT _part_starting_offset + _part_offset
    FROM events
    WHERE user_id = 42
)
SETTINGS enable_shared_storage_snapshot_in_query = 1

Proyección de ejemplo con cláusula `WHERE`

Las proyecciones pueden incluir una cláusula WHERE para almacenar solo un subconjunto de filas. Esto resulta útil cuando las consultas filtran con frecuencia según un predicado conocido: la proyección materializa únicamente las filas que coinciden, lo que reduce el almacenamiento y mejora el rendimiento de las consultas. Crear una tabla y añadir una proyección filtrada:

CREATE TABLE events
(
    `event_type` String,
    `time` DateTime,
    `message` String
)
ENGINE = MergeTree()
ORDER BY time;

ALTER TABLE events ADD PROJECTION proj_pageview (
    SELECT event_type, time, message
    WHERE event_type = 'pageview'
    ORDER BY time
);

ALTER TABLE events MATERIALIZE PROJECTION proj_pageview;

Inserción de datos:

INSERT INTO events VALUES
    ('pageview', '2024-01-01', 'homepage'),
    ('click', '2024-01-02', 'button'),
    ('pageview', '2024-01-03', 'about');

Cuando la cláusula WHERE de una consulta implica la cláusula WHERE de la proyección (es decir, cada condición del filtro de la proyección también está presente en el filtro de la consulta), el optimizador puede usar automáticamente la proyección cuando determina que resulta beneficioso:

-- This query implies the projection's WHERE, so the projection may be used:
SELECT time, message FROM events WHERE event_type = 'pageview';

-- A stricter query also implies the projection's WHERE:
SELECT time, message FROM events WHERE event_type = 'pageview' AND time > '2024-01-01';

-- This query does NOT imply the projection, so the base table is scanned:
SELECT time, message FROM events WHERE event_type = 'click';

La comprobación de implicación es conservadora: se basa en una coincidencia exacta de conjunciones sobre la forma canónica de la expresión. Puede pasar por alto algunas oportunidades de optimización válidas (p. ej., implicaciones de rango), pero nunca producirá resultados incorrectos.

Gestión de proyecciones

Están disponibles las siguientes operaciones con proyecciones:

ADD PROJECTION

Use la siguiente sentencia para añadir la descripción de una proyección a los metadatos de una tabla:

-- Normal projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [ORDER BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

-- Aggregate projection (supports WHERE)
ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] ) [WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)]

Cuando una proyección define una cláusula WHERE, solo se materializan las filas que cumplen el predicado. El optimizador puede usar esa proyección cuando el WHERE de la consulta implica lógicamente el WHERE de la proyección y esta resulta ventajosa para el plan de consulta. Esto se aplica tanto a las proyecciones normales como a las de agregación.

Cláusula `WITH SETTINGS`

WITH SETTINGS define ajustes a nivel de proyección, que personalizan cómo la proyección almacena los datos (por ejemplo, index_granularity o index_granularity_bytes). Estos corresponden directamente a los ajustes de tabla de MergeTree, pero se aplican solo a esta proyección. Ejemplo:

ALTER TABLE t
ADD PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 4096,
    index_granularity_bytes = 1048576
);

Los ajustes de la proyección prevalecen sobre los ajustes efectivos de la tabla para la proyección, con sujeción a las reglas de validación (p. ej., se rechazarán las anulaciones no válidas o incompatibles).

MODIFY PROJECTION

Utilice la siguiente sentencia para modificar la cláusula WITH SETTINGS de una proyección existente sin reconstruir sus datos:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name ( SELECT <COLUMN LIST EXPR> [WHERE <expr>] [GROUP BY] [ORDER BY] ) WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

Para un índice de proyección, especifique de nuevo la declaración INDEX en lugar de la consulta SELECT:

ALTER TABLE [db.]name [ON CLUSTER cluster] MODIFY PROJECTION [IF EXISTS] name INDEX <index_expr> TYPE <index_type> WITH SETTINGS ( setting_name1 = setting_value1, setting_name2 = setting_value2, ...)

La sentencia vuelve a declarar la definición completa de la proyección, pero solo la cláusula WITH SETTINGS puede diferir de la definición actual. La consulta de la proyección (o, en el caso de un índice de proyección, la expresión y el tipo del índice) debe permanecer igual, ya que las partes de proyección existentes almacenan datos generados a partir de ella; para modificarla, use DROP PROJECTION seguido de ADD PROJECTION. El comando solo modifica los metadatos de la tabla y no reescribe datos: las partes de proyección existentes conservan los ajustes con los que se escribieron, mientras que las partes de proyección creadas por futuras inserciones y fusiones usan los nuevos ajustes. MATERIALIZE PROJECTION tampoco aplica los nuevos ajustes a las partes existentes: solo genera la proyección en las partes donde falta o está dañada, y deja sin cambios las partes de proyección que ya existen. Para reconstruir las partes existentes con los nuevos ajustes, ejecute CLEAR PROJECTION seguido de MATERIALIZE PROJECTION, o fusione las partes, por ejemplo, con OPTIMIZE TABLE ... FINAL. Ejemplo:

ALTER TABLE t
MODIFY PROJECTION p (
    SELECT x ORDER BY x
) WITH SETTINGS (
    index_granularity = 128
);

DROP PROJECTION

Utilice la siguiente sentencia para eliminar la descripción de una proyección de los metadatos de una tabla y borrar del disco los archivos de la proyección. Esto se implementa como una mutación.

ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name

MATERIALIZE PROJECTION

Utilice la siguiente sentencia para construir la proyección name en las partes en las que falte o esté dañada, opcionalmente solo en la partición partition_name. Las partes que ya tienen la proyección no se modifican, aunque los ajustes de la proyección se hayan cambiado con MODIFY PROJECTION después de que se escribieran; para reconstruirlas, ejecute primero CLEAR PROJECTION. Esto se implementa como una mutación.

ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

CLEAR PROJECTION

Use la sentencia siguiente para eliminar del disco los archivos de la proyección sin eliminar su descripción. Esto se implementa como una mutación.

ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name]

Los comandos ADD, MODIFY, DROP y CLEAR son ligeros en el sentido de que solo modifican metadatos o eliminan archivos. Además, se replican y sincronizan los metadatos de las proyecciones mediante ClickHouse Keeper o ZooKeeper.

La manipulación de proyecciones solo se admite en tablas con motor *MergeTree (incluidas las variantes replicadas).

Control del comportamiento de las fusiones de proyecciones

Cuando ejecuta una consulta, ClickHouse elige entre leer de la tabla original o de una de sus proyecciones. La decisión de leer de la tabla original o de una de sus proyecciones se toma de forma individual para cada parte de la tabla. Por lo general, ClickHouse intenta leer la menor cantidad de datos posible y emplea un par de técnicas para identificar la mejor parte desde la que leer; por ejemplo, el muestreo de la clave primaria de una parte. En algunos casos, las partes de la tabla de origen no tienen partes de proyección correspondientes. Esto puede ocurrir, por ejemplo, porque la creación de una proyección para una tabla en SQL es “perezosa” de forma predeterminada: solo afecta a los datos nuevos que se insertan, pero deja intactas las partes existentes. Como una de las proyecciones ya contiene los valores agregados precalculados, ClickHouse intenta leer de las partes de proyección correspondientes para evitar volver a agregar durante la ejecución de la consulta. Si una parte concreta no tiene la parte de proyección correspondiente, la ejecución de la consulta recurre a la parte original. Pero, ¿qué ocurre si las filas de la tabla original cambian de una forma no trivial debido a fusiones en segundo plano no triviales de partes de datos? Por ejemplo, suponga que la tabla se almacena usando el motor de tabla ReplacingMergeTree. Si se detecta la misma fila en varias partes de entrada durante la fusión, solo se conservará la versión más reciente de la fila (la de la parte insertada más recientemente), mientras que todas las versiones anteriores se descartarán. De forma similar, si la tabla se almacena usando el motor de tabla AggregatingMergeTree, la operación de fusión puede combinar las mismas filas de las partes de entrada (según los valores de la clave primaria) en una sola fila para actualizar los estados de agregación parciales. Antes de ClickHouse v24.8, las partes de proyección o bien quedaban desincronizadas silenciosamente con los datos principales, o bien ciertas operaciones, como actualizaciones y eliminaciones, no podían ejecutarse en absoluto, ya que la base de datos lanzaba automáticamente una excepción si la tabla tenía proyecciones. Desde la versión v24.8, una nueva configuración a nivel de tabla deduplicate_merge_projection_mode controla el comportamiento cuando las operaciones en segundo plano de fusión no triviales mencionadas anteriormente se producen en partes de la tabla original. Las mutaciones de eliminación son otro ejemplo de operaciones de fusión de partes que eliminan filas en las partes de la tabla original. Desde la versión v24.7, también disponemos de una configuración para controlar el comportamiento con respecto a las mutaciones de eliminación activadas por eliminaciones ligeras: lightweight_mutation_projection_mode. A continuación se indican los posibles valores tanto de deduplicate_merge_projection_mode como de lightweight_mutation_projection_mode:

throw (predeterminado): Se lanza una excepción, lo que evita que las partes de proyección queden desincronizadas.
drop: Se eliminan las partes afectadas de la tabla de proyección. Las consultas recurrirán a la parte de la tabla original para las partes de proyección afectadas.
rebuild: La parte de proyección afectada se reconstruye para mantener la coherencia con los datos de la parte de la tabla original.

Limitaciones

No se puede usar una columna ALIAS en la cláusula ORDER BY de una proyección. Por ejemplo:

CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 ALIAS a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;
-- Falla con UNKNOWN_IDENTIFIER

Las columnas ALIAS no se almacenan físicamente y se calculan sobre la marcha en el momento de la consulta, por lo que no están disponibles durante la fase de escritura de partes de la proyección, cuando se evalúa la expresión de ordenación. En su lugar, use columnas MATERIALIZED o escriba la expresión directamente en línea:

-- usando columna MATERIALIZED
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    ab_sum UInt64 MATERIALIZED a + 1,
    PROJECTION p (SELECT a ORDER BY ab_sum)
)
ENGINE = MergeTree ORDER BY id;

-- usando una expresión en línea
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;

Referencia de SQL

Tipos de datos

Motores

Funciones

Formatos

Configuración

Tablas del sistema

Lagos de datos

Descripción general de las proyecciones

Uso de las proyecciones

Ejemplo de filtrado sin usar claves primarias

Ejemplo de consulta de preagregación

Creación y uso de índices de proyección

Proyección de ejemplo con cláusula `WHERE`

Gestión de proyecciones

ADD PROJECTION

Cláusula `WITH SETTINGS`

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

Control del comportamiento de las fusiones de proyecciones

Limitaciones

Véase también

​Descripción general de las proyecciones

​Uso de las proyecciones

​Ejemplo de filtrado sin usar claves primarias

​Ejemplo de consulta de preagregación

​Creación y uso de índices de proyección

​Proyección de ejemplo con cláusula WHERE

​Gestión de proyecciones

​ADD PROJECTION

​Cláusula WITH SETTINGS

​MODIFY PROJECTION

​DROP PROJECTION

​MATERIALIZE PROJECTION

​CLEAR PROJECTION

​Control del comportamiento de las fusiones de proyecciones

​Limitaciones

​Véase también

Descripción general de las proyecciones

Uso de las proyecciones

Ejemplo de filtrado sin usar claves primarias

Ejemplo de consulta de preagregación

Creación y uso de índices de proyección

Proyección de ejemplo con cláusula `WHERE`

Gestión de proyecciones

ADD PROJECTION

Cláusula `WITH SETTINGS`

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

Control del comportamiento de las fusiones de proyecciones

Limitaciones

Véase también