検索インデックスを管理する

検索インデックスは、SEARCH 関数を使って極めて効率的な検索ができるよう設計されたデータ構造です。検索インデックスは、サポートされている関数と演算子を使用する一部のクエリを最適化することもできます。

ちょうど書籍の最後にある索引と同様に、文字列データのいずれかの列に関する検索インデックスは補助テーブルのように機能します。つまり、一意の単語を示す 1 つの列と、その単語がデータ内のどこにあるかを示すもう 1 つの列からなるテーブルのようなものです。

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

検索インデックスを作成するには、CREATE SEARCH INDEX DDL ステートメントを使用します。インデックスに登録するプリミティブ データ型を指定する方法については、検索インデックスを作成し、列とデータ型を指定するをご覧ください。データ型を指定しない場合、デフォルトでは、STRING データを含む次の型の列にインデックスが作成されます。

  • STRING
  • ARRAY<STRING>
  • STRING 型または ARRAY<STRING> 型のネストされたフィールドを 1 つ以上含む STRUCT
  • JSON

検索インデックスを作成するときには、使用するテキスト アナライザのタイプを指定できます。テキスト アナライザは、インデックス登録および検索でデータがどのようにトークン化されるかを制御します。デフォルトは LOG_ANALYZER です。このアナライザは、機械生成されるログに適しています。また、IP アドレスやメールなどのオブザーバビリティ データによく見られるトークンに関して特別なルールがあります。正確に一致させたい前処理済みのデータがある場合は、NO_OP_ANALYZER を使用してください。 PATTERN_ANALYZER は、正規表現を使用してテキストからトークンを抽出します。

デフォルトのテキスト アナライザを使用して検索インデックスを作成する

次の例では、simple_table の a 列と c 列に検索インデックスを作成し、デフォルトで LOG_ANALYZER テキスト アナライザを使用します。

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, c);

NO_OP_ANALYZER アナライザを使用してすべての列に検索インデックスを作成する

ALL COLUMNS で検索インデックスを作成すると、テーブル内のすべての STRING または JSON データがインデックスに登録されます。テーブルにそのようなデータが含まれない場合（たとえばすべての列に整数が含まれている場合）、インデックスの作成は失敗します。いずれかの STRUCT 列をインデックスに登録するよう指定すると、ネストされたすべてのサブフィールドがインデックスに登録されます。

次の例では、a、c.e、c.f.g に検索インデックスを作成し、NO_OP_ANALYZER テキスト アナライザを使用します。

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS (analyzer = 'NO_OP_ANALYZER');

検索インデックスは ALL COLUMNS で作成されたため、テーブルに追加される列に STRING データが含まれる場合は、そのインデックスが自動的に作成されます。

検索インデックスを作成し、列とデータ型を指定する

検索インデックスを作成する際には、使用するデータ型を指定できます。データ型は、インデックス登録用の JSON 列と STRUCT 列の列とサブフィールドの型を制御します。インデックスのデフォルトのデータ型は STRING です。より多くのデータ型（数値型など）を含む検索インデックスを作成するには、data_types オプションを含めた CREATE SEARCH INDEX ステートメントを使用します。

次の例では、テーブル simple_table の列 a、b、c、d に対して検索インデックスが作成されます。サポートされている列のデータ型は、STRING、INT64、TIMESTAMP です。

CREATE TABLE dataset.simple_table(a STRING, b INT64, c JSON, d TIMESTAMP);

CREATE SEARCH INDEX my_index
ON dataset.simple_table(a, b, c, d)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

すべての列に検索インデックスを作成し、データ型を指定する

data_types オプションを指定して ALL COLUMNS に検索インデックスを作成すると、指定したデータ型のいずれかに一致する列がインデックスに登録されます。JSON 列と STRUCT 列の場合、指定されたデータ型のいずれかに一致するネストされたサブフィールドはすべてインデックスに登録されます。

次の例では、データ型を指定して ALL COLUMNS に検索インデックスを作成します。テーブル my_table の列 a、b、c、d.e、d.f、d.g.h、d.g.i がインデックスに登録されます。

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c TIMESTAMP,
  d STRUCT <e INT64,
            f ARRAY<STRING>,
            g STRUCT<h STRING, i INT64>>)
AS (
  SELECT
    'hello' AS a,
    10 AS b,
    TIMESTAMP('2008-12-25 15:30:00 UTC') AS c,
    (20, ['x', 'y'], ('z', 30)) AS d;
)

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS)
OPTIONS ( data_types = ['STRING', 'INT64', 'TIMESTAMP']);

検索インデックスは ALL COLUMNS で作成されたため、テーブルに追加される列が指定されたデータ型のいずれかに一致する場合は、自動的にそのインデックスに登録されます。

インデックスの更新について

検索インデックスは BigQuery によって完全に管理されており、テーブルが変更されると自動的に更新されます。テーブルで次のようなスキーマ変更があると、完全更新がトリガーされる可能性があります。

  • ALL COLUMNS の検索インデックスを持つテーブルに、インデックス登録可能な新しい列が追加された。
  • テーブル スキーマの変更により、インデックス対象の列が更新された。

テーブル内でインデックスが付いた唯一の列を削除するか、テーブル自体の名前を変更すると、検索インデックスが自動的に削除されます。

検索インデックスは大規模なテーブル向けに設計されています。10 GB 未満のテーブルに対して検索インデックスを作成すると、そのインデックスにはデータが挿入されません。同様に、インデックス登録されたテーブルからデータを削除してテーブルサイズが 10 GB を下回るようになった場合、インデックスは一時的に無効になります。この場合、検索クエリはインデックスを使用せず、IndexUnusedReason コードは BASE_TABLE_TOO_SMALL です。この動作は、インデックス管理ジョブ用に独自の予約を使用するかどうかに関係なく発生します。インデックス登録されたテーブルのサイズが 10 GB を超えると、自動的にインデックスにデータが入ります。検索インデックスにデータが入ってアクティブになるまでは、ストレージに課金されることはありません。SEARCH 関数を使用するクエリは、一部のデータにまだインデックスが付いていない場合でも、常に正しい結果を返します。

検索インデックスに関する情報を取得する

検索インデックスの存在と準備状況を確認するには、INFORMATION_SCHEMA をクエリできます。検索インデックスのメタデータを含むビューが 2 つあります。INFORMATION_SCHEMA.SEARCH_INDEXES ビューには、データセットに対して作成されたそれぞれの検索インデックスに関する情報が含まれます。INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS ビューには、データセット内の各テーブルのどの列がインデックスに登録されているかの情報が含まれます。

次の例では、プロジェクト my_project にあるデータセット my_dataset 内のテーブルに対するアクティブな検索インデックスがすべて表示されます。これには、名前、作成に使用された DDL ステートメント、カバレッジの割合、およびテキスト アナライザが含まれます。インデックス付きベーステーブルが 10 GB 未満の場合、インデックスにはデータが入らず、その場合の coverage_percentage は 0 です。

SELECT table_name, index_name, ddl, coverage_percentage, analyzer
FROM my_project.my_dataset.INFORMATION_SCHEMA.SEARCH_INDEXES
WHERE index_status = 'ACTIVE';

結果は次のようになります。

+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| table_name  | index_name  | ddl                                                                                  | coverage_percentage | analyzer       |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+
| small_table | names_index | CREATE SEARCH INDEX `names_index` ON `my_project.my_dataset.small_table`(names)      | 0                   | NO_OP_ANALYZER |
| large_table | logs_index  | CREATE SEARCH INDEX `logs_index` ON `my_project.my_dataset.large_table`(ALL COLUMNS) | 100                 | LOG_ANALYZER   |
+-------------+-------------+--------------------------------------------------------------------------------------+---------------------+----------------+

次の例では、my_table のすべての列に対して検索インデックスを作成します。

CREATE TABLE dataset.my_table(
  a STRING,
  b INT64,
  c STRUCT <d INT64,
            e ARRAY<STRING>,
            f STRUCT<g STRING, h INT64>>) AS
SELECT 'hello' AS a, 10 AS b, (20, ['x', 'y'], ('z', 30)) AS c;

CREATE SEARCH INDEX my_index
ON dataset.my_table(ALL COLUMNS);

次のクエリは、インデックス付けするフィールドに関する情報を抽出します。index_field_path は、インデックス付けする列のフィールドを示します。これは、インデックス付きフィールドへのフルパスが指定されている STRUCT の場合だけは index_column_name と異なります。この例では、列 c に ARRAY<STRING> フィールド e と、STRING フィールド g を含む f という別の STRUCT が含まれていて、それぞれにインデックスが付けられます。

SELECT table_name, index_name, index_column_name, index_field_path
FROM my_project.dataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS

次のような結果になります。

+------------+------------+-------------------+------------------+
| table_name | index_name | index_column_name | index_field_path |
+------------+------------+-------------------+------------------+
| my_table   | my_index   | a                 | a                |
| my_table   | my_index   | c                 | c.e              |
| my_table   | my_index   | c                 | c.f.g            |
+------------+------------+-------------------+------------------+

次のクエリは、INFORMATION_SCHEMA.SEARCH_INDEX_COUMNS ビューを INFORMATION_SCHEMA.SEARCH_INDEXES と INFORMATION_SCHEMA.COLUMNS ビューに結合し、検索インデックスのステータスと各列のデータ型を含めます。

SELECT
  index_columns_view.index_catalog AS project_name,
  index_columns_view.index_SCHEMA AS dataset_name,
  indexes_view.TABLE_NAME AS table_name,
  indexes_view.INDEX_NAME AS index_name,
  indexes_view.INDEX_STATUS AS status,
  index_columns_view.INDEX_COLUMN_NAME AS column_name,
  index_columns_view.INDEX_FIELD_PATH AS field_path,
  columns_view.DATA_TYPE AS data_type
FROM
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEXES indexes_view
INNER JOIN
  mydataset.INFORMATION_SCHEMA.SEARCH_INDEX_COLUMNS index_columns_view
  ON
    indexes_view.TABLE_NAME = index_columns_view.TABLE_NAME
    AND indexes_view.INDEX_NAME = index_columns_view.INDEX_NAME
LEFT OUTER JOIN
  mydataset.INFORMATION_SCHEMA.COLUMNS columns_view
  ON
    indexes_view.INDEX_CATALOG = columns_view.TABLE_CATALOG
    AND indexes_view.INDEX_SCHEMA = columns_view.TABLE_SCHEMA
    AND index_columns_view.TABLE_NAME = columns_view.TABLE_NAME
    AND index_columns_view.INDEX_COLUMN_NAME = columns_view.COLUMN_NAME
ORDER BY
  project_name,
  dataset_name,
  table_name,
  column_name;

次のような結果になります。 

+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| project    | dataset    | table    | index_name | status | column_name | field_path | data_type                                                     |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+
| my_project | my_dataset | my_table | my_index   | ACTIVE | a           | a          | STRING                                                        |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.e        | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
| my_project | my_dataset | my_table | my_index   | ACTIVE | c           | c.f.g      | STRUCT<d INT64, e ARRAY<STRING>, f STRUCT<g STRING, h INT64>> |
+------------+------------+----------+------------+--------+-------------+------------+---------------------------------------------------------------+

インデックス管理オプション

インデックスを作成して BigQuery に維持させるには、次の 2 つの方法があります。

  • デフォルトの共有スロットプールを使用する: インデックスが付く予定のデータが組織ごとの上限を下回っている場合は、インデックス管理用に無料の共有スロットプールを使用できます。
  • 独自の予約を使用する: 大規模な本番環境ワークロードでより予測可能かつ一貫したインデックス付けを進めるため、インデックス管理用に独自の予約を使用できます。

共有スロットを使用する

インデックス専用の予約を使用するようプロジェクトをまだ構成していない場合、インデックス管理は次の制約に従い、無料の共有スロットプールで処理されます。

テーブルにデータを追加した結果としてインデックス付きテーブルの合計サイズが組織の上限を超えた場合、BigQuery はすべてのインデックス付きテーブルに対するインデックス管理を一時停止します。この場合、INFORMATION_SCHEMA.SEARCH_INDEXES ビューの index_status フィールドに PENDING DISABLEMENT が表示され、インデックスは削除対象のキューに入ります。インデックスの無効化が保留状態になっている間、そのインデックスは引き続き検索クエリで使用され、インデックス ストレージに対して料金が発生します。インデックスが削除されると、index_status フィールドにはインデックスが TEMPORARILY DISABLED として表示されます。この状態では、クエリはインデックスを使用せず、インデックス ストレージに対して料金は発生しません。この場合、IndexUnusedReason コードは BASE_TABLE_TOO_LARGE です。

テーブルからデータを削除した結果、インデックス付きテーブルの合計サイズが組織ごとの上限を下回ると、すべてのインデックス付きテーブルのインデックス管理が再開します。INFORMATION_SCHEMA.SEARCH_INDEXES ビューの index_status フィールドは ACTIVE になり、クエリでインデックスを使用でき、インデックス ストレージに対して料金が発生します。

BigQuery は、共有プールの使用可能容量や確認されるインデックス処理スループットを保証することはありません。本番環境アプリケーションでは、インデックス処理に専用スロットを使用することをおすすめします。

独自の予約を使用する

デフォルトの共有スロットプールを使用する代わりに、必要に応じて、独自の予約を指定してテーブルにインデックスを付けることもできます。独自の予約を使用すると、作成、更新、バックグラウンド最適化などのインデックス管理ジョブのパフォーマンスが予測可能で一貫したものになります。

  • 予約でインデックス ジョブが実行されるときには、テーブルサイズの上限がありません。
  • 独自の予約を使用すると、インデックスを柔軟に管理できます。非常に大きなインデックスを作成する必要がある場合、またはインデックス付きテーブルを大幅に更新する必要がある場合には、より多くのスロットを一時的に割り当てに追加できます。

指定された予約を使用してプロジェクト内のテーブルにインデックスを付けるには、テーブルが配置されているリージョンで予約を作成します。次に、job_type を BACKGROUND に設定して、プロジェクトを予約に割り当てます。

SQL

CREATE ASSIGNMENT DDL ステートメントを使用します。

  1. Google Cloud コンソールで [BigQuery] ページに移動します。

    BigQuery に移動

  2. クエリエディタで次のステートメントを入力します。

    CREATE ASSIGNMENT
  `ADMIN_PROJECT_ID.region-LOCATION.RESERVATION_NAME.ASSIGNMENT_ID`
OPTIONS (
  assignee = 'projects/PROJECT_ID',
  job_type = 'BACKGROUND');

    以下を置き換えます。

    • ADMIN_PROJECT_ID: 予約リソースを所有する管理プロジェクトのプロジェクト ID
    • LOCATION: 予約のロケーション
    • RESERVATION_NAME: 予約の名前

    • ASSIGNMENT_ID: 割り当ての ID。

      ID はプロジェクトとロケーションごとに一意でなければならず、先頭と末尾を英小文字または数字にする必要があり、英小文字、数字、ダッシュのみを使用できます。

    • PROJECT_ID: インデックスを付けるテーブルを含むプロジェクトの ID。このプロジェクトは予約に割り当てられます。

  3. [実行] をクリックします。

クエリの実行方法については、インタラクティブ クエリを実行するをご覧ください。

bq

bq mk コマンドを使用します。

bq mk \
    --project_id=ADMIN_PROJECT_ID \
    --location=LOCATION \
    --reservation_assignment \
    --reservation_id=RESERVATION_NAME \
    --assignee_id=PROJECT_ID \
    --job_type=BACKGROUND \
    --assignee_type=PROJECT

以下を置き換えます。

  • ADMIN_PROJECT_ID: 予約リソースを所有する管理プロジェクトのプロジェクト ID
  • LOCATION: 予約のロケーション
  • RESERVATION_NAME: 予約の名前
  • PROJECT_ID: この予約に割り当てられるプロジェクトの ID

インデックス ジョブの表示

単一のテーブルでインデックスが作成または更新されるたびに、新しいインデックス ジョブが作成されます。ジョブに関する情報を表示するには、INFORMATION_SCHEMA.JOBS* ビューをクエリします。クエリの WHERE 句で job_type IS NULL AND SEARCH(job_id, '`search_index`') を設定すると、インデックス ジョブをフィルタリングできます。次の例では、プロジェクト my_project 内の最新の 5 つのインデックス ジョブが一覧表示されます。

SELECT *
FROM
 region-us.INFORMATION_SCHEMA.JOBS
WHERE
  project_id  = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
ORDER BY
 creation_time DESC
LIMIT 5;

予約サイズの選択

予約に適切なスロット数を選択するには、インデックス管理ジョブが実行されるタイミング、使用されるスロット数、時間の経過に伴う使用状況の推移を考慮する必要があります。BigQuery は、次のようなときにインデックス管理ジョブをトリガーします。

  • テーブルにインデックスを作成したとき。
  • インデックス付きテーブルでデータが変更されたとき。
  • テーブルのスキーマが変更された影響として、どの列がインデックスに登録されるかが変化したとき。
  • インデックスのデータとメタデータが定期的に最適化または更新されるとき。

テーブルに対するインデックス管理ジョブに必要なスロット数は、次の要因に依存します。

  • テーブルのサイズ
  • テーブルへのデータ取り込みのレート
  • テーブルに適用される DML ステートメントのレート
  • インデックスを構築および維持するときに許容される遅延
  • インデックスの複雑さ（通常は、重複する語の数などのデータ属性によって決まります）
初期の見積もり

次の見積もりを参考にして、予約に必要なスロット数を大まかに判断できます。インデックス ワークロードの性質はさまざまに異なるので、データのインデックス登録を開始した後で、再び要件を評価してください。

  • 既存のデータ: 1,000 スロットの予約では、BigQuery の既存のテーブルを 1 秒あたり最大 4 GiB（つまり 1 日あたり約 336 TiB）の平均レートでインデックス処理できます。
  • 新しく取り込まれるデータ: 通常、新しく取り込まれるデータのインデックス処理ではより多くのリソースが消費されます。これは、テーブルとそのインデックスに対して変換的な最適化が複数回行われるためです。新しく取り込まれたデータのインデックス処理は、同じデータの最初のバックフィル インデックス処理と比較して平均で 3 倍のリソースを消費します。
  • 変更頻度の低いデータ: データ変更がほとんど、またはまったく生じないインデックス付きテーブルでは、継続的なインデックス維持に必要なリソースが大幅に減ります。開始点として、同じデータの最初のバックフィル インデックス処理に必要なスロットの 1/5（ただし少なくとも 250 スロット）を維持することをおすすめします。
  • インデックス処理の進捗は、予約のサイズにほぼ比例します。ただし、250 スロット未満の予約をインデックス処理に使用すると、非効率になってインデックス処理の進捗が遅くなる可能性があるため、おすすめしません。
  • これらの見積もりは、機能、最適化、実際の使用量によって異なる可能性があります。
  • 組織の合計テーブルサイズがリージョンのインデックス処理の上限を超える場合は、インデックス処理用に非ゼロの予約を割り当てておくべきです。そうしない場合、インデックス処理がデフォルト階層にフォールバックしてすべてのインデックスが意図せず削除される可能性があります。
使用状況と進捗状況のモニタリング

インデックス管理ジョブを効率的に実行するのに必要なスロット数を評価する最良の方法は、スロットの使用状況をモニタリングし、それに応じて予約サイズを調整することです。次のクエリは、インデックス管理ジョブの日次スロット使用状況を生成します。リージョン us-west1 では過去 30 日間のみが含まれます。

SELECT
  TIMESTAMP_TRUNC(job.creation_time, DAY) AS usage_date,
  -- Aggregate total_slots_ms used for index-management jobs in a day and divide
  -- by the number of milliseconds in a day. This value is most accurate for
  -- days with consistent slot usage.
  SAFE_DIVIDE(SUM(job.total_slot_ms), (1000 * 60 * 60 * 24)) AS average_daily_slot_usage
FROM
  `region-us-west1`.INFORMATION_SCHEMA.JOBS job
WHERE
  project_id = 'my_project'
  AND job_type IS NULL
  AND SEARCH(job_id, '`search_index`')
GROUP BY
  usage_date
ORDER BY
  usage_date DESC
limit 30;

インデックス管理ジョブを実行するための十分なスロットがない場合、インデックスがテーブルと非同期状態になってインデックス ジョブが失敗することがあります。その場合、BigQuery はインデックスをまったくゼロの状態から再構築します。非同期インデックスを回避するには、データ取り込みと最適化によるインデックスの更新をサポートできるような十分なスロットがあることを確認してください。スロット使用状況のモニタリングの詳細については、管理リソースグラフをご覧ください。

おすすめの方法

  • 検索インデックスは大規模なテーブル向けに設計されています。テーブルのサイズが大きいほど、検索インデックスによるパフォーマンス向上が大きくなります。
  • 一意の値が非常に少ない列をインデックスに登録することは避けてください。
  • SEARCH 関数やその他のサポートされている関数と演算子で使用しない列は、インデックスに登録しないでください。
  • ALL COLUMNS に対して検索インデックスを作成するときは、注意が必要です。STRING または JSON のデータを含む列を追加するたびに、インデックスが作成されます。
  • 本番環境のアプリケーションでインデックス管理を行うには、独自の予約を使用するのが適切です。インデックス管理ジョブにデフォルトの共有スロットプールを使用する場合は、組織ごとのサイズ制限が適用されます。

検索インデックスを削除する

検索インデックスが不要になった場合や、テーブルのどの列がインデックスに登録されるかを変更したい場合には、そのテーブルに現在あるインデックスを削除できます。DROP SEARCH INDEX DDL ステートメントを使用します。

インデックスのあるテーブルが削除されると、そのインデックスは自動的に削除されます。

例:

DROP SEARCH INDEX my_index ON dataset.simple_table;

次のステップ