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

検索最適化サービスにより、Snowflakeテーブルの半構造化データ（VARIANT、 OBJECT、および ARRAY 列 のデータ）に対するポイントルックアップと部分文字列のクエリのパフォーマンスを向上させることができます。

検索最適化サービスの VARIANT サポートがテーブル内の列に対して構成されている場合、検索最適化サービスは自動的に VARIANT、 OBJECT、および ARRAY 列を検索アクセスパスに含めます。これは、構造が深くネストされ、構造が頻繁に変更される列にも当てはまります。また、半構造化列内の特定のフィールドに対する検索最適化を有効にすることもできます。

注釈

ON 句を使用した ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドで 特定の列に対してこの機能を明示的に有効にする必要があります。（テーブルレベルで検索最適化を有効にしても、 VARIANT 列では有効になりません。）例:

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);
Copy

ON 句を省略すると、 VARIANT 列に対するクエリは最適化されません。

次のセクションでは、このサポートについて詳しく説明します。

VARIANT 型の述語の定数およびキャストでサポートされるデータ型

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

  • FIXED （有効な精度とスケールを指定するキャストを含む）

  • INTEGER

  • TEXT

  • DATE （スケールを指定するキャストを含む）

  • TIME （スケールを指定するキャストを含む）

  • TIMESTAMP、 TIMESTAMP_LTZ、 TIMESTAMP_NTZ、 TIMESTAMP_TZ （スケールを指定するキャストを含む）

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

VARIANT 値キャストを TEXT としてサポート

検索最適化サービスにより、 VARIANT 列が TEXT にキャストされ、 TEXT にキャストされる定数と比較されるポイントルックアップのパフォーマンスも向上させることができます。

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

create or replace TABLE test_table
(
    ID INTEGER,
    SRC VARIANT
);

insert into test_table select 1, to_variant('true'::boolean);          -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date);       -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time);  -- TIME
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 = '01:02:03.899213';
Copy

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

検索最適化サービスにより、以下にリストされている述語の型を使用してポイントルックアップクエリを向上させることができます。以下の例では、 src は VARIANT 列であり、 path_to_variant_field は VARIANT 列 のフィールドへのパスです。

  • 次の形式の等価述語:

    where path_to_variant_field[::target_data_type] = constant

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

    たとえば、検索最適化サービスは以下をサポートしています。

    • 要素を明示的にキャストせずに、要素を NUMBER 定数と一致させる。

      where src:person.age = 42;
      Copy

    • 指定された精度とスケールで、要素を NUMBER に明示的にキャストする。

      where src:location.temperature::NUMBER(8, 6) = 23.456789;
      Copy

    • 要素を明示的にキャストせずに、要素を TEXT 定数と一致させる。

      where src:sender_info.ip_address = '123.123.123.123';
      Copy

    • 要素を TEXT に明示的にキャストする。

      where src:salesperson.name::TEXT = 'John Appleseed';
      Copy

    • 要素を DATE に明示的にキャストする。

      where src:events.date::DATE = '2021-03-26';
      Copy

    • 指定されたスケールで要素を TIME に明示的にキャストする。

      where src:events.time_info::TIME(6) = '01:02:03.456789';
      Copy

    • 指定されたスケールで要素を TIMESTAMP に明示的にキャストする。

      where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
      Copy

  • 次のような ARRAY 関数を使用する述語:


    • where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

      この構文では、 constant は NULL であってはならず、 constant のデータ型は、 サポートされている型 のいずれかでなければなりません。

      例:

      where ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
      Copy

    • where ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), path_to_variant_field)

      各定数のデータ型 (constant_1 、 constant_2 など) は、 サポートされている型 のいずれかでなければなりません。構築された ARRAY には、NULL 定数を含めることができます。

      例:

      where ARRAYS_OVERLAP(
    ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
      Copy

  • NULL 値をチェックする次の述語:

    • where IS_NULL_VALUE(path_to_variant_field)

      IS_NULL_VALUE は JSON null値に適用され、 SQL NULL 値には適用されないことに注意してください。

    • where path_to_variant_field IS NOT NULL

    • where variant_column IS NULL

      variant_column は、半構造化データ内の要素へのパスではなく、列を参照します。

      たとえば、検索最適化サービスは VARIANT 列 src の使用をサポートしていますが、その VARIANT 列にあるフィールド src:person.age へのパスはサポートしていません。

VARIANT 型の部分文字列検索

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

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

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

同様に、列全体の検索最適化を有効にすると、その列のすべての部分文字列検索が最適化されます。

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）のみを使用することができます。

VARIANT 型のサポートにおける現在の制限事項

現在、検索最適化サービスにおける VARIANT 型のサポートには、次の制限があります。

  • XMLGET を使用する述語はサポートされていません。

  • 形式 variant_field IS NULL の述語はサポートされていません。

  • 定数がスカラーサブクエリの結果である述語はサポートされていません。

  • サブ要素を含む要素へのパスを指定する述語はサポートされていません。

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

言語: 日本語