こんにちは。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;
クエリ結果
まとめ
BigQueryのSEARCH関数は、大規模データセットから特定の情報を効率的に検索する際に活用できます。検索クエリやテキスト解析の設定を適切に利用することで、検索の幅が広がりそうですね。