Busca de texto completo com índices de texto

Índices de texto (também conhecidos como índices invertidos) permitem realizar buscas de texto completo com rapidez em dados textuais. Um índice de texto armazena um mapeamento de tokens para os números das linhas que contêm cada token. Os tokens são gerados por um processo chamado tokenização. Por exemplo, o tokenizador padrão do ClickHouse converte a frase em inglês "The cat likes mice." nos tokens ["The", "cat", "likes", "mice"].

Como exemplo, considere uma tabela com uma única coluna e três linhas

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

Os tokens correspondentes são:

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

Normalmente, preferimos pesquisar sem diferenciar maiúsculas de minúsculas; por isso, convertemos os tokens para minúsculas:

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

Também removeremos palavras de preenchimento, como "I", "the" e "and", pois elas aparecem em quase todas as linhas:

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

Um índice de texto contém (conceitualmente) estas informações:

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

Dado um token de busca, essa estrutura de índice permite localizar rapidamente todas as linhas correspondentes.

Os índices de texto estão disponíveis de forma geral (GA) no ClickHouse 26.2 e versões mais recentes. Nessas versões, não é necessário configurar nenhuma opção especial para usar o índice de texto. Recomendamos fortemente usar versões do ClickHouse >= 26.2 em produção.

Para criar um índice de texto, use a seguinte sintaxe:

CREATE TABLE table
(
    key UInt64,
    str String,
    INDEX text_idx str TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | splitByRegexp(re)
                                            | asciiCJK
                                            | chinese[(granularity)]
                                            | icu(locale)
                                            | japanese
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- 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' ]
                            )
)
ENGINE = MergeTree
ORDER BY key

Índices de texto podem ser definidos em colunas dos seguintes tipos:

• String e FixedString,
• Array(String) e Array(FixedString),
• Map (por meio das funções mapKeys e mapValues), e
• JSON (por meio das funções JSONAllPaths e JSONAllValues).

Colunas do tipo Nullable(T) e LowCardinality() também são suportadas, incluindo Array(Nullable(String or FixedString)).

Como alternativa, para adicionar um índice de texto a uma tabela existente:

ALTER TABLE table
    ADD INDEX text_idx str TYPE text(
                                -- Mandatory parameters:
                                tokenizer = splitByNonAlpha
                                            | splitByString[(S)]
                                            | splitByRegexp(re)
                                            | asciiCJK
                                            | chinese[(granularity)]
                                            | icu(locale)
                                            | japanese
                                            | ngrams[(N)]
                                            | sparseGrams[(min_length[, max_length[, min_cutoff_length]])]
                                            | array
                                -- 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' ]
                            )

Se você adicionar um índice a uma tabela existente, recomendamos materializar o índice para as partes existentes da tabela (caso contrário, a busca nas partes sem índice recorrerá a varreduras lentas por força bruta).

ALTER TABLE table MATERIALIZE INDEX text_idx SETTINGS mutations_sync = 2;

Para remover um índice de texto, execute

ALTER TABLE table DROP INDEX text_idx;

Argumento do tokenizador (obrigatório). O argumento tokenizer especifica o tokenizador:

• splitByNonAlpha divide strings em caracteres ASCII não alfanuméricos (consulte a função splitByNonAlpha).
• splitByString(S) divide strings usando determinadas strings separadoras S definidas pelo usuário (consulte a função splitByString). Os separadores podem ser especificados usando um parâmetro opcional; por exemplo, tokenizer = splitByString([', ', '; ', '\n', '\\']). Observe que cada string pode ser composta por vários caracteres (', ' no exemplo). A lista padrão de separadores, se não for especificada explicitamente (por exemplo, tokenizer = splitByString), é um único espaço em branco [' '].
• splitByRegexp(re) divide strings usando um separador de expressão regular re definido pelo usuário (consulte a função splitByRegexp). O argumento re é obrigatório; por exemplo, tokenizer = splitByRegexp('[^\p{L}\p{N}#+]+'). Diferentemente das strings separadoras fixas de splitByString, um separador de expressão regular pode preservar tokens que contêm caracteres especiais (como C++ ou C#) que os outros tokenizadores separariam.
• asciiCJK divide strings em tokens usando regras de limite de palavras do Unicode (semelhantes a Unicode Text Segmentation (UAX #29)). Caracteres ASCII alfanuméricos e sublinhados formam tokens com conectores (ASCII : para letras, . e ' para caracteres do mesmo tipo). Caracteres Unicode não ASCII, incluindo caracteres CJK, tornam-se tokens de um único caractere.
• chinese[(granularity)] segmenta texto chinês em palavras usando um dicionário e um modelo oculto de Markov (o algoritmo segue jieba; o dicionário e os dados do modelo embutidos são derivados de cppjieba). Diferentemente de asciiCJK, que trata cada caractere não ASCII como um token de um único caractere, chinese agrupa caracteres chineses consecutivos em palavras (por exemplo, 北京大学 se torna um token 北京大学, em vez de quatro tokens de um único caractere). Isso gera tokens mais significativos para texto chinês e maior qualidade de busca. Para texto geral/misto, use asciiCJK; para texto somente em chinês, use chinese. O argumento opcional granularity é 'coarse_grained' (o padrão, se não for especificado) ou 'fine_grained'. Este último também enumera subpalavras sobrepostas; por exemplo, 北京邮电大学 também gera 北京, 邮电, 大学. A tokenização granular melhora o recall, ao custo de um índice maior. Pesquise um índice de texto chinese com hasAnyTokens / hasAllTokens (que tokenizam o termo buscado com o tokenizador chinese), e não com hasToken (que divide apenas em separadores ASCII).
• icu(locale) divide strings em tokens de palavras usando a segmentação de palavras Unicode (UAX #29) da biblioteca ICU. Para sistemas de escrita que não usam espaços em branco entre palavras (por exemplo, chinês, japonês e tailandês), a ICU aplica segmentação baseada em dicionário; portanto — diferentemente de asciiCJK — esse texto é dividido em palavras significativas com vários caracteres, em vez de caracteres únicos. Aqui, "dicionário" refere-se às listas de palavras incluídas na ICU para esses sistemas de escrita (consulte brkitr/dictionaries); a ICU escolhe a divisão mais provável entre essas palavras. locale é a localidade da ICU passada ao segmentador; a segmentação é orientada principalmente pelo sistema de escrita e pelo dicionário, e a localidade seleciona as personalizações específicas da localidade na ICU. É um parâmetro obrigatório; por exemplo, tokenizer = icu('ja') ou tokenizer = icu('zh'). As localidades disponíveis podem ser listadas com SELECT * FROM system.collations.
• japanese divide texto em japonês em palavras usando o analisador morfológico MeCab. Diferentemente de asciiCJK, que gera tokens de um único caractere para entradas CJK, este tokenizador realiza a segmentação adequada de palavras. Ele requer um dicionário carregado em tempo de execução a partir da configuração do servidor (consulte Dicionário do tokenizador japonês).
• ngrams(N) divide strings em n-grams de tamanho fixo N (consulte a função ngrams). O comprimento do ngram pode ser especificado usando um parâmetro inteiro opcional entre 1 e 8; por exemplo, tokenizer = ngrams(3). O tamanho padrão do ngram, se não for especificado explicitamente (por exemplo, tokenizer = ngrams), é 3.
• sparseGrams(min_length, max_length, min_cutoff_length) divide strings em n-grams de comprimento variável com no mínimo min_length e no máximo max_length caracteres (inclusive) (consulte a função sparseGrams). A menos que sejam especificados explicitamente, min_length e max_length assumem, por padrão, os valores 3 e 100. Se o parâmetro min_cutoff_length for fornecido, somente n-grams com comprimento maior ou igual a min_cutoff_length serão retornados. Em comparação com ngrams(N), o tokenizador sparseGrams produz N-grams de comprimento variável, permitindo uma representação mais flexível do texto original. Por exemplo, tokenizer = sparseGrams(3, 5, 4) gera internamente 3-, 4- e 5-grams a partir da string de entrada, mas apenas os 4- e 5-grams são retornados.
• array não realiza tokenização, ou seja, cada valor da linha é um token (consulte a função array). Para compatibilidade com outros sistemas, keyword está disponível como um alias de array.

Todos os tokenizadores disponíveis estão listados em system.tokenizers.

O tokenizador japanese requer um dicionário MeCab, que não é fornecido com o ClickHouse. Forneça um na configuração do servidor:

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

• dictionary_location é o local de um arquivo compactado contendo um dicionário MeCab compilado. Qualquer dicionário oficial funciona, como IPADIC ou UniDic. O local deve terminar em uma extensão de arquivo compactado compatível (por exemplo, .tar.gz, .tar.zst ou .zip) — o tipo de arquivo compactado é detectado por ela —, portanto, uma URL sem essa extensão (por exemplo, https://example.com/download) é rejeitada. Locais compatíveis:
  • um caminho local file://;
  • uma URL http(s)://, obtida por download direto (use-a para um objeto público ou pré-assinado);
  • um armazenamento de objetos compatível com S3 — AWS S3, GCS, MinIO, local etc. (não apenas AWS) — acessado como s3:///gs:///oss:// ou como uma URL completa http(s)://endpoint/bucket/key. Para um bucket privado, forneça as credenciais do S3 como elementos filhos de <japanese> (consulte o exemplo abaixo).
• dictionary_sha é o SHA-256 desse arquivo compactado. Ele é verificado antes de o dicionário ser carregado; em caso de divergência, o dicionário não é carregado e um erro é gerado.

O arquivo compactado é baixado e extraído uma vez e, em seguida, armazenado em cache localmente. Como o dicionário determina como os documentos são tokenizados, o mesmo dictionary_sha deve ser configurado em todas as réplicas.

Para ler de um bucket privado compatível com S3, adicione as configurações do S3 como elementos filhos de <japanese>, ao lado de dictionary_location e dictionary_sha:

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

As configurações reconhecidas (access_key_id, secret_access_key, region, no_sign_request, use_environment_credentials, …) são as mesmas configurações de autenticação do S3 usadas em outras partes do ClickHouse. A presença delas também faz com que uma URL http(s):// seja obtida por meio do cliente S3 (com assinatura de solicitações), em vez de um simples download.

Depois que o Dicionário estiver configurado, o tokenizador japanese poderá ser usado da seguinte forma:

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 │
└────┘

Para entender como um tokenizador divide a string de entrada, você pode usar as funções tokens e tokensForLikePattern:

Exemplo:

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

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

Trabalhando com entradas não ASCII. Índices de texto podem ser criados com dados textuais em qualquer idioma e conjunto de caracteres. Para texto não ASCII, recomenda-se o tokenizador asciiCJK, pois ele lida corretamente com os limites de palavras Unicode, incluindo caracteres CJK. Para idiomas que não separam palavras por espaços em branco (por exemplo, chinês, japonês ou tailandês), o tokenizador icu(locale) produz tokens de palavras significativos com vários caracteres por meio da segmentação de palavras baseada em dicionário do ICU. Especificamente para o japonês, o tokenizador japanese (MeCab) segmenta o texto em palavras, em vez de caracteres individuais, e geralmente fornece melhores resultados de busca. Especificamente para o chinês, o tokenizador chinese (jieba) segmenta o texto em palavras, em vez de caracteres individuais, e geralmente fornece melhores resultados de busca.

Argumento do preprocessador (opcional). O preprocessador refere-se a uma expressão aplicada à string de entrada antes da tokenização.

Casos de uso típicos para o argumento do preprocessador incluem

1. Conversão para minúsculas/maiúsculas, ou case folding para permitir correspondência sem diferenciar maiúsculas de minúsculas, por exemplo, lower, lowerUTF8, caseFoldUTF8.
2. Normalização UTF-8, por exemplo, normalizeUTF8NFC, normalizeUTF8NFD, normalizeUTF8NFKC, normalizeUTF8NFKD, normalizeUTF8NFKCCasefold, toValidUTF8.
3. Remoção ou transformação de caracteres ou substrings indesejados, como acentos, por exemplo, extractTextFromHTML, substring, idnaEncode, translate, removeDiacriticsUTF8.

A expressão do pré-processador deve transformar um valor de entrada do tipo String ou FixedString em um valor do mesmo tipo. Se o índice de texto foi criado em uma coluna do tipo Nullable(T) ou LowCardinality(T), então a expressão do pré-processador deve aceitar valores anuláveis ou de baixa cardinalidade (ou seja, sem lançar uma exceção).

Exemplos:

• 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)))

Além disso, a expressão do pré-processador deve referenciar apenas a coluna ou expressão sobre a qual o índice de texto está definido.

Exemplos:

• 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)))
• Não é permitido: INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(col, col))

O uso de funções não determinísticas não é permitido.

As funções hasToken, hasAllTokens, hasAnyTokens e hasPhrase usam o preprocessador para transformar primeiro o termo de busca antes de tokenizá-lo. Observe que, como o preprocessador é aplicado apenas no caminho do índice de texto, os resultados dessas funções podem diferir entre consultas que usam o índice de texto e consultas que não o usam (por exemplo, SETTINGS use_skip_indexes = 0).

Por exemplo,

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');

é equivalente a:

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'));

Nesse caso, a expressão de preprocessador transforma individualmente os elementos do array.

Exemplo:

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');

Para definir um preprocessador em um índice de texto criado em colunas do tipo Map, os usuários precisam decidir se o índice é criado com base nas chaves ou nos valores do map.

Exemplo:

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');

Argumento de pós-processador (opcional). O pós-processador se refere a uma expressão aplicada a cada token de saída após a tokenização.

Diferentemente do preprocessador, que transforma toda a string de entrada antes que o tokenizador a divida em tokens, o pós-processador opera sobre os próprios tokens, um de cada vez. Esse é o local natural para transformações inerentemente no nível do token.

Casos de uso típicos para o argumento de pós-processador incluem:

1. Filtragem de stop words (tokens extremamente frequentes). Tokens muito comuns, como "the", "a" e "is", têm pouca relevância para busca e aumentam o índice. Você pode usar o pós-processador para descartá-los convertendo-os em tokens vazios — tokens vazios são ignorados, isto é, não são adicionados ao índice. Exemplo: if(str IN ('the', 'a', 'an', 'of', 'in', 'is', 'it'), '', str)
2. Remoção de timestamp. Linhas de log frequentemente começam com ou contêm um timestamp estruturado, como 2024-01-15T10:23:45. O indexamento de tokens de timestamp infla o índice com strings que não têm relevância para busca. Há duas abordagens complementares para ignorar timestamps:
   • Abordagem com pós-processador: use o tokenizador splitByString (divisão por espaços em branco) para que o timestamp inteiro se torne um único token e, em seguida, use parseDateTimeOrNull para detectá-lo e descartá-lo. Exemplo: if(isNull(parseDateTimeOrNull(str, '%Y-%m-%dT%H:%i:%S')), str, '') Para timestamps com offsets de timezone ou segundos fracionários, use parseDateTimeBestEffortOrNull(str) sem uma format string explícita.
   • Abordagem com pré-processador: remova o timestamp da linha de log completa antes da tokenização com uma expressão regular. Exemplo: replaceRegexpAll(str, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '') Isso funciona com qualquer tokenizador e é mais eficiente, pois os caracteres do timestamp nunca são tokenizados. As duas abordagens podem ser combinadas: o pré-processador remove o timestamp, enquanto o pós-processador normaliza ou filtra os tokens restantes (por exemplo, lowercase + remoção de palavras de severidade como ERROR ou INFO).
3. Stemming. Mapear cada token para seu radical melhora a abrangência da busca ao corresponder variantes morfológicas que compartilham a mesma raiz. Por exemplo, com stemming em inglês, "running", "runs" e "run" são todos reduzidos a "run", de modo que uma consulta por qualquer uma dessas variantes corresponde a todas elas. O ClickHouse fornece uma função interna stem para vários idiomas. Exemplo: stem(str, 'en')
4. Normalização de maiúsculas/minúsculas. Converter tokens para minúsculas ou maiúsculas para permitir correspondência sem diferenciar maiúsculas de minúsculas, por exemplo lower, lowerUTF8. Para conversão para minúsculas e maiúsculas, recomendamos um pré-processador em vez de um pós-processador.

A expressão de pós-processador transforma tokens do tipo String em tokens do mesmo tipo. Além disso, a expressão de pós-processador deve referenciar apenas a coluna ou expressão sobre a qual o índice de texto é definido. Quando a coluna é do tipo Array(String), o pós-processador ainda opera em tokens individuais como valores simples String.

O uso de funções não determinísticas não é permitido.

O pós-processador é aplicado a cada token gerado durante a construção do índice (para o tokenizador array, cada elemento do array é um token). Durante a consulta, o comportamento depende da função:

• Para hasToken, hasAllTokens, hasAnyTokens e hasPhrase (com qualquer tokenizador compatível): o pós-processador é aplicado tanto aos tokens do haystack quanto ao needle de busca, permitindo correspondência totalmente normalizada (por exemplo, busca sem diferenciar maiúsculas de minúsculas). Para hasPhrase, os tokens pós-processados são posicionados de forma densa, portanto, um token que o pós-processador descarta não deixa nenhuma lacuna posicional e a frase ainda corresponde através dele — por exemplo, com um pós-processador de stop words que descarta the, hasPhrase(col, 'see cat') corresponde a um documento see the cat. A única exceção é hasPhrase em um índice splitByRegexp, que não oferece suporte a um pós-processador (a combinação é rejeitada com uma exceção).
• Para todas as outras funções (=, IN, has, hasAny, hasAll, mapContains*): apenas o needle de busca é pós-processado para a busca por dica de índice; o predicado no nível da linha ainda compara com os valores originais da coluna.

Exemplos:

• Remova stop words usando uma expressão de pós-processador:

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();

• Remova timestamps usando uma expressão de pós-processador:

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

• Remova os timestamps usando uma expressão de preprocessador:

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

• Remova os timestamps usando uma expressão combinada de preprocessador e pós-processamento:

-- 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']);

• Reduza os tokens aos seus radicais usando uma expressão de pós-processamento:

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']);

Suporte a funções.

Para predicados que consultam o índice de texto, o pré-processador e o pós-processador são aplicados ao valor de busca antes da verificação no nível do grânulo, para que a consulta ao índice use os mesmos tokens armazenados na construção do índice. Para a maioria das funções (=, IN, startsWith, endsWith, LIKE, mapContains*), o índice de texto é usado apenas para ignorar blocos de dados irrelevantes; o ClickHouse ainda verifica cada linha restante usando o predicado original sobre os dados originais da coluna. Para funções de busca por token (hasToken, hasAllTokens, hasAnyTokens), o índice de texto é o principal caminho de avaliação: o ClickHouse normaliza o termo buscado por meio do mesmo pré-processador, tokenizador e pós-processador aplicados na construção do índice, e usa essa forma normalizada tanto para partes de tabela indexadas quanto não indexadas. Com um pós-processador, os tokens do texto pesquisado também são normalizados em tempo de consulta (para qualquer tokenizador, não apenas array), para que ambos os lados da comparação sejam transformados de forma consistente e o resultado não dependa de o índice ser lido diretamente (configuração query_plan_direct_read_from_text_index) nem de uma determinada parte ter um índice materializado — por exemplo, habilitando correspondência sem diferenciar maiúsculas de minúsculas para hasAllTokens(col, ['FOO']) com um pós-processador lower. Sem support_phrase_search, hasPhrase usa o índice apenas como uma dica e verifica cada linha restante com o predicado original; um pós-processador também normaliza a frase e os tokens do texto pesquisado da mesma forma, para que o resultado seja independente do caminho de leitura, e os tokens descartados pelo pós-processador não quebrem a adjacência da frase. Com support_phrase_search = 1, hasPhrase usa leituras diretas exatas (ainda aplicando o pós-processador, se houver). Esse suporte ao pós-processador não se estende ao tokenizador splitByRegexp: hasPhrase em um índice splitByRegexp combinado com um pós-processador é rejeitado (consulte a nota de rodapé ³ abaixo). Os tokens de busca que o pós-processador mapeia para uma string vazia são ignorados, ou seja, tratados como ausentes da frase de busca.

¹ LIKE e match usam leitura direta como dica para os tokenizadores listados; caso contrário, recorrem a uma varredura por força bruta. Além disso, LIKE oferece suporte a leitura direta (sem dica) (habilitada por use_text_index_like_evaluation_by_dictionary_scan) para os tokenizadores splitByNonAlpha e array, sem pré-processador nem pós-processador.

² ILIKE é compatível apenas por meio de leitura direta (sem dica) (use_text_index_like_evaluation_by_dictionary_scan = 1, tokenizador splitByNonAlpha ou array). Não há fallback para usar o índice como dica: se a configuração estiver desabilitada ou o tokenizador não estiver no conjunto compatível, o índice não será usado para ILIKE. O pré-processador, se presente, deve ser lower ou upper; pós-processadores não são compatíveis.

³ hasPhrase em um índice de texto splitByRegexp não oferece suporte a um pós-processador: a combinação é rejeitada com uma exceção, pois a reescrita em nível de linha do pós-processador pressupõe tokens no estilo splitByNonAlpha divididos por espaço em branco. Sem um pós-processador, splitByRegexp é totalmente compatível com hasPhrase.

Experimental: argumento de suporte à busca por frase (opcional).

O parâmetro experimental support_phrase_search (padrão: 0) controla se o índice armazena as posições dos tokens. Quando definido como 1, o índice também armazena dados posicionais (em um arquivo .pos), o que permite correspondência exata de frases por meio de leituras diretas com a função hasPhrase. Armazenar posições aumenta o tamanho do índice em disco e o custo de escrita, portanto esse recurso é opcional. O formato em disco ainda não é estável, portanto este parâmetro é experimental e pode mudar em um lançamento futuro. Criar um índice com support_phrase_search = 1 exige, portanto, que a configuração do MergeTree allow_experimental_text_index_phrase_search esteja habilitada. Defina support_phrase_search = 0 (o padrão) para manter o armazenamento somente com posting list; os índices de texto criados sem esse argumento continuam sem posições.

CREATE TABLE table(
    s String,
    -- Este índice usa 'bitpacking', substituindo o padrão no nível da tabela abaixo:
    INDEX idx_a s TYPE text(tokenizer = 'splitByNonAlpha', posting_list_codec = 'bitpacking'),
    -- Este índice herda 'none' da configuração da tabela:
    INDEX idx_b lower(s) TYPE text(tokenizer = 'splitByNonAlpha'))
ENGINE = MergeTree()
ORDER BY tuple()
SETTINGS text_index_posting_list_codec = 'none';

Granularidade do índice. Os índices de texto são implementados no ClickHouse como um tipo de skip indexes. No entanto, diferentemente de outros skip indexes, os índices de texto usam granularidade infinita (100 milhões). Isso pode ser observado na definição da tabela de um índice de texto.

Exemplo:

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                                      │
└────────────────────────────────────────────────────────────────────────┘

A granularidade de índice muito alta garante que o índice de texto seja criado para toda a parte. Uma granularidade de índice especificada explicitamente é ignorada.

Usar um índice de texto em consultas SELECT é simples, pois as funções comuns de busca em strings usarão o índice automaticamente. Se não houver um índice em uma coluna ou parte da tabela, as funções de busca em strings recorrerão a varreduras lentas por força bruta.

O índice de texto pode ser usado com funções de texto na cláusula WHERE ou nas cláusulas PREWHERE:

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

= (equals) corresponde ao termo de busca informado por completo.

Exemplo:

SELECT * from table WHERE str = 'Hello';

IN (in) é semelhante a equals, mas faz correspondência com todos os termos pesquisados.

Exemplo:

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

Para usar LIKE (like) e a função match com índices de texto, o ClickHouse precisa conseguir extrair tokens completos do termo de busca. No caso do índice com o tokenizador ngrams, isso acontece se o comprimento das strings pesquisadas entre caracteres curinga for igual ou maior que o comprimento do ngram.

Exemplo de índice de texto com o tokenizador splitByNonAlpha:

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

support no exemplo pode corresponder a support, supports, supporting etc. Esse tipo de consulta é uma consulta de substring e não pode ser acelerada com um índice de texto.

Para usar um índice de texto em consultas LIKE, o padrão LIKE deve ser reescrito da seguinte forma:

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

Os espaços à esquerda e à direita de support garantem que o termo possa ser extraído como um token.

Felizmente, há um caso especial em que o ClickHouse pode aproveitar o índice invertido para acelerar significativamente consultas LIKE.

Consulte a seção sobre otimização de desempenho de LIKE/ILIKE para mais detalhes.

multiSearchAny e sua variante UTF-8 multiSearchAnyUTF8 testam se alguma entre várias substrings literais ocorre no texto de entrada, e multiMatchAny testa se alguma entre várias expressões regulares encontra correspondência. Essas funções usam o índice de texto nas mesmas condições que LIKE e match (veja acima): o ClickHouse precisa conseguir extrair tokens completos de cada needle, e a lista de needles precisa ser constante. Um grânulo é lido se algum needle puder estar presente nele.

Para multiMatchAny, se um único padrão não puder ser reduzido a um requisito de token (por exemplo, .*, que corresponde a qualquer documento), o índice de texto não poderá ser usado, e a consulta recorrerá a uma varredura completa.

Assim como em LIKE e match, a busca por substring e por expressão regular funciona melhor com os tokenizadores ngrams e sparseGrams. Esses tokenizadores indexam n-grams de caracteres sobrepostos, de modo que um needle é decomposto em n-grams presentes no índice em qualquer lugar em que o needle ocorra como substring, independentemente de começar ou terminar no meio de uma palavra. Portanto, um needle pode ser usado como está, desde que tenha pelo menos o tamanho do n-gram.

Exemplo de índice de texto com o tokenizador ngrams:

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

O tokenizador splitByNonAlpha, em contrapartida, indexa apenas tokens completos (palavras inteiras). Como um padrão pode começar ou terminar no meio de uma palavra, o ClickHouse descarta os tokens do início e do fim de cada padrão, de modo que o índice só possa podar grânulos usando tokens completos. Para que a busca por substring e por expressão regular use o índice com splitByNonAlpha, envolva cada padrão com caracteres separadores (como espaços), para que ele forme um ou mais tokens completos.

Exemplo de índice de texto com o tokenizador splitByNonAlpha:

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

Assim como LIKE, as funções startsWith e endsWith só podem usar um índice de texto se for possível extrair tokens completos do termo de busca. No índice com o tokenizador ngrams, isso acontece quando o comprimento das Strings pesquisadas entre caracteres curinga é igual ou maior que o comprimento do ngram. Quando um índice de texto usa um pós-processador, essas funções ainda podem usar o índice no modo Hint se os tokens de dica extraídos continuarem não vazios após a normalização. Se a normalização remover todos os tokens de dica, o índice não será usado para esse predicado.

Exemplo de índice de texto com o tokenizador splitByNonAlpha:

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

No exemplo, apenas clickhouse é considerado um token. support não é considerado um token porque pode corresponder a support, supports, supporting etc.

Para encontrar todas as linhas que começam com clickhouse supports, termine o padrão de busca com um espaço no final:

startsWith(comment, 'clickhouse supports ')`

Da mesma forma, endsWith deve ser usado com um espaço no início:

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

A função hasToken faz correspondência com um único token fornecido.

Ao contrário das funções mencionadas anteriormente, ela não tokeniza o termo de busca (parte do pressuposto de que a entrada é um único token).

Exemplo:

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

As funções hasAnyTokens e hasAllTokens fazem a correspondência com um ou com todos os tokens fornecidos.

Essas duas funções aceitam os tokens de busca como uma string, que será tokenizada usando o mesmo tokenizador da coluna de índice, ou como um array de tokens já processados, aos quais nenhuma tokenização será aplicada antes da busca. Consulte a documentação da função para mais informações.

Exemplo:

-- Tokens de busca passados como argumento string
SELECT count() FROM table WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM table WHERE hasAllTokens(comment, 'clickhouse olap');

-- Tokens de busca passados como Array(String)
SELECT count() FROM table WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

A função hasPhrase faz correspondência com uma frase: todos os tokens devem aparecer de forma consecutiva e na mesma ordem da string de busca.

Ao contrário de hasAllTokens, que exige apenas que todos os tokens estejam presentes em algum ponto, hasPhrase exige que eles apareçam em sequência. A frase de busca é tokenizada usando o mesmo tokenizador configurado para a coluna de índice. Quando o índice de texto usa um pós-processador, a frase de busca também é normalizada antes da consulta ao índice. Observe que a função requer um dos tokenizadores splitByNonAlpha, splitByString, splitByRegexp, ngrams, asciiCJK ou icu.

Exemplo:

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

A função Array has verifica a correspondência de um único token em um array de strings.

Exemplo:

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

As funções de array hasAny e hasAll verificam se a coluna de array indexada contém alguma ou todas as strings procuradas de um conjunto constante.

Exemplo:

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

A função mapContains (um alias de mapContainsKey) faz a correspondência com os tokens extraídos da string pesquisada nas chaves de um map. O comportamento é semelhante ao da função equals com uma coluna String. O índice de texto é usado apenas se tiver sido criado em uma expressão mapKeys(map).

Exemplo:

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

A função mapContainsValue faz correspondência com os tokens extraídos da string pesquisada nos valores de um map. O comportamento é semelhante ao da função equals com uma coluna String. O índice de texto só é usado se tiver sido criado em uma expressão mapValues(map).

Exemplo:

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

As funções mapContainsKeyLike e mapContainsValueLike comparam um padrão com todas as chaves ou valores (respectivamente) de um map.

Exemplo:

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

O operador de acesso operator[] pode ser usado com o índice de texto para filtrar as chaves e os valores. O índice de texto só é usado se for criado nas expressões mapKeys(map) ou mapValues(map), ou em ambas.

Exemplo:

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

Veja os exemplos a seguir de como usar colunas do tipo Array(T) e Map(K, V) com o índice de texto.

Imagine uma plataforma de blogs, em que os autores categorizam suas publicações usando palavras-chave. Queremos que os usuários descubram conteúdo relacionado pesquisando tópicos ou clicando neles.

Considere esta definição de tabela:

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

Sem um índice de texto, encontrar posts com uma palavra-chave específica (por exemplo, clickhouse) exige a varredura de todos os registros:

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- varredura lenta da tabela inteira - verifica cada keyword em cada post

À medida que a plataforma cresce, isso se torna cada vez mais lento, porque a consulta precisa examinar o array keywords em cada linha. Para contornar esse problema de desempenho, definimos um índice de texto para a coluna keywords:

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- Não se esqueça de reconstruir o índice para os dados existentes

Em muitos casos de uso de observabilidade, as mensagens de log são divididas em "componentes" e armazenadas nos tipos de dados apropriados, por exemplo, data e hora para o timestamp, enum para o nível de log etc. Os campos de métricas são mais bem armazenados como pares chave-valor. As equipes de operações precisam pesquisar nos logs com eficiência para depuração, incidentes de segurança e monitoramento.

Considere esta tabela de logs:

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

Sem um índice de texto, pesquisar dados do tipo Map exige varreduras completas da tabela:

-- Encontra todos os logs com dados de rate limiting:
SELECT * FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- varredura lenta na tabela completa

-- Encontra todos os logs de um IP específico:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- varredura lenta na tabela completa

À medida que o volume de logs aumenta, essas consultas ficam lentas.

A solução é criar um índice de texto para as chaves e os valores do Map. Use mapKeys para criar um índice de texto quando precisar encontrar logs por nomes de campos ou tipos de atributos:

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

Use mapValues para criar um índice de texto quando precisar pesquisar no conteúdo real dos atributos:

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

Exemplos de consultas:

-- Encontra todas as requisições com limite de taxa:
SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast

-- Encontra todos os logs de um IP específico:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast

-- Encontra todos os logs em que algum atributo inclui um erro:
SELECT * FROM logs WHERE mapContainsValueLike(attributes, '% error %'); -- fast

Índices de texto podem ser usados com colunas JSON de três maneiras:

1. Índices em subcolunas específicas — crie um índice de texto em um caminho JSON conhecido, assim como em uma coluna comum. Isso indexa os valores nesse caminho.
2. Índices baseados em caminhos com JSONAllPaths — indexam todos os caminhos presentes em cada grânulo para ignorar grânulos que não podem conter o caminho consultado. Semelhante ao que ocorre com colunas Map.
3. Índices baseados em valores com JSONAllValues — indexam todos os valores em todos os caminhos JSON para acelerar a busca de texto completo em qualquer subcoluna JSON com um único índice.

Você pode criar um skip index em qualquer subcoluna JSON usando a mesma sintaxe das colunas comuns.

Há duas maneiras de referenciar uma subcoluna JSON em uma expressão de índice:

• Caminho tipado declarado no type hint de JSON — acesse-o diretamente pelo nome: json.a.
• Caminho dinâmico com cast explícito — use a sintaxe de cast ::: json.b::String.

Exemplo de definição de índice:

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);

Exemplo de consulta:

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

Exemplo de consulta:

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

Assim como nas colunas Map, é possível criar índices de texto em colunas JSON usando JSONAllPaths. O índice armazena o conjunto de caminhos JSON presentes em cada grânulo e os utiliza para ignorar grânulos nos quais o caminho consultado está ausente.

Definição de exemplo do índice:

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"}');

Você pode usar EXPLAIN indexes = 1 para verificar se o skip index está sendo usado. Quando um caminho existe apenas em uma parte, o índice ignora a outra parte.

Exemplo:

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

Quando um caminho não existe em nenhuma parte, todas as partes e todos os grânulos são ignorados.

Exemplo:

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

IS NOT NULL também usa o índice — ele ignora os grânulos em que o caminho não existe (já que o valor seria NULL):

Exemplo:

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

Índices de texto podem ser usados para acelerar pesquisas em colunas JSON por meio da função JSONAllValues.

JSONAllValues retorna todos os valores de uma coluna JSON como Array(String). Valores de tipos de dados que não são string (por exemplo, inteiros e arrays) são convertidos para sua representação em texto. Um índice de texto criado com JSONAllValues indexa essas representações textuais em todos os caminhos JSON de cada linha. Esse índice pode então acelerar consultas que filtram subcolunas JSON específicas. Quando uma consulta filtra uma subcoluna específica (por exemplo, data.user_name = 'alice'), o índice de texto pode rapidamente ignorar linhas (e grânulos) que não contêm os tokens pesquisados em nenhum de seus valores JSON.

Exemplo de definição de índice:

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

Depois de criado, o índice pode acelerar consultas em subcolunas JSON usando as mesmas funções usadas com colunas String e a função equals para todas as colunas.

Acesso à subcoluna:

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');

Acesso à subcoluna com CAST explícito:

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')

operador IN:

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

Uma busca comum em um índice de texto, por exemplo

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

corresponde a todas as linhas que contêm os tokens fornecidos em qualquer ordem. No exemplo, a linha While she stayed in Tokyo, the weather was great. corresponde ao filtro.

Em contraste, a busca por frase consiste em corresponder aos tokens na ordem fornecida. Por exemplo,

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

corresponde a qualquer linha que contenha a sequência de tokens weather in Tokyo, como em How is the weather in Tokyo??

O índice de texto acelera a busca por frase fazendo a interseção das listas de postings de todos os tokens da frase para identificar grânulos candidatos. Dentro desses grânulos, o ClickHouse então verifica a adjacência exata dos tokens. Esse processo é relativamente custoso e mais lento do que consultas comuns de busca textual. Para acelerar as consultas de busca por frase, habilite o armazenamento das posições no índice de texto (consulte Optional parameters acima).

hasPhrase pode ser usado em conjunto com os tokenizers splitByNonAlpha, splitByString, splitByRegexp, ngrams, asciiCJK e icu. A string da frase fornecida é tokenizada usando o tokenizer do índice. Os caracteres separadores na frase são ignorados: hasPhrase(text, 'quick+brown') é equivalente a hasPhrase(text, 'quick brown'), supondo que splitByNonAlpha seja usado como tokenizer.

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 │
   └────┴─────────────────────┘

A linha 2 ('New weather in York') não corresponde porque os tokens estão na ordem incorreta. A linha 3 ('weather in New Orleans') não corresponde porque não contém o token 'York'.

Certos tipos de consultas de texto podem ser acelerados significativamente por uma otimização chamada "leitura direta".

Exemplo:

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

A otimização de leitura direta responde à consulta exclusivamente usando o índice de texto (isto é, lookups no índice de texto), sem acessar a coluna de texto subjacente. Os lookups no índice de texto leem relativamente poucos dados e, por isso, são muito mais rápidos do que os skip indexes usuais no ClickHouse (que fazem um lookup no skip index, seguido do carregamento e da filtragem dos grânulos restantes).

A leitura direta é controlada por duas configurações:

• A configuração query_plan_direct_read_from_text_index (true por padrão) especifica se a leitura direta está habilitada de modo geral.
• A configuração use_skip_indexes_on_data_read era um pré-requisito para a leitura direta em versões do ClickHouse < 26.4.

Funções compatíveis

A otimização de leitura direta oferece suporte às funções hasToken, hasAllTokens e hasAnyTokens. Se o índice de texto for definido com um tokenizer array, a leitura direta também terá suporte para as funções equals, has, hasAny, hasAll, mapContainsKey e mapContainsValue. Essas funções também podem ser combinadas com os operadores AND, OR e NOT. As cláusulas WHERE ou PREWHERE também podem conter filtros adicionais de funções que não sejam de busca de texto (para colunas de texto ou outras colunas) - nesse caso, a otimização de leitura direta ainda será usada, mas será menos eficaz (ela se aplica apenas às funções de busca de texto compatíveis).

Para verificar se uma consulta utiliza leitura direta, execute a consulta com EXPLAIN PLAN actions = 1. Como exemplo, uma consulta com a leitura direta desabilitada

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

retorna

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

ao executar a mesma consulta com query_plan_direct_read_from_text_index = 1

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

retorna

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

A segunda saída de EXPLAIN PLAN contém uma coluna virtual __text_index_<index_name>_<function_name>_<id>. Se essa coluna estiver presente, a leitura direta será usada.

Se a cláusula de filtro WHERE contiver apenas funções de busca de texto, a consulta poderá evitar completamente a leitura dos dados da coluna e obter o maior ganho de desempenho com a leitura direta. No entanto, mesmo que a coluna de texto seja acessada em outra parte da consulta, a leitura direta ainda proporcionará melhoria de desempenho.

Leitura direta como hint

A leitura direta como hint se baseia nos mesmos princípios da leitura direta normal, mas adiciona um filtro extra construído a partir dos dados do índice de texto, sem remover a coluna de texto subjacente. Ela é usada para funções em que ler apenas do índice de texto produziria falsos positivos.

As funções compatíveis são: like, startsWith, endsWith, equals, has, hasPhrase, mapContainsKey e mapContainsValue.

O filtro adicional pode oferecer seletividade extra para restringir ainda mais o conjunto de resultados em combinação com outros filtros, ajudando a reduzir a quantidade de dados lidos de outras colunas.

A leitura direta como hint é controlada pela configuração query_plan_text_index_add_hint (ativada por padrão).

Exemplo de consulta sem hint:

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

retorna

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

enquanto a mesma consulta é executada com query_plan_text_index_add_hint = 1

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

retorna

[...]
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)
[...]

Na segunda saída de EXPLAIN PLAN, você pode ver que um termo adicional (__text_index_...) foi adicionado à condição de filtro. Graças à otimização PREWHERE, a condição de filtro é dividida em três termos separados, aplicados em ordem crescente de complexidade computacional. Para esta consulta, a ordem de aplicação é __text_index_..., depois greaterOrEquals(...) e, por fim, like(...). Essa ordenação permite ignorar ainda mais grânulos de dados do que os já ignorados pelo índice de texto e pelo filtro original, antes da leitura das colunas pesadas usadas na consulta após a cláusula WHERE, reduzindo ainda mais a quantidade de dados a ser lida.

Quando um padrão de consulta LIKE/ILIKE é %<caracteres-alfanuméricos-sem-espaços>% e o tokenizer do índice de texto é splitByNonAlpha ou array, o ClickHouse usa o índice invertido para acelerar significativamente as consultas LIKE/ILIKE. Para isso, o ClickHouse examina o dicionário do índice invertido em vez de fazer uma varredura completa da tabela para encontrar o padrão correspondente.

Quando a otimização está habilitada, as consultas LIKE/ILIKE devem ser significativamente mais rápidas do que uma varredura completa da tabela. No entanto, quando o padrão corresponde à maioria dos tokens do dicionário, o desempenho pode ser pior do que em uma varredura completa da tabela. Felizmente, há um mecanismo de fallback para evitar isso.

A otimização é controlada por uma configuração:

• use_text_index_like_evaluation_by_dictionary_scan

O mecanismo de fallback é controlado por duas configurações:

• text_index_like_min_pattern_length
• text_index_like_max_postings_to_read

Essa otimização é compatível apenas com as funções like e ilike.

Uma consulta que apenas conta as linhas que correspondem a um predicado de busca textual

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

é respondida pelo índice de texto, sem ler as linhas correspondentes. O ClickHouse obtém a contagem a partir das cardinalidades das listas de postings no índice, ignorando a coluna virtual que marca as linhas correspondentes e a agregação sobre ela. Um único token precisa apenas da cardinalidade do dicionário, portanto nenhuma lista de postings é lida; predicados com vários tokens leem os postings apenas para uni-los (hasAnyTokens) ou intersectá-los (hasAllTokens). Como um índice de texto abrange toda a parte, a contagem é exata e continua rápida mesmo em partes muito grandes.

A otimização se aplica a um count() simples filtrado por um único predicado hasToken, hasAnyTokens ou hasAllTokens (ou um equivalente de Array/Map). Predicados combinados com AND/OR/NOT, um filtro adicional (por exemplo, ... AND id > 10), um predicado no modo de indicação, como m['key'] = 'value', selecionar qualquer coisa além da contagem ou uma busca por frase, LIKE ou padrão fazem com que a consulta leia as linhas. Partes sem um índice materializado ainda são contadas corretamente mediante a leitura de suas linhas.

Para confirmar que a otimização está sendo aplicada, verifique se há ReadFromTextIndexCount no plano de consulta:

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

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

A otimização é controlada por query_plan_optimize_count_from_text_index (ativada por padrão). Ela se baseia na leitura direta; portanto, query_plan_direct_read_from_text_index, use_skip_indexes e optimize_trivial_count_query também devem estar ativadas (todas ativadas por padrão).

Há diferentes caches de servidor disponíveis para armazenar em buffer, na memória, partes do índice de texto (consulte a seção Detalhes de implementação): Atualmente, há caches para os cabeçalhos desserializados, tokens e lista de postings do índice de texto, para reduzir a E/S. Use as configurações use_text_index_header_cache, use_text_index_tokens_cache e use_text_index_postings_cache para desabilitar a leitura e a gravação nos caches individuais pelas consultas. O armazenamento em cache de tokens ausentes de uma parte de dados é habilitado por padrão e pode ser controlado independentemente com use_text_index_negative_tokens_cache.

Para limpar os caches, use a instrução SYSTEM CLEAR TEXT INDEX CACHES

Consulte as configurações de servidor a seguir para configurar os caches.

No momento, o índice de texto tem as seguintes limitações:

• A materialização de índices de texto com um grande número de tokens (por exemplo, 10 bilhões de tokens) pode consumir quantidades significativas de memória. A materialização de índices de texto pode ocorrer diretamente (ALTER TABLE <table> MATERIALIZE INDEX <index>) ou indiretamente durante mesclagens de partes.
• Não é possível materializar índices de texto em partes com mais de 4.294.967.296 (= 2^32 = aprox. 4,2 bilhões) linhas. Sem um índice de texto materializado, as consultas recorrem a uma busca lenta por força bruta dentro da parte. Como estimativa de pior caso, suponha que uma parte contenha uma única coluna do tipo String e que a configuração do MergeTree max_bytes_to_merge_at_max_space_in_pool (padrão: 150 GB) não tenha sido alterada. Nesse caso, isso ocorre se a coluna contiver, em média, menos de 29,5 caracteres por linha. Na prática, as tabelas também contêm outras colunas, e esse limite é várias vezes menor do que isso (dependendo do número, tipo e tamanho das outras colunas).

A versão do formato em disco dos índices de texto é controlada pela configuração de tabela text_index_serialization_version (padrão: v2_with_positions). A configuração é uma preferência, não uma restrição rígida: se a versão configurada não puder representar um índice, uma versão mais recente capaz de representá-lo será escolhida automaticamente. Portanto, a gravação de um índice de texto nunca falhará devido a essa configuração. Durante uma atualização gradual, fixe o formato com a configuração compatibility nos servidores já atualizados: quando definida como uma versão anterior à que introduziu o formato correspondente, text_index_serialization_version reverte automaticamente para um valor mais antigo, e os servidores mais recentes continuam gravando em um formato que os servidores mais antigos ainda conseguem ler.

Predicados sobre strings podem ser acelerados com índices de texto e índices baseados em filtro de Bloom (tipos de índice bloom_filter, ngrambf_v1, tokenbf_v1, sparse_grams), mas eles diferem fundamentalmente em seu design e nos casos de uso a que se destinam:

Índices de filtro de Bloom

• Baseiam-se em estruturas de dados probabilísticas que podem produzir falsos positivos.
• Só conseguem responder a perguntas de pertinência a conjuntos, ou seja: a coluna pode conter o token X vs. definitivamente não contém X.
• Armazenam informações no nível de grânulo, o que permite ignorar intervalos mais amplos durante a execução da consulta.
• São difíceis de ajustar corretamente (veja aqui um exemplo).
• São relativamente compactos (alguns quilobytes ou megabytes por parte).

Índices de texto

• Constroem um índice invertido determinístico sobre tokens. O próprio índice não pode gerar falsos positivos.
• São especificamente otimizados para cargas de trabalho de pesquisa de texto.
• Armazenam informações no nível da linha, o que permite a busca eficiente de termos.
• São relativamente grandes (de dezenas a centenas de megabytes por parte).

Os índices baseados em filtro de Bloom oferecem suporte à pesquisa de texto completo apenas como um "efeito colateral":

• Eles não oferecem suporte a tokenização e preprocessamento avançados.
• Eles não oferecem suporte à pesquisa por múltiplos tokens.
• Eles não fornecem as características de desempenho esperadas de um índice invertido.

Os índices de texto, em contraste, são criados especificamente para pesquisa de texto completo:

• Eles oferecem tokenização e preprocessamento
• Eles oferecem suporte eficiente a hasAllTokens, LIKE, match e funções semelhantes de pesquisa de texto.
• Eles têm escalabilidade significativamente melhor para grandes corpora de texto.

Cada índice de texto consiste em duas estruturas de dados (abstratas):

• um dicionário que mapeia cada token para uma lista de postings, e
• um conjunto de listas de postings, cada uma representando um conjunto de números de linha.

O índice de texto é construído para a parte inteira. Ao contrário de outros skip indexes, o índice de texto pode ser mesclado em vez de reconstruído durante a mesclagem das partes de dados (veja abaixo).

Durante a criação do índice, três arquivos são criados (por parte):

Arquivo de blocos do dicionário (.dct)

Os tokens no índice de texto são ordenados e armazenados em blocos de dicionário de 512 tokens cada (o tamanho do bloco é configurável pelo parâmetro dictionary_block_size). Um arquivo de blocos do dicionário (.dct) consiste em todos os blocos de dicionário de todos os grânulos de índice em uma parte.

Arquivo de cabeçalho do índice (.idx)

O arquivo de cabeçalho do índice contém, para cada bloco de dicionário, o primeiro token do bloco e seu deslocamento relativo no arquivo de blocos do dicionário.

Essa estrutura de índice esparso é semelhante ao índice primário esparso) do ClickHouse.

Arquivo de listas de postings (.pst)

As listas de postings de todos os tokens são organizadas sequencialmente no arquivo de listas de postings. Para economizar espaço e ainda permitir operações rápidas de interseção e união, as listas de postings são armazenadas como bitmaps Roaring. Se a lista de postings for maior que posting_list_block_size, ela será dividida em vários blocos, que são armazenados sequencialmente no arquivo de listas de postings.

Arquivo de posições (.pos)

Opcional, somente se o argumento do índice support_phrase_search = 1. Armazena as posições dos tokens dentro das linhas correspondentes.

Mesclagem de índices de texto

Quando partes de dados são mescladas, o índice de texto não precisa ser reconstruído do zero; em vez disso, ele pode ser mesclado com eficiência em uma etapa separada do processo de mesclagem. Durante essa etapa, os dicionários ordenados dos índices de texto de cada parte de entrada são lidos e combinados em um novo dicionário unificado. Os números de linha nas listas de postings também são recalculados para refletir suas novas posições na parte de dados mesclada, usando um mapeamento de números de linha antigos para novos criado durante a fase inicial da mesclagem. Esse método de mesclar índices de texto é semelhante à forma como projeções com a coluna _part_offset são mescladas. Se o índice não estiver materializado na parte de origem, ele será construído, gravado em um arquivo temporário e depois mesclado com os índices das outras partes e de outros arquivos de índice temporários.

Depuração

A função de tabela mergeTreeTextIndex pode ser usada para inspecionar índices de texto.

Vamos analisar as melhorias de desempenho dos índices de texto em um grande conjunto de dados com muito conteúdo textual. Usaremos 28,7 milhões de linhas de comentários do popular site Hacker News. Aqui está a tabela sem índice de texto:

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);

As 28,7 milhões de linhas estão em um arquivo Parquet no S3 — vamos inseri-las na tabela hackernews:

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');

Usaremos ALTER TABLE e adicionaremos um índice de texto à coluna comment e, em seguida, o materializaremos:

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

Agora, vamos executar consultas usando as funções hasToken, hasAnyTokens e hasAllTokens. Os exemplos a seguir mostram a grande diferença de desempenho entre uma varredura de índice padrão e a otimização de leitura direta.

hasToken verifica se o texto contém um token específico. Vamos buscar o token que diferencia maiúsculas de minúsculas 'ClickHouse'.

Leitura direta desabilitada (varredura padrão) Por padrão, o ClickHouse usa o skip index para filtrar grânulos e depois lê os dados da coluna desses grânulos. Podemos simular esse comportamento desabilitando a leitura direta.

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

┌

Leitura direta habilitada (Leitura rápida do índice) Agora executamos a mesma consulta com leitura direta habilitada (o padrão).

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

┌

A consulta com leitura direta é mais de 45 vezes mais rápida (0.362s vs 0.008s) e processa significativamente menos dados (9.51 GB vs 3.15 MB) ao ler apenas o índice.

hasAnyTokens verifica se o texto contém pelo menos um dos tokens informados. Vamos procurar comentários que contenham 'love' ou 'ClickHouse'.

Leitura direta desativada (Varredura padrão)

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

┌

Leitura direta ativada (leitura rápida do índice)

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

┌

O ganho de velocidade é ainda mais impressionante para esta busca comum com "OR". A consulta é quase 89 vezes mais rápida (1.329s vs 0.015s) ao evitar a varredura completa da coluna.

hasAllTokens verifica se o texto contém todos os tokens fornecidos. Vamos buscar comentários que contenham tanto 'love' quanto 'ClickHouse'.

Leitura direta desativada (varredura padrão) Mesmo com a leitura direta desativada, o skip index padrão continua eficaz. Ele reduz as 28.7M linhas para apenas 147.46K linhas, mas ainda precisa ler 57.03 MB da coluna.

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

┌

Leitura direta ativada (Leitura rápida do índice) A leitura direta responde à consulta usando os dados do índice e lê apenas 147.46 KB.

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

┌

Para esta busca com "AND", a otimização de leitura direta é mais de 26 vezes mais rápida (0.184s vs 0.007s) do que a varredura padrão com skip index.

A otimização de leitura direta também se aplica a expressões booleanas compostas. Aqui, faremos uma busca sem diferenciar maiúsculas de minúsculas por 'ClickHouse' OR 'clickhouse'.

Leitura direta desativada (varredura padrão)

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

┌

Leitura direta ativada (Leitura rápida do índice)

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

┌

Ao combinar os resultados do índice, a consulta com leitura direta é 34 vezes mais rápida (0,450s vs 0,013s) e evita a leitura de 9,58 GB de dados de colunas. Para este caso específico, hasAnyTokens(comment, ['ClickHouse', 'clickhouse']) seria a sintaxe mais indicada e mais eficiente.

• Blog: Anunciando a disponibilidade geral da pesquisa de texto completo do ClickHouse
• Blog: Criando pesquisa de texto completo de alto desempenho para armazenamento de objetos
• Vídeo: Introdução à pesquisa de texto completo no ClickHouse
• Vídeo: Nos bastidores: pesquisa de texto completo no ClickHouse em escala e com alta velocidade
• Apresentação: Por dentro da pesquisa de texto completo no ClickHouse: rápida, nativa e colunar
• Apresentação: Índices invertidos em bancos de dados: o porquê, o quê e como, FOSDEM 2026

Material desatualizado

• Blog: Introdução aos índices invertidos no ClickHouse
• Blog: Por dentro da pesquisa de texto completo no ClickHouse: rápida, nativa e colunar
• Vídeo: Índices de texto completo: design e experimentos