機能紹介：BigQueryのSEARCH関数がGA！

Last updated at 2024-08-30Posted at 2024-08-30

こんにちは。codepoetです。

はじめに

BigQueryで、データを効率的に検索するためのSEARCH関数がGAになりました。この記事では、SEARCH関数の使い方とその応用方法について解説します。

SEARCH関数の基本構文

SEARCH関数は、指定されたデータが検索クエリに基づいて特定の検索用語（トークン）を含んでいるかどうかを確認するために使用されます。関数は、検索クエリとテキスト解析のルールに基づいて、すべての検索用語がデータ内に現れる場合にTRUEを返し、それ以外の場合はFALSEを返します。

SEARCH(
  data_to_search, search_query
  [, json_scope => { 'JSON_VALUES' | 'JSON_KEYS' | 'JSON_KEYS_AND_VALUES' } ]
  [, analyzer => { 'LOG_ANALYZER' | 'NO_OP_ANALYZER' | 'PATTERN_ANALYZER'} ]
  [, analyzer_options => analyzer_options_values ]
)

パラメータの詳細

パラメータ	説明	デフォルト値	備考
`data_to_search`	検索対象のデータ。`STRING`、`ARRAY<STRING>`、`JSON`、`STRUCT`など、様々なデータ型が使用可能。テーブル参照も可能。	なし	テーブルのカラムを含む`STRUCT`として評価される場合あり。
`search_query`	検索クエリを表す`STRING`リテラルまたは定数式。`NULL`の場合はエラーが発生。	なし	例： `'foobar'`
`json_scope`	`JSON`データに対する検索スコープを指定するオプション。	`'JSON_VALUES'`	値の検索を行う。
`analyzer`	テキスト解析を行う際の解析方法を指定。	`'LOG_ANALYZER'`	`NO_OP_ANALYZER`（正規化なし）、`PATTERN_ANALYZER`（正規表現）など。
`analyzer_options`	JSON形式で指定されるオプション。テキスト解析ルールを設定。	なし	例： `{ "case_sensitive": false }`

検索クエリのルール

検索クエリは、初めに空白で分割され、それぞれの用語（サブクエリ）がさらにテキスト解析ルールに基づいてトークンに分解されます。具体的には以下のようなルールがあります。

バッククォートで囲まれたテキスト: LOG_ANALYZERを使用すると、バッククォートで囲まれたテキストは正確に一致する必要があります。たとえば、Hello WorldはHello Worldとして扱われます。
予約文字: クエリ内でエスケープが必要な予約文字には、[ ] < > ( ) { } | ! ' " * & ? + / : = - \ ~ ^があります。これらはダブルバックスラッシュ\\でエスケープします。
フレーズ検索: ダブルクォートで囲まれたテキストはフレーズとして扱われ、そのフレーズ内のトークンが指定された順序で連続してデータに現れる場合に一致します。

使用例

以下に、SEARCH関数を使用したいくつかの例を示します。

-- テーブルを作成する
WITH sample_data AS (
  SELECT 'foobar' AS text_column
  UNION ALL
  SELECT 'foo bar'
)

-- 検索を実行する
SELECT
  -- 正確な一致をチェックする
  SEARCH(text_column, 'foobar', analyzer=>'NO_OP_ANALYZER') AS exact_match,

  -- 区切り文字を考慮した一致をチェックする
  SEARCH(text_column, 'foo bar', analyzer=>'LOG_ANALYZER') AS delimiter_match,

  -- 正規表現を使用したパターンマッチ
  SEARCH(text_column, R'"foo[ \d]*"', analyzer=>'PATTERN_ANALYZER') AS regex_match_space_or_digit
FROM sample_data;