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

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

검색 최적화 서비스는 Snowflake 테이블의 반정형 데이터(VARIANT, OBJECT, ARRAY 열 의 데이터)에 대한 포인트 조회 및 부분 문자열 쿼리의 성능을 향상시킬 수 있습니다. 구조가 깊게 중첩되고 자주 변경되더라도 이러한 유형의 열에 검색 최적화를 구성할 수 있습니다. 또한 반정형 열 내의 특정 요소에 대해 검색 최적화를 활성화할 수 있습니다.

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

• 반정형 데이터 쿼리에 대한 검색 최적화 활성화
• 반정형 타입 조건부의 상수 및 캐스트에 대해 지원되는 데이터 타입
• VARCHAR로 캐스트된 반정형 데이터 타입 값 지원
• VARIANT 타입 포인트 조회에 대해 지원되는 조건부
• VARIANT 타입의 부분 문자열 검색
• 반정형 타입 지원의 현재 제한 사항

출처: Snowflake 문서

본문

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

테이블에서 반정형 데이터 쿼리의 성능을 향상시키려면 특정 열 또는 열의 요소에 대해 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);

자세한 내용은 검색 최적화 활성화 및 비활성화 를 참고하세요.

반정형 타입 조건부의 상수 및 캐스트에 대해 지원되는 데이터 타입

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

• FIXED (유효한 정밀도와 스케일을 지정하는 캐스트 포함)
• INTEGER (동의어 타입 포함)
• VARCHAR (동의어 타입 포함)
• DATE (스케일을 지정하는 캐스트 포함)
• TIME (스케일을 지정하는 캐스트 포함)
• TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (스케일을 지정하는 캐스트 포함)

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

• CAST 및 :: 연산자
• TRY_CAST

VARCHAR로 캐스트된 반정형 데이터 타입 값 지원

검색 최적화 서비스는 반정형 데이터 타입의 열이 VARCHAR로 캐스트되고 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);

이 테이블에 대해 검색 최적화 서비스는 VARIANT 열을 VARCHAR로 캐스트하고 열을 문자열 상수와 비교하는 다음 쿼리를 향상시킬 수 있습니다.

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';

VARIANT 타입 포인트 조회에 대해 지원되는 조건부

검색 최적화 서비스는 아래 나열된 유형의 조건부를 가진 포인트 조회 쿼리를 향상시킬 수 있습니다. 아래 예시에서 src는 반정형 데이터 타입의 열이고, path_to_element는 반정형 데이터 타입 열의 요소 경로 입니다.

• 다음 형태의 동등 조건부: WHERE path_to_element[::target_data_type] = constant 이 구문에서 target_data_type(지정된 경우)과 constant의 데이터 타입은 지원되는 타입 중 하나여야 합니다. 예를 들어 검색 최적화 서비스는 다음을 지원합니다.

요소를 명시적으로 캐스트하지 않고 VARIANT 요소를 NUMBER 상수와 매칭.

WHERE src:person.age = 42;

• VARIANT 요소를 지정된 정밀도와 스케일로 NUMBER에 명시적으로 캐스트.

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

• 요소를 명시적으로 캐스트하지 않고 VARIANT 요소를 VARCHAR 상수와 매칭.

WHERE src:sender_info.ip_address = '123.123.123.123';

• VARIANT 요소를 VARCHAR로 명시적으로 캐스트.

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

• VARIANT 요소를 DATE로 명시적으로 캐스트.

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

• VARIANT 요소를 지정된 스케일의 TIMESTAMP로 명시적으로 캐스트.

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

• 명시적으로 타입으로 캐스트하거나 하지 않고 ARRAY 요소를 지원되는 타입 의 값과 매칭. 예:

WHERE my_array_column[2] = 5;

WHERE my_array_column[2]::NUMBER(4, 1) = 5;

• 명시적으로 타입으로 캐스트하거나 하지 않고 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;

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

• WHERE ARRAY_CONTAINS(value_expr, array) 이 구문에서 value_expr는 NULL이 아니어야 하고 VARIANT로 평가되어야 합니다. 값의 데이터 타입은 지원되는 타입 중 하나여야 합니다. 예:

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

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

WHERE ARRAY_CONTAINS(300, my_array_column)

• 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)

이 예시에서 배열은 ARRAY 열입니다.

WHERE ARRAYS_OVERLAP(
  ARRAY_CONSTRUCT('a', 'b'), my_array_column)

• 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 열 — 또는 그러한 열의 요소에서 와일드카드 또는 정규 표현식 검색 을 최적화할 수 있습니다.

검색 최적화 서비스는 다음 함수를 사용하는 조건부를 최적화할 수 있습니다.

• LIKE
• LIKE ANY
• LIKE ALL
• ILIKE
• ILIKE ANY
• CONTAINS
• ENDSWITH
• STARTSWITH
• SPLIT_PART
• RLIKE
• REGEXP
• REGEXP_LIKE

열 또는 열 내의 여러 개별 요소에 대해 부분 문자열 검색 최적화를 활성화할 수 있습니다. 예를 들어 다음 문은 열의 중첩 요소에 대해 부분 문자열 검색 최적화를 활성화합니다.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

검색 액세스 경로가 구축된 후 다음 쿼리가 최적화될 수 있습니다.

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

그러나 WHERE 절 필터가 검색 최적화가 활성화될 때 지정된 요소(col2:data.search)에 적용되지 않으므로 다음 쿼리는 최적화되지 않습니다.

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

최적화할 여러 요소를 지정할 수 있습니다. 다음 예시에서 열 col2의 두 특정 요소에 대해 검색 최적화가 활성화됩니다.

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

주어진 요소에 대해 검색 최적화를 활성화하면 중첩된 모든 요소에 대해 활성화됩니다. 아래 두 번째 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);

마찬가지로 전체 열에 대해 검색 최적화를 활성화하면 그 열의 모든 부분 문자열 검색이 최적화될 수 있으며, 열 내부에 어떤 깊이로든 중첩된 요소를 포함합니다.

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

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

검색 최적화 서비스의 반정형 타입 지원은 다음과 같이 제한됩니다.

• path_to_element IS NULL 형태의 조건부는 지원되지 않습니다.
• 상수가 스칼라 서브쿼리의 결과인 조건부는 지원되지 않습니다.
• 하위 요소(sub-elements)를 포함하는 요소에 대한 경로를 지정하는 조건부는 지원되지 않습니다.
• XMLGET 함수를 사용하는 조건부는 지원되지 않습니다.

검색 최적화 서비스의 현재 제한 사항 이 반정형 타입에도 적용됩니다.

더 알아보기 (Learn more)

• 검색 최적화로 구조화 데이터 쿼리 속도 향상
• 검색 최적화로 부분 문자열 쿼리 속도 향상
• 검색 최적화로 혜택을 받는 쿼리 식별