검색 최적화로 반정형 데이터 쿼리 속도 높이기¶
검색 최적화 서비스는 Snowflake 테이블의 반정형 데이터(즉, VARIANT, OBJECT 및 ARRAY 열 의 데이터)에 대한 포인트 조회 및 하위 문자열 쿼리의 성능을 개선할 수 있습니다. 구조가 깊게 중첩되어 있고 자주 변경되는 경우에도 이러한 유형의 열에 대해 검색 최적화를 구성할 수 있습니다. 반정형 열 내의 특정 요소에 대한 검색 최적화를 활성화할 수도 있습니다.
참고
ALTER TABLE … ADD SEARCH OPTIMIZATION 명령에서 ON 절을 사용하여 특정 열 또는 열의 요소에 대해 이 기능을 명시적으로 활성화해야 합니다. 테이블 수준에서 검색 최적화를 사용하도록 설정해도 반정형 데이터 타입을 가진 열에는 검색 최적화가 활성화되지 않습니다. 예:
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);
ON 절을 생략하면 VARIANT, OBJECT, ARRAY 열에 대한 쿼리가 최적화되지 않습니다.
다음 섹션에서는 이 지원에 대한 자세한 정보를 제공합니다.
반정형 타입의 조건자에서 상수 및 형 변환에 대해 지원되는 데이터 타입¶
검색 최적화 서비스는 요소에 대해 상수와 암시적 또는 명시적 캐스트 에 다음 타입이 사용되는 반정형 데이터의 포인트 조회 성능을 개선할 수 있습니다.
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);
이 테이블의 경우 검색 최적화 서비스는 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';
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 요소를 TEXT 상수와 일치시킵니다.
WHERE src:sender_info.ip_address = '123.123.123.123';
VARIANT 요소를 TEXT 로 명시적으로 형 변환합니다.
WHERE src:salesperson.name::TEXT = '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 열) 또는 해당 열의 요소에서 와일드카드 또는 정규식 검색 을 최적화할 수 있습니다. 여기에는 다음을 사용하는 조건자가 포함됩니다.
열 또는 열 내의 여러 개별 요소에 대해 하위 문자열 검색 최적화를 사용 설정할 수 있습니다. 예를 들어, 다음 문은 열의 중첩된 요소에 대한 하위 문열 검색 최적화를 활성화합니다.
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
검색 액세스 경로가 만들어지면 다음 쿼리를 최적화할 수 있습니다.
SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';
그러나 다음 쿼리는 검색 최적화를 사용하도록 설정(col2:data.search
)했을 때 지정한 요소에 WHERE 절 필터가 적용되지 않기 때문에 최적화되지 않습니다.
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자 길이의 토큰만 고려합니다.
조건자의 예 |
검색 최적화 서비스가 쿼리를 처리하는 방법 |
---|---|
|
검색 최적화 서비스는 다음 조건자에 대해 검색 액세스 경로를 사용하지 않으며, 이는 하위 문자열이 5자보다 짧기 때문입니다. |
|
검색 최적화 서비스는 검색 액세스 경로를 사용하여 |
|
검색 최적화 서비스는 이 쿼리를 최적화하여 검색 액세스 경로를 사용해 |
|
검색 최적화 서비스는 이 문자열을 |
|
검색 최적화 서비스는 이를 토큰 |
|
검색 최적화 서비스는 이 문자열을 |
현재 반정형 지원의 제한 사항¶
검색 최적화 서비스에서 반정형 유형에 대한 지원은 다음과 같은 방식으로 제한됩니다.
XMLGET 을 사용하는 조건자는 지원되지 않습니다.
path_to_element IS NULL
형식의 조건자는 지원되지 않습니다.상수가 스칼라 하위 쿼리의 결과인 조건자는 지원되지 않습니다.
하위 요소를 포함하는 요소의 경로를 지정하는 조건자는 지원되지 않습니다.
이 기능에는 검색 최적화 서비스의 현재 제한 사항 도 적용됩니다.