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

검색 최적화 서비스는 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 는 SQL NULL 값이 아닌 JSON 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

특정 요소에 대해 검색 최적화를 활성화하면 중첩된 모든 요소에 대해 검색 최적화가 활성화됩니다. 아래의 두 번째 ALTER TABLE 문은 첫 번째 문이 중첩된 search 요소를 포함하여 전체 data 요소에 대한 검색 최적화를 활성화하기 때문에 중복됩니다.

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.

언어: 한국어