Moteur de table VersionedCollapsingMergeTree

Ce moteur :

Permet d’écrire rapidement des états d’objets qui changent en permanence.
Supprime les anciens états d’objet en arrière-plan. Cela réduit considérablement le volume de stockage.

Voir la section Collapsing pour plus de détails. Le moteur hérite de MergeTree et ajoute à l’algorithme de fusion des parties de données une logique de collapsing des lignes. VersionedCollapsingMergeTree remplit le même rôle que CollapsingMergeTree, mais utilise un algorithme de collapsing différent qui permet d’insérer les données dans n’importe quel ordre avec plusieurs threads. En particulier, la colonne Version aide à effectuer correctement le collapsing des lignes, même si elles sont insérées dans le mauvais ordre. À l’inverse, CollapsingMergeTree n’autorise qu’une insertion strictement consécutive.

Création d’une table

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(
    name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],
    name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],
    ...
) ENGINE = VersionedCollapsingMergeTree(sign, version)
[PARTITION BY expr]
[ORDER BY expr]
[SAMPLE BY expr]
[SETTINGS name=value, ...]

Pour une description des paramètres de requête, voir la description de la requête.

Paramètres du moteur

VersionedCollapsingMergeTree(sign, version)

Paramètre	Description	Type
`sign`	Nom de la colonne contenant le type de ligne : `1` correspond à une ligne d’« état », `-1` à une ligne d’« annulation ».	`Int8`
`version`	Nom de la colonne contenant la version de l’état de l’objet.	`Int`, `UInt`, `Date`, `Date32`, `DateTime` ou `DateTime64`

Clauses de requête

Lors de la création d’une table VersionedCollapsingMergeTree, les mêmes clauses sont requises que pour la création d’une table MergeTree.

Collapsing

Données

Supposons que vous deviez enregistrer des données qui évoluent en permanence pour un objet. Il est logique d’avoir une ligne par objet et de mettre cette ligne à jour à chaque changement. Cependant, l’opération de mise à jour est coûteuse et lente pour un SGBD, car elle oblige à réécrire les données dans le stockage. La mise à jour n’est pas adaptée si vous devez écrire les données rapidement, mais vous pouvez écrire les modifications d’un objet de façon séquentielle, comme suit. Utilisez la colonne Sign lors de l’écriture de la ligne. Si Sign = 1, cela signifie que la ligne représente l’état d’un objet (appelons-la la ligne d’« état »). Si Sign = -1, cela indique l’annulation de l’état d’un objet ayant les mêmes attributs (appelons-la la ligne d’« annulation »). Utilisez également la colonne Version, qui doit identifier chaque état d’un objet par un numéro distinct. Par exemple, nous voulons calculer combien de pages les utilisateurs ont consultées sur un site donné et combien de temps ils y sont restés. À un moment donné, nous écrivons la ligne suivante avec l’état de l’activité de l’utilisateur :

┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │         5 │      146 │    1 │       1 |
└─────────────────────┴───────────┴──────────┴──────┴─────────┘

À un stade ultérieur, nous enregistrons le changement d’activité de l’utilisateur et l’écrivons dans les deux lignes suivantes.

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

La première ligne annule l’état précédent de l’objet (user). Elle doit recopier tous les champs de l’état annulé, sauf Sign. La deuxième ligne contient l’état actuel. Comme nous n’avons besoin que du dernier état de l’activité de l’utilisateur, les lignes

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

peut être supprimé, ce qui élimine l’état invalide (ancien) de l’objet. VersionedCollapsingMergeTree le fait lors de la fusion des parties de données. Pour comprendre pourquoi nous avons besoin de deux lignes pour chaque modification, consultez Algorithm. Remarques sur l’utilisation

Le programme qui écrit les données doit mémoriser l’état d’un objet pour pouvoir l’annuler. La chaîne “Annulation” doit contenir des copies des champs de la clé primaire, de la version de la chaîne “état” et du Sign opposé. Cela augmente la taille initiale du stockage, mais permet d’écrire les données rapidement.
Les tableaux qui s’allongent dans les colonnes réduisent l’efficacité du moteur en raison de la charge d’écriture. Plus les données sont simples, meilleure est l’efficacité.
Les résultats de SELECT dépendent fortement de la cohérence de l’historique des modifications de l’objet. Soyez précis lors de la préparation des données à insérer. Des données incohérentes peuvent produire des résultats imprévisibles, comme des valeurs négatives pour des métriques non négatives telles que la profondeur de session.

Algorithme

Lorsque ClickHouse fusionne des parties de données, il supprime chaque paire de lignes ayant la même clé primaire, la même version et un Sign différent. L’ordre des lignes n’a pas d’importance. Lorsque ClickHouse insère des données, il trie les lignes selon la clé primaire. Si la colonne Version ne fait pas partie de la clé primaire, ClickHouse l’y ajoute implicitement comme dernier champ et l’utilise pour le tri.

Sélection des données

ClickHouse ne garantit pas que toutes les lignes ayant la même clé primaire se retrouveront dans la même part de données résultante, ni même sur le même serveur physique. Cela vaut aussi bien pour l’écriture des données que pour la fusion ultérieure des parts de données. De plus, ClickHouse traite les requêtes SELECT avec plusieurs threads et ne peut pas prédire l’ordre des lignes dans le résultat. Cela signifie qu’une agrégation est nécessaire si vous devez obtenir des données entièrement « collapsées » à partir d’une table VersionedCollapsingMergeTree. Pour finaliser le collapsing, écrivez une requête avec une clause GROUP BY et des fonctions d’agrégation qui tiennent compte du signe. Par exemple, pour calculer une quantité, utilisez sum(Sign) au lieu de count(). Pour calculer une somme, utilisez sum(Sign * x) au lieu de sum(x), et ajoutez HAVING sum(Sign) > 0. 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 VersionedCollapsingMergeTree n’enregistre pas l’historique des valeurs des états collapsés. Si vous devez extraire les données avec « collapsing », mais sans agrégation (par exemple, pour vérifier si des lignes sont présentes dont les valeurs les plus récentes correspondent à certaines conditions), vous pouvez utiliser le modificateur FINAL dans la clause FROM. Cette approche est inefficace et ne doit pas être utilisée avec de grandes tables.

Exemple d’utilisation

Données d’exemple :

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

Création de la table :

CREATE TABLE UAct
(
    UserID UInt64,
    PageViews UInt8,
    Duration UInt8,
    Sign Int8,
    Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID

Insertion des données :

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

INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1, 1),(4324182021466249494, 6, 185, 1, 2)

Nous utilisons deux requêtes INSERT pour créer deux parties de données différentes. Si nous insérons les données avec une seule requête, ClickHouse crée une seule partie de données et n’effectuera jamais de merge. Récupération des données :

SELECT * FROM UAct

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

Que voyons-nous ici, et où sont les parties de données après collapsing ? Nous avons créé deux parties de données à l’aide de deux requêtes INSERT. La requête SELECT a été exécutée dans deux threads, et le résultat est un ordre aléatoire des lignes. Le collapsing ne s’est pas produit, car les parties de données n’ont pas encore été fusionnées. ClickHouse fusionne les parties de données à un moment que nous ne pouvons pas prévoir. C’est pourquoi nous avons besoin d’une agrégation :

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

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

Si nous n’avons pas besoin d’agrégation et que nous voulons forcer le collapsing, nous pouvons utiliser le modificateur FINAL dans la clause FROM.

SELECT * FROM UAct FINAL

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

Il s’agit d’une méthode très inefficace pour extraire des données. Ne l’utilisez pas avec des tables volumineuses.

​Création d’une table

​Paramètres du moteur

​Clauses de requête

​Collapsing

​Données

​Algorithme

​Sélection des données

​Exemple d’utilisation