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

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

次のセクションでは、構造化データのクエリの検索最適化サポートについて詳しく説明します。

構造化データのクエリに対する検索最適化の有効化

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

例:

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

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

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column:user.uuid);

これらの ALTERTABLE... ADDSEARCHOPTIMIZATION コマンドで使用するキーワードには、次のルールが適用されます。

  • EQUALITY キーワードは、任意の内部要素または列自体で使用できます。

  • SUBSTRING キーワードは、 テキスト文字列 データ型を持つ内部要素でのみ使用できます。

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

構造化された型の述語における定数とキャストでサポートされているデータ型

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

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

  • INTEGER（同義タイプを含む）

  • VARCHAR（同義タイプを含む）

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

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

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

検索最適化サービスは、次の変換関数を使用して型のキャストをサポートします。

構造化された型のポイントルックアップでサポートされる述語

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

  • 次の形式の等価述語:

    WHERE path_to_element[::target_data_type] = constant

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

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

    • 要素を明示的にキャストせずに、 OBJECT または MAP 要素を NUMBER 定数と照合します。

      WHERE src:person.age = 42;

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

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

    • 要素を明示的にキャストせずに、 OBJECT または MAP 要素を VARCHAR 定数と照合します。

      WHERE src:sender_info.ip_address = '123.123.123.123';

    • OBJECT または MAP 要素を VARCHAR に明示的にキャストします。

      WHERE src:salesperson.name::VARCHAR = 'John Appleseed';

    • OBJECT または MAP 要素を DATE に明示的にキャストします。

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

    • 指定されたスケールで、 OBJECT または MAP 要素を TIMESTAMP に明示的にキャストします。

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

    • 明示的なキャストの有無にかかわらず、 ARRAY 要素を サポート対象の型 の値と照合します。

      WHERE my_array_column[2] = 5;

WHERE my_array_column[2]::NUMBER(4, 1) = 5;

    • 明示的なキャストの有無にかかわらず、 OBJECT または MAP 要素を サポート対象の型 の値と照合します。

      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;

  • 次の術語のように、 ARRAY 関数を使用する術語:

    • WHERE ARRAY_CONTAINS(value_expr, array)

      この構文では、 value_expr は NULL であってはならず、 VARIANT と評価されなければなりません。値のデータ型は、 サポート対象の型 のいずれかでなければなりません。

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

      この例では、値は暗黙のうちに OBJECTにキャストされた定数です:

      WHERE ARRAY_CONTAINS(300, my_array_column)

    • WHERE ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), array)

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

      この例では、配列は OBJECT の値に含まれます:

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

      この例では、配列は ARRAY 列に含まれます:

      WHERE ARRAYS_OVERLAP(
  ARRAY_CONSTRUCT('a', 'b'), my_array_column)

  • 次の術語は NULL 値をチェックします:

    • WHERE IS_NULL_VALUE(path_to_element)

      注釈

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

    • WHERE path_to_element IS NOT NULL

    • WHERE structured_column IS NULL

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

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

構造化された型の部分文字列検索

ターゲットの構造化要素が テキスト文字列 データ型の場合にのみ、部分文字列検索を有効にすることができます。

例えば、次のテーブルを考えます：

CREATE TABLE t(
  col OBJECT(
    a INTEGER,
    b STRING,
    c MAP(INTEGER, STRING),
    d ARRAY(STRING)
  )
);

このテーブルの場合、次のターゲット構造化要素に SUBSTRING 検索の検索最適化を追加 できます。

  • col:b は型が STRING であるため。

  • col:c[value] ---例: col:c[0]、 col:c[100]---値がテキスト文字列型の場合。

このテーブルの場合、次のターゲット構造化要素に SUBSTRING 検索の検索最適化を追加 できません。

  • col は型が OBJECT であるため。

  • col:a は型が INTEGER であるため。

  • col:c は型が MAP であるため。

  • col:d は型が ARRAY であるため。

検索最適化サービスにより、次の関数を使用する述語を最適化できます。

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

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

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

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

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

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

最適化する要素は複数指定できます。ここでは、列 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);

指定した要素の検索最適化を有効にすると、テキスト文字列型のネストされていない要素すべてで検索最適化が有効になります。検索最適化は、ネストされた要素またはテキスト文字列以外の型の要素に対しては有効ではありません。

構造化された部分文字列検索における定数の評価方法

クエリ内の定数文字列（例: 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）のみを使用することができます。

スキーマ進化のサポート

構造化列のスキーマは、時間の経過とともに進化する可能性があります。スキーマの進化に関する詳細については、 ALTER ICEBERG TABLE ... ALTER COLUMN ... SET DATA TYPE（構造化された型） をご参照ください。

単一のスキーマ進化操作の一環として、次の変更を実行できます。

  • 型拡張

  • 要素の並べ替え

  • 要素の追加

  • 要素の削除

  • 要素の名前変更

スキーマ進化操作の一環として、検索最適化サービスが無効になることはありません。代わりに、検索最適化サービスは次の方法で操作を処理します。

型拡張（例: INT から NUMBER）

検索最適化のアクセスパスは影響を受けません。

要素の追加

新しく追加された要素は、既存の検索最適化アクセスパスに自動的に反映されます。

要素の削除

構造化列から要素が削除されると、検索最適化サービスは、削除された要素によってプレフィックスが付けられたアクセスパスを自動的に削除します。

たとえば、 OBJECT タイプの列を持つテーブルを作成し、データを挿入します。

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

データを表示するには、テーブルをクエリします。

SELECT * FROM test_struct;

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

次のステートメントはオブジェクトから要素 c を削除します。

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER);

このステートメントを実行すると、 a、 a:c、 a:c:d および a:c:e のアクセスパスが削除されます。

要素の名前変更

要素の名前が変更されると、検索最適化サービスは、名前が変更された要素によってプレフィックスされたアクセスパスを自動的に削除し、新しい名前のパスで再度追加します。この操作には、検索最適化サービスで新しく追加されたパスを処理するための追加のメンテナンスコストが発生します。

たとえば、 OBJECT タイプの列を持つテーブルを作成し、データを挿入します。

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

データを表示するには、テーブルをクエリします。

SELECT * FROM test_struct;

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

次のステートメントは、オブジェクト内の要素 c の名前を c_new に変更します。

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER,
    c_new OBJECT(
      d STRING,
      e VARIANT
    )
  ) RENAME FIELDS;

a、 a:c、 a:c:d、 a:c:e のアクセスパスが削除され、 a、 a:c_new、 a:c_new:d、 a:c_new:e として再追加されます。

要素の並べ替え

検索最適化のアクセスパスは影響を受けません。

構造化された型のサポートにおける現在の制限事項

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

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

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

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

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

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

検索最適化サービスの現在の制限 も構造化された型に適用されます。