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

검색 최적화 서비스는 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 타입에 대한 조건자의 상수와 캐스트를 위해 지원되는 데이터 타입
TEXT로 캐스팅된 VARIANT 값 지원
VARIANT 유형의 포인트 조회에 지원되는 조건자
VARIANT 유형의 하위 문자열 검색
VARIANT 타입의 지원에서 현재 제한 사항

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

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

FIXED(유효한 전체 자릿수와 소수 자릿수를 지정하는 캐스트 포함)
INTEGER
TEXT
DATE(소수 자릿수를 지정하는 캐스트 포함)
TIME(소수 자릿수를 지정하는 캐스트 포함)
TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ(소수 자릿수를 지정하는 캐스트 포함)

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

TEXT로 캐스팅된 VARIANT 값 지원¶

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

예를 들어, src 가 VARIANT로 변환된 부울, 날짜 및 시간 값을 포함하는 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 형식의 조건자는 지원되지 않습니다.
상수가 스칼라 하위 쿼리의 결과인 조건자는 지원되지 않습니다.
하위 요소를 포함하는 요소에 대한 경로를 지정하는 조건자는 지원되지 않습니다.

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