ALTER TABLE ... PROJECTION

Cette page présente les projections, leur utilisation et les différentes options disponibles pour les gérer.

Vue d’ensemble des projections

Les projections stockent les données dans un format qui optimise l’exécution des requêtes. Cette fonctionnalité est utile pour :

Exécuter des requêtes sur une colonne qui ne fait pas partie de la clé primaire
Pré-agréger des colonnes afin de réduire à la fois les calculs et les IO

Vous pouvez définir une ou plusieurs projections pour une table et, lors de l’analyse de la requête, ClickHouse sélectionnera la projection qui nécessite le moins de données à parcourir, sans modifier la requête fournie par l’utilisateur.

Utilisation du disqueLes projections créent en interne une nouvelle table cachée, ce qui signifie que davantage d’IO et d’espace disque seront nécessaires. Par exemple, si la projection définit une clé primaire différente, toutes les données de la table d’origine seront dupliquées.

Vous trouverez plus de détails techniques sur le fonctionnement interne des projections sur cette page.

Utilisation des projections

Exemple de filtrage sans utiliser de clés primaires

Création de la table :

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

Avec ALTER TABLE, on peut ajouter la projection à une table existante :

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

ALTER TABLE visits_order MATERIALIZE PROJECTION user_name_projection

Insertion des données :

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

La projection nous permettra de filtrer rapidement sur user_name, même si, dans la table d’origine, user_name n’a pas été défini comme PRIMARY_KEY. Au moment de l’exécution de la requête, ClickHouse détermine que moins de données seront traitées si la projection est utilisée, puisque les données sont ordonnées par user_name.

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

Pour vérifier qu’une requête utilise bien la projection, nous pouvons consulter la table system.query_log. Dans le champ projections, nous trouvons le nom de la projection utilisée, ou rien si aucune n’a été utilisée :

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

Exemple de requête de pré-agrégation

Créez la table avec la projection 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

Insérez les données :

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

Exécutez une première requête avec GROUP BY sur le champ user_agent. Cette requête n’utilisera pas la projection définie, car la pré-agrégation ne correspond pas.

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

Pour utiliser la projection, vous pouvez exécuter des requêtes qui sélectionnent tout ou partie des champs de pré-agrégation et 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

Comme indiqué précédemment, vous pouvez consulter la table system.query_log pour vérifier si une projection a été utilisée. Le champ projections indique le nom de la projection utilisée. Il sera vide si aucune projection n’a été utilisée :

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

Création et utilisation d’index de projection

Création d’un index de projection :

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

Insertion de quelques données d’exemple :

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

Le champ _part_offset conserve sa valeur après les fusions et les mutations, ce qui le rend utile pour l’indexation secondaire. On peut en tirer parti dans les requêtes :

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

Exemple de projection avec clause `WHERE`

Les projections peuvent inclure une clause WHERE pour ne stocker qu’un sous-ensemble de lignes. Cela est utile lorsque les requêtes filtrent fréquemment selon un prédicat connu : la projection ne matérialise que les lignes correspondantes, ce qui réduit le stockage et améliore les performances des requêtes. Création d’une table et ajout d’une projection filtrée :

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;

Insertion de données :

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

Lorsque la clause WHERE d’une requête implique la clause WHERE de la projection (c’est-à-dire que chaque condition du filtre de la projection est également présente dans le filtre de la requête), l’optimiseur peut utiliser automatiquement la projection s’il détermine que cela est bénéfique :

-- 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 vérification d’implication est conservatrice — elle s’appuie sur une correspondance exacte des conjonctions dans la forme canonique de l’expression. Elle peut passer à côté de certaines optimisations valides (par ex., les implications sur des plages de valeurs), mais elle ne produira jamais de résultats incorrects.

Gestion des projections

Les opérations suivantes sur les projections sont disponibles :

ADD PROJECTION

Utilisez l’instruction ci-dessous pour ajouter une description de projection aux métadonnées d’une table :

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

Lorsqu’une projection définit une clause WHERE, seules les lignes qui correspondent au prédicat sont matérialisées. L’optimiseur peut utiliser une telle projection lorsque la clause WHERE de la requête implique logiquement celle de la projection et que cette projection est avantageuse pour le plan de requête. Cela s’applique aussi bien aux projections normales qu’aux projections d’agrégation.

Clause `WITH SETTINGS`

WITH SETTINGS définit des paramètres de projection qui personnalisent la façon dont la projection stocke les données (par exemple, index_granularity ou index_granularity_bytes). Ils correspondent directement aux paramètres de table MergeTree, mais s’appliquent uniquement à cette projection. Exemple :

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

Les paramètres de projection prévalent sur les paramètres effectifs de la table pour la projection, sous réserve des règles de validation (par ex., les remplacements invalides ou incompatibles seront rejetés).

MODIFY PROJECTION

Utilisez l’instruction ci-dessous pour modifier la clause WITH SETTINGS d’une projection existante sans reconstruire ses données :

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

Pour un index de projection, indiquez de nouveau la déclaration INDEX plutôt que la requête 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, ...)

L’instruction reprend la définition complète de la projection, mais seule la clause WITH SETTINGS peut différer de la définition existante. La requête de projection elle-même (ou, pour un index de projection, l’expression et le type de l’index) doit rester identique, car les parts existantes stockent des données construites à partir de celle-ci ; pour la modifier, utilisez DROP PROJECTION, suivi de ADD PROJECTION. La commande modifie uniquement les métadonnées de la table et ne réécrit aucune donnée : les parts existantes conservent les paramètres avec lesquels elles ont été écrites, tandis que celles créées par de futurs inserts et fusions utilisent les nouveaux paramètres. MATERIALIZE PROJECTION n’applique pas non plus les nouveaux paramètres aux parts existantes : elle construit la projection uniquement dans les parts où elle est absente ou endommagée, et laisse inchangées les parts de projection déjà existantes. Pour reconstruire les parts existantes avec les nouveaux paramètres, exécutez CLEAR PROJECTION suivi de MATERIALIZE PROJECTION, ou fusionnez les parts, par exemple avec OPTIMIZE TABLE ... FINAL. Exemple :

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

DROP PROJECTION

Utilisez l’instruction ci-dessous pour supprimer la description d’une projection des métadonnées d’une table et supprimer les fichiers de projection sur le disque. Cela est implémenté sous la forme d’une mutation.

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

MATERIALIZE PROJECTION

Utilisez l’instruction ci-dessous pour construire la projection name dans les parts où elle est manquante ou corrompue, éventuellement uniquement dans la partition partition_name. Les parts qui contiennent déjà la projection ne sont pas modifiées, même si les paramètres de la projection ont été changés avec MODIFY PROJECTION après leur écriture ; pour les reconstruire, exécutez d’abord CLEAR PROJECTION. Cette opération est implémentée sous forme de mutation.

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

CLEAR PROJECTION

Utilisez l’instruction ci-dessous pour supprimer les fichiers de projection sur disque sans supprimer leur description. Cette opération est implémentée sous forme de mutation.

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

Les commandes ADD, MODIFY, DROP et CLEAR sont légères, au sens où elles ne font que modifier les métadonnées ou supprimer des fichiers. De plus, elles sont répliquées et synchronisent les métadonnées des projections via ClickHouse Keeper ou ZooKeeper.

La manipulation des projections n’est prise en charge que pour les tables utilisant le moteur *MergeTree (y compris les variantes répliquées).

Contrôler le comportement de fusion des projections

Lorsque vous exécutez une requête, ClickHouse choisit de lire soit dans la table d’origine, soit dans l’une de ses projections. Cette décision est prise individuellement pour chaque part de la table. ClickHouse cherche généralement à lire le moins de données possible et utilise plusieurs techniques pour identifier la meilleure part à lire, par exemple en échantillonnant la clé primaire d’une part. Dans certains cas, des parts de la table source n’ont pas de parts de projection correspondantes. Cela peut se produire, par exemple, parce que la création d’une projection pour une table en SQL est « lazy » par défaut : elle ne s’applique qu’aux données nouvellement insérées et laisse les parts existantes inchangées. Comme l’une des projections contient déjà les valeurs d’agrégation précalculées, ClickHouse essaie de lire les parts de projection correspondantes pour éviter de refaire l’agrégation au moment de l’exécution de la requête. Si une part donnée ne possède pas de part de projection correspondante, l’exécution de la requête revient à la part d’origine. Mais que se passe-t-il si les lignes de la table d’origine changent de manière non triviale à la suite de background merges non triviaux de data parts ? Par exemple, supposons que la table utilise le table engine ReplacingMergeTree. Si la même row est détectée dans plusieurs parts d’entrée lors de la fusion, seule la version la plus récente de la ligne (issue de la part insérée le plus récemment) est conservée, tandis que toutes les versions plus anciennes sont supprimées. De même, si la table utilise le table engine AggregatingMergeTree, l’opération de fusion peut regrouper des rows identiques dans les parts d’entrée (sur la base des primary key values) en une seule row afin de mettre à jour les partial aggregation states. Avant ClickHouse v24.8, les parts de projection se désynchronisaient silencieusement des données principales, ou bien certaines opérations comme les mises à jour et les suppressions ne pouvaient pas être exécutées du tout, car la database levait automatiquement une exception si la table avait des projections. Depuis la v24.8, un nouveau paramètre au niveau de la table deduplicate_merge_projection_mode contrôle le comportement lorsque les background merge operations non triviales mentionnées ci-dessus se produisent dans les parts de la table d’origine. Les delete mutations sont un autre exemple de part merge operations qui suppriment des rows dans les parts de la table d’origine. Depuis la v24.7, nous disposons également d’un paramètre pour contrôler le comportement vis-à-vis des delete mutations déclenchées par les lightweight deletes : lightweight_mutation_projection_mode. Voici les valeurs possibles pour deduplicate_merge_projection_mode et lightweight_mutation_projection_mode :

throw (par défaut) : une exception est levée, ce qui empêche les parts de projection de se désynchroniser.
drop : les parts de projection affectées sont supprimées. Les requêtes reviennent alors à la part de la table d’origine pour les projections affectées.
rebuild : la part de projection affectée est reconstruite afin de rester cohérente avec les données de la part de la table d’origine.

Limites

Il n’est pas possible d’utiliser une colonne ALIAS dans la clause ORDER BY d’une projection. Par exemple :

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;
-- Fails with UNKNOWN_IDENTIFIER

Les colonnes ALIAS ne sont pas stockées physiquement et sont calculées à la volée à l’exécution de la requête. Elles ne sont donc pas disponibles lors de l’écriture de la partie de projection, au moment où l’expression de tri est évaluée. Utilisez plutôt des colonnes MATERIALIZED ou intégrez directement l’expression :

-- using MATERIALIZED column
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;

-- using an inline expression
CREATE TABLE t
(
    id UInt64,
    a UInt32,
    PROJECTION p (SELECT a ORDER BY a + 1)
)
ENGINE = MergeTree ORDER BY id;

Référence SQL

Types de données

Moteurs

Fonctions

Formats

Paramètres

Tables système

Lacs de données

Vue d’ensemble des projections

Utilisation des projections

Exemple de filtrage sans utiliser de clés primaires

Exemple de requête de pré-agrégation

Création et utilisation d’index de projection

Exemple de projection avec clause `WHERE`

Gestion des projections

ADD PROJECTION

Clause `WITH SETTINGS`

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

Contrôler le comportement de fusion des projections

Limites

Voir aussi

​Vue d’ensemble des projections

​Utilisation des projections

​Exemple de filtrage sans utiliser de clés primaires

​Exemple de requête de pré-agrégation

​Création et utilisation d’index de projection

​Exemple de projection avec clause WHERE

​Gestion des projections

​ADD PROJECTION

​Clause WITH SETTINGS

​MODIFY PROJECTION

​DROP PROJECTION

​MATERIALIZE PROJECTION

​CLEAR PROJECTION

​Contrôler le comportement de fusion des projections

​Limites

​Voir aussi

Vue d’ensemble des projections

Utilisation des projections

Exemple de filtrage sans utiliser de clés primaires

Exemple de requête de pré-agrégation

Création et utilisation d’index de projection

Exemple de projection avec clause `WHERE`

Gestion des projections

ADD PROJECTION

Clause `WITH SETTINGS`

MODIFY PROJECTION

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

Contrôler le comportement de fusion des projections

Limites

Voir aussi