検索最適化で半構造化データのクエリを高速化¶

検索最適化サービスにより、Snowflakeテーブルの半構造化データ（つまり、 VARIANT、 OBJECT、および ARRAY 列のデータ）に対するポイントルックアップと部分文字列のクエリのパフォーマンスを向上させることができます。このようなタイプの列は、構造が深く入れ子になっていて、頻繁に変更される場合でも、検索最適化を構成することができます。また、半構造化列内の特定の要素に対する検索最適化を有効にすることもできます。

以下のセクションでは、半構造化データのクエリに対する検索最適化サポートの詳細情報を提供します。

半構造化データのクエリにおける検索最適化の実現
半構造化型の述語の定数およびキャストでサポートされるデータ型
TEXT にキャストされた半構造化データ型の値のサポート
VARIANT 型のポイントルックアップでサポートされる述語
VARIANT 型の部分文字列検索
半構造型のサポートにおける現在の制限事項

半構造化データのクエリにおける検索最適化の実現¶

テーブル上の半構造化データのクエリのパフォーマンスを向上させるには、 ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドで、特定の列または列内の要素に対して ON 句を使用します。ON 句を省略した場合、 VARIANT、 OBJECT、 ARRAY 列に対するクエリは最適化されません。テーブルレベルで検索最適化を有効にしても、半構造化データ型の列では有効にはなりません。

例:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(object_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(object_column);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(array_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(array_column);

Copy

詳細については、検索最適化の有効化と無効化をご参照ください。

半構造化型の述語の定数およびキャストでサポートされるデータ型¶

検索最適化サービスにより、次の型が定数に使用され、要素に暗黙的または明示的なキャストが使用される半構造化データのポイントルックアップのパフォーマンスを向上させることができます。

FIXED （有効な精度とスケールを指定するキャストを含む）
INTEGER
TEXT
DATE （スケールを指定するキャストを含む）
TIME （スケールを指定するキャストを含む）
TIMESTAMP、 TIMESTAMP_LTZ、 TIMESTAMP_NTZ、 TIMESTAMP_TZ （スケールを指定するキャストを含む）

検索最適化サービスは、以下を使用した型のキャストをサポートしています。

TEXT にキャストされた半構造化データ型の値のサポート¶

検索最適化サービスは、半構造化データタイプを持つ列が TEXT にキャストされ、 TEXT にキャストされる定数と比較されるポイント検索のパフォーマンスも向上させることができます。

たとえば、 src が、 VARIANT に変換された BOOLEAN、 DATE、および TIMESTAMP の値を含む VARIANT 列であるとします。

CREATE OR REPLACE TABLE test_table
(
  id INTEGER,
  src VARIANT
);

INSERT INTO test_table SELECT 1, TO_VARIANT('true'::BOOLEAN);
INSERT INTO test_table SELECT 2, TO_VARIANT('2020-01-09'::DATE);
INSERT INTO test_table SELECT 3, TO_VARIANT('2020-01-09 01:02:03.899'::TIMESTAMP);

Copy

このテーブルの場合は、検索最適化サービスにより、 VARIANT 列を TEXT にキャストし、列を文字列定数と比較する次のクエリを向上させることができます。

SELECT * FROM test_table WHERE src::TEXT = 'true';
SELECT * FROM test_table WHERE src::TEXT = '2020-01-09';
SELECT * FROM test_table WHERE src::TEXT = '2020-01-09 01:02:03.899';

Copy

VARIANT 型のポイントルックアップでサポートされる述語¶

検索最適化サービスにより、以下にリストされている述語の型を使用してポイントルックアップクエリを向上させることができます。以下の例では、 src が半構造化データ型を持つ列で、 path_to_element が半構造化データ型を持つ列の要素へのパスです。

次の形式の等価述語:

WHERE path_to_element[::target_data_type] = constant

この構文では、 target_data_type （指定されている場合）、および constant のデータ型は、サポートされている型のいずれかでなければなりません。

たとえば、検索最適化サービスは以下をサポートしています。
- VARIANT 要素を明示的にキャストせずに、要素を NUMBER 定数と一致させる。
  WHERE src:person.age = 42;
  Copy
- VARIANT 要素を、指定された精度とスケールで NUMBER に明示的にキャストする。
  WHERE src:location.temperature::NUMBER(8, 6) = 23.456789;
  Copy
- VARIANT 要素を明示的にキャストせずに、要素を TEXT 定数と一致させる。
  WHERE src:sender_info.ip_address = '123.123.123.123';
  Copy
- VARIANT 要素を明示的に TEXT にキャストする。
  WHERE src:salesperson.name::TEXT = 'John Appleseed';
  Copy
- VARIANT 要素を明示的に DATE にキャストする。
  WHERE src:events.date::DATE = '2021-03-26';
  Copy
- 指定されたスケールで VARIANT 要素を TIMESTAMP に明示的にキャストする。
  WHERE src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
  Copy
- サポートされるタイプの値をARRAY 要素と一致させる（明示的にキャストするかどうかは問わない）。例:
  WHERE my_array_column[2] = 5; WHERE my_array_column[2]::NUMBER(4, 1) = 5;
  Copy
- サポートされるタイプの値をOBJECT 要素と一致させる（明示的にキャストするかどうかは問わない）。例:
  WHERE object_column['mykey'] = 3; WHERE object_column:mykey = 3; WHERE object_column['mykey']::NUMBER(4, 1) = 3; WHERE object_column:mykey::NUMBER(4, 1) = 3;
  Copy
次のような ARRAY 関数を使用する述語:
- WHERE ARRAY_CONTAINS(value_expr, array)
  
  この構文では、 value_expr は NULL であってはならず、 VARIANT と評価されなければなりません。値のデータ型は、サポートされる型のいずれかでなければなりません。
  
  例:
  WHERE ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
  Copy
  この例では、値は暗黙のうちに VARIANTにキャストされた定数です：
  WHERE ARRAY_CONTAINS(300, my_array_column)
  Copy
- WHERE ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), array)
  
  各定数のデータ型（constant_1 、 constant_2 など）は、サポートされている型のいずれかでなければなりません。構築された ARRAY には、NULL 定数を含めることができます。
  
  この例では、配列は VARIANT の値に含まれます：
  WHERE ARRAYS_OVERLAP( ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
  Copy
  この例では、配列は ARRAY 列です：
  WHERE ARRAYS_OVERLAP( ARRAY_CONSTRUCT('a', 'b'), my_array_column)
  Copy
NULL 値をチェックする次の述語:
- WHERE IS_NULL_VALUE(path_to_element)
  
  IS_NULL_VALUE は JSON null値に適用され、 SQL NULL 値には適用されないことに注意してください。
- WHERE path_to_element IS NOT NULL
- WHERE semistructured_column IS NULL
  
  semistructured_column は、半構造化データ内の要素へのパスではなく、列を参照します。
  
  たとえば、検索最適化サービスは VARIANT 列 src の使用をサポートしていますが、その VARIANT 列にある要素 src:person.age へのパスはサポートしていません。

VARIANT 型の部分文字列検索¶

検索最適化サービスは、半構造化列（つまり、 VARIANT、 OBJECT、および ARRAY 列）、またはそのような列の要素で、ワイルドカードまたは正規表現検索を最適化することができます。これには、以下を使用する述部が含まれます。

列または列内の複数の要素に対して部分文字列検索最適化を有効にすることができます。たとえば、以下のステートメントは、列内のネストされた要素の部分文字列検索最適化を有効にします。

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Copy

検索アクセスパスが構築されると、以下のクエリを最適化することができます。

SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';

Copy

しかし、次のクエリは、 WHERE 句フフィルターが検索最適化が有効なときに指定された要素（col2:data.search）に適用されないため、最適化されません。

SELECT * FROM test_table WHERE col2:name LIKE '%simon%parker%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';

Copy

最適化する要素は複数指定できます。ここでは、列 col2 にある2つの特定の要素に対して検索最適化が有効になっています。

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Copy

指定した要素の検索最適化を有効にすると、入れ子になったすべての要素で検索最適化が有効になります。最初のステートメントは、ネストされた search 要素を含む、 data 要素全体の検索最適化を有効にするため、以下の2番目の ALTER TABLE ステートメントは冗長です。

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Copy

同様に、列全体の検索最適化を有効にすると、その列のすべての部分文字列検索が（入れ子になったすべての要素を含めて）最適化されます。

car_sales テーブルの VARIANT 列とそのデータ（半構造化データのクエリで説明）に対して FULL_TEXT 検索最適化を可能にする例については、 VARIANT 列で FULL_TEXT 検索最適化を有効にするをご参照ください。

VARIANT 部分文字列検索における定数の評価方法¶

クエリ内の定数文字列（例: LIKE 'constant_string'）を評価する際、検索最適化サービスは以下の文字を区切り文字として文字列をトークンに分割します。

角括弧（[ および ]）。
中括弧（{ および }）。
コロン（:）。
コンマ（,）。
二重引用符（"）。

文字列をトークンに分割した後、検索最適化サービスは5文字以上のトークンだけを考慮します。

述語の例	検索最適化サービスがクエリを処理する方法
`LIKE '%TEST%'`	検索最適化サービスは、次の述語に対して検索アクセスパスを使用しません。部分文字列が5文字より短いためです。
`LIKE '%SEARCH%IS%OPTIMIZED%'`	検索最適化サービスはこのクエリを最適化することができます。検索アクセスパスを使用して、 `SEARCH` と `OPTIMIZED` を検索しますが、 `IS` は検索しません。 `IS` は5文字未満です。
`LIKE '%HELLO_WORLD%'`	検索最適化サービスはこのクエリを最適化することができます。検索アクセスパスを使用して `HELLO_WORLD` の部分文字列を検索します。
`LIKE '%COL:ON:S:EVE:RYWH:ERE%'`	検索最適化サービスはこの文字列を `COL`、 `ON`、 `S`、 `EVE`、 `RYWH`、 `ERE` に分割します。これらのトークンはすべて5文字より短いため、検索最適化サービスはこのクエリを最適化できません。
`LIKE '%{\"KEY01\":{\"KEY02\":\"value\"}%'`	検索最適化サービスはこれを `KEY01`、 `KEY02`、 `VALUE` というトークンに分割し、クエリを最適化する際にそのトークンを使用します。
`LIKE '%quo\"tes_and_com,mas,\"are_n\"ot\"_all,owed%'`	検索最適化サービスはこの文字列を `quo`、 `tes_and_com`、 `mas`、 `are_n`、 `ot`、 `_all`、 `owed` というトークンに分割します。検索最適化サービスは、クエリを最適化する際に、5文字以上のトークン（`tes_and_com`、 `are_n`）のみを使用することができます。

半構造型のサポートにおける現在の制限事項¶

検索最適化サービスにおける半構造化タイプのサポートは、以下の点で制限されています：

XMLGET を使用する述語はサポートされていません。
path_to_element IS NULL という形式の述語はサポートされていません。
定数がスカラーサブクエリの結果である述語はサポートされていません。
サブ要素を含む要素へのパスを指定する述語はサポートされていません。

検索最適化サービスの現在の制限もこの機能に適用されます。