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

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

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);
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 형식의 조건자는 지원되지 않습니다.

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

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

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

언어: 한국어