インデックス登録されたデータを検索する
このページでは、BigQuery での検索の例を示します。データをインデックスに登録すると、BigQuery で SEARCH 関数や、=、IN、LIKE、STARTS_WITH などその他の関数と演算子を使用するクエリを最適化できるようになります。
SQL クエリでは、一部のデータがまだインデックスに登録されていない場合でも、取り込まれたすべてのデータから正しい結果が返されます。ただし、インデックスを使用することにより、クエリのパフォーマンスが大幅に向上します。スキャンされるデータが少ないために検索結果の数がテーブル内の合計行数に対してごく少なくなる場合に、処理されたバイト数とスロットのミリ秒数が最大限削減されます。クエリにインデックスが使用されたかどうかを確認するには、検索インデックスの使用をご覧ください。
検索インデックスを作成する
次の Logs というテーブルを使用して、SEARCH 関数のさまざまな使用方法を示します。このサンプル テーブルは非常に小さなものですが、実際には、SEARCH を使用して得られるパフォーマンスはテーブルのサイズに合わせて向上します。
CREATE TABLE my_dataset.Logs (Level STRING, Source STRING, Message STRING) AS ( SELECT 'INFO' as Level, '65.177.8.234' as Source, 'Entry Foo-Bar created' as Message UNION ALL SELECT 'WARNING', '132.249.240.10', 'Entry Foo-Bar already exists, created by 65.177.8.234' UNION ALL SELECT 'INFO', '94.60.64.181', 'Entry Foo-Bar deleted' UNION ALL SELECT 'SEVERE', '4.113.82.10', 'Entry Foo-Bar does not exist, deleted by 94.60.64.181' UNION ALL SELECT 'INFO', '181.94.60.64', 'Entry Foo-Baz created' );
テーブルは次のようになります。
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 65.177.8.234 | Entry Foo-Bar created | | WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 | | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | | INFO | 181.94.60.64 | Entry Foo-Baz created | +---------+----------------+-------------------------------------------------------+
デフォルトのテキスト アナライザを使用して、Logs テーブルに検索インデックスを作成します。
CREATE SEARCH INDEX my_index ON my_dataset.Logs(ALL COLUMNS);
検索インデックスの詳細については、検索インデックスを管理するをご覧ください。
SEARCH 関数を使用する
SEARCH 関数は、データに対してトークン化された検索を行います。SEARCH は、ルックアップを最適化するためにインデックスとともに使用するように設計されています。SEARCH 関数を使用すると、テーブル全体を検索することも、検索対象を特定の列に制限することもできます。
テーブル全体を検索する
次のクエリは、Logs テーブルのすべての列で値 bar を検索し、表記の大文字小文字を問わず、この値を含む行を返します。検索インデックスではデフォルトのテキスト アナライザを使用するため、SEARCH 関数で指定する必要はありません。
SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, 'bar');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 65.177.8.234 | Entry Foo-Bar created | | WARNING | 132.249.240.10 | Entry Foo-Bar already exists, created by 65.177.8.234 | | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
次のクエリは、Logs テーブルのすべての列で値 `94.60.64.181` を検索し、この値を含む行を返します。バッククォートを使用すると完全一致検索を行うため、Logs テーブル中の 181.94.60.64 を含む最後の行は結果に含まれません。
SELECT * FROM my_dataset.Logs WHERE SEARCH(Logs, '`94.60.64.181`');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
列のサブセットを検索する
SEARCH を使用すると、データを検索する列のサブセットを簡単に指定できます。次のクエリは、Logs テーブルの Message 列で値 94.60.64.181 を検索し、この値を含む行を返します。
SELECT * FROM my_dataset.Logs WHERE SEARCH(Message, '`94.60.64.181`');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
次のクエリは、Logs テーブルの Source 列と Message 列の両方を検索します。いずれかの列の値 94.60.64.181 を含む行が返されます。
SELECT * FROM my_dataset.Logs WHERE SEARCH((Source, Message), '`94.60.64.181`');
+---------+----------------+-------------------------------------------------------+ | Level | Source | Message | +---------+----------------+-------------------------------------------------------+ | INFO | 94.60.64.181 | Entry Foo-Bar deleted | | SEVERE | 4.113.82.10 | Entry Foo-Bar does not exist, deleted by 94.60.64.181 | +---------+----------------+-------------------------------------------------------+
検索から列を除外する
テーブルに多数の列があり、その大部分を検索する場合は、検索から除外する列のみを指定する方が簡単です。次のクエリは、Logs テーブルの Message 列を除くすべての列を検索します。Message 以外の列の値 94.60.64.181 を含む行を返します。
SELECT * FROM my_dataset.Logs WHERE SEARCH( (SELECT AS STRUCT Logs.* EXCEPT (Message)), '`94.60.64.181`');
+---------+----------------+---------------------------------------------------+ | Level | Source | Message | +---------+----------------+---------------------------------------------------+ | INFO | 94.60.64.181 | Entry Foo-Bar deleted | +---------+----------------+---------------------------------------------------+
別のテキスト アナライザを使用する
次の例では、NO_OP_ANALYZER テキスト アナライザを使用するインデックスを持つ contact_info というテーブルを作成します。
CREATE TABLE my_dataset.contact_info (name STRING, email STRING) AS ( SELECT 'Kim Lee' AS name, 'kim.lee@example.com' AS email UNION ALL SELECT 'Kim' AS name, 'kim@example.com' AS email UNION ALL SELECT 'Sasha' AS name, 'sasha@example.com' AS email ); CREATE SEARCH INDEX noop_index ON my_dataset.contact_info(ALL COLUMNS) OPTIONS (analyzer = 'NO_OP_ANALYZER');
+---------+---------------------+ | name | email | +---------+---------------------+ | Kim Lee | kim.lee@example.com | | Kim | kim@example.com | | Sasha | sasha@example.com | +---------+---------------------+
次のクエリでは、name 列で Kim を検索し、email 列で kim を検索します。検索インデックスではデフォルトのテキスト アナライザを使用しないため、アナライザの名前を SEARCH 関数に渡す必要があります。
SELECT name, SEARCH(name, 'Kim', analyzer=>'NO_OP_ANALYZER') AS name_Kim, email, SEARCH(email, 'kim', analyzer=>'NO_OP_ANALYZER') AS email_kim FROM my_dataset.contact_info;
NO_OP_ANALYZER はテキストを変更しないため、SEARCH 関数は完全一致の場合にのみ TRUE を返します。
+---------+----------+---------------------+-----------+ | name | name_Kim | email | email_kim | +---------+----------+---------------------+-----------+ | Kim Lee | FALSE | kim.lee@example.com | FALSE | | Kim | TRUE | kim@example.com | FALSE | | Sasha | FALSE | sasha@example.com | FALSE | +---------+----------+---------------------+-----------+
テキスト アナライザのオプションを構成する
LOG_ANALYZER と PATTERN_ANALYZER のテキスト アナライザをカスタマイズするには、構成オプションに JSON 形式の文字列を追加します。テキスト アナライザは、SEARCH 関数、CREATE
SEARCH INDEX DDL ステートメント、TEXT_ANALYZE 関数で構成できます。
次の例では、LOG_ANALYZER テキスト アナライザを使用するインデックスを持つ complex_table というテーブルを作成します。ここでは JSON 形式の文字列を使用して、アナライザ オプションを構成します。
CREATE TABLE dataset.complex_table( a STRING, my_struct STRUCT<string_field STRING, int_field INT64>, b ARRAY<STRING> ); CREATE SEARCH INDEX my_index ON dataset.complex_table(a, my_struct, b) OPTIONS (analyzer = 'LOG_ANALYZER', analyzer_options = '''{ "token_filters": [ { "normalization": {"mode": "NONE"} } ] }''');
次の表に、さまざまなテキスト アナライザを使用した SEARCH 関数の呼び出し例とその結果を示します。最初の表では、デフォルトのテキスト アナライザである LOG_ANALYZER を使用して SEARCH 関数を呼び出しています。
| 関数呼び出し | 戻り値 | 理由 |
|---|---|---|
| SEARCH('foobarexample', NULL) | ERROR | search_terms が「NULL」です。 |
| SEARCH('foobarexample', '') | ERROR | search_terms にトークンが含まれていません。 |
| SEARCH('foobar-example', 'foobar example') | TRUE | 「-」と「 」は区切り文字です。 |
| SEARCH('foobar-example', 'foobarexample') | FALSE | search_terms が分割されていません。 |
| SEARCH('foobar-example', 'foobar\\&example') | TRUE | 区切り文字であるアンパサンドが二重バックスラッシュでエスケープされます。 |
| SEARCH('foobar-example', R'foobar\&example') | TRUE | 元の文字列のアンパサンドが 1 つのバックスラッシュでエスケープされます。 |
| SEARCH('foobar-example', '`foobar&example`') | FALSE | バッククォートが使用されているため、foobar&example の完全一致が必要です。 |
| SEARCH('foobar&example', '`foobar&example`') | TRUE | 完全一致が見つかりました。 |
| SEARCH('foobar-example', 'example foobar') | TRUE | キーワードの順序は関係ありません。 |
| SEARCH('foobar-example', 'foobar example') | TRUE | トークンが小文字になります。 |
| SEARCH('foobar-example', '`foobar-example`') | TRUE | 完全一致が見つかりました。 |
| SEARCH('foobar-example', '`foobar`') | FALSE | バッククォートでは大文字と小文字の区別が維持されます。 |
| SEARCH('`foobar-example`', '`foobar-example`') | FALSE | data_to_search に使用するバッククォートは特別な意味を持ちません。 |
| SEARCH('foobar@example.com', '`example.com`') | TRUE | data_to_search の区切り文字の後に、完全一致が見つかりました。 |
| SEARCH('a foobar-example b', '`foobar-example`') | TRUE | スペース区切り文字の間に完全一致が見つかりました。 |
| SEARCH(['foobar', 'example'], 'foobar example') | FALSE | すべての検索キーワードに一致する単一の配列エントリがありません。 |
| SEARCH('foobar=', '`foobar\\=`') | FALSE | search_terms は foobar\= と同等です。 |
| SEARCH('foobar=', R'`foobar\=`') | FALSE | 上記の例と同じです。 |
| SEARCH('foobar=', 'foobar\\=') | TRUE | データとクエリに使用する等号は区切り文字です。 |
| SEARCH('foobar=', R'foobar\=') | TRUE | 上記の例と同じです。 |
| SEARCH('foobar.example', '`foobar`') | TRUE | 完全一致が見つかりました。 |
| SEARCH('foobar.example', '`foobar.`') | FALSE | `foobar.` は、バッククォートが使用されているため分析されません。 |
| SEARCH('foobar..example', '`foobar.`') | TRUE | `foobar.` は、バッククォートが使用されているため分析されません。直後に「.」が続いています。 |
次の表に、NO_OP_ANALYZER テキスト アナライザを使用した SEARCH 関数の呼び出しの例と、さまざまな戻り値の理由を示ます。
| 関数呼び出し | 戻り値 | 理由 |
|---|---|---|
| SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER') | TRUE | 完全一致が見つかりました。 |
| SEARCH('foobar', '`foobar`', analyzer=>'NO_OP_ANALYZER') | FALSE | NO_OP_ANALYZER では、バッククォートは特殊文字ではありません。 |
| SEARCH('foobar', 'foobar', analyzer=>'NO_OP_ANALYZER') | FALSE | 大文字と小文字が一致しません。 |
| SEARCH('foobar example', 'foobar', analyzer=>'NO_OP_ANALYZER') | FALSE | NO_OP_ANALYZER には区切り文字がありません。 |
| SEARCH('', '', analyzer=>'NO_OP_ANALYZER') | TRUE | NO_OP_ANALYZER には区切り文字がありません。 |
その他の演算子と関数
検索インデックスの最適化は、複数の演算子、関数、述語を使用して行うことができます。
演算子と比較関数を使用して最適化する
BigQuery では、equal 演算子（=）、IN 演算子、LIKE 演算子または STARTS_WITH 関数を使用するクエリを、文字列リテラルをインデックス登録されたデータと比較するように最適化できます。
文字列述語で最適化する
検索インデックスの最適化の対象となる述語は次のとおりです。
column_name = 'string_literal''string_literal' = column_namestruct_column.nested_field = 'string_literal'string_array_column[OFFSET(0)] = 'string_literal'string_array_column[ORDINAL(1)] = 'string_literal'column_name IN ('string_literal1', 'string_literal2', ...)STARTS_WITH(column_name, 'prefix')column_name LIKE 'prefix%'
数値述語で最適化する
プレビュー版のサポートについては、bq-search-team@google.com までメールでお問い合わせください。
検索インデックスが数値データ型で作成されている場合、BigQuery は、インデックス登録されたデータに対して等価演算子（=）または IN 演算子を使用する一部のクエリを最適化できます。検索インデックスの最適化の対象となる述語は次のとおりです。
INT64(json_column.int64_field) = 1int64_column = 1int64_array_column[OFFSET(0)] = 1int64_column IN (1, 2)struct_column.nested_int64_field = 1struct_column.nested_timestamp_field = TIMESTAMP "2024-02-15 21:31:40"timestamp_column = "2024-02-15 21:31:40"timestamp_column IN ("2024-02-15 21:31:40", "2024-02-16 21:31:40")
インデックス データを生成する関数を最適化する
BigQuery では、特定の関数がインデックス登録されたデータに適用される場合の検索インデックスの最適化がサポートされています。検索インデックスでデフォルトの LOG_ANALYZER テキスト アナライザを使用する場合は、UPPER 関数または LOWER 関数を列に適用できます（例: UPPER(column_name) = 'STRING_LITERAL'）。
インデックス登録された JSON 列から抽出された JSON スカラー文字列の場合、STRING 関数またはその安全なバージョン SAFE.STRING を適用できます。抽出された JSON 値が文字列でない場合、STRING 関数はエラーを生成し、SAFE.STRING 関数は NULL を返します。
インデックス登録された JSON 形式の STRING（JSON ではない）データの場合、次の関数を適用できます。
たとえば次のように、dataset.person_data という名前で JSON 列と STRING 列がある、インデックス登録されたテーブルがあるとします。
+----------------------------------------------------------------+-----------------------------------------+
| json_column | string_column |
+----------------------------------------------------------------+-----------------------------------------+
| { "name" : "Ariel", "email" : "cloudysanfrancisco@gmail.com" } | { "name" : "Ariel", "job" : "doctor" } |
+----------------------------------------------------------------+-----------------------------------------+
以下のクエリは最適化の対象となります。
SELECT * FROM dataset.person_data WHERE SAFE.STRING(json_column.email) = 'cloudysanfrancisco@gmail.com';
SELECT * FROM dataset.person_data WHERE JSON_VALUE(string_column, '$.job') IN ('doctor', 'lawyer', 'teacher');
これらの関数の組み合わせも最適化されます（UPPER(JSON_VALUE(json_string_expression)) = 'FOO' など）。
検索インデックスの使用
クエリで検索インデックスが使用されたかどうかを判断するには、[クエリ結果] でクエリの [ジョブ情報] を確認します。[Index Usage Mode] フィールドと [Index Unused Reasons] フィールドに検索インデックスの使用について詳細情報が表示されます。
検索インデックスの使用に関する情報は、Jobs.Get API メソッドの searchStatistics フィールドでも確認できます。searchStatistics の indexUsageMode フィールドは、次の値の検索インデックスが使用されたかどうかを示します。
UNUSED: 検索インデックスは使用されていません。PARTIALLY_USED: クエリの一部では検索インデックスが使用され、一部では使用されていません。FULLY_USED: クエリ内のすべてのSEARCH関数で検索インデックスが使用されています。
indexUsageMode が UNUSED または PARTIALLY_USED の場合、indexUnusuedReasons フィールドには、クエリで検索インデックスが使用されなかった理由を示す情報が含まれます。
クエリの searchStatistics を表示するには、bq show コマンドを実行します。
bq show --format=prettyjson -j JOB_ID
例
テーブル内のデータに対して SEARCH 関数を呼び出すクエリを実行するとします。クエリのジョブの詳細を表示してジョブ ID を確認し、bq show コマンドを実行して詳細情報を確認できます。
bq show --format=prettyjson --j my_project:US.bquijob_123x456_789y123z456c
出力には、以下に示すような searchStatistics など、多くのフィールドが含まれます。この例では、indexUsageMode はインデックスが使用されていないことを示しています。これは、テーブルに検索インデックスがないためです。この問題を解決するには、テーブルに検索インデックスを作成します。クエリで検索インデックスが使用されない理由の一覧については、indexUnusedReason code フィールドをご覧ください。
"searchStatistics": {
"indexUnusedReasons": [
{
"baseTable": {
"datasetId": "my_dataset",
"projectId": "my_project",
"tableId": "my_table"
},
"code": "INDEX_CONFIG_NOT_AVAILABLE",
"message": "There is no search index configuration for the base table `my_project:my_dataset.my_table`."
}
],
"indexUsageMode": "UNUSED"
},
ベスト プラクティス
以降のセクションでは、検索のベスト プラクティスについて説明します。
選択的に検索
検索は、検索結果が少ない場合に特に効果的です。できるだけ具体的な検索を行ってください。
ORDER BY LIMIT の最適化
非常に大きなパーティション分割テーブルに対して SEARCH、=、IN、LIKE、STARTS_WITH を使用するクエリは、パーティション分割フィールドで ORDER BY 句と LIMIT 句を使用することにより最適化できます。SEARCH 関数を含まないクエリでは、最適化機能を利用するために、その他の演算子と関数を使用できます。この最適化は、テーブルにインデックスが登録されているかどうかにかかわらず適用されます。これは、一般的な用語を検索する場合に便利です。たとえば、以前に作成した Logs テーブルが day という追加の DATE 型の列でパーティション分割されたとします。次のクエリが最適化されます。
SELECT Level, Source, Message FROM my_dataset.Logs WHERE SEARCH(Message, "foo") ORDER BY day LIMIT 10;
検索のスコープの設定
SEARCH 関数を使用すると、テーブル中で検索キーワードが含まれると想定される列のみを検索します。これにより、パフォーマンスが向上し、スキャンが必要なバイト数が削減されます。
バッククォートの使用
LOG_ANALYZER テキスト アナライザで SEARCH 関数を使用する場合は、検索クエリをバッククォートで囲むと完全一致になります。これは、検索で大文字と小文字を区別する場合や、区切り文字として解釈すべきでない文字が含まれている場合に有用です。たとえば、IP アドレス 192.0.2.1 を検索するには、`192.0.2.1` を使用します。バッククォートがない場合、検索では、任意の順序で個々のトークン 192、0、2、1 を含む行が返されます。