Projections

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

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 :

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

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

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 reconstruire la projection name dans la partition partition_name. 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, 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 parties 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 parties 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 partie 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 parties 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 parties de projection de se désynchroniser.
drop : les parties 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 partie 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

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

Gestion des projections

ADD PROJECTION

Clause `WITH SETTINGS`

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

​Gestion des projections

​ADD PROJECTION

​Clause WITH SETTINGS

​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

Gestion des projections

ADD PROJECTION

Clause `WITH SETTINGS`

DROP PROJECTION

MATERIALIZE PROJECTION

CLEAR PROJECTION

Contrôler le comportement de fusion des projections

Limites

Voir aussi