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

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

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

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

• INTEGER

• TEXT

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

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

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

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

• CAST および::演算子

• TRY_CAST

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

このテーブルの場合は、検索最適化サービスにより、 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';

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 列）、またはそのような列の要素で、 ワイルドカードまたは正規表現検索 を最適化することができます。これには、以下を使用する述部が含まれます。

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• RLIKE

• REGEXP

• REGEXP_LIKE

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

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

指定した要素の検索最適化を有効にすると、入れ子になったすべての要素で検索最適化が有効になります。最初のステートメントは、ネストされた 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);

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

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

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

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

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

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

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

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

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