Moteur de table CollapsingMergeTree

Le moteur CollapsingMergeTree hérite de 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 de ce document.

Tous les paramètres de ce moteur de table, à l'exception du paramètre Sign, ont la même signification que dans 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.

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

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)

• Pour une description des paramètres de requête, voir description de la requête.
• Lors de la création d’une table CollapsingMergeTree, les mêmes clauses de requête sont requises que pour la création d’une table MergeTree.

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 :

┌──────────────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 :

┌──────────────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 :

┌──────────────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.

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.

Lorsque ClickHouse fusionne des 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 :

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

Considérons les données d’exemple suivantes :

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         5 │      146 │    1 │
│ 4324182021466249494 │         5 │      146 │   -1 │
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

Créons une table UAct en utilisant CollapsingMergeTree :

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 :

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1)

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.

Nous pouvons sélectionner les données à l'aide de :

SELECT * FROM UAct

┌──────────────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 et la clause HAVING :

SELECT
    UserID,
    sum(PageViews * Sign) AS PageViews,
    sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0

┌──────────────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.

SELECT * FROM UAct FINAL

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

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 :

┌──────────────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 :

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 :

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;

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘

SELECT
    UserID,
    sum(PageViews) AS PageViews,
    sum(Duration) AS Duration
FROM UAct
GROUP BY UserID

┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │         6 │      185 │
└─────────────────────┴───────────┴──────────┘

SELECT COUNT() FROM UAct

┌─count()─┐
│       3 │
└─────────┘

OPTIMIZE TABLE UAct FINAL;

SELECT * FROM UAct

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │         6 │      185 │    1 │
└─────────────────────┴───────────┴──────────┴──────┘