Comment filtrer une table ClickHouse à l’aide d’une colonne de type tableau ?

Filtrer une table ClickHouse à l’aide d’une colonne de type tableau est une opération courante, et ClickHouse propose de nombreuses fonctions pour manipuler les colonnes de type tableau.

Dans cet article, nous allons nous concentrer sur le filtrage d’une table à l’aide d’une colonne de type tableau, mais la vidéo ci-dessous présente aussi de nombreuses autres fonctions liées aux tableaux :

Nous utiliserons ici l’exemple d’une table comportant deux colonnes, tags_string et tags_int, qui contiennent respectivement un tableau de chaînes de caractères et un tableau d’entiers.

• Créez une base de données et une table d’exemple.

CREATE DATABASE db1;

• Créer une table d’exemple

CREATE TABLE db1.tags_table
(
    `id` UInt64,
    `tags_string` Array(String),
    `tags_int` Array(UInt64),
)
ENGINE = MergeTree
ORDER BY id;

• Insérez des données d’exemple dans la table.

INSERT INTO db1.tags_table VALUES (1, ['tag1', 'tag2', 'tag3'], [1, 2, 3]), (2, ['tag2', 'tag3', 'tag4'], [2, 3, 4]), (3, ['tag1', 'tag3', 'tag5'], [1, 3, 5]);

Filtrez la table à l’aide de la fonction has(arr, elem) afin de renvoyer les lignes pour lesquelles le tableau arr contient l’élément elem.

Filtrez la table afin de renvoyer les lignes pour lesquelles le tableau tags_string contient l’élément tag1.

SELECT * FROM db1.tags_table WHERE has(tags_string, 'tag1');

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘

Utilisez la fonction hasAll(arr, elems) pour renvoyer les lignes dans lesquelles tous les éléments du tableau elems sont présents dans le tableau arr.

Filtrez la table pour renvoyer les lignes dans lesquelles tous les éléments du tableau tags_string sont présents dans le tableau ['tag1', 'tag2'].

SELECT * FROM db1.tags_table WHERE hasAll(tags_string, ['tag1', 'tag2']);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
└────┴────────────────────────┴──────────┘

Utilisez la fonction hasAny(arr, elems) pour renvoyer les lignes dans lesquelles au moins un élément du tableau elems est présent dans le tableau arr.

Filtrez la table pour renvoyer les lignes dans lesquelles au moins un élément du tableau tags_string est présent dans le tableau ['tag1', 'tag2'].

SELECT * FROM db1.tags_table WHERE hasAny(tags_string, ['tag1', 'tag2']);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  1 │ ['tag1','tag2','tag3'] │ [1,2,3]  │
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘

Nous pouvons utiliser une fonction lambda pour filtrer la table à l’aide de la fonction arrayExists(lambda, arr).

Filtrez la table pour renvoyer les lignes dans lesquelles au moins un élément du tableau tags_int est supérieur à 3.

SELECT * FROM db1.tags_table WHERE arrayExists(x -> x > 3, tags_int);

┌─id─┬─tags_string────────────┬─tags_int─┐
│  2 │ ['tag2','tag3','tag4'] │ [2,3,4]  │
│  3 │ ['tag1','tag3','tag5'] │ [1,3,5]  │
└────┴────────────────────────┴──────────┘