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

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

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

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

テーブル上の半構造化データのクエリのパフォーマンスを向上させるには、 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（同義タイプを含む）

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

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

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

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

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

Support for semi-structured data type values cast to VARCHAR

The search optimization service can also improve the performance of point lookups in which columns with semi-structured data types are cast to VARCHAR and are compared to constants that are cast to VARCHAR.

たとえば、 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

For this table, the search optimization service can improve the following queries, which cast the VARIANT column to VARCHAR and compare the column to string constants:

SELECT * FROM test_table WHERE src::VARCHAR = 'true';
SELECT * FROM test_table WHERE src::VARCHAR = '2020-01-09';
SELECT * FROM test_table WHERE src::VARCHAR = '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

    • Matching a VARIANT element against a VARCHAR constant without explicitly casting the element.

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

    • Explicitly casting a VARIANT element to VARCHAR.

      WHERE src:salesperson.name::VARCHAR = '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 型の部分文字列検索

The search optimization service can optimize wildcard or regular expression searches in semi-structured columns --- that is, VARIANT, OBJECT, and ARRAY columns --- or elements in such columns.

The search optimization service can optimize predicates that use the following functions:

You can enable substring search optimization for a column or for multiple individual elements within a column. For example, the following statement enables substring search optimization for a nested element in a column:

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

After the search access path has been built, the following query can be optimized:

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

However, the following queries aren't optimized because the WHERE clause filters don't apply to the element that was specified when search optimization was enabled (col2:data.search):

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

You can specify multiple elements to be optimized. In the following example, search optimization is enabled for two specific elements in the column col2:

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

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

For an example that enables FULL_TEXT search optimization on a VARIANT column in the car_sales table and its data, which is described in 半構造化データのクエリ, see VARIANT 列で FULL_TEXT 検索最適化を有効にする.

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

When it evaluates the constant string in a query --- for example, LIKE 'constant_string' --- the search optimization service splits the string into tokens by using the following characters as delimiters:

  • 角括弧（[ および ]）。

  • 中括弧（{ および }）。

  • コロン（:）。

  • コンマ（,）。

  • 二重引用符（"）。

After it splits the string into tokens, the search optimization service considers only tokens that are at least five characters long. The following table explains how the search optimization service handles various predicate examples:

述語の例

検索最適化サービスがクエリを処理する方法

LIKE '%TEST%'

The search optimization service doesn't use search access paths for the following predicate because the substring is shorter than five characters.

LIKE '%SEARCH%IS%OPTIMIZED%'

The search optimization service can optimize this query, by using search access paths to search for SEARCH and OPTIMIZED but not IS. IS is shorter than five characters.

LIKE '%HELLO_WORLD%'

The search optimization service can optimize this query, by using search access paths to search for HELLO_WORLD.

LIKE '%COL:ON:S:EVE:RYWH:ERE%'

検索最適化サービスはこの文字列を COL、 ON、 S、 EVE、 RYWH、 ERE に分割します。これらのトークンはすべて5文字より短いため、検索最適化サービスはこのクエリを最適化できません。

LIKE '%{\"KEY01\":{\"KEY02\":\"value\"}%'

The search optimization service splits this string into the tokens KEY01, KEY02, VALUE and uses the tokens when it optimizes the query.

LIKE '%quo\"tes_and_com,mas,\"are_n\"ot\"_all,owed%'

The search optimization service splits this string into the tokens quo, tes_and_com, mas, are_n, ot, _all, owed. The search optimization service can only use the tokens that are five characters or longer (tes_and_com, are_n) when it optimizes the query.

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

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

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

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

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

  • Predicates that use the XMLGET function aren't supported.

The current limitations of the search optimization service also apply to semi-structured types.