> ## Documentation Index > Fetch the complete documentation index at: https://clickhouse.com/docs/llms.txt > Use this file to discover all available pages before exploring further. > Hérite de MergeTree, mais ajoute une logique de collapsing des lignes lors du processus de fusion. # Moteur de table CollapsingMergeTree
## Description
Le moteur `CollapsingMergeTree` hérite de [MergeTree](/docs/fr/reference/engines/table-engines/mergetree-family/mergetree) et ajoute une logique de collapsing des lignes pendant le processus de fusion. Le moteur de table `CollapsingMergeTree` supprime (collapsing) de manière asynchrone des paires de lignes si tous les champs d'une clé de tri (`ORDER BY`) sont équivalents, à l'exception du champ spécial `Sign`, qui peut prendre la valeur `1` ou `-1`. Les lignes qui n'ont pas de paire avec une valeur `Sign` opposée sont conservées. Pour plus de détails, consultez la section [Collapsing](#table_engine-collapsingmergetree-collapsing) de ce document. Ce moteur peut réduire considérablement le volume de stockage, et ainsi améliorer l'efficacité des requêtes `SELECT`.
## Paramètres
Tous les paramètres de ce moteur de table, à l'exception du paramètre `Sign`, ont la même signification que dans [`MergeTree`](/docs/fr/reference/engines/table-engines/mergetree-family/mergetree). * `Sign` — Le nom donné à une colonne indiquant le type de ligne, où `1` correspond à une ligne d'état et `-1` à une ligne d'annulation. Type : [Int8](/docs/fr/reference/data-types/int-uint).
## Créer une table
```sql theme={null} CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE = CollapsingMergeTree(Sign) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...] ```
Méthode obsolète pour créer une table La méthode ci-dessous n’est pas recommandée dans les nouveaux projets. Nous vous conseillons, si possible, de mettre à jour les anciens projets pour utiliser la nouvelle méthode. ```sql theme={null} CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... ) ENGINE [=] CollapsingMergeTree(date-column [, sampling_expression], (primary, key), index_granularity, Sign) ``` `Sign` — Nom donné à une colonne indiquant le type de ligne : `1` correspond à une ligne "d’état" et `-1` à une ligne "d’annulation". [Int8](/docs/fr/reference/data-types/int-uint).
* Pour une description des paramètres de requête, voir [description de la requête](/docs/fr/reference/statements/create/table). * Lors de la création d’une table `CollapsingMergeTree`, les mêmes [clauses de requête](/docs/fr/reference/engines/table-engines/mergetree-family/mergetree#table_engine-mergetree-creating-a-table) sont requises que pour la création d’une table `MergeTree`.
## Collapsing
### Données
Considérez le cas où vous devez enregistrer des données qui changent continuellement pour un objet donné. Il peut sembler logique d'avoir une ligne par objet et de la mettre à jour chaque fois que quelque chose change, cependant, les opérations de mise à jour sont coûteuses et lentes pour le SGBD, car elles nécessitent de réécrire les données dans le stockage. Si nous devons écrire des données rapidement, effectuer un grand nombre de mises à jour n'est pas une approche acceptable, mais nous pouvons toujours écrire les modifications d'un objet de manière séquentielle. Pour ce faire, nous utilisons la colonne spéciale `Sign`. * Si `Sign` = `1`, cela signifie que la ligne est une ligne d'"état" : *une ligne contenant des champs qui représentent un état valide actuel*. * Si `Sign` = `-1`, cela signifie que la ligne est une ligne d'"annulation" : *une ligne utilisée pour annuler l'état d'un objet ayant les mêmes attributs*. Par exemple, nous voulons calculer combien de pages les utilisateurs ont consultées sur un certain site web et combien de temps ils les ont visitées. À un instant donné, nous écrivons la ligne suivante avec l'état de l'activité de l'utilisateur : ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` À un moment ultérieur, nous enregistrons le changement d’activité de l’utilisateur et le consignons dans les deux lignes suivantes : ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ -1 │ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` La première ligne annule l’état précédent de l’objet (qui représente ici un utilisateur). Elle doit copier tous les champs de la clé de tri de la ligne "canceled", à l’exception de `Sign`. La deuxième ligne ci-dessus contient l’état actuel. Comme nous n’avons besoin que du dernier état de l’activité de l’utilisateur, la ligne "state" d’origine et la ligne "cancel" que nous avons insérées peuvent être supprimées comme indiqué ci-dessous, ce qui provoque la suppression par collapsing de l’état invalide (ancien) d’un objet : ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ 1 │ -- old "state" row can be deleted │ 4324182021466249494 │ 5 │ 146 │ -1 │ -- "cancel" row can be deleted │ 4324182021466249494 │ 6 │ 185 │ 1 │ -- new "state" row remains └─────────────────────┴───────────┴──────────┴──────┘ ``` `CollapsingMergeTree` met précisément en œuvre ce comportement de *collapsing* lors de la fusion des parties de données. La raison pour laquelle deux lignes sont nécessaires pour chaque modification est expliquée plus en détail dans le paragraphe [Algorithme](#table_engine-collapsingmergetree-collapsing-algorithm). **Les particularités d'une telle approche** 1. Le programme qui écrit les données doit mémoriser l'état d'un objet pour pouvoir l'annuler. La ligne "cancel" doit contenir des copies des champs de la clé de tri de la ligne "state", ainsi que le `Sign` opposé. Cela augmente la taille initiale du stockage, mais permet d'écrire les données rapidement. 2. De longs tableaux dans les colonnes réduisent l'efficacité du moteur en raison de la charge d'écriture plus élevée. Plus les données sont simples, plus l'efficacité est élevée. 3. Les résultats de `SELECT` dépendent fortement de la cohérence de l'historique des modifications de l'objet. Soyez vigilant lors de la préparation des données à insérer. Des données incohérentes peuvent produire des résultats imprévisibles. Par exemple, des valeurs négatives pour des métriques non négatives, comme la profondeur de session.
### Algorithme
Lorsque ClickHouse fusionne des [parts](/docs/fr/concepts/core-concepts/glossary#parts), chaque groupe de lignes consécutives ayant la même clé de tri (`ORDER BY`) est réduit à deux lignes au maximum : la ligne « état » avec `Sign` = `1` et la ligne « annulation » avec `Sign` = `-1`. Autrement dit, dans ClickHouse, les entrées sont collapsées. Pour chaque partie de données résultante, ClickHouse conserve : | | | | -- | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 1. | La première ligne « annulation » et la dernière ligne « état », si le nombre de lignes « état » et « annulation » est identique et que la dernière ligne est une ligne « état ». | | 2. | La dernière ligne « état », s'il y a plus de lignes « état » que de lignes « annulation ». | | 3. | La première ligne « annulation », s'il y a plus de lignes « annulation » que de lignes « état ». | | 4. | Aucune ligne, dans tous les autres cas. | De plus, lorsqu'il y a au moins deux lignes « état » de plus que de lignes « annulation », ou au moins deux lignes « annulation » de plus que de lignes « état », la fusion se poursuit. ClickHouse traite toutefois cette situation comme une erreur logique et l'enregistre dans le journal du serveur. Cette erreur peut se produire si les mêmes données sont insérées plusieurs fois. Ainsi, le collapsing ne doit pas modifier les résultats du calcul des statistiques. Les modifications sont progressivement collapsées, de sorte qu'à la fin, seul le dernier état de presque chaque objet subsiste. La colonne `Sign` est requise, car l'algorithme de fusion ne garantit pas que toutes les lignes ayant la même clé de tri se retrouveront dans la même partie de données résultante, ni même sur le même serveur physique. ClickHouse traite les requêtes `SELECT` avec plusieurs threads et ne peut pas prédire l'ordre des lignes dans le résultat. Une agrégation est nécessaire si vous devez obtenir des données entièrement « collapsées » à partir de la table `CollapsingMergeTree`. Pour finaliser le collapsing, écrivez une requête avec la clause `GROUP BY` et des fonctions d'agrégation qui tiennent compte du signe. Par exemple, pour calculer la quantité, utilisez `sum(Sign)` au lieu de `count()`. Pour calculer la somme d'une valeur, utilisez `sum(Sign * x)` avec `HAVING sum(Sign) > 0` au lieu de `sum(x)` comme dans l'[exemple](#example-of-use) ci-dessous. Les agrégats `count`, `sum` et `avg` peuvent être calculés de cette manière. L'agrégat `uniq` peut être calculé si un objet possède au moins un état non collapsé. Les agrégats `min` et `max` ne peuvent pas être calculés, car `CollapsingMergeTree` ne conserve pas l'historique des états collapsés. Si vous devez extraire des données sans agrégation (par exemple, pour vérifier si des lignes dont les valeurs les plus récentes correspondent à certaines conditions sont présentes), vous pouvez utiliser le modificateur [`FINAL`](/docs/fr/reference/statements/select/from#final-modifier) pour la clause `FROM`. Il fusionnera les données avant de renvoyer le résultat. Pour CollapsingMergeTree, seule la ligne d'état la plus récente pour chaque clé est renvoyée.
## Exemples
### Exemple d’utilisation
Considérons les données d’exemple suivantes : ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ 1 │ │ 4324182021466249494 │ 5 │ 146 │ -1 │ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` Créons une table `UAct` en utilisant `CollapsingMergeTree` : ```sql theme={null} CREATE TABLE UAct ( UserID UInt64, PageViews UInt8, Duration UInt8, Sign Int8 ) ENGINE = CollapsingMergeTree(Sign) ORDER BY UserID ``` Ensuite, nous allons insérer des données : ```sql theme={null} INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1) ``` ```sql theme={null} INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1),(4324182021466249494, 6, 185, 1) ``` Nous utilisons deux requêtes `INSERT` pour créer deux `parties de données` distinctes. Si nous insérons les données avec une seule requête, ClickHouse ne crée qu'une seule `partie de données` et n'effectuera jamais de fusion. Nous pouvons sélectionner les données à l'aide de : ```sql theme={null} SELECT * FROM UAct ``` ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ -1 │ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` Examinons les données renvoyées ci-dessus pour voir si le collapsing a eu lieu... Avec deux requêtes `INSERT`, nous avons créé deux parties de données. La requête `SELECT` a été exécutée dans deux threads, et nous avons obtenu les lignes dans un ordre aléatoire. Cependant, le collapsing **n'a pas eu lieu**, car il n'y a pas encore eu de fusion des parties de données, et ClickHouse fusionne les parties de données en arrière-plan à un moment imprévisible. Nous avons donc besoin d'une agrégation, que nous effectuons avec la fonction d'agrégation [`sum`](/docs/fr/reference/functions/aggregate-functions/sum) et la clause [`HAVING`](/docs/fr/reference/statements/select/having) : ```sql theme={null} SELECT UserID, sum(PageViews * Sign) AS PageViews, sum(Duration * Sign) AS Duration FROM UAct GROUP BY UserID HAVING sum(Sign) > 0 ``` ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┐ │ 4324182021466249494 │ 6 │ 185 │ └─────────────────────┴───────────┴──────────┘ ``` Si nous n’avons pas besoin d’agrégation et souhaitons forcer le collapsing, nous pouvons également utiliser le modificateur `FINAL` dans la clause `FROM`. ```sql theme={null} SELECT * FROM UAct FINAL ``` ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` Cette façon de sélectionner les données est moins efficace et n’est pas recommandée pour de grands volumes de données parcourues (des millions de lignes).
### Exemple d'une autre approche
Cette approche repose sur l'idée que les fusions ne prennent en compte que les champs clés. Dans la ligne "annulation", nous pouvons donc spécifier des valeurs négatives qui annulent l'effet de la version précédente de la ligne lors de la somme, sans utiliser la colonne `Sign`. Pour cet exemple, nous utiliserons les données d'exemple ci-dessous : ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 5 │ 146 │ 1 │ │ 4324182021466249494 │ -5 │ -146 │ -1 │ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` Pour cette approche, il est nécessaire de modifier les types de données de `PageViews` et `Duration` afin de pouvoir stocker des valeurs négatives. Nous modifions donc le type de ces colonnes, de `UInt8` à `Int16`, lorsque nous créons notre table `UAct` à l’aide de `collapsingMergeTree` : ```sql theme={null} CREATE TABLE UAct ( UserID UInt64, PageViews Int16, Duration Int16, Sign Int8 ) ENGINE = CollapsingMergeTree(Sign) ORDER BY UserID ``` Testons cette approche en insérant des données dans notre table. Pour des exemples ou de petites tables, cela reste toutefois acceptable : ```sql theme={null} INSERT INTO UAct VALUES(4324182021466249494, 5, 146, 1); INSERT INTO UAct VALUES(4324182021466249494, -5, -146, -1); INSERT INTO UAct VALUES(4324182021466249494, 6, 185, 1); SELECT * FROM UAct FINAL; ``` ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ``` ```sql theme={null} SELECT UserID, sum(PageViews) AS PageViews, sum(Duration) AS Duration FROM UAct GROUP BY UserID ``` ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┐ │ 4324182021466249494 │ 6 │ 185 │ └─────────────────────┴───────────┴──────────┘ ``` ```sql theme={null} SELECT COUNT() FROM UAct ``` ```text theme={null} ┌─count()─┐ │ 3 │ └─────────┘ ``` ```sql theme={null} OPTIMIZE TABLE UAct FINAL; SELECT * FROM UAct ``` ```text theme={null} ┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 4324182021466249494 │ 6 │ 185 │ 1 │ └─────────────────────┴───────────┴──────────┴──────┘ ```