ALTER TABLE ... PROJECTION

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

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.

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

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>'

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>'

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

CREATE TABLE events
(
    `event_time` DateTime,
    `event_id` UInt64,
    `user_id` UInt64,
    `huge_string` String,
    PROJECTION order_by_user_id
    (
        SELECT
            _part_offset
        ORDER BY user_id
    )
)
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

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.

Están disponibles las siguientes operaciones con proyecciones:

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, ...)]

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).

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. Para reconstruir las partes existentes con los nuevos ajustes, ejecute MATERIALIZE PROJECTION.

Ejemplo:

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

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

Utilice la siguiente sentencia para reconstruir la proyección name en la partición partition_name. Esto se implementa como una mutación.

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

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.

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.

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;

• "Control de las proyecciones durante las fusiones" (entrada del blog)
• "Proyecciones" (guía)
• "Vistas materializadas frente a proyecciones"