インデックス登録されたテキストを検索する

このページでは、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,
  b ARRAY
);

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_name
struct_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%'

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"
},

ベストプラクティス

以降のセクションでは、SEARCH 関数を使用する際のベストプラクティスについて説明します。

選択的に検索

検索は、検索結果が少ない場合に特に効果的です。できるだけ具体的な検索を行ってください。

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 を含む行が返されます。

インデックス登録されたテキストを検索する

検索インデックスを作成する

SEARCH 関数を使用する

テーブル全体を検索する

列のサブセットを検索する

検索から列を除外する

別のテキスト アナライザを使用する

テキスト アナライザのオプションを構成する

その他の演算子と関数

検索インデックスの使用

例

ベスト プラクティス

選択的に検索

ORDER BY LIMIT の最適化

検索のスコープの設定

バッククォートの使用

`SEARCH` 関数を使用する

別のテキストアナライザを使用する

テキストアナライザのオプションを構成する

ベストプラクティス