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

검색 최적화 서비스는 Snowflake 테이블의 반정형 데이터(VARIANT, OBJECT 및 ARRAY 열 의 데이터)에 대한 포인트 조회 및 하위 문자열 쿼리의 성능을 개선할 수 있습니다.

검색 최적화 서비스에 대한 VARIANT 지원이 테이블의 열에 대해 구성된 경우 검색 최적화 서비스는 검색 액세스 경로에 VARIANT, OBJECT 및 ARRAY 열을 자동으로 포함합니다. 이는 구조가 깊이 중첩되고 자주 변경되는 열에도 적용됩니다. 반정형 열 내의 특정 필드에 대해 검색 최적화를 활성화할 수도 있습니다.

참고

ALTER TABLE … ADD SEARCH OPTIMIZATION 명령에서 ON 절을 사용하여 특정 열 또는 열의 필드에 대해 이 기능을 명시적으로 활성화해야 합니다. (테이블 수준에서 검색 최적화를 활성화해도 VARIANT 열에 대해서는 활성화되지 않습니다.) 예:

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

ON 절을 생략하면 VARIANT 열에 대한 쿼리가 최적화되지 않습니다.

다음 섹션에서는 이 지원에 대한 자세한 내용을 제공합니다.

VARIANT 타입에 대한 조건자의 상수와 캐스트를 위해 지원되는 데이터 타입

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

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

  • INTEGER

  • TEXT

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

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

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

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

TEXT로 캐스팅된 VARIANT 값 지원

또한 검색 최적화 서비스는 VARIANT 열이 TEXT로 캐스팅되고 TEXT로 캐스팅된 상수와 비교되는 포인트 조회의 성능을 개선할 수도 있습니다.

예를 들어, src 가 VARIANT로 변환된 BOOLEAN, DATE 및 TIME 값을 포함하는 VARIANT 열이라고 가정합니다.

create or replace TABLE test_table
(
    ID INTEGER,
    SRC VARIANT
);

insert into test_table select 1, to_variant('true'::boolean);          -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date);       -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time);  -- TIME
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 = '01:02:03.899213';
Copy

VARIANT 유형의 포인트 조회에 지원되는 조건자

검색 최적화 서비스는 아래에 나열된 조건자의 유형으로 포인트 조회 쿼리를 개선할 수 있습니다. 아래 예에서 src 는 VARIANT 열이고 path_to_variant_field 는 VARIANT 열의 필드에 대한 경로 입니다.

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

    where path_to_variant_field[::target_data_type] = constant

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

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

    • 요소를 명시적으로 캐스팅하지 않고 NUMBER 상수에 대해 요소 일치시키기.

      where src:person.age = 42;
      Copy

    • 지정된 전체 자릿수와 소수 자릿수로 요소를 NUMBER로 명시적으로 캐스팅하기.

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

    • 요소를 명시적으로 캐스팅하지 않고 TEXT 상수에 대해 요소 일치시키기.

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

    • 요소를 TEXT로 명시적으로 캐스팅하기.

      where src:salesperson.name::TEXT = 'John Appleseed';
      Copy

    • 요소를 DATE로 명시적으로 캐스팅하기.

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

    • 지정된 소수 자릿수로 요소를 TIME으로 명시적으로 캐스팅하기.

      where src:events.time_info::TIME(6) = '01:02:03.456789';
      Copy

    • 지정된 소수 자릿수로 요소를 TIMESTAMP로 명시적으로 캐스팅하기.

      where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
      Copy

  • 다음과 같은 ARRAY 함수를 사용하는 조건자:


    • where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

      이 구문에서 constant 는 NULL이 아니어야 하며 constant 의 데이터 타입은 지원 타입 중 하나여야 합니다.

      예:

      where ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
      Copy

    • where ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), path_to_variant_field)

      각 상수의 데이터 타입(constant_1, constant_2 등)은 위에 나열된 지원 타입 중 하나여야 합니다. 생성된 ARRAY는 NULL 상수를 포함할 수 있습니다.

      예:

      where ARRAYS_OVERLAP(
    ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
      Copy

  • 다음 조건자는 NULL 값 여부를 검사합니다.

    • where IS_NULL_VALUE(path_to_variant_field)

      IS_NULL_VALUE 는 SQL NULL 값이 아닌 JSON null 값에 적용됩니다.

    • where path_to_variant_field IS NOT NULL

    • where variant_column IS NULL

      여기서 variant_column 은 반정형 데이터의 요소에 대한 경로가 아니라 열을 나타냅니다.

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

VARIANT 유형의 하위 문자열 검색

검색 최적화 서비스는 반정형 열 (ARRAY, OBJECT 및 VARIANT 열 포함) 또는 이러한 열의 필드에서 와일드 카드 또는 정규식 검색 을 최적화할 수 있습니다. 여기에는 다음을 사용하는 조건자가 포함됩니다.

열 또는 열 내의 여러 개별 필드에 대해 하위 문자열 검색 최적화를 활성화할 수 있습니다. 예를 들어, 다음 문은 열의 중첩 필드에 대한 하위 문자열 검색 최적화를 활성화합니다.

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

특정 필드에 대해 검색 최적화를 활성화하면 모든 하위 필드에 대해서도 활성화됩니다. 첫 번째 문이 중첩된 search 필드를 포함하여 전체 data 필드에 대한 검색 최적화를 활성화하므로 아래의 두 번째 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

마찬가지로, 전체 열에 대해 검색 최적화를 활성화하면 해당 열 내의 모든 깊이에 중첩된 필드를 포함하여 해당 열의 모든 하위 문자열 검색을 최적화할 수 있습니다.

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)만 사용할 수 있습니다.

VARIANT 타입의 지원에서 현재 제한 사항

현재, 검색 최적화 서비스의 VARIANT 타입 지원에는 다음과 같은 제한 사항이 있습니다.

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

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

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

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

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

언어: 한국어