Mettre à jour et supprimer des données ClickHouse

Bien que ClickHouse soit conçu pour des charges de travail analytiques à fort volume, il est possible, dans certains cas, de modifier ou de supprimer des données existantes. Ces opérations sont appelées « mutations » et s’exécutent à l’aide de la commande ALTER TABLE.

Utilisez la commande ALTER TABLE...UPDATE pour mettre à jour des lignes d’une table :

ALTER TABLE [<database>.]<table> UPDATE <column> = <expression> WHERE <filter_expr>

<expression> est la nouvelle valeur de la colonne pour laquelle <filter_expr> est satisfait. <expression> doit être du même type de données que la colonne ou pouvoir être convertie dans ce même type à l’aide de l’opérateur CAST. <filter_expr> doit renvoyer une valeur UInt8 (zéro ou non nulle) pour chaque ligne de données. Plusieurs instructions UPDATE <column> peuvent être combinées dans une seule commande ALTER TABLE, séparées par des virgules.

Exemples:

1. Une mutation comme celle-ci permet de mettre à jour visitor_ids en les remplaçant par de nouveaux identifiants à l’aide d’une recherche dans un dictionnaire:

   ALTER TABLE website.clicks
   UPDATE visitor_id = getDict('visitors', 'new_visitor_id', visitor_id)
   WHERE visit_date < '2022-01-01'

2. Modifier plusieurs valeurs dans une seule commande peut être plus efficace que d’exécuter plusieurs commandes:

   ALTER TABLE website.clicks
   UPDATE url = substring(url, position(url, '://') + 3), visitor_id = new_visit_id
   WHERE visit_date < '2022-01-01'

3. Les mutations peuvent être exécutées ON CLUSTER pour les tables shardées:

   ALTER TABLE clicks ON CLUSTER main_cluster
   UPDATE click_count = click_count / 2
   WHERE visitor_id ILIKE '%robot%'

Utilisez la commande ALTER TABLE pour supprimer des lignes :

ALTER TABLE [<database>.]<table> DELETE WHERE <filter_expr>

Le <filter_expr> doit renvoyer une valeur UInt8 pour chaque ligne.

Exemples

1. Supprimez tous les enregistrements dont une colonne figure dans un tableau de valeurs :

   ALTER TABLE website.clicks DELETE WHERE visitor_id in (253, 1002, 4277)

2. Que modifie cette requête ?

   ALTER TABLE clicks ON CLUSTER main_cluster DELETE WHERE visit_date < '2022-01-02 15:00:00' AND page_id = '573'

Consultez la page de documentation de l’instruction DELETE pour plus de détails.

Une autre option pour supprimer des lignes consiste à utiliser la commande DELETE FROM, appelée suppression légère. Les lignes supprimées sont immédiatement marquées comme telles et seront automatiquement exclues de toutes les requêtes suivantes ; vous n’avez donc pas besoin d’attendre la fusion des parts ni d’utiliser le mot-clé FINAL. Le nettoyage des données s’effectue de manière asynchrone en arrière-plan.

DELETE FROM [db.]table [ON CLUSTER cluster] [WHERE expr]

Par exemple, la requête suivante supprime toutes les lignes de la table hits dont la colonne Title contient le texte hello :

DELETE FROM hits WHERE Title LIKE '%hello%';

Quelques remarques sur les suppressions légères :

• Cette fonctionnalité est disponible uniquement pour la famille de moteurs de table MergeTree.
• Les suppressions légères sont synchrones par défaut et attendent que toutes les répliques aient traité la suppression. Ce comportement est contrôlé par le paramètre lightweight_deletes_sync.