검색 최적화로 구조화 데이터 쿼리 속도 향상

검색 최적화로 구조화 데이터 쿼리 속도 향상

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

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

• 구조화 데이터 쿼리에 대한 검색 최적화 활성화
• 구조화 타입 포인트 조회에 대해 지원되는 조건부
• 구조화 타입의 부분 문자열 검색
• 스키마 진화 지원
• 구조화 타입 지원의 현재 제한 사항

출처: Snowflake 문서

본문

구조화 데이터 쿼리에 대한 검색 최적화 활성화

테이블에서 구조화 데이터 타입 쿼리의 성능을 향상시키려면 특정 열 또는 열의 요소에 대해 ALTER TABLE … ADD SEARCH OPTIMIZATION 명령의 ON 절 을 사용하세요. ON 절을 생략하면 구조화 ARRAY, OBJECT, MAP 열에 대한 쿼리는 최적화되지 않습니다. 테이블 수준에서 검색 최적화를 활성화해도 구조화 데이터 타입이 있는 열에서는 활성화되지 않습니다.

예를 들어:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(array_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(array_column[1]);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(object_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(object_column:key);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column:user.uuid);

이러한 ALTER TABLE … ADD SEARCH OPTIMIZATION 명령에서 사용하는 키워드에는 다음 규칙이 적용됩니다.

• EQUALITY 키워드를 내부 요소 또는 열 자체와 함께 사용할 수 있습니다.
• SUBSTRING 키워드는 텍스트 문자열 데이터 타입을 가진 내부 요소에만 사용할 수 있습니다.

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

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

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

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

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

• CAST 및 :: 연산자
• TRY_CAST

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

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

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

요소를 명시적으로 캐스트하지 않고 OBJECT 또는 MAP 요소를 NUMBER 상수와 매칭:

WHERE src:person.age = 42;

• OBJECT 또는 MAP 요소를 지정된 정밀도와 스케일로 NUMBER에 명시적으로 캐스트:

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

• 요소를 명시적으로 캐스트하지 않고 OBJECT 또는 MAP 요소를 VARCHAR 상수와 매칭:

WHERE src:sender_info.ip_address = '123.123.123.123';

• OBJECT 또는 MAP 요소를 VARCHAR로 명시적으로 캐스트:

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

• OBJECT 또는 MAP 요소를 DATE로 명시적으로 캐스트:

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

• OBJECT 또는 MAP 요소를 지정된 스케일의 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 또는 MAP 요소를 지원되는 타입 의 값과 매칭:

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)

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

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 상수를 포함할 수 있습니다. 이 예시에서 배열은 OBJECT 값 안에 있습니다.

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 structured_column IS NULL 여기서 structured_column은 구조화 데이터의 요소 경로가 아닌 열을 가리킵니다. 예를 들어 검색 최적화 서비스는 OBJECT 열 src를 지원하지만 해당 OBJECT 열의 요소 경로 src:person.age는 지원하지 않습니다.

구조화 타입의 부분 문자열 검색

대상 구조화 요소가 텍스트 문자열 데이터 타입인 경우에만 부분 문자열 검색을 활성화할 수 있습니다.

예를 들어 다음 테이블을 고려해 보세요.

CREATE TABLE t(
  col OBJECT(
    a INTEGER,
    b STRING,
    c MAP(INTEGER, STRING),
    d ARRAY(STRING)
  )
);

이 테이블에 대해 다음 대상 구조화 요소에는 SUBSTRING 검색을 위한 검색 최적화를 추가할 수 있습니다.

• col:b — 타입이 STRING이므로.
• col:c[value] — 예: col:c[0], col:c[100] — 값이 텍스트 문자열 타입인 경우.

이 테이블에 대해 다음 대상 구조화 요소에는 SUBSTRING 검색을 위한 검색 최적화를 추가할 수 없습니다.

• col — 타입이 구조화 OBJECT이므로.
• col:a — 타입이 INTEGER이므로.
• col:c — 타입이 MAP이므로.
• col:d — 타입이 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);

주어진 요소에 대해 검색 최적화를 활성화하면 텍스트 문자열 타입의 모든 비중첩(unnested) 요소에 대해 활성화됩니다. 중첩 요소나 텍스트 문자열이 아닌 타입의 요소에는 검색 최적화가 활성화되지 않습니다.

구조화 부분 문자열 검색에서 상수가 평가되는 방식

쿼리의 상수 문자열 — 예: 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)만 사용할 수 있습니다.

스키마 진화 지원

구조화 열의 스키마는 시간이 지나면서 진화할 수 있습니다. 스키마 진화에 대한 자세한 내용은 ALTER ICEBERG TABLE … ALTER COLUMN … SET DATA TYPE (structured types) 를 참고하세요.

단일 스키마 진화 작업의 일부로 다음 수정이 발생할 수 있습니다.

• 타입 확장(Type widening)
• 요소 재정렬
• 요소 추가
• 요소 제거
• 요소 이름 변경

검색 최적화 서비스는 스키마 진화 작업의 일부로 무효화되지 않습니다. 대신 검색 최적화 서비스는 작업을 다음 방식으로 처리합니다.

• 타입 확장(예: INT에서 NUMBER로): 검색 최적화 액세스 경로는 영향을 받지 않습니다.

• 요소 추가: 새로 추가된 요소는 기존 검색 최적화 액세스 경로에 자동으로 반영됩니다.

• 요소 제거: 요소가 구조화 열에서 제거되면 검색 최적화 서비스는 제거된 요소로 접두사(prefix)가 붙은 액세스 경로를 자동으로 드롭합니다.

예를 들어 OBJECT 타입의 열로 테이블을 생성한 다음 데이터를 삽입하세요.

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

데이터를 보려면 테이블을 쿼리하세요.

SELECT * FROM test_struct;

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

다음 문은 객체에서 요소 c를 제거합니다.

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER);

이 문이 실행되면 a, a:c, a:c:d 및 a:c:e의 액세스 경로가 드롭됩니다.

• 요소 이름 변경: 요소 이름이 변경되면 검색 최적화 서비스는 이름이 변경된 요소로 접두사가 붙은 액세스 경로를 자동으로 드롭하고 새 이름의 경로로 다시 추가합니다. 이 작업은 검색 최적화 서비스에서 새로 추가된 경로를 처리하는 추가 유지 관리 비용을 발생시킵니다.

예를 들어 OBJECT 타입의 열로 테이블을 생성한 다음 데이터를 삽입하세요.

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

데이터를 보려면 테이블을 쿼리하세요.

SELECT * FROM test_struct;

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

다음 문은 객체에서 요소 c의 이름을 c_new로 바꿉니다.

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER,
    c_new OBJECT(
      d STRING,
      e VARIANT
    )
  ) RENAME FIELDS;

a, a:c, a:c:d, a:c:e의 액세스 경로가 드롭되고 a, a:c_new, a:c_new:d, a:c_new:e로 다시 추가됩니다.

• 요소 재정렬: 검색 최적화 액세스 경로는 영향을 받지 않습니다.

구조화 타입 지원의 현재 제한 사항

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

• path_to_element IS NULL 형태의 조건부는 지원되지 않습니다.

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

• 하위 요소(sub-elements)를 포함하는 요소에 대한 경로를 지정하는 조건부는 지원되지 않습니다.

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

• MAP_CONTAINS_KEY 함수를 사용하는 조건부는 지원되지 않습니다.

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

더 알아보기 (Learn more)

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