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

검색 최적화 서비스는 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

  • TEXT

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

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

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

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

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

이 테이블의 경우 검색 최적화 서비스는 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';
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

    • 명시적으로 요소를 형 변환하지 않고 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 는 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 타입의 하위 문자열 검색

검색 최적화 서비스는 반정형 열 (즉, VARIANT, OBJECT, 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

최적화할 요소를 여러 개 지정할 수 있습니다. 여기에서는 열의 두 가지 특정 요소 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

마찬가지로 전체 열에 대해 검색 최적화를 활성화하면 해당 열의 모든 하위 문자열 검색을 최적화할 수 있으며, 여기에는 열 내의 모든 깊이까지 중첩된 요소도 포함됩니다.

car_sales 테이블의 VARIANT 열과 해당 데이터(반정형 데이터 쿼리하기 에 설명되어 있음)에서 FULL_TEXT 검색 최적화를 활성화하는 예제는 VARIANT 열에서 FULL_TEXT 검색 최적화 활성화하기 섹션을 참조하십시오.

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

쿼리에서 상수 문자열(예: 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)만 사용할 수 있습니다.

현재 반정형 지원의 제한 사항

검색 최적화 서비스에서 반정형 유형에 대한 지원은 다음과 같은 방식으로 제한됩니다.

  • XMLGET 을 사용하는 조건자는 지원되지 않습니다.

  • path_to_element IS NULL 형식의 조건자는 지원되지 않습니다.

  • 상수가 스칼라 하위 쿼리의 결과인 조건자는 지원되지 않습니다.

  • 하위 요소를 포함하는 요소의 경로를 지정하는 조건자는 지원되지 않습니다.

이 기능에는 검색 최적화 서비스의 현재 제한 사항 도 적용됩니다.

언어: 한국어