검색 최적화로 정형 데이터 쿼리 속도 높이기

검색 최적화 서비스는 Snowflake 테이블의 정형 데이터(즉, :doc:`정형 ARRAY, OBJECT 및 MAP 열</sql-reference/data-types-structured>`의 데이터)에 대한 포인트 조회 및 하위 문자열 쿼리의 성능을 개선할 수 있습니다. 구조가 깊게 중첩되어 있고 자주 변경되는 경우에도 이러한 유형의 열에 대해 검색 최적화를 구성할 수 있습니다. 정형 열 내의 특정 요소에 대한 검색 최적화를 활성화할 수도 있습니다.

다음 섹션에서는 정형 데이터 쿼리에 대한 검색 최적화 지원에 대한 자세한 정보를 제공합니다.

정형 데이터 쿼리에 대한 검색 최적화 활성화하기

테이블에서 정형 데이터 타입의 쿼리 성능을 개선하려면 특정 열 또는 열의 요소에 대해 ALTER TABLE … ADD SEARCH OPTIMIZATION 명령<label-alter_table_searchoptimizationaction_add>`의 :ref:`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);
Copy

이러한 ALTER TABLE … ADD SEARCH OPTIMIZATION 명령에서 사용하는 키워드에는 다음 규칙이 적용됩니다.

  • 내부 요소 또는 열 자체가 있는 EQUALITY 키워드를 사용합니다.

  • 텍스트 문자열 데이터 타입이 포함된 내부 요소가 있는 SUBSTRING 키워드만 사용합니다.

자세한 내용은 검색 최적화 활성화 및 비활성화하기 섹션을 참조하십시오.

정형 타입의 조건자에서 상수 및 캐스트 변환에 대해 지원되는 데이터 타입

검색 최적화 서비스는 요소에 대해 상수 및 암시적 또는 명시적 캐스트 에 다음 타입이 사용되는 정형 데이터의 포인트 조회 성능을 개선할 수 있습니다.

  • FIXED(유효한 전체 자릿수와 소수 자릿수를 지정하는 캐스트 포함)

  • INTEGER(동의어 타입 포함)

  • VARCHAR(동의어 타입 포함)

  • DATE(소수 자릿수를 지정하는 캐스트 포함)

  • TIME(소수 자릿수를 지정하는 캐스트 포함)

  • TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ(소수 자릿수를 지정하는 캐스트 포함)

검색 최적화 서비스는 다음 변환 함수를 사용하여 타입의 캐스팅을 지원합니다.

정형 타입의 포인트 조회에 지원되는 조건자

검색 최적화 서비스는 다음 목록에 표시된 조건자의 타입으로 포인트 조회 쿼리를 개선할 수 있습니다. 해당 예제에서 ``src``은 정형 데이터 타입이 있는 열이고 :samp:`{path_to_element}`는 정형 데이터 타입이 있는 열의 요소에 대한 경로입니다.

  • 다음 형식의 같음 조건자:

    WHERE path_to_element[::target_data_type] = constant

    이 구문에서 target_data_type (지정된 경우) 및 constant 의 데이터 타입은 지원 타입 중 하나여야 합니다.

    예를 들어, 검색 최적화 서비스는 다음 조건자를 지원합니다.

    • 요소를 명시적으로 캐스팅하지 않고 NUMBER 상수에 대해 OBJECT 또는 MAP 요소를 일치시킵니다.

      WHERE src:person.age = 42;
      Copy

    • 지정된 전체 자릿수와 소수 자릿수로 OBJECT 또는 MAP 요소를 NUMBER로 명시적으로 캐스팅합니다.

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

    • 요소를 명시적으로 캐스팅하지 않고 VARCHAR 상수에 대해 OBJECT 또는 MAP 요소를 일치시킵니다.

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

    • OBJECT 또는 MAP 요소를 VARCHAR로 명시적으로 캐스팅합니다.

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

    • OBJECT 또는 MAP 요소를 DATE로 명시적으로 캐스팅합니다.

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

    • 지정된 소수 자릿수로 OBJECT 또는 MAP 요소를 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 또는 MAP 요소를 :ref:`지원되는 유형<label-search_optimization_service_variant_predicates_types>`의 값과 일치시킵니다(명시적으로 캐스팅 여부와 관계없이).

      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

      이 예제에서 값은 OBJECT로 암시적으로 캐스팅되는 상수입니다.

      WHERE ARRAY_CONTAINS(300, my_array_column)
      Copy

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

      각 상수(constant_1, constant_2 등)의 데이터 타입은 :ref:`지원되는 타입 중 하나<label-search_optimization_service_variant_predicates_types>`여야 합니다. 생성된 ARRAY는 NULL 상수를 포함할 수 있습니다.

      이 예제에서 배열은 OBJECT 값입니다.

      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)

      참고

      :doc:`/sql-reference/functions/is_null_value`는 SQL NULL 값이 아닌 JSON null 값에 적용됩니다.

    • WHERE path_to_element IS NOT NULL

    • WHERE structured_column IS NULL

      여기서 :samp:`{structured_column}`은 정형 데이터의 요소에 대한 경로가 아닌 열을 나타냅니다.

      예를 들어, 검색 최적화 서비스는 OBJECT 열 src 을 사용하지만 해당 src:person.age 열의 OBJECT 요소 경로는 지원하지 않습니다.

정형 타입의 하위 문자열 검색

대상 정형 요소가 텍스트 문자열 데이터 타입인 경우에만 하위 문자열 검색을 활성화할 수 있습니다.

다음과 같은 테이블을 예로 들어 보겠습니다.

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

이 테이블의 경우 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);
Copy

검색 액세스 경로가 만들어지면 다음 쿼리를 최적화할 수 있습니다.

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

그러나 다음 쿼리는 검색 최적화를 활성화(col2:data.search)했을 때 지정한 요소에 WHERE 절 필터가 적용되지 않기 때문에 최적화되지 않습니다.

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

최적화할 요소를 여러 개 지정할 수 있습니다. 다음 예제에서는 열 :code:`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

주어진 요소에 대해 검색 최적화를 활성화하면 텍스트 문자열 유형의 중첩되지 않은 모든 요소에 대해 검색 최적화가 활성화됩니다. 중첩된 요소 또는 텍스트가 아닌 문자열 유형의 요소에 대해서는 검색 최적화가 활성화되지 않습니다.

하위 문자열 검색에 대해 상수를 평가하는 방법

쿼리에서 상수 문자열(예: 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
    )
);
Copy

데이터를 표시하려면 테이블을 쿼리합니다.

SELECT * FROM test_struct;
Copy
+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

다음 문은 오브젝트에서 c 요소를 제거합니다.

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

이 문이 실행되면 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
    )
);
Copy

데이터를 표시하려면 테이블을 쿼리합니다.

SELECT * FROM test_struct;
Copy
+--------------------+
| 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;
Copy

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 함수를 사용하는 조건자는 지원되지 않습니다.

:ref:`현재 검색 최적화 서비스의 제한 사항 <label-search_optimization_limitations>`은 정형 타입에도 적용됩니다.