テキスト索引による全文検索

テキスト索引 (転置索引 とも呼ばれます) を使用すると、テキストデータに対して高速な全文検索が可能になります。 テキスト索引には、各トークンを含む行番号への対応付けが保存されます。 トークンは、トークン化と呼ばれる処理によって生成されます。 たとえば、ClickHouse のデフォルトのトークナイザーは、英語の文 "The cat likes mice." を ["The", "cat", "likes", "mice"] というトークン列に変換します。

例として、1 つのカラムと 3 行を持つテーブルを考えます

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

また、ほぼすべての行に現れる "I"、"the"、"and" などのストップワードも削除します:

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]

検索トークンを指定すると、この索引構造により一致するすべての行をすばやく見つけられます。

テキスト索引は、ClickHouse バージョン 26.2 以降で一般提供 (GA) されています。 これらのバージョンでは、テキスト索引を使用するために特別な設定を行う必要はありません。 本番環境で使用する場合は、ClickHouse バージョン >= 26.2 の利用を強く推奨します。

テキスト索引を作成するには、次の構文を使用します。

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

テキスト索引は、次の型のカラムに定義できます。

• String と FixedString
• Array(String) と Array(FixedString)
• Map (mapKeys および mapValues 関数経由)
• JSON (JSONAllPaths および JSONAllValues 関数経由)

Nullable(T) 型および LowCardinality() 型のカラムにも対応しており、Array(Nullable(String or FixedString)) も含まれます。

また、既存のテーブルにテキスト索引を追加するには:

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

既存のテーブルに索引を追加する場合は、既存のテーブルパーツに対して索引をマテリアライズすることを推奨します (そうしないと、索引のないパーツの検索では低速な総当たりスキャンにフォールバックします) 。

ALTER TABLE table MATERIALIZE INDEX text_idx SETTINGS mutations_sync = 2;

テキスト索引を削除するには、次を実行します

ALTER TABLE table DROP INDEX text_idx;

トークナイザー引数 (必須) 。tokenizer 引数では、トークナイザーを指定します。

• splitByNonAlpha は、ASCII の英数字以外の文字で String を分割します (関数 splitByNonAlpha を参照) 。
• splitByString(S) は、ユーザー定義の区切り String S で String を分割します (関数 splitByString を参照) 。 区切り文字は省略可能なパラメータで指定できます。たとえば、tokenizer = splitByString([', ', '; ', '\n', '\\']) のように指定します。 各 String は複数文字で構成することもできます (例では ', ') 。 明示的に指定しない場合 (たとえば tokenizer = splitByString) 、デフォルトの区切り文字リストは単一の空白文字 [' '] です。
• splitByRegexp(re) は、ユーザー定義の正規表現区切り文字 re で String を分割します (関数 splitByRegexp を参照) 。 re 引数は必須です。たとえば、tokenizer = splitByRegexp('[^\p{L}\p{N}#+]+') のように指定します。 固定の区切り String を使用する splitByString とは異なり、正規表現区切り文字では、他のトークナイザーでは分割される C++ や C# などの特殊文字を含むトークンを保持できます。
• asciiCJK は、Unicode の単語境界規則を使用して String をトークンに分割します (Unicode Text Segmentation (UAX #29) に類似) 。 ASCII の英数字とアンダースコアは、コネクタ (文字に対する ASCII :、同種の文字に対する . および ') を含むトークンを構成します。非 ASCII の Unicode 文字は、CJK 文字を含め、1 文字のトークンになります。
• chinese[(granularity)] は、Dictionary と隠れマルコフモデルを使用して中国語テキストを単語に分割します (アルゴリズムは jieba に従い、埋め込み Dictionary とモデルデータは cppjieba に由来します) 。非 ASCII 文字をすべて 1 文字のトークンとして扱う asciiCJK とは異なり、chinese は連続する中国語文字を単語にまとめます (たとえば、北京大学 は 4 つの 1 文字トークンではなく、1 つのトークン 北京大学 になります) 。これにより、中国語テキストに対してより意味のあるトークンが得られ、検索品質が向上します。一般的なテキストや混在テキストには asciiCJK を、中国語のみのテキストには chinese を使用してください。省略可能な granularity 引数には、'coarse_grained' (指定しない場合のデフォルト) または 'fine_grained' を指定します。後者では、たとえば 北京邮电大学 に対して 北京、邮电、大学 も生成されるよう、重複するサブワードも列挙します。きめ細かいトークン化は、より大きな索引を必要とする代わりに再現率を向上させます。 chinese テキスト索引の検索には、hasToken (ASCII の区切り文字でのみ分割) ではなく、hasAnyTokens / hasAllTokens (needle を chinese トークナイザーでトークン化) を使用します。
• icu(locale) は、ICU ライブラリの Unicode 単語分割 (UAX #29) を使用して String を単語トークンに分割します。単語間に空白を入れない文字体系 (中国語、日本語、タイ語など) では、ICU は Dictionaryベースの分割を適用するため、asciiCJK とは異なり、このようなテキストは単一文字ではなく意味のある複数文字の単語に分割されます。ここでの「Dictionary」とは、これらの文字体系用に ICU にバンドルされている単語リストを意味します (brkitr/dictionaries を参照) 。ICU はこれらの単語に基づいて最も可能性の高い分割を選択します。 locale はセグメンターに渡される ICU ロケールです。分割は主に文字体系と Dictionary に基づいて行われ、ロケールは ICU のロケール固有の調整を選択します。これは必須パラメータであり、たとえば tokenizer = icu('ja') または tokenizer = icu('zh') のように指定します。 使用可能なロケールは SELECT * FROM system.collations で一覧表示できます。
• japanese は、MeCab 形態素アナライザを使用して日本語テキストを単語に分割します。CJK 入力に対して単一文字のトークンを出力する asciiCJK とは異なり、このトークナイザーは適切な単語分割を行います。実行時にサーバー設定から読み込まれる Dictionary が必要です (Japanese tokenizer dictionary を参照) 。
• ngrams(N) は、String を同じ長さの N-gram に分割します (関数 ngrams を参照) 。 ngram の長さは、1 から 8 までの省略可能な整数パラメータで指定できます。たとえば、tokenizer = ngrams(3) のように指定します。 明示的に指定しない場合 (たとえば tokenizer = ngrams) 、デフォルトの ngram サイズは 3 です。
• sparseGrams(min_length, max_length, min_cutoff_length) は、min_length 文字以上 max_length 文字以下 (両端を含む) の可変長 n-gram に String を分割します (関数 sparseGrams を参照) 。 明示的に指定しない限り、min_length と max_length のデフォルト値は 3 と 100 です。 パラメータ min_cutoff_length を指定した場合、長さが min_cutoff_length 以上の n-gram のみが返されます。 ngrams(N) と比べると、sparseGrams トークナイザーは可変長の N-gram を生成するため、元のテキストをより柔軟に表現できます。 たとえば、tokenizer = sparseGrams(3, 5, 4) では、内部的には入力文字列から 3-gram、4-gram、5-gram を生成しますが、返されるのは 4-gram と 5-gram のみです。
• array はトークン化を行いません。つまり、各行の値がトークンになります (関数 array を参照) 。他のシステムとの互換性のため、keyword は array のエイリアスとして使用できます。

使用可能なすべてのトークナイザーは system.tokenizers に一覧表示されています。

japanese トークナイザーには、ClickHouse に同梱されていない MeCab Dictionary が必要です。サーバー設定で Dictionary を指定してください。

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

• dictionary_location は、コンパイル済み MeCab Dictionary のアーカイブの場所です。IPADIC や UniDic など、任意の公式 Dictionary を使用できます。場所はサポートされるアーカイブ拡張子 (たとえば .tar.gz、.tar.zst、.zip) で終わる必要があります。アーカイブタイプはこの拡張子から検出されるため、このような拡張子のない URL (例: https://example.com/download) は拒否されます。サポートされる場所:
  • ローカルの file:// パス。
  • 通常のダウンロードとして取得される http(s):// URL (パブリックまたは事前署名済みオブジェクトにはこれを使用します)。
  • AWS S3、GCS、MinIO、オンプレミスなど (AWS のみではありません) の S3 互換オブジェクトストア。s3:///gs:///oss://、または完全な http(s)://endpoint/bucket/key URL として指定します。プライベート bucket の場合は、S3 認証情報を <japanese> の子要素として指定します (以下の例を参照)。
• dictionary_sha は、そのアーカイブの SHA-256 です。Dictionary をロードする前に検証されます。不一致の場合、Dictionary はロードされず、エラーが発生します。

アーカイブは一度ダウンロードおよび展開され、その後ローカルにキャッシュされます。Dictionary はドキュメントのトークン化方法を決定するため、同じ dictionary_sha をすべてのレプリカで設定する必要があります。

プライベートな S3 互換 bucket から読み取るには、dictionary_location および dictionary_sha と並べて、S3 設定を <japanese> の子要素として追加します:

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

認識される設定 (access_key_id、secret_access_key、region、no_sign_request、use_environment_credentials、…) は、ClickHouse の他の箇所で使用される S3 認証設定と同じです。これらの設定が存在することで、http(s):// URL は通常のダウンロードではなく、(リクエスト署名を行う) S3 クライアントを介して取得されます。

Dictionary を設定すると、japanese トークナイザーを次のように使用できます:

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

トークナイザーが入力文字列をどのように分割するかを確認するには、tokens 関数および tokensForLikePattern 関数を使用できます。

例:

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

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

非 ASCII 入力の処理。 テキスト索引は、あらゆる言語および文字セットのテキストデータに対して構築できます。 非 ASCII テキストには、CJK 文字を含む Unicode の単語境界を正しく処理する asciiCJK トークナイザーを推奨します。 単語を空白で区切らない言語 (中国語、日本語、タイ語など) では、icu(locale) トークナイザーが ICU の辞書ベースの単語分割により、意味のある複数文字の単語トークンを生成します。 日本語の場合、japanese トークナイザー (MeCab) はテキストを単一文字ではなく単語に分割するため、一般により良い検索結果が得られます。 中国語の場合、chinese トークナイザー (jieba) はテキストを単一文字ではなく単語に分割するため、一般により良い検索結果が得られます。

プリプロセッサ引数 (任意) 。プリプロセッサは、トークン化の前に入力文字列に適用される式を指します。

プリプロセッサ引数の一般的なユースケースには、次のものがあります。

1. 大文字/小文字の変換、または大文字小文字を区別しないマッチングを可能にするケースフォールディング。例: lower, lowerUTF8, caseFoldUTF8。
2. UTF-8 の正規化。例: normalizeUTF8NFC, normalizeUTF8NFD, normalizeUTF8NFKC, normalizeUTF8NFKD, normalizeUTF8NFKCCasefold, toValidUTF8。
3. アクセント記号など、不要な文字や部分文字列の削除または変換。例: extractTextFromHTML, substring, idnaEncode, translate, removeDiacriticsUTF8。

プリプロセッサ式は、String または FixedString 型の入力値を、同じ型の値に変換する必要があります。 テキスト索引が Nullable(T) または LowCardinality(T) 型のカラムに対して構築されている場合、プリプロセッサ式は nullable または low-cardinality の値を受け入れられる必要があります (つまり、例外をスローしてはいけません) 。

例:

• 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)))
• 許可されません: INDEX idx lower(col) TYPE text(tokenizer = 'splitByNonAlpha', preprocessor = concat(col, col))

非決定論的関数は使用できません。

関数 hasToken、hasAllTokens、hasAnyTokens、および hasPhrase は、検索語をトークン化する前に、まずプリプロセッサを使用して変換します。 プリプロセッサはテキスト索引の経路でのみ適用されるため、これらの関数の結果は、テキスト索引を使用するクエリと使用しないクエリ (例: SETTINGS use_skip_indexes = 0) で異なる場合があることに注意してください。

たとえば、

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

Map 型のカラムに対するテキスト索引でプリプロセッサを定義するには、その索引を マップのキーと値のどちらを対象に構築するかを決める必要があります。

例:

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 つずつ処理を行います。 本質的にトークン単位の変換を行うのに適した場所です。

ポストプロセッサ引数の典型的なユースケースは次のとおりです:

1. ストップワード (極めて高頻度なトークン) のフィルタリング。"the"、"a"、"is" のような非常によく出現するトークンは、検索との関連性が低いうえ、索引を肥大化させます。 ポストプロセッサを使えば、それらを空トークンに変換して除外できます。空トークンは無視され、つまり索引には追加されません。 例: if(str IN ('the', 'a', 'an', 'of', 'in', 'is', 'it'), '', str)
2. タイムスタンプの除去。ログ行は、2024-01-15T10:23:45 のような構造化されたタイムスタンプで始まっていることや、それを含んでいることがよくあります。 タイムスタンプのトークンを索引化すると、検索上の関連性を持たない文字列によって索引が肥大化します。 タイムスタンプを無視するための相補的な方法は 2 つあります:
   • ポストプロセッサ方式: splitByString トークナイザー (空白で分割) を使用してタイムスタンプ全体を 1 つのトークンにし、その後 parseDateTimeOrNull を使って検出して除外します。 例: if(isNull(parseDateTimeOrNull(str, '%Y-%m-%dT%H:%i:%S')), str, '') タイムゾーンオフセットや小数秒を含むタイムスタンプには、明示的なフォーマット文字列を使わずに parseDateTimeBestEffortOrNull(str) を使用します。
   • プリプロセッサ方式: トークン化の 前 に、正規表現でログ行全体からタイムスタンプを取り除きます。 例: replaceRegexpAll(str, '^[0-9]{4}-[0-9]{2}-[0-9]{2}T[0-9]{2}:[0-9]{2}:[0-9]{2} ', '') この方法はどのトークナイザーでも機能し、タイムスタンプ文字列がまったくトークン化されないため、より効率的です。 両方の方法を組み合わせることもできます。プリプロセッサでタイムスタンプを除去しつつ、ポストプロセッサで残りのトークンを正規化またはフィルタリングします (たとえば、小文字化 + ERROR や INFO のような重大度を表す語の除去)。
3. ステミング。各トークンをその語幹に対応付けることで、同じ語根を共有する語形の違いにも一致するようになり、検索の再現率が向上します。 たとえば、英語のステミングでは "running"、"runs"、"run" はすべて "run" に語幹化されるため、これらの異形のいずれかに対するクエリですべてに一致します。 ClickHouse には、複数の言語向けの組み込み stem 関数があります。 例: stem(str, 'en')
4. 大文字小文字の正規化。たとえば lower や lowerUTF8 を使って、トークンを小文字化または大文字化し、大文字小文字を区別しないマッチングを可能にします。 小文字化および大文字化には、ポストプロセッサではなくプリプロセッサを推奨します。

ポストプロセッサ式は、String 型のトークンを同じ型のトークンに変換します。 また、ポストプロセッサ式は、テキスト索引が定義されているカラムまたは式のみを参照する必要があります。 カラムが Array(String) 型の場合でも、ポストプロセッサは通常の String 値として個々のトークンを処理します。

非決定論的関数の使用は許可されていません。

ポストプロセッサは、索引構築時に生成される各トークンに適用されます (array トークナイザーでは、各配列要素がトークンになります)。クエリ時の動作は関数によって異なります:

• hasToken、hasAllTokens、hasAnyTokens、および hasPhrase (サポートされている任意のトークナイザーの場合): ポストプロセッサは検索対象のトークンと検索ニードルの両方に適用され、完全に正規化されたマッチング (たとえば、大文字小文字を区別しない検索) を可能にします。hasPhrase では、ポストプロセッサ処理後のトークンが密に配置されるため、ポストプロセッサが除外したトークンによって位置の空白は生じず、そのトークンをまたいでフレーズは引き続き一致します。たとえば、the を除外するストップワードポストプロセッサでは、hasPhrase(col, 'see cat') はドキュメント see the cat に一致します。唯一の例外は splitByRegexp 索引に対する hasPhrase で、ポストプロセッサをサポートしていません (この組み合わせは例外として拒否されます)。
• その他すべての関数 (=, IN, has, hasAny, hasAll, mapContains*): 索引ヒントのルックアップでは検索ニードルのみがポストプロセッサ処理されます。行レベルの述語では、引き続き元のカラム値と比較されます。

例:

• ポストプロセッサ式を使用してストップワードを除去する:

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

• ポストプロセッサ式を使用してタイムスタンプを除去します:

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

• プリプロセッサ式でタイムスタンプを削除します:

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

• プリプロセッサとポストプロセッサを組み合わせた式でタイムスタンプを削除します:

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

• 後処理式でトークンをステミングします:

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

関数のサポート。

テキスト索引を参照する述語では、索引構築時に格納されたものと同じトークンを索引のルックアップで使えるように、グラニュールレベルのチェックの前にプリプロセッサとポストプロセッサが検索値に適用されます。 ほとんどの関数 (=, IN, startsWith, endsWith, LIKE, mapContains*) では、テキスト索引は無関係なデータブロックをスキップするためにのみ使用されます。ClickHouse はその後も、元のカラムデータに対して元の述語で、残った各行を検証します。 トークン検索関数 (hasToken, hasAllTokens, hasAnyTokens) では、テキスト索引が主な評価経路になります。ClickHouse は、索引構築時に適用されたものと同じプリプロセッサ、トークナイザー、ポストプロセッサを通して needle を正規化し、この正規化形を索引付き・索引なしの両方のテーブルパーツに対して使用します。ポストプロセッサがある場合は、haystack のトークンもクエリ時に正規化されるため (array に限らず、どのトークナイザーでも同様) 、比較の両側が一貫して変換され、結果は索引を直接読み取るかどうか (設定 query_plan_direct_read_from_text_index) 、または特定のパーツにマテリアライズされた索引があるかどうかに依存しません。たとえば、lower ポストプロセッサを使うと、hasAllTokens(col, ['FOO']) で大文字と小文字を区別しないマッチングを有効にできます。 support_phrase_search がない場合、hasPhrase は索引をヒントとしてのみ使用し、残った各行を元の述語で検証します。さらに、ポストプロセッサはフレーズと haystack のトークンの両方を同じ方法で正規化するため、結果は読み取り経路に依存せず、ポストプロセッサが削除するトークンによってフレーズの隣接関係が崩れることもありません。support_phrase_search = 1 の場合、hasPhrase は正確な direct read を使用します (存在する場合は、ポストプロセッサも引き続き適用されます) 。このポストプロセッサのサポートは splitByRegexp トークナイザーには適用されません。ポストプロセッサと組み合わせた splitByRegexp 索引に対する hasPhrase は拒否されます (以下の脚注 ³ を参照) 。 ポストプロセッサによって空文字列にマップされる検索トークンは無視され、つまり検索フレーズに存在しないものとして扱われます。

¹ LIKE と match は、記載されたトークナイザーではヒントとして direct read を使用し、それ以外では 総当たりスキャン にフォールバックします。 LIKE はさらに、プリプロセッサやポストプロセッサを使わない splitByNonAlpha および array トークナイザーに対して、direct read (ヒントなし) もサポートします (use_text_index_like_evaluation_by_dictionary_scan で有効化) 。

² ILIKE は、direct read (ヒントなし) でのみサポートされます (use_text_index_like_evaluation_by_dictionary_scan = 1、splitByNonAlpha または array トークナイザー) 。 索引をヒントとして使うフォールバックはありません。設定が無効になっている場合、またはトークナイザーがサポート対象外の場合、ILIKE では索引は使用されません。 プリプロセッサがある場合は lower または upper である必要があり、ポストプロセッサはサポートされません。

³ splitByRegexp テキスト索引に対する hasPhrase は、ポストプロセッサをサポートしません。ポストプロセッサの行レベル Rewrite は、空白で分割する splitByNonAlpha 形式のトークンを前提としているため、この組み合わせは例外で拒否されます。ポストプロセッサなしの場合、splitByRegexp は hasPhrase で完全にサポートされます。

Experimental: フレーズ検索引数のサポート (任意) 。

実験的なパラメータ support_phrase_search (デフォルト: 0) は、索引にトークン位置を保存するかどうかを制御します。 1 に設定すると、索引は位置データ (.pos ファイル内) も追加で保存し、これにより hasPhrase 関数で direct read を介した完全なフレーズ一致が可能になります。 位置情報を保存すると、索引のディスク上のサイズと書き込みコストが増加するため、これはオプトインです。 ディスク上フォーマットはまだ stable ではないため、このパラメータは Experimental であり、将来の release で変更される可能性があります。 そのため、support_phrase_search = 1 を指定して索引を作成するには、MergeTree setting allow_experimental_text_index_phrase_search を有効にする必要があります。 ポスティングリストのみの保存を維持するには support_phrase_search = 0 (デフォルト) を設定してください。この引数を指定せずに作成されたテキスト索引には位置情報は含まれません。

CREATE TABLE table(
    s String,
    -- この索引は 'bitpacking' を使用し、以下のテーブルレベルのデフォルトを上書きします:
    INDEX idx_a s TYPE text(tokenizer = 'splitByNonAlpha', posting_list_codec = 'bitpacking'),
    -- この索引はテーブル設定から 'none' を継承します:
    INDEX idx_b lower(s) TYPE text(tokenizer = 'splitByNonAlpha'))
ENGINE = MergeTree()
ORDER BY tuple()
SETTINGS text_index_posting_list_codec = 'none';

索引の粒度。 テキスト索引は、ClickHouse では スキップ索引 の一種として実装されています。 ただし、他のスキップ索引とは異なり、テキスト索引では無限粒度 (1 億) が使用されます。 これは、テキスト索引のテーブル定義を見ると確認できます。

例:

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

非常に大きな索引粒度により、テキスト索引はパート全体に対して作成されます。 明示的に指定した索引粒度は無視されます。

SELECT クエリでテキスト索引を使用するのは簡単で、一般的な文字列検索関数は自動的に索引を利用します。 カラムまたはテーブルパートに索引がない場合、文字列検索関数は低速な総当たりスキャンにフォールバックします。

テキスト関数を WHERE 句または PREWHERE 句で使用している場合は、テキスト索引を利用できます。

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

= (equals) は、指定された検索語全体と一致します。

例:

SELECT * from table WHERE str = 'Hello';

IN (in) は equals と似ていますが、すべての検索語句に一致します。

例:

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

テキスト索引で LIKE (like) および match 関数を使用するには、ClickHouse が検索語から完全なトークンを抽出できる必要があります。 ngrams トークナイザーを使用する索引では、ワイルドカードに挟まれた検索文字列の長さが N-gram の長さ以上であれば、これに該当します。

splitByNonAlpha トークナイザーを使用するテキスト索引の例:

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

support はこの例では、support、supports、supporting などに一致する可能性があります。 この種のクエリは部分文字列クエリであり、テキスト索引で高速化することはできません。

LIKE クエリでテキスト索引を活用するには、LIKE パターンを次のように書き換える必要があります。

SELECT count() FROM table WHERE comment LIKE ' support %'; -- または `% support %`

support の左右に空白があることで、その語を token として抽出できます。

幸い、ClickHouse が転置索引を活用して LIKE クエリを大幅に高速化できる特別なケースがあります。

詳しくは、LIKE/ILIKE パフォーマンスチューニングのセクションを参照してください。

multiSearchAny とその UTF-8 版である multiSearchAnyUTF8 は、複数のリテラルな部分文字列のうちいずれかが検索対象文字列に現れるかどうかを判定し、multiMatchAny は複数の正規表現のうちいずれかに一致するかどうかを判定します。 これらの関数は、LIKE および match と同じ条件でテキスト索引を使用します (上記参照) 。つまり、ClickHouse が各 needle から完全なトークンを抽出でき、かつ needles のリストが定数である必要があります。 いずれかの needle が含まれている可能性があるグラニュールは読み取られます。

multiMatchAny では、1 つの pattern をトークン要件に還元できない場合 (たとえば任意の document に一致する .* など) 、テキスト索引は使用できず、クエリは完全走査にフォールバックします。

LIKE や match と同様に、部分文字列検索と正規表現検索は ngrams および sparseGrams トークナイザーで最も効果的に機能します。 これらのトークナイザーは、互いに重なり合う文字 N-gram を索引化します。そのため needle は N-gram に分解され、単語の途中で始まるか終わるかにかかわらず、needle が部分文字列として現れる箇所であれば索引内に存在します。 したがって、needle は N-gram サイズ以上の長さがあれば、そのまま使用できます。

ngrams トークナイザーを使ったテキスト索引の例:

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

これに対して、splitByNonAlpha トークナイザーは完全なトークン (単語全体) だけを索引付けします。 needle は単語の途中で始まったり終わったりすることがあるため、ClickHouse は各 needle の先頭と末尾のトークンを除外します。そのため、索引がグラニュールを絞り込めるのは、完全なトークンを使う場合に限られます。 splitByNonAlpha で substring 検索や正規表現検索に索引を利用させるには、各 needle を区切り文字 (スペースなど) で囲み、1 つ以上の完全なトークンになるようにします。

splitByNonAlpha トークナイザーを使用したテキスト索引の例:

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

LIKE と同様に、関数 startsWith と endsWith も、検索語から完全なトークンを抽出できる場合にのみ、テキスト索引を利用できます。 ngrams トークナイザーを使用する索引では、ワイルドカードに挟まれた検索文字列の長さが N-gram 長以上の場合に該当します。 テキスト索引で ポストプロセッサ を使用している場合でも、正規化後に抽出されたヒント トークン が空でなければ、これらの関数は Hint モードでその索引を利用できます。正規化によってすべてのヒント トークン が削除される場合、その predicate では索引は使用されません。

splitByNonAlpha トークナイザーを使用するテキスト索引の例:

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

この例では、トークンとして扱われるのは clickhouse のみです。 support は support、supports、supporting などに一致する可能性があるため、トークンではありません。

clickhouse supports で始まるすべての行を検索するには、検索パターンの末尾にスペースを入れてください：

startsWith(comment, 'clickhouse supports ')`

同様に、endsWith も先頭にスペースを付けて使用する必要があります。

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

関数 hasToken は、指定した単一のトークンに対して照合を行います。

前述の関数とは異なり、これらの関数は検索語をトークン化しません (入力が単一のトークンであることを前提としています) 。

例:

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

関数 hasAnyTokens と hasAllTokens は、指定したトークンのいずれか、またはすべてに一致します。

これら 2 つの関数では、検索トークンとして、索引カラムで使用されているものと同じトークナイザーでトークン化される文字列、または検索前にトークン化されない、処理済みトークンの配列を指定できます。 詳しくは、各関数のドキュメントを参照してください。

例:

-- 文字列引数として渡された検索トークン
SELECT count() FROM table WHERE hasAnyTokens(comment, 'clickhouse olap');
SELECT count() FROM table WHERE hasAllTokens(comment, 'clickhouse olap');

-- Array(String)として渡された検索トークン
SELECT count() FROM table WHERE hasAnyTokens(comment, ['clickhouse', 'olap']);
SELECT count() FROM table WHERE hasAllTokens(comment, ['clickhouse', 'olap']);

関数 hasPhrase はフレーズとの一致を判定します。すべてのトークンが連続して、かつ検索文字列と同じ順序で出現する必要があります。

すべてのトークンがどこかに含まれていればよい hasAllTokens とは異なり、hasPhrase ではそれらが連続した並びとして出現する必要があります。 検索フレーズは、索引カラムに設定されているものと同じトークナイザーでトークン化されます。 テキスト索引で ポストプロセッサ を使用している場合、検索フレーズも索引のルックアップ前に正規化されます。 この関数を使用するには、splitByNonAlpha、splitByString、splitByRegexp、ngrams、asciiCJK、icu のいずれかのトークナイザーが必要です。

例:

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

Array関数 has は、String の配列内の単一のトークン にマッチします。

例:

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

Array 関数の hasAny と hasAll は、索引が設定された配列カラムに、定数の検索文字列の集合のいずれかまたはすべてが含まれているかどうかを判定します。

例:

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

関数 mapContains (mapContainsKey のエイリアス) は、マップのキーに対して、検索文字列から抽出されたトークンとの照合を行います。 この動作は、String カラムに対する equals 関数と似ています。 テキスト索引が使用されるのは、mapKeys(map) 式に対して作成されている場合のみです。

例:

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

関数 mapContainsValue は、map の値について、検索対象の文字列から抽出されたトークンとの一致を判定します。 この動作は、String カラムに対する equals 関数に似ています。 テキスト索引が使用されるのは、mapValues(map) 式に対して作成されている場合のみです。

例:

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

関数 mapContainsKeyLike と mapContainsValueLike は、Map のすべてのキーまたは値に対して、それぞれパターン照合を行います。

例:

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

アクセスoperator[]は、テキスト索引と組み合わせて使用することで、キーと値を絞り込めます。テキスト索引が使用されるのは、mapKeys(map) または mapValues(map) 式、あるいはその両方に対して作成されている場合のみです。

例:

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

テキスト索引で Array(T) 型および Map(K, V) 型のカラムを使用する方法については、以下の例を参照してください。

著者がキーワードでブログ記事を分類するブログプラットフォームを想像してみてください。 ユーザーがトピックを検索したりクリックしたりして、関連するコンテンツを見つけられるようにしたいとします。

次のテーブル定義を考えてみましょう。

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

テキスト索引がない場合、特定のキーワード (例: clickhouse) を含む投稿を見つけるには、すべてのエントリをスキャンする必要があります。

SELECT count() FROM posts WHERE has(keywords, 'clickhouse'); -- 低速なフルテーブルスキャン - すべての投稿のすべてのキーワードをチェックする

プラットフォームの拡大に伴い、クエリは各行の keywords 配列をすべて調べる必要があるため、これは次第に遅くなります。 このパフォーマンス上の問題を解決するため、カラム keywords にテキスト索引を定義します。

ALTER TABLE posts ADD INDEX keywords_idx(keywords) TYPE text(tokenizer = splitByNonAlpha);
ALTER TABLE posts MATERIALIZE INDEX keywords_idx; -- 既存データの索引の再構築を忘れずに

オブザーバビリティの多くのユースケースでは、ログメッセージを「要素」に分割し、それぞれを適切なデータ型で保存します。たとえば、timestamp には日時、ログレベルには enum などを使用します。 メトリクスのフィールドは、キー・バリューのペアとして保存するのが最適です。 運用チームは、デバッグ、セキュリティインシデント、監視のために、ログを効率的に検索できる必要があります。

次のログテーブルを考えてみましょう:

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

テキスト索引がない場合、Map データを検索するには、テーブル全体をスキャンする必要があります。

-- rate limitingデータを含むすべてのログを検索:
SELECT * FROM logs WHERE has(mapKeys(attributes), 'rate_limit'); -- 低速なフルテーブルスキャン

-- 特定のIPからのすべてのログを検索:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- 低速なフルテーブルスキャン

ログの量が増えると、これらのクエリは遅くなります。

解決策は、Map のキーと値に対してテキスト索引を作成することです。 フィールド名や属性タイプでログを検索する必要がある場合は、mapKeys を使ってテキスト索引を作成します。

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

属性の実際の内容内を検索する必要がある場合は、mapValues を使用してテキスト索引を作成します。

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

クエリの例:

-- レート制限されたリクエストをすべて検索:
SELECT * FROM logs WHERE mapContainsKey(attributes, 'rate_limit'); -- fast

-- 特定のIPからのログをすべて検索:
SELECT * FROM logs WHERE has(mapValues(attributes), '192.168.1.1'); -- fast

-- いずれかの属性にエラーが含まれるログをすべて検索:
SELECT * FROM logs WHERE mapContainsValueLike(attributes, '% error %'); -- fast

テキスト索引は、JSONカラムに対して次の 3 つの方法で使用できます。

1. 特定のサブカラムに対する索引 — 通常のカラムと同じように、既知の JSON パスにテキスト索引を作成します。これにより、そのパスにある値が索引化されます。
2. JSONAllPaths を使用したパスベースの索引 — 各グラニュールに存在するすべてのパスを索引化し、クエリ対象のパスを含み得ないグラニュールをスキップします。Mapカラムの場合と同様です。
3. JSONAllValues を使用した値ベースの索引 — すべての JSON パスにまたがるすべての値を索引化し、単一の索引で任意の JSON サブカラムに対する全文検索を高速化します。

通常のカラムと同じ構文で、任意の JSON サブカラムにスキップ索引を作成できます。

索引式で JSON サブカラムを参照する方法は 2 つあります。

• JSON 型ヒントで宣言された 型付きパス — 名前で直接アクセスします: json.a
• 明示的にキャストする 動的パス — :: キャスト構文を使用します: 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

Map カラムと同様に、JSON カラムでも JSONAllPaths を使ってテキスト索引を作成できます。 この索引は各グラニュールに存在する JSON パスの集合を格納し、クエリされたパスが存在しないグラニュールをスキップするために利用されます。

索引定義の例:

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 を使用すると、スキップ索引が使われていることを確認できます。 あるパスが一方のパートにしか存在しない場合、索引によってもう一方のパートはスキップされます。

例:

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

IS NOT NULL でも索引が使用され、path が存在しないグラニュールはスキップされます (その場合、値は NULL になるためです) ：

例:

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

テキスト索引を使用すると、関数 JSONAllValues を介して JSON カラムに対する検索を高速化できます。

JSONAllValues は、JSON カラム内のすべての値を Array(String) として返します。 文字列以外のデータ型の値 (たとえば整数や配列) は、テキスト表現に変換されます。 JSONAllValues を使って構築したテキスト索引は、各行のすべての JSON パスにまたがるこれらのテキスト表現に索引を作成します。 この索引により、個々の JSON サブカラムで絞り込むクエリを高速化できます。 クエリが特定のサブカラムでフィルタする場合 (例: data.user_name = 'alice') 、テキスト索引は、どの JSON 値にも検索トークンが含まれていない行 (およびグラニュール) をすばやくスキップできます。

索引定義の例:

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

索引を作成すると、JSONサブカラムに対するクエリを高速化できます。使用できるのは、String カラムで使うものと同じ関数、およびすべてのカラムで使える関数 equals です。

サブカラムへのアクセス:

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

明示的に CAST を使用したサブカラムへのアクセス:

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

IN 演算子:

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

通常のテキスト索引検索の例

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

指定されたトークンを任意の順序で含むすべての行に一致します。 この例では、While she stayed in Tokyo, the weather was great. という行がフィルタに一致します。

これに対して、フレーズ検索では、指定された順序どおりに並んだトークンに一致します。 例えば、

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

weather in Tokyo というトークン列を含む任意の行 (たとえば How is the weather in Tokyo?) に一致しますか？

テキスト索引は、フレーズ内のすべてのトークンの ポスティングリスト の積集合を求めて候補となる グラニュール を特定することで、フレーズ検索を高速化します。 その後、ClickHouse はそれらの グラニュール 内で、トークンが正確に隣接していることを検証します。 この処理は比較的コストが高く、通常のテキスト検索クエリより低速です。 フレーズ検索クエリを高速化するには、テキスト索引で位置情報の保存を有効にしてください (上記の Optional parameters を参照) 。

hasPhrase は、トークナイザー splitByNonAlpha、splitByString、splitByRegexp、ngrams、asciiCJK、icu とあわせて使用できます。 指定したフレーズ文字列は、索引のトークナイザーを使ってトークン化されます。 フレーズ内の区切り文字は無視されます。splitByNonAlpha をトークナイザーとして使用している場合、hasPhrase(text, 'quick+brown') は hasPhrase(text, 'quick brown') と同等です。

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

2 行目 ('New weather in York') は、トークンの順序が正しくないため一致しません。 3 行目 ('weather in New Orleans') は、トークン 'York' を含まないため一致しません。

一部の種類のテキスト検索クエリは、「direct read」と呼ばれる最適化によって大幅に高速化できます。

例:

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

direct read 最適化では、基になるテキストカラムにアクセスせず、テキスト索引 (つまりテキスト索引ルックアップ) のみを使ってクエリを処理します。 テキスト索引ルックアップで読み取るデータ量は比較的少ないため、ClickHouse の通常のスキップ索引 (スキップ索引のルックアップを行った後、残りのグラニュールを読み込んでフィルタリングする方式) よりも大幅に高速です。

direct read は 2 つの設定で制御されます。

• 設定 query_plan_direct_read_from_text_index (デフォルトは true) は、direct read を全体として有効にするかどうかを指定します。
• 設定 use_skip_indexes_on_data_read は、ClickHouse バージョン < 26.4 では direct read の前提条件でした。

サポートされる関数

direct read 最適化は、hasToken、hasAllTokens、hasAnyTokens 関数をサポートします。 テキスト索引が array トークナイザーで定義されている場合、direct read は equals、has、hasAny、hasAll、mapContainsKey、mapContainsValue 関数でもサポートされます。 これらの関数は、AND、OR、NOT 演算子で組み合わせることもできます。 WHERE 句または PREWHERE 句には、追加の非テキスト検索関数のフィルタ (テキストカラムまたは他のカラムに対するフィルタ) を含めることもできます。この場合でも direct read 最適化は使用されますが、効果はやや低下します (適用されるのはサポート対象のテキスト検索関数のみです) 。

クエリが direct read を利用しているか確認するには、EXPLAIN PLAN actions = 1 を付けてクエリを実行します。 例として、direct read を無効にしたクエリは

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

一方、同じクエリを 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

戻り値

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

2 番目の EXPLAIN PLAN の出力には、仮想カラム __text_index_<index_name>_<function_name>_<id> が含まれます。 このカラムが存在する場合、direct read が使用されています。

WHERE フィルタ句にテキスト検索関数しか含まれていない場合、クエリはカラムデータをまったく読み取らずに済むため、direct read によるパフォーマンス上のメリットを最大限に得られます。 ただし、クエリ内のほかの箇所でテキストカラムにアクセスしている場合でも、direct read によってパフォーマンス改善は得られます。

ヒントとしての direct read

ヒントとしての direct read は、通常の direct read と同じ原理に基づきますが、基になるテキストカラムを除外する代わりに、テキスト索引データから構築した追加のフィルタを加えます。 これは、テキスト索引だけを読み取ると偽陽性が発生する関数で使用されます。

サポートされている関数は次のとおりです: like, startsWith, endsWith, equals, has, hasPhrase, mapContainsKey, mapContainsValue。

この追加フィルタは、ほかのフィルタと組み合わせることで結果セットをさらに絞り込むための選択性を高め、他のカラムから読み取るデータ量の削減に役立ちます。

ヒントとしての direct read は、設定 query_plan_text_index_add_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

戻り値

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

一方、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

返す

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

2つ目の EXPLAIN PLAN の出力では、追加の論理積条件 (__text_index_...) がフィルタ条件に加えられていることがわかります。 PREWHERE の最適化により、フィルタ条件は3つの個別の論理積条件に分解され、計算コストの低い順に適用されます。 このクエリでは、適用順は __text_index_...、次に greaterOrEquals(...)、最後に like(...) です。 この順序により、WHERE 句の後でクエリ内で使用される重いカラムを読み取る前に、テキスト索引と元のフィルタでスキップされるグラニュールよりもさらに多くのデータグラニュールをスキップでき、読み取るデータ量をいっそう削減できます。

LIKE/ILIKE クエリのパターンが %<スペースを含まない英数字文字>% で、テキスト索引のトークナイザーが splitByNonAlpha または array の場合、ClickHouse は転置索引を利用して LIKE/ILIKE クエリを大幅に高速化します。これを実現するために、ClickHouse は一致するパターンを見つける際、フルテーブルスキャンの代わりに転置索引の Dictionary をスキャンします。

この最適化が有効な場合、LIKE/ILIKE クエリはフルテーブルスキャンより大幅に高速になるはずです。ただし、パターンが Dictionary 内のトークンの大半に一致する場合は、フルテーブルスキャンと比べて性能が悪化することがあります。幸い、それを防ぐためのフォールバックの仕組みがあります。

この最適化は、次の設定で制御されます。

• use_text_index_like_evaluation_by_dictionary_scan

フォールバックの仕組みは、次の 2 つの設定で制御されます。

• text_index_like_min_pattern_length
• text_index_like_max_postings_to_read

この最適化でサポートされるのは、関数 like と ilike のみです。

テキスト検索の条件に一致する行だけをカウントするクエリ

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

テキスト索引から応答されるため、一致する行を読み取る必要はありません。 ClickHouse は索引内のポスティングリストのカーディナリティから count を取得するため、一致する行を示す仮想カラムやその集約をスキップします。単一トークンの場合は Dictionary のカーディナリティだけで済むため、ポスティングリストは読み取りません。複数トークンの述語では、ポスティングリストをユニオン (hasAnyTokens) または積集合 (hasAllTokens) する場合にのみ読み取ります。テキスト索引はパーツ全体をカバーするため、count は正確であり、非常に大きなパーツでも高速です。

この最適化は、単一の hasToken、hasAnyTokens、または hasAllTokens 述語 (または Array/Map での同等の表現) でフィルタリングされた単独の count() に適用されます。AND/OR/NOT で結合した述語、追加のフィルタ (例: ... AND id > 10) 、m['key'] = 'value' のようなヒントモードの述語、count 以外の値の選択、フレーズ検索、または LIKE/パターン検索がある場合、クエリは代わりに行を読み取ります。マテリアライズされた索引を持たないパーツも、行を読み取ることで正しくカウントされます。

最適化が有効になっていることを確認するには、クエリプランに ReadFromTextIndexCount が含まれているか確認します。

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

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

この最適化は、query_plan_optimize_count_from_text_index (デフォルトで有効) で制御されます。direct readを前提としているため、query_plan_direct_read_from_text_index、use_skip_indexes、およびoptimize_trivial_count_queryも有効にする必要があります (いずれもデフォルトで有効) 。

テキスト索引の一部をメモリ上に保持するための、サーバー全体で共有されるさまざまな cache があります (実装の詳細 セクションを参照してください) 。 現在、I/O を削減するために、テキスト索引のデシリアライズ済みヘッダー、トークン、ポスティングリスト用の cache が用意されています。 設定 use_text_index_header_cache、use_text_index_tokens_cache、および use_text_index_postings_cache を使用すると、クエリによる個々の cache への読み取りと書き込みを無効にできます。 データパーツに存在しないトークンのキャッシュはデフォルトで有効になっており、use_text_index_negative_tokens_cache を使用して個別に制御できます。

cache をクリアするには、ステートメント SYSTEM CLEAR TEXT INDEX CACHES を使用します。

cache を設定するには、以下のサーバー設定を参照してください。

現在、テキスト索引には次の制限があります。

• トークン数が非常に多いテキスト索引 (例: 100 億トークン) のマテリアライズでは、大量のメモリを消費する可能性があります。テキスト 索引のマテリアライズは、直接 (ALTER TABLE <table> MATERIALIZE INDEX <index>) 行われる場合と、パーツのマージで間接的に行われる場合があります。
• 4,294,967,296 (= 2^32 = 約 42 億) 行を超えるパーツでは、テキスト索引をマテリアライズできません。テキスト索引がマテリアライズされていない場合、クエリはそのパーツ内での低速な総当たり検索にフォールバックします。最悪ケースの見積もりとして、パーツには String 型のカラムが 1 つだけ含まれ、MergeTree setting max_bytes_to_merge_at_max_space_in_pool (デフォルト: 150 GB) が変更されていないと仮定してください。この場合、そのカラムの 1 行あたりの平均文字数が 29.5 文字未満であれば、この状況が発生します。実際には、テーブルにはほかのカラムも含まれるため、しきい値はこれより何倍も小さくなります (ほかのカラムの数、型、サイズに依存します) 。

テキスト索引のオンディスクフォーマットのバージョンは、テーブルレベルの設定 text_index_serialization_version (デフォルト: v2_with_positions) で制御されます。 この設定は厳密な制約ではなく優先指定です。設定されたバージョンで索引を表現できない場合は、表現可能なより新しいバージョンが自動的に選択されるため、この設定が原因でテキスト索引の書き込みに失敗することはありません。 ローリングアップグレード中は、すでにアップグレード済みのサーバーで compatibility 設定を使用してフォーマットを固定します。この設定を対応するフォーマットが導入されたバージョンより古いバージョンにすると、text_index_serialization_version は自動的に古い値に戻り、新しいサーバーでも古いサーバーが引き続き読み取れるフォーマットで書き込まれます。

文字列述語は、テキスト索引やブルームフィルタベースの索引 (索引タイプ bloom_filter、ngrambf_v1、tokenbf_v1、sparse_grams) によって高速化できますが、両者は設計と想定ユースケースの点で本質的に異なります。

ブルームフィルタ索引

• 偽陽性が発生しうる確率的データ構造に基づいています。
• 集合への所属判定、つまりそのカラムにトークン X が含まれている可能性があるか、あるいは確実に含まれていないか、ということしか判定できません。
• クエリ実行時に大まかな範囲をスキップできるよう、granule レベルの情報を格納します。
• 適切にチューニングするのが難しいです (例は こちら を参照) 。
• 比較的コンパクトです (1 パーツあたり数 KB ～数 MB) 。

テキスト索引

• トークンに対して決定論的な転置索引を構築します。索引自体による偽陽性は発生しません。
• テキスト検索ワークロード向けに特化して最適化されています。
• 効率的な用語ルックアップを可能にするため、行レベルの情報を格納します。
• 比較的大きくなります (1 パーツあたり数十～数百 MB) 。

ブルームフィルタベースの索引が全文検索をサポートするのは、あくまで「副次的な効果」にすぎません。

• 高度なトークン化や前処理には対応していません。
• 複数トークンの検索には対応していません。
• 転置索引に期待されるような性能特性は得られません。

一方、テキスト索引は全文検索向けに専用設計されています。

• トークン化と前処理を提供します
• hasAllTokens、LIKE、match などのテキスト検索関数を効率的にサポートします。
• 大規模なテキストコーパスに対して、はるかに優れたスケーラビリティを発揮します。

各テキスト索引は、 (抽象的には) 2つのデータ構造で構成されます。

• 各トークンをポスティングリストに対応付けるDictionary
• それぞれが行番号の集合を表す、ポスティングリストの集合

テキスト索引は、パーツ全体に対して構築されます。 ほかのスキップ索引とは異なり、テキスト索引はデータパーツのマージ時に再構築するのではなく、そのままマージできます (詳細は以下を参照) 。

索引の作成時には、 (パーツごとに) 3つのファイルが作成されます。

Dictionaryブロックファイル (.dct)

テキスト索引内のトークンはソートされ、512トークンごとのDictionaryブロックに格納されます (ブロックサイズはパラメータ dictionary_block_size で設定できます) 。 Dictionaryブロックファイル (.dct) には、パーツ内のすべてのインデックスグラニュールに含まれるすべてのDictionaryブロックが格納されます。

索引ヘッダーファイル (.idx)

索引ヘッダーファイルには、各Dictionaryブロックについて、そのブロックの先頭トークンと、Dictionaryブロックファイル内での相対オフセットが格納されます。

このスパースインデックス構造は、ClickHouse のスパース主キー索引) に似ています。

ポスティングリストファイル (.pst)

すべてのトークンのポスティングリストは、ポスティングリストファイル内に順番に配置されます。 容量を節約しつつ高速な積集合およびユニオン操作を可能にするため、ポスティングリストは roaring bitmaps として格納されます。 ポスティングリストが posting_list_block_size より大きい場合は、複数のブロックに分割され、ポスティングリストファイルに順番に格納されます。

位置ファイル (.pos)

任意。索引引数 support_phrase_search = 1 の場合のみ作成されます。 一致した行内におけるトークンの位置を格納します。

テキスト索引のマージ

データパーツがマージされる際、テキスト索引を最初から再構築する必要はありません。代わりに、マージ処理内の別ステップで効率的にマージできます。 このステップでは、各入力パーツのテキスト索引にあるソート済みDictionaryを読み込み、新しい統合Dictionaryへ結合します。 また、ポスティングリスト内の行番号も、初期マージフェーズで作成された旧行番号から新行番号への対応関係を用いて、マージ後のデータパーツ内での新しい位置を反映するよう再計算されます。 このテキスト索引のマージ方法は、_part_offset カラムを持つ projections のマージ方法に似ています。 ソースパーツ内で索引がマテリアライズされていない場合は、索引を構築して一時ファイルに書き込み、その後、ほかのパーツの索引およびほかの一時索引ファイルの索引とともにマージされます。

デバッグ

テーブル関数 mergeTreeTextIndex を使用すると、テキスト索引の内部を調査できます。

テキストが多い大規模なデータセットに対して、テキスト索引によってどの程度パフォーマンスが向上するかを見てみましょう。 人気サイト Hacker News のコメント 2,870 万行を使用します。 以下は、テキスト索引がないテーブルです。

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

2,870万行のデータはS3上のParquetファイルにあります。これを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');

ALTER TABLE を使用して comment カラムにテキスト索引を追加し、その後マテリアライズします：

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

それでは、hasToken、hasAnyTokens、hasAllTokens 関数を使ってクエリを実行してみましょう。 以下の例では、通常の索引スキャンと direct read 最適化の間にある大きな性能差を示します。

hasToken は、テキストに特定の単一トークンが含まれているかどうかを確認します。 大文字と小文字を区別するトークン 'ClickHouse' を検索します。

direct read 無効 (標準スキャン) デフォルトでは、ClickHouse はスキップ索引を使ってグラニュールをフィルタリングし、その後、それらのグラニュールのカラムデータを読み取ります。 この動作は、direct read を無効にすることで再現できます。

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

┌

direct read 有効 (高速な索引読み取り) ここでは、direct read を有効にした状態 (デフォルト) で、同じクエリを実行します。

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

┌

direct readクエリは、索引のみを参照することで、45倍以上高速で (0.362秒 vs 0.008秒) 、処理するデータ量も大幅に少なくなります (9.51 GB vs 3.15 MB) 。

hasAnyTokens は、テキストに指定したトークンのうち少なくとも 1 つが含まれているかどうかを判定します。 'love' または 'ClickHouse' を含むコメントを検索します。

Direct read 無効 (標準スキャン)

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

┌

Direct read が有効 (索引の高速読み取り)

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

┌

この一般的な "OR" 検索では、高速化の効果がさらに顕著です。 フルカラムスキャンを回避することで、クエリは約89倍高速になります (1.329秒 対 0.015秒) 。

hasAllTokens は、テキストに指定したすべてのトークンが含まれているかどうかを判定します。 'love' と 'ClickHouse' の両方を含むコメントを検索します。

Direct read 無効時 (標準スキャン) Direct read が無効でも、標準のスキップ索引は引き続き有効です。 28.7M 行を 147.46K 行まで絞り込めますが、それでもカラムから 57.03 MB を読み取る必要があります。

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

┌

Direct read 有効 (高速な索引読み取り) Direct read では索引データを直接利用してクエリに応答するため、読み取り量は 147.46 KB のみです。

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

┌

この"AND"検索では、direct read最適化は標準的なスキップ索引スキャンと比べて26倍以上高速です (0.184秒に対し0.007秒) 。

direct read の最適化は、複合ブール式にも適用されます。 ここでは、'ClickHouse' OR 'clickhouse' の大文字と小文字を区別しない検索を行います。

Direct read 無効 (標準スキャン)

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

┌

Direct read が有効 (高速な索引読み取り)

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

┌

索引の結果を組み合わせることで、direct read クエリは 34 倍高速になり (0.450 秒に対して 0.013 秒) 、9.58 GB のカラムデータを読み取る必要がありません。 このケースでは、hasAnyTokens(comment, ['ClickHouse', 'clickhouse']) を使うほうが、より効率的で推奨される構文です。

• ブログ: ClickHouse 全文検索の一般提供を発表
• ブログ: オブジェクトストレージ向けの高性能な全文検索の構築
• ビデオ: ClickHouse における全文検索の紹介
• ビデオ: 舞台裏: ClickHouse のスケールと速度を支える全文検索
• プレゼンテーション: ClickHouse 全文検索の内部: 高速・ネイティブ・列指向
• プレゼンテーション: 転置データベース索引: 必要性、概要、実装方法、FOSDEM 2026

旧資料

• ブログ: ClickHouse における転置索引の紹介
• ブログ: ClickHouse 全文検索の内部: 高速・ネイティブ・列指向
• ビデオ: 全文索引: 設計と実験