[アップデート] BigQuery で search index (Preview) が利用可能になりテキストや半構造データを効率的に検索可能に

ウィスキー、シガー、パイプをこよなく愛する大栗です。

BigQuery に大きなアップデートが来てました。search index と SEARCH 関数です！テキストデータの検索が効率的になります。早速試してみました。

Google BigQuery search index は2022年4月8日現在において、プレビューのステータスです。このプロダクトまたは機能は、Google Cloud Platform の利用規約の一般提供前のサービス規約の対象となります。一般提供前のプロダクトと機能では、サポートが制限されることがあります。また、一般提供前のプロダクトや機能に変更が加えられると、他の一般提供前バージョンと互換性がない場合があります。詳細については、リリースステージの説明をご覧ください。

search index と SEARCH 関数

今まで BigQuery では全文検索エンジンのような長い文章を効率的に検索する手段がなかったため、文章を検索するような用途では使用しにくい状況でした。しかし、本日非構造テキストや半構造の JSON データを簡単に検索できるようになりました。

search index

search index は以下のデータ型に対して作成できます。

• STRING
• ARRAY
• STRUCT （STRING または ARRAY の少なくとも一つのフィールドを含む場合）
• JSON

CREATE SEARCH INDEX文で作成できます。以下のようにON dataset.simple_table(col1, col2)で対象の列を選択できます。各列の替わりにALL COLUMNSですべての列を対象としたインデックスを作成できます。

CREATE SEARCH INDEX my_index
ON dataset.simple_table(col1, col2);

また設定した search index の情報はINFORMATION_SCHEMAから確認することが可能です。

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

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

SEARCH 関数

SEARCH 関数はテキストやJSONの値やキーなどを対象に検索が可能です。

検索対象の行を取得するには、以下のように WHERE 区に SEARCH 関数を設定します。以下の例では、fooとbarが含まれる文字列を検索します。

SELECT * FROM Logs WHERE SEARCH(Logs, 'foo bar');

以下のようにバッククオートで囲む場合はfooとbarを検索するのではなくfoo barを検索します。詳しくはドキュメントを参照ください。

SELECT * FROM Logs WHERE SEARCH(Logs, '`foo bar`');

実は SEARCH 関数は search index が設定されていない列でも機能しますが、インデックスを使用するとパフォーマンスが大幅に上がります。

利用可能なロケーション

現時点では、以下のロケーションのみで利用可能です。

• US
• EU

制限事項

以下の制限事項があります。

• ビューやマテリアライズド・ビューに直接 search index を作成できませんがインデックスが付いているテーブルのビューで SEARCH 関数を呼び出すと元の search index が使用されます。
• search index は SEARCH 関数を最適化するために設計されています。CONTAINS_SUBSTR の様な他の種類のフィルタリングでは search index によって向上しません。
• テーブルにいすれかの列にポリシータグがあるか、テーブルに列レベルのアクセスポリシーがある場合は、テーブルに search index を作成できませんが、インデックス作成後にこれらを追加できます。
• search index が作成された後にベーステーブルの名前が変更されるとインデックスが無効になります。
• SEARCH 関数はポイント検索向けに設計されています。あいまい検索やタイポ修正、ワイルドカードなどの文書検索はできません。
• search index が 100% カバーできない場合でも INFORMATION_SCHEMA.SEARCH_INDEXES ビューでレポートされるインデックスストレージに対して課金されます。

やってみる

search index と SEARCH 関数を実際に動かしてみます。使用するデータは Google Cloud の Release Note を使用しました。実は Google Cloud の Release Note は BigQuery の一般公開データセットとしても公開されています。

サンプルデータの取得

まずは Release Note のデータを取り出します。

以下の SQL でデータを GCS へエクスポートします。なおエクスポート先の GCS バケットのロケーションは US にしています。

EXPORT DATA
OPTIONS (
    uri='gs://<BUCKET NAME>/<PATH>/*',
    compression='GZIP',
    format='JSON',
    overwrite=true
) AS
SELECT * FROM bigquery-public-data.google_cloud_release_notes.release_notes

次に検証用のデータセットを作成します。自分のプロジェクトの右のメニューからCreate datasetをクリックします。

Dataset ID に任意の名称を入力し、Data location はusを選択してCREATE DATASETをクリックします。

作成したデータセットの右のメニューからCreate tableをクリックします。

以下のように入力して、CREATE TABLEをクリックします。

この様にテーブルが作成されます。

インデックスの作成

search index を作成します。以下のようにCREATE SEARCH INDEX文を入力してRunをクリックします。

CREATE SEARCH INDEX desc_index
ON search_index.release_notes(description);

INFORMATION_SCHEMA で search index を確認します。

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

インデックスを確認するとcoverage_percentageが 0 となっています。これはインデックスを使用できないことを指します。実はテーブルのデータが 1GB 未満の場合はインデックスが作成されません。大きなテーブルを用意するのも大変なので、ここは先を進めてしまいます。。。

検索

最後に検索を試してみます。前章の最後でテーブルサイズが小さすぎて search index が作成されないという悲しい事実が発覚しましたが、SEARCH 関数のパフォーマンスが落ちるだけで検索自体は可能なのでやってみます。

まずはリリースノートの内容をBigQueryで検索してみます。

SELECT * FROM search_index.release_notes WHERE SEARCH(description, 'BigQuery');

以下のように 709 行が出てきました。

次にBigQuery Availableで検索してみます。

SELECT * FROM search_index.release_notes WHERE SEARCH(description, 'BigQuery Available');

するとBigQuery Availableという文字列ではなく、BigQueryとAvailableの両方が含まれる行を取り出します。ここではAvailableと先頭が大文字で検索しましたが、availableという先頭が小文字のデータも検索しています。

今度はバッククオートで囲んだ`BigQuery Available`で検索してみます。

SELECT * FROM search_index.release_notes WHERE SEARCH(description, '`BigQuery Available`');

行が選択されません。これはバッククオートで囲んだ文字列を一つの塊として検索するためです。

バッククオートで囲んだ`BigQuery now`で検索してみます。

SELECT * FROM search_index.release_notes WHERE SEARCH(description, '`BigQuery now`');

BigQuery nowの文字列で検索できました。

バッククオートで囲んだ`BigQuery Now`も試してみます。今回はNowの先頭が大文字です。

SELECT * FROM search_index.release_notes WHERE SEARCH(description, '`BigQuery Now`');

検索されませんでした。バッククオートで囲んだ文字列は大文字と小文字を識別して検索するようです。

さいごに

search index により BigQuery へ長いテキストを保存して検索することが現実的になっていました。SEARCH 関数にはあいまい検索やタイポ修正などが無いため、一般的な文章を保存すると言うよりログなどを格納して検索するといった使い方がマッチしやすそうです。

まだプレビューの段階ですが、早く GA になってほしい機能です。