Recherche en texte intégral avec des index de texte

1: The cat likes mice.
2: Mice are afraid of dogs.
3: I have two dogs and a cat.

1: The, cat, likes, mice
2: Mice, are, afraid, of, dogs
3: I, have, two, dogs, and, a, cat

1: the, cat, likes, mice
2: mice, are, afraid, of, dogs
3: i, have, two, dogs, and, a, cat

1: cat, likes, mice
2: mice, afraid, dogs
3: have, two, dogs, cat

afraid : [2]
cat    : [1, 3]
dogs   : [2, 3]
have   : [3]
likes  : [1]
mice   : [1]
two    : [3]

​

Création d’un index de texte

CREATE TABLE table
(
    key UInt64,
    str String,
    INDEX text_idx str TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | splitByRegexp(regexp[, match_tokens])
                                            | asciiCJK
                                            | chinese[(granularity)]
                                            | icu(locale)
                                            | japanese
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                            | keyValuePairs
                                -- Optional parameters:
                                [, preprocessor = expression(str)]
                                [, postprocessor = expression(str)]
                                [, support_phrase_search = 0 | 1 ] -- experimental
                                -- Optional advanced parameters:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' | 'pfor' ]
                            )
)
ENGINE = MergeTree
ORDER BY key

• String et FixedString,
• Array(String) et Array(FixedString),
• Map, soit sur la colonne Map à l’aide du tokenizer keyValuePairs, soit uniquement sur les clés ou les valeurs de la map à l’aide de la fonction mapKeys ou mapValues, et
• JSON (à l’aide des fonctions JSONAllPaths et JSONAllValues).

ALTER TABLE table
    ADD INDEX text_idx str TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | splitByRegexp(regexp[, match_tokens])
                                            | asciiCJK
                                            | chinese[(granularity)]
                                            | icu(locale)
                                            | japanese
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                            | keyValuePairs
                                -- Optional parameters:
                                [, preprocessor = expression(str)]
                                [, postprocessor = expression(str)]
                                [, support_phrase_search = 0 | 1 ] -- experimental
                                -- Optional advanced parameters:
                                [, dictionary_block_size = D]
                                [, dictionary_block_frontcoding_compression = B]
                                [, posting_list_block_size = C]
                                [, posting_list_codec = 'none' | 'bitpacking' | 'pfor' ]
                            )

ALTER TABLE table MATERIALIZE INDEX text_idx SETTINGS mutations_sync = 2;

ALTER TABLE table DROP INDEX text_idx;

• splitByNonAlpha divise les chaînes selon les caractères ASCII non alphanumériques (voir la fonction splitByNonAlpha).
• splitByString(S) divise les chaînes à l’aide de certaines chaînes séparatrices S définies par l’utilisateur (voir la fonction splitByString). Les séparateurs peuvent être spécifiés à l’aide d’un paramètre facultatif, par exemple tokenizer = splitByString([', ', '; ', '\n', '\\']). Notez que chaque chaîne peut être composée de plusieurs caractères (', ' dans l’exemple). La liste de séparateurs par défaut, si elle n’est pas explicitement spécifiée (par exemple tokenizer = splitByString), est un seul espace [' '].
• splitByRegexp(regexp[, match_tokens]) divise les chaînes selon une expression régulière regexp définie par l’utilisateur. L’argument regexp est obligatoire, par exemple tokenizer = splitByRegexp('[a-zA-Z]+[0-9]'). L’argument facultatif match_tokens (false par défaut ; 0/1 sont également acceptés) détermine ce que représente regexp :
  • avec match_tokens = false (la valeur par défaut), regexp représente des séparateurs : les tokens sont les portions de texte (non vides) situées entre deux correspondances successives (même sémantique que la fonction splitByRegexp).
  • avec match_tokens = true, regexp fait au contraire l’objet d’une correspondance directe : chaque correspondance produit au plus un token — son premier groupe de capture, ou la correspondance entière si regexp ne comporte aucun groupe de capture — et tout ce qui se trouve en dehors des correspondances est ignoré. Par exemple, tokenizer = splitByRegexp('tag:(\w+)', true) indexe hello et world à partir de tag:hello tag:world, en supprimant le préfixe tag:. La correspondance s’appuie sur RE2, le même moteur que celui utilisé par toutes les fonctions fondées sur les expressions régulières dans ClickHouse ; seul le premier groupe de capture est utilisé, les groupes suivants ne font donc que restreindre ce qui correspond. Un groupe de capture n’ayant pas participé à la correspondance, ou ayant correspondu à une chaîne vide, ne produit aucun token, et le parcours reprend toujours après la correspondance complète plutôt qu’après le span capturé (les correspondances ne se chevauchent donc jamais). Avec match_tokens = true, notez que les needles de recherche des fonctions de recherche de texte sont tokenisés avec le tokenizer propre à l’index : un needle sous forme de chaîne simple qui ne correspond pas lui-même à regexp ne produit donc aucun token, et par conséquent aucun résultat. Les needles transmis sous forme de tableaux aux fonctions de recherche de texte ne sont pas tokenisés ; leur utilisation est donc recommandée avec les tokenizers regexp.
• asciiCJK divise les chaînes en tokens selon les règles de délimitation des mots Unicode (comme dans Unicode Text Segmentation (UAX #29)). Les caractères ASCII alphanumériques et les traits de soulignement forment des tokens avec des connecteurs (ASCII : pour les lettres, . et ' pour les caractères de même type). Les caractères Unicode non ASCII, y compris les caractères CJK, deviennent des tokens d’un seul caractère.
• chinese[(granularity)] segmente le texte chinois en mots à l’aide d’un dictionnaire et d’un modèle de Markov caché (l’algorithme suit jieba ; les données du dictionnaire et du modèle intégrés sont dérivées de cppjieba). Contrairement à asciiCJK, qui traite chaque caractère non ASCII comme un token d’un seul caractère, chinese regroupe les caractères chinois consécutifs en mots (par exemple, 北京大学 devient un seul token 北京大学 plutôt que quatre tokens d’un seul caractère). Cela produit des tokens plus significatifs pour le texte chinois et améliore la qualité de recherche. Pour le texte général ou mixte, utilisez asciiCJK ; pour le texte exclusivement chinois, utilisez chinese. L’argument facultatif granularity est soit 'coarse_grained' (la valeur par défaut s’il n’est pas spécifié), soit 'fine_grained'. Ce dernier répertorie également les sous-mots qui se chevauchent ; par exemple, 北京邮电大学 produit également 北京, 邮电, 大学. La tokenisation fine améliore le rappel au prix d’un index plus volumineux. Recherchez dans un index de texte chinese avec hasAnyTokens / hasAllTokens (qui tokenisent le needle avec le tokenizer chinese), et non avec hasToken (qui divise uniquement selon les séparateurs ASCII).
• icu(locale) divise les chaînes en tokens de mots à l’aide de la segmentation des mots Unicode (UAX #29) de la bibliothèque ICU. Pour les écritures qui ne séparent pas les mots par des espaces (p. ex. le chinois, le japonais, le thaï), ICU applique une segmentation fondée sur un dictionnaire : contrairement à asciiCJK, ce texte est donc divisé en mots significatifs de plusieurs caractères plutôt qu’en caractères uniques. Ici, « dictionnaire » désigne les listes de mots intégrées d’ICU pour ces écritures (voir brkitr/dictionaries) ; ICU choisit le découpage le plus probable parmi ces mots. locale correspond aux paramètres régionaux ICU transmis au segmenteur ; la segmentation est principalement déterminée par l’écriture et le dictionnaire, et les paramètres régionaux sélectionnent les adaptations propres à ICU. Il s’agit d’un paramètre obligatoire, par exemple tokenizer = icu('ja') ou tokenizer = icu('zh'). Les paramètres régionaux disponibles peuvent être répertoriés avec SELECT * FROM system.collations.
• japanese divise le texte japonais en mots à l’aide de l’analyseur morphologique MeCab. Contrairement à asciiCJK, qui produit des tokens d’un seul caractère pour les entrées CJK, ce tokenizer effectue une segmentation correcte des mots. Il nécessite un dictionnaire chargé à l’exécution à partir de la configuration du serveur (voir Dictionnaire du tokenizer japonais).
• ngrams(N) divise les chaînes en N-grammes de taille identique (voir la fonction ngrams). La longueur des n-grammes peut être spécifiée à l’aide d’un paramètre entier facultatif compris entre 1 et 8, par exemple tokenizer = ngrams(3). La taille des n-grammes par défaut, si elle n’est pas explicitement spécifiée (par exemple tokenizer = ngrams), est de 3.
• sparseGrams(min_length, max_length, min_cutoff_length) divise les chaînes en n-grammes de longueur variable d’au moins min_length et d’au plus max_length caractères (bornes incluses) (voir la fonction sparseGrams). Sauf indication explicite, min_length et max_length valent par défaut 3 et 100. Si le paramètre min_cutoff_length est fourni, seuls les n-grammes dont la longueur est supérieure ou égale à min_cutoff_length sont renvoyés. Comparé à ngrams(N), le tokenizer sparseGrams produit des N-grammes de longueur variable, ce qui permet une représentation plus souple du texte d’origine. Par exemple, tokenizer = sparseGrams(3, 5, 4) génère en interne des 3-, 4- et 5-grammes à partir de la chaîne d’entrée, mais seuls les 4- et 5-grammes sont renvoyés.
• array ne réalise aucune tokenisation, c.-à-d. que chaque valeur de ligne constitue un token (voir la fonction array). Pour assurer la compatibilité avec d’autres systèmes, keyword est disponible comme alias de array.
• keyValuePairs combine les key-value pairs d’une colonne Map en un seul token. Cela permet d’effectuer des lookups combinés clé-valeur tels que WHERE map['key'] = 'value' (voir le tokenizer keyValuePairs).

​

Dictionnaire du tokenizer japonais

<tokenizer>
    <japanese>
        <dictionary_location>https://example.com/dictionary.tar.zst</dictionary_location>
        <dictionary_sha>0123...cdef</dictionary_sha>
    </japanese>
</tokenizer>

• dictionary_location correspond à l’emplacement d’une archive d’un dictionnaire MeCab compilé. Tout dictionnaire officiel convient, comme IPADIC ou UniDic. L’emplacement doit se terminer par une extension d’archive prise en charge (par exemple .tar.gz, .tar.zst ou .zip) — le type d’archive est déterminé à partir de celle-ci —, donc une URL sans une telle extension (p. ex. https://example.com/download) est rejetée. Emplacements pris en charge :
  • un chemin local file:// ;
  • une URL http(s)://, récupérée comme téléchargement simple (utilisez-la pour un objet public ou pré-signé) ;
  • un stockage d’objets compatible S3 — AWS S3, GCS, MinIO, sur site, etc. (pas uniquement AWS) — adressé sous la forme s3:///gs:///oss:// ou d’une URL complète http(s)://endpoint/bucket/key. Pour un bucket privé, fournissez les informations d’identification S3 en tant qu’éléments enfants de <japanese> (voir l’exemple ci-dessous).
• dictionary_sha correspond au SHA-256 de cette archive. Il est vérifié avant le chargement du dictionnaire ; en cas de non-correspondance, le dictionnaire n’est pas chargé et une erreur est générée.

<tokenizer>
    <japanese>
        <dictionary_location>s3://my-bucket/dictionary.tar.zst</dictionary_location>
        <dictionary_sha>0123...cdef</dictionary_sha>
        <access_key_id>...</access_key_id>
        <secret_access_key>...</secret_access_key>
        <region>us-east-1</region>
    </japanese>
</tokenizer>

CREATE TABLE docs
(
    id UInt64,
    body String,
    INDEX body_idx body TYPE text(tokenizer = 'japanese')
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO docs VALUES (1, '日本語の形態素解析エンジン'), (2, 'これはテストの文章です');

SELECT id FROM docs WHERE hasAllTokens(body, '形態 解析', 'japanese') ORDER BY id;

┌─id─┐
│  1 │
└────┘

SELECT tokens('abc def', 'ngrams', 3);

['abc','bc ','c d',' de','def']

1. Conversion en minuscules/majuscules, ou normalisation de la casse pour permettre une correspondance insensible à la casse, par ex., lower, lowerUTF8, caseFoldUTF8.
2. Normalisation UTF-8, par ex. normalizeUTF8NFC, normalizeUTF8NFD, normalizeUTF8NFKC, normalizeUTF8NFKD, normalizeUTF8NFKCCasefold, toValidUTF8.
3. Suppression ou transformation de caractères ou de sous-chaînes indésirables, comme les accents, par ex. extractTextFromHTML, substring, idnaEncode, translate, removeDiacriticsUTF8.

• INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(col))
• INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = substringIndex(col, '\n', 1))
• INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(extractTextFromHTML(col)))
• INDEX idx col TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = removeDiacriticsUTF8(caseFoldUTF8(col)))

• INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = upper(lower(col)))
• INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(lower(col), lower(col)))
• Non autorisé : INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(col, col))

CREATE TABLE table
(
    str String,
    INDEX idx str TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(str))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, 'Foo');

CREATE TABLE table
(
    str String,
    INDEX idx lower(str) TYPE text(tokenizer = 'splitByNonAlpha')
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM table WHERE hasToken(str, lower('Foo'));

CREATE TABLE table
(
    arr Array(String),
    INDEX idx arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(arr))

    -- This is not legal:
    INDEX idx_illegal arr TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = arraySort(arr))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(arr, 'foo');

CREATE TABLE table
(
    map Map(String, String),
    INDEX idx mapKeys(map)  TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = lower(mapKeys(map)))
)
ENGINE = MergeTree
ORDER BY tuple();

SELECT count() FROM tab WHERE hasAllTokens(mapKeys(map), 'foo');

1. Filtrage des stop words (tokens extrêmement fréquents). Les tokens très courants tels que “the”, “a” et “is” ont peu de pertinence pour la recherche et alourdissent l’index. Vous pouvez utiliser le postprocesseur pour les éliminer en les convertissant en tokens vides — les tokens vides sont ignorés, c’est-à-dire qu’ils ne sont pas ajoutés à l’index. Exemple : if(str IN ('the', 'a', 'an', 'of', 'in', 'is', 'it'), '', str)
2. Suppression des timestamps. Les lignes de log commencent souvent par un timestamp structuré tel que 2024-01-15T10:23:45, ou en contiennent un. L’indexation des tokens de timestamp gonfle l’index avec des chaînes qui n’ont aucune pertinence pour la recherche. Il existe deux approches complémentaires pour ignorer les timestamps :
   • Approche avec postprocesseur : utilisez le tokenizer splitByString (découpage par espaces) afin que le timestamp entier devienne un seul token, puis utilisez parseDateTimeOrNull pour le détecter et le supprimer. Exemple : if(isNull(parseDateTimeOrNull(str, '%Y-%m-%dT%H:%i:%S')), str, '') Pour les timestamps avec décalages de fuseau horaire ou secondes fractionnaires, utilisez parseDateTimeBestEffortOrNull(str) sans chaîne de format explicite.
   • Approche avec préprocesseur : retirez le timestamp de la ligne de log complète avant la tokenisation à l’aide d’une regular expression. Exemple : replaceRegexpAll(str, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '') Cela fonctionne avec n’importe quel tokenizer et est plus efficace, car les caractères du timestamp ne sont jamais tokenized. Les deux approches peuvent être combinées : le préprocesseur retire le timestamp tandis que le postprocesseur normalise ou filtre les tokens restants (par exemple, passage en minuscules + suppression des mots de sévérité comme ERROR ou INFO).
3. Racinisation. Ramener chaque token à sa racine améliore le rappel de recherche en faisant correspondre des variantes morphologiques qui partagent la même racine. Par exemple, avec la racinisation anglaise, “running”, “runs” et “run” sont tous ramenés à “run”, de sorte qu’une requête sur l’une de ces variantes correspond à toutes. ClickHouse fournit une fonction stem intégrée pour plusieurs langues. Exemple : stem(str, 'en')
4. Normalisation de la casse. Conversion des tokens en minuscules ou en majuscules pour permettre une correspondance insensible à la casse, par exemple lower, lowerUTF8. Pour la conversion en minuscules et en majuscules, nous recommandons un préprocesseur plutôt qu’un postprocesseur.

• Pour hasToken, hasAllTokens, hasAnyTokens et hasPhrase (avec n’importe quel tokenizer pris en charge) : le postprocesseur est appliqué à la fois aux tokens du haystack et au needle de recherche, ce qui permet une correspondance entièrement normalisée (par exemple, une recherche insensible à la casse). Pour hasPhrase, les tokens post-traités sont positionnés de manière dense : un token supprimé par le postprocesseur ne laisse donc aucun écart de position et la phrase correspond toujours à travers celui-ci — par exemple, avec un postprocesseur de stop words qui supprime the, hasPhrase(col, 'see cat') correspond à un document see the cat. La seule exception est hasPhrase sur un index splitByRegexp, qui ne prend pas en charge de postprocesseur (la combinaison est rejetée avec une exception).
• Pour toutes les autres fonctions (=, IN, has, hasAny, hasAll, mapContains*) : seul le needle de recherche est post-traité pour la recherche par indication d’index ; le prédicat au niveau des lignes compare toujours les valeurs de colonne d’origine.

• Supprimez les stop words à l’aide d’une expression de postprocesseur :

CREATE TABLE table
(
    str String,
    INDEX idx(str) TYPE text(
        tokenizer = 'splitByNonAlpha',
        postprocessor = if(str IN ('the', 'a', 'an', 'of', 'in', 'is', 'it'), '', str)
    )
)
ENGINE = MergeTree
ORDER BY tuple();

• Supprimez les timestamps à l’aide d’une expression de postprocesseur :

-- Log lines: '2024-01-15T10:23:45 ERROR connection failed'
-- The splitByString tokenizer (default: whitespace) keeps the full timestamp as one token.
-- parseDateTimeOrNull detects and drops it; non-timestamp words are kept.
CREATE TABLE logs
(
    id   UInt64,
    line String,
    INDEX idx(line) TYPE text(
        tokenizer    = 'splitByString',
        postprocessor = if(isNull(parseDateTimeOrNull(line, '%Y-%m-%dT%H:%i:%S')), line, '')
    )
)
ENGINE = MergeTree ORDER BY id;

-- Only message-level words are indexed; timestamp tokens are not stored.
SELECT count() FROM logs WHERE hasAllTokens(line, ['ERROR']);       -- fast index lookup
SELECT count() FROM logs WHERE hasAllTokens(line, ['2024-01-15T10:23:45']);  -- returns 0: token was never indexed

• Supprimez les horodatages à l’aide d’une expression de préprocesseur :

-- The preprocessor strips the ISO timestamp prefix before tokenization.
-- Any tokenizer can be used; timestamp characters are never seen by the tokenizer.
CREATE TABLE logs
(
    id   UInt64,
    line String,
    INDEX idx(line) TYPE text(
        tokenizer   = 'splitByNonAlpha',
        preprocessor = replaceRegexpAll(line, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '')
    )
)
ENGINE = MergeTree ORDER BY id;

• Supprimez les horodatages au moyen d’une expression combinant un préprocesseur et un postprocesseur :

-- Preprocessor strips the timestamp, then lowercases the remainder.
-- Postprocessor drops the severity word (error, info, warn, debug) after tokenization.
-- Result: only substantive message words are stored in the index.
CREATE TABLE logs
(
    id   UInt64,
    line String,
    INDEX idx(line) TYPE text(
        tokenizer    = 'splitByNonAlpha',
        preprocessor = lower(replaceRegexpAll(line, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '')),
        postprocessor = if(line IN ('error', 'info', 'warn', 'warning', 'debug', 'critical'), '', line)
    )
)
ENGINE = MergeTree ORDER BY id;

-- Example log line: '2024-01-15T10:23:45 ERROR connection failed'
-- After preprocessor:  'error connection failed'
-- After tokenization:  ['error', 'connection', 'failed']
-- After postprocessor: ['connection', 'failed']   ← 'error' dropped as severity word
SELECT count() FROM logs WHERE hasAllTokens(line, ['connection']);

• Réduisez les tokens à leur racine à l’aide d’une expression de postprocesseur :

CREATE TABLE table
(
    str String,
    INDEX idx(str) TYPE text(
        tokenizer = 'splitByNonAlpha',
        postprocessor = stem(str, 'en')
    )
)
ENGINE = MergeTree
ORDER BY tuple();

-- The query token 'running' is stemmed to 'run' before the lookup,
-- matching rows that contain 'run', 'runs', 'ran', 'running', etc.
SELECT count() FROM table WHERE hasAllTokens(str, ['running']);

CREATE TABLE table(
    k UInt64,
    s String,
    INDEX idx s TYPE text(tokenizer = ngrams(2)))
ENGINE = MergeTree()
ORDER BY k;

SHOW CREATE TABLE table;

┌─statement──────────────────────────────────────────────────────────────┐
│ CREATE TABLE default.table                                            ↴│
│↳(                                                                     ↴│
│↳    `k` UInt64,                                                       ↴│
│↳    `s` String,                                                       ↴│
│↳    INDEX idx s TYPE text(tokenizer = ngrams(2)) GRANULARITY 100000000↴│ <-- here
│↳)                                                                     ↴│
│↳ENGINE = MergeTree                                                    ↴│
│↳ORDER BY k                                                            ↴│
│↳SETTINGS index_granularity = 8192                                      │
└────────────────────────────────────────────────────────────────────────┘

​

Utiliser un index de texte

​

Fonctions prises en charge

SELECT [...]
FROM [...]
WHERE string_search_function(column_with_text_index)

​

=

SELECT * from table WHERE str = 'Hello';

​

IN

SELECT * from table WHERE str IN ('Hello', 'World');

​

LIKE et match

SELECT count() FROM table WHERE comment LIKE 'support%';

SELECT count() FROM table WHERE comment LIKE ' support %'; -- or `% support %`

​

multiSearchAny and multiMatchAny

SELECT count() FROM table WHERE multiSearchAny(comment, ['clickhouse', 'support']);

SELECT count() FROM table WHERE multiSearchAny(comment, [' clickhouse ', ' support ']);

​

startsWith et endsWith

SELECT count() FROM table WHERE startsWith(comment, 'clickhouse support');

startsWith(comment, 'clickhouse supports ')`

SELECT count() FROM table WHERE endsWith(comment, ' olap engine');

​

hasToken

SELECT count() FROM table WHERE hasToken(comment, 'clickhouse');

​

hasAnyTokens and hasAllTokens

-- Search tokens passed as string argument
SELECT count() FROM table WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM table WHERE hasAllTokens(comment, 'clickhouse olap');

-- Search tokens passed as Array(String)
SELECT count() FROM table WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

​

hasPhrase

-- Matches: 'clickhouse' and 'olap' must appear consecutively in that order
SELECT count() FROM table WHERE hasPhrase(comment, 'clickhouse olap');

-- Does NOT match a row containing 'olap clickhouse' (wrong order)
-- Does NOT match a row containing 'clickhouse fast olap' (non-consecutive)

​

has

SELECT count() FROM table WHERE has(array, 'clickhouse');

​

hasAny et hasAll

SELECT count() FROM table WHERE hasAny(tags, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAll(tags, ['clickhouse', 'olap']);

​

mapContains

SELECT count() FROM table WHERE mapContainsKey(map, 'clickhouse');
-- OR
SELECT count() FROM table WHERE mapContains(map, 'clickhouse');

​

mapContainsValue

SELECT count() FROM table WHERE mapContainsValue(map, 'clickhouse');

​

mapContainsKeyLike et mapContainsValueLike

SELECT count() FROM table WHERE mapContainsKeyLike(map, '% clickhouse %');
SELECT count() FROM table WHERE mapContainsValueLike(map, '% clickhouse %');

​

operator[]

SELECT count() FROM table WHERE map['engine'] = 'clickhouse';

​

Indexation des colonnes Array(String)

CREATE TABLE posts
(
    post_id UInt64,
    title String,
    content String,
    keywords Array(String)
)
ENGINE = MergeTree
ORDER BY (post_id);

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- slow full-table scan - checks every keyword in every post

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- Don't forget to rebuild the index for existing data

​

Indexation des colonnes de type Map

CREATE TABLE logs
(
    id UInt64,
    timestamp DateTime,
    message String,
    attributes Map(String, String)
)
ENGINE = MergeTree
ORDER BY (timestamp);

-- Finds all logs with rate limiting data:
SELECT * FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- slow full-table scan

-- Finds all logs from a specific IP:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- slow full-table scan

​

Recherches clé-valeur combinées avec le tokenizer keyValuePairs

ALTER TABLE logs ADD INDEX attributes_kv_idx attributes TYPE text(tokenizer = keyValuePairs);
ALTER TABLE logs MATERIALIZE INDEX attributes_kv_idx;

-- Finds all error logs:
SELECT * FROM logs WHERE attributes['level'] = 'error'; -- fast, answered from the index alone

• L’index doit être créé sur un Map dont les clés et les valeurs sont de type String ou LowCardinality(String). FixedString est rejeté car la colonne stocke les octets de complétion alors que la constante recherchée ne les contient pas, ce qui conduirait à ignorer silencieusement des lignes. Nullable est rejeté car l’encodage ne permet pas de distinguer une valeur vide de NULL.
• Les arguments preprocessor, postprocessor et support_phrase_search sont rejetés à la création de la table.
• Seul = sur un élément de map est pour l’instant résolu à partir de l’index. Les autres recherches sur map, telles que mapContainsKey, mapContainsValue, leurs variantes *Like et IN, basculent vers un balayage exhaustif.
• map['key'] renvoie la valeur par défaut du type de la valeur lorsque la clé est absente, si bien que map['key'] = '' vaut également true pour les lignes qui ne contiennent pas la clé et n’ont donc aucun token. Un tel prédicat bascule vers un balayage exhaustif.
• Si une ligne contient plusieurs fois la même clé, map['key'] correspond à la valeur de sa première occurrence, et l’index apparie cette occurrence.

​

Recherche de clés et de valeurs avec mapKeys et mapValues

ALTER TABLE logs ADD INDEX attributes_keys_idx mapKeys(attributes) TYPE text(tokenizer = array);
ALTER TABLE logs MATERIALIZE INDEX attributes_keys_idx;

ALTER TABLE logs ADD INDEX attributes_vals_idx mapValues(attributes) TYPE text(tokenizer = array);
ALTER TABLE logs MATERIALIZE INDEX attributes_vals_idx;

-- Find all rate-limited requests:
SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast

-- Finds all logs from a specific IP:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast

-- Finds all logs where any attribute includes an error:
SELECT * FROM logs WHERE mapContainsValueLike(attributes, '% error %'); -- fast

​

Indexation des colonnes JSON

1. Index sur des sous-colonnes spécifiques — créez un index de texte sur un chemin JSON connu, comme pour une colonne classique. Cela indexe les valeurs de ce chemin.
2. Index basés sur les chemins avec JSONAllPaths — indexent tous les chemins présents dans chaque granule afin d’ignorer les granules qui ne peuvent pas contenir le chemin recherché. Comme pour les colonnes Map.
3. Index basés sur les valeurs avec JSONAllValues — indexent toutes les valeurs de tous les chemins JSON afin d’accélérer la recherche en texte intégral sur n’importe quelle sous-colonne JSON avec un seul index.

​

Index sur des sous-colonnes spécifiques

• Chemin typé déclaré dans l’indication de type JSON — accès direct par son nom : json.a.
• Chemin dynamique avec conversion de type explicite — utilisez la syntaxe de cast :: : json.b::String.

CREATE TABLE sensor_data
(
    data JSON(sensor_id String),
    INDEX idx_sensor data.sensor_id TYPE text(tokenizer = splitByNonAlpha),
    INDEX idx_location data.location::String TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY tuple()
SETTINGS index_granularity = 1;

INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number , 'location', 'room_' || toString(number))) FROM numbers(4);
INSERT INTO sensor_data SELECT toJSONString(map('sensor_id', 'id_' || number, 'location', 'room_' || toString(number))) FROM numbers(4, 4);

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.sensor_id = 'id_5';

...
    Indexes:
      Skip
        Name: idx_sensor
        Description: text
        Condition: (mode: All; tokens: ["5", "id"])
        Parts: 1/2
        Granules: 1/8

EXPLAIN indexes = 1 SELECT * FROM sensor_data WHERE data.location::String = 'room_5';

...
    Indexes:
      Skip
        Name: idx_location
        Description: text
        Condition: (mode: All; tokens: ["5", "room"])
        Parts: 1/2
        Granules: 1/8

​

Index basés sur les chemins avec JSONAllPaths

CREATE TABLE events
(
    data JSON,
    INDEX idx JSONAllPaths(data) TYPE text(tokenizer = array)
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO events VALUES ('{"user": {"name": "Alice"}, "action": "login"}');
INSERT INTO events VALUES ('{"metric": {"cpu": 0.95}, "host": "srv1"}');

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name = 'Alice';

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.nonexistent = 1;

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["nonexistent"])
        Parts: 0/2
        Granules: 0/2

EXPLAIN indexes = 1 SELECT * FROM events WHERE data.user.name IS NOT NULL;

...
    Indexes:
      Skip
        Name: idx
        Description: text
        Condition: (mode: All; tokens: ["user.name"])
        Parts: 1/2
        Granules: 1/2

​

Index basés sur les valeurs avec JSONAllValues

Création de l’index

CREATE TABLE events
(
    id UInt64,
    data JSON,
    INDEX json_idx JSONAllValues(data) TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;

Types de requêtes pris en charge

SELECT * FROM events WHERE data.user_name = 'alice';
SELECT * FROM events WHERE data.message LIKE '% error %';
SELECT * FROM events WHERE startsWith(data.status, 'fail');
SELECT * FROM events WHERE hasToken(data.title, 'clickhouse');

SELECT * FROM events WHERE hasAllTokens(data.message::String, 'connection timeout');
SELECT * FROM events WHERE data.status_code::UInt64 = 404;
SELECT * FROM events WHERE has(data.tags::Array(String), 'bug')

SELECT * FROM events WHERE data.level IN ('error', 'critical');

​

Recherche d’expressions

SELECT *
FROM tab
WHERE hasAllTokens(col, 'weather in Tokyo')

SELECT *
FROM tab
WHERE hasPhrase(col, 'weather in Tokyo')

​

Exemple

CREATE TABLE tab (
    id UInt32,
    text String,
    INDEX idx text TYPE text(tokenizer = splitByNonAlpha)
)
ENGINE = MergeTree
ORDER BY id;

INSERT INTO tab VALUES
    (1, 'weather in New York'),
    (2, 'New weather in York'),
    (3, 'weather in New Orleans');

SELECT id, text FROM tab WHERE hasPhrase(text, 'weather in New York');

   ┌─id─┬─text────────────────┐
1. │  1 │ weather in New York │
   └────┴─────────────────────┘

​

Optimisation des performances

​

lecture directe

SELECT column_a, column_b, ...
FROM [...]
WHERE string_search_function(column_with_text_index)

• Le paramètre query_plan_direct_read_from_text_index (true par défaut) indique si la lecture directe est activée de manière générale.
• Le paramètre use_skip_indexes_on_data_read était un prérequis pour la lecture directe dans les versions de ClickHouse < 26.4.

EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 0, -- disable direct read

[...]
Filter ((WHERE + Change column names to column identifiers))
Filter column: hasToken(__table1.col, 'some_token'_String) (removed)
Actions: INPUT : 0 -> col String : 0
         COLUMN Const(String) -> 'some_token'_String String : 1
         FUNCTION hasToken(col :: 0, 'some_token'_String :: 1) -> hasToken(__table1.col, 'some_token'_String) UInt8 : 2
[...]

EXPLAIN PLAN actions = 1
SELECT count()
FROM table
WHERE hasToken(col, 'some_token')
SETTINGS query_plan_direct_read_from_text_index = 1, -- enable direct read

[...]
Expression (Before GROUP BY)
Positions:
  Filter
  Filter column: __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 (removed)
  Actions: INPUT :: 0 -> __text_index_idx_hasToken_94cc2a813036b453d84b6fb344a63ad3 UInt8 : 0
[...]

EXPLAIN actions = 1
SELECT count()
FROM table
WHERE (col LIKE '%some-token%') AND (d >= today())
SETTINGS query_plan_text_index_add_hint = 0
FORMAT TSV

[...]
Prewhere filter column: and(like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed)
[...]

EXPLAIN actions = 1
SELECT count()
FROM table
WHERE col LIKE '%some-token%'
SETTINGS query_plan_text_index_add_hint = 1

[...]
Prewhere filter column: and(__text_index_idx_col_like_d306f7c9c95238594618ac23eb7a3f74, like(__table1.col, \'%some-token%\'_String), greaterOrEquals(__table1.d, _CAST(20440_Date, \'Date\'_String))) (removed)
[...]

​

Requêtes LIKE/ILIKE

• %value% correspond à une ligne si et seulement si elle correspond à l’un des tokens de la ligne ; l’index tranche donc à lui seul la requête : une lecture directe (sans indice) qui supprime le prédicat d’origine.
• value% et %value ancrent le needle sur la valeur entière, alors que le dictionary parcours ne peut l’ancrer que sur un token ; le parcours renvoie donc un superset des lignes correspondantes et sert de lecture directe en tant qu’indice.

• use_text_index_like_evaluation_by_dictionary_scan

• text_index_like_min_pattern_length
• text_index_like_max_postings_to_read

• Le needle contient la lettre k. ilike traite U+212A KELVIN SIGN comme un k, mais le dictionary parcours compare des octets et manquerait une ligne écrite de cette façon. U+212A est le seul caractère que ilike replie sur une lettre ou un chiffre ASCII, c’est donc le seul caractère de needle concerné.
• L’expression de preprocessor de l’index contient lowerUTF8 ou upperUTF8. Celles-ci réécrivent les caractères non ASCII en lettres ASCII (ß en SS, ſ en S), ce qui amènerait l’index à signaler des lignes qui ne correspondent pas à ilike.

​

Requêtes de comptage simples

SELECT count()
FROM [...]
WHERE hasToken(column_with_text_index, 'token')
-- or: hasAllTokens(column_with_text_index, ['token', ...])
-- or: hasAnyTokens(column_with_text_index, ['token', ...])

EXPLAIN
SELECT count() FROM table WHERE hasToken(col, 'sometoken')

[...]
Aggregating
└──ReadFromTextIndexCount (Trivial count from text index (idx, token = 'sometoken'))

​

Mise en cache

​

Paramètres du cache des tokens

​

Paramètres du cache des en-têtes

​

Paramètres du cache des listes de postings

​

Limites

• La matérialisation d’index de texte comportant un grand nombre de tokens (par ex. 10 milliards de tokens) peut consommer une quantité importante de mémoire. La matérialisation d’un index de texte peut se produire directement (ALTER TABLE <table> MATERIALIZE INDEX <index>) ou indirectement lors des fusions de parts.
• Il n’est pas possible de matérialiser des index de texte sur des parts de plus de 4.294.967.296 (= 2^32 = env. 4,2 milliards) lignes. Sans index de texte matérialisé, les requêtes reviennent à une recherche brute-force lente dans la part. Dans le pire des cas, supposons qu’une part contienne une seule colonne de type String et que le paramètre MergeTree max_bytes_to_merge_at_max_space_in_pool (par défaut : 150 GB) n’ait pas été modifié. Dans ce cas, cela se produit si la colonne contient en moyenne moins de 29,5 caractères par ligne. En pratique, les tables contiennent aussi d’autres colonnes et le seuil est alors plusieurs fois plus faible (selon le nombre, le type et la taille des autres colonnes).

​

Notes de mise à niveau

​

Index de texte vs index basés sur des filtres de Bloom

• Reposent sur des structures de données probabilistes qui peuvent produire des faux positifs.
• Peuvent uniquement répondre à des questions d’appartenance à un ensemble, c.-à-d. déterminer si la colonne peut contenir le token X ou si elle ne contient certainement pas X.
• Stockent des informations au niveau des granules afin de permettre d’ignorer de larges plages lors de l’exécution d’une requête.
• Sont difficiles à paramétrer correctement (voir ici pour un exemple).
• Sont relativement compacts (quelques kilo-octets ou mégaoctets par part).

• Construisent un index inversé déterministe sur des tokens. L’index lui-même ne peut pas produire de faux positifs.
• Sont spécifiquement optimisés pour les charges de travail de recherche textuelle.
• Stockent des informations au niveau des lignes, ce qui permet une recherche de termes efficace.
• Sont relativement volumineux (de dizaines à des centaines de mégaoctets par part).

• Ils ne prennent pas en charge la tokenisation ni le prétraitement avancés.
• Ils ne prennent pas en charge la recherche sur plusieurs tokens.
• Ils n’offrent pas les performances attendues d’un index inversé.

• Ils fournissent la tokenisation et le prétraitement
• Ils prennent efficacement en charge hasAllTokens, LIKE, match et des fonctions de recherche textuelle similaires.
• Ils offrent une bien meilleure capacité de passage à l’échelle pour les grands corpus textuels.

​

Détails d’implémentation

• un dictionnaire qui associe chaque token à une liste de postings, et
• un ensemble de listes de postings, chacune représentant un ensemble de numéros de ligne.

​

Exemple : jeu de données Hacker News

CREATE TABLE hackernews (
    id UInt64,
    deleted UInt8,
    type String,
    author String,
    timestamp DateTime,
    comment String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    children Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32
)
ENGINE = MergeTree
ORDER BY (type, author);

INSERT INTO hackernews
    SELECT * FROM s3Cluster(
        'default',
        'https://datasets-documentation.s3.eu-west-3.amazonaws.com/hackernews/hacknernews.parquet',
        'Parquet',
        '
    id UInt64,
    deleted UInt8,
    type String,
    by String,
    time DateTime,
    text String,
    dead UInt8,
    parent UInt64,
    poll UInt64,
    kids Array(UInt32),
    url String,
    score UInt32,
    title String,
    parts Array(UInt32),
    descendants UInt32');

-- Add the index
ALTER TABLE hackernews ADD INDEX comment_idx comment TYPE text(tokenizer = splitByNonAlpha);

-- Materialize the index for existing data
ALTER TABLE hackernews MATERIALIZE INDEX comment_idx SETTINGS mutations_sync = 2;

​

1. Utilisation de hasToken

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

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

1 row in set. Elapsed: 0.362 sec. Processed 24.90 million rows, 9.51 GB

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

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

1 row in set. Elapsed: 0.008 sec. Processed 3.15 million rows, 3.15 MB

​

2. Utilisation de hasAnyTokens

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

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

1 row in set. Elapsed: 1.329 sec. Processed 28.74 million rows, 9.72 GB

SELECT count()
FROM hackernews
WHERE hasAnyTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

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

1 row in set. Elapsed: 0.015 sec. Processed 27.99 million rows, 27.99 MB

​

3. Utilisation de hasAllTokens

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

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

1 row in set. Elapsed: 0.184 sec. Processed 147.46 thousand rows, 57.03 MB

SELECT count()
FROM hackernews
WHERE hasAllTokens(comment, 'love ClickHouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

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

1 row in set. Elapsed: 0.007 sec. Processed 147.46 thousand rows, 147.46 KB

​

4. Recherche composée : OR, AND, NOT, …

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 0;

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

1 row in set. Elapsed: 0.450 sec. Processed 25.87 million rows, 9.58 GB

SELECT count()
FROM hackernews
WHERE hasToken(comment, 'ClickHouse') OR hasToken(comment, 'clickhouse')
SETTINGS query_plan_direct_read_from_text_index = 1;

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

1 row in set. Elapsed: 0.013 sec. Processed 25.87 million rows, 51.73 MB

​

Contenu connexe

• Blog : Annonce de la disponibilité générale de la recherche en texte intégral dans ClickHouse
• Blog : Concevoir une recherche en texte intégral haute performance pour le stockage objet
• Vidéo : Introduction à la recherche en texte intégral dans ClickHouse
• Vidéo : Sous le capot : la recherche en texte intégral à l’échelle et à la vitesse de ClickHouse
• Présentation : Dans les coulisses de la recherche en texte intégral de ClickHouse : rapide, native et columnaire
• Présentation : Index inversés de base de données : pourquoi, quoi et comment, FOSDEM 2026

• Blog : Présentation des index inversés dans ClickHouse
• Blog : Dans les coulisses de la recherche en texte intégral de ClickHouse : rapide, native et columnaire
• Vidéo : Index en texte intégral : conception et expérimentations