콜레이션 지원

콜레이션 지원 (Collation support)

콜레이션(collation)은 텍스트 문자열을 비교하는 대체 규칙을 지정할 수 있게 해 주는 기능이에요. 특정 언어나 사용자가 지정한 다른 규칙에 따라 데이터를 비교하고 정렬하는 데 사용할 수 있어요. 이 페이지에서는 콜레이션의 개념, 활용 방법, 콜레이션 지정(specification) 문법과 지정자(specifier), 그리고 내부 동작 세부 사항을 다뤄요.

출처: Snowflake SQL Reference

본문

콜레이션은 텍스트 문자열을 비교하는 대체 규칙을 지정할 수 있게 해 주며, 특정 언어나 사용자가 지정한 다른 규칙에 따라 데이터를 비교하고 정렬하는 데 사용할 수 있어요.

콜레이션 지원 개요

다음 섹션들은 콜레이션이 무엇이고 문자열을 비교할 때 어떻게 사용하는지 설명해요.

• 콜레이션 이해
• 콜레이션 용도
• 콜레이션 제어

콜레이션 이해

Snowflake의 텍스트 문자열은 UTF-8 문자 집합으로 저장되며, 기본적으로 문자열은 문자열의 문자를 나타내는 유니코드 코드에 따라 비교돼요.

그러나 UTF-8 문자 표현을 기반으로 문자열을 비교하는 것이 바람직하거나 기대되는 동작을 제공하지 못할 수 있어요. 예를 들어:

• 특정 언어의 특수 문자가 그 언어의 정렬 표준에 따라 정렬되지 않으면 정렬이 예상치 못한 결과를 반환할 수 있어요.
• 문자가 대문자인지 소문자인지 무시하는 것 같은 다른 규칙으로 문자열을 정렬하고 싶을 수 있어요.

콜레이션은 다음을 기반으로 문자열 비교에 사용할 규칙을 명시적으로 지정할 수 있게 해 줘요.

• 서로 다른 로케일(locale) (즉, 언어별로 다른 문자 집합).
• 대소문자 구분(case-sensitivity) (즉, 문자열을 변환하기 위해 UPPER나 LOWER 함수를 명시적으로 호출하지 않고 대소문자 구분·비구분 문자열 비교를 사용할지 여부).
• 악센트 구분(accent-sensitivity) (예: Z, Ź, Ż가 같은 글자인지 다른 글자인지).
• 구두점 구분(punctuation-sensitivity) (즉, 비교가 글자만 사용하는지 모든 문자를 포함하는지). 예를 들어 구두점 비구분 비교라면 A-B-C와 ABC가 동등하게 취급돼요.
• 추가 옵션, 예: 문자열의 첫 글자를 기반으로 정렬하는 선호와 앞·뒤 공백 정리(trimming) 선호.

콜레이션 용도

콜레이션은 광범위한 연산에서 사용할 수 있어요.

용도	예	링크
단순 비교	... WHERE column1 = column2 ...	WHERE
조인	... ON table1.column1 = table2.column2 ...	JOIN
정렬	... ORDER BY column1 ...	ORDER BY
Top-K 정렬	... ORDER BY column1 LIMIT N ...	LIMIT / FETCH
집계	... GROUP BY ...	GROUP BY
윈도우 함수	... PARTITION BY ... ORDER BY ...	윈도우 함수
스칼라 함수	... LEAST(column1, column2, column3) ...	스칼라 함수
집계 함수	... MIN(column1), MAX(column1) ...	집계 함수
데이터 클러스터링	... CLUSTER BY (column1) ...	클러스터링 키 & 클러스터링된 테이블

콜레이션 제어

콜레이션 제어는 세분화(granular)돼요. 다음에 사용할 콜레이션을 명시적으로 지정할 수 있어요.

• 계정: 계정 수준 파라미터 DEFAULT_DDL_COLLATION 사용.
• 데이터베이스에 추가되는 모든 테이블의 모든 컬럼: ALTER DATABASE 명령 사용.
• 스키마에 추가되는 모든 테이블의 모든 컬럼: ALTER SCHEMA 명령 사용.
• 테이블에 추가되는 모든 컬럼: ALTER TABLE 명령 사용.
• 테이블의 개별 컬럼: CREATE TABLE 명령 사용.
• SQL 문 안의 특정 비교(예: WHERE col1 = col2). 문에 여러 콜레이션이 적용되면 Snowflake는 우선순위에 따라 사용할 콜레이션을 결정해요. 우선순위에 대한 자세한 내용은 다중 문자열 연산에서의 콜레이션 우선순위를 참고해요.

Snowflake 관리 Iceberg 테이블의 경우 DEFAULT_DDL_COLLATION 대신 ICEBERG_DEFAULT_DDL_COLLATION 파라미터를 사용해요. Iceberg 테이블에서의 콜레이션 지원을 참고해요.

콜레이션 SQL 구성 요소(Constructs)

콜레이션에 다음 SQL 구성 요소를 사용할 수 있어요.

• 테이블 컬럼 정의의 COLLATE 절
• COLLATE 함수
• COLLATION 함수

테이블 컬럼 정의의 COLLATE 절

테이블 컬럼 정의에 선택적 COLLATE 절을 추가하면, 지정된 콜레이션이 컬럼의 데이터에 대해 수행되는 비교 및 기타 관련 연산에 사용됨을 나타내요.

CREATE TABLE <table_name> ( <col_name> <col_type> COLLATE '<collation_specification>'
                            [ , <col_name> <col_type> COLLATE '<collation_specification>' ... ]
                            [ , ... ]
                          )

컬럼에 COLLATE 절을 지정하지 않으면 Snowflake는 기본값을 사용하며, 문자열은 UTF-8 문자 표현을 기반으로 비교돼요.

또한 Snowflake는 콜레이션 지정에 빈 문자열을 지정하는 것(예: COLLATE '')을 지원하며, 이는 컬럼에 콜레이션을 지정하지 않는 것과 같아요.

그러나 우선순위 때문에 컬럼에 COLLATE ''를 지정하는 것은 COLLATE 'utf8'을 명시적으로 지정하는 것과 같은 효과는 아니에요. 자세한 내용은 다중 문자열 연산에서의 콜레이션 우선순위를 참고해요.

하이브리드 테이블(hybrid tables)의 인덱스된 컬럼에는 COLLATE 절을 지정할 수 없어요. 자세한 내용은 하이브리드 테이블 컬럼의 콜레이션을 참고해요.

테이블 컬럼에 콜레이션이 지정되었는지 보려면 DESCRIBE TABLE을 사용해요. DESCRIBE TABLE 명령을 실행하면 출력의 type 컬럼에 콜레이션 지정이 있어요. 또는 COLLATION 함수를 사용해 특정 컬럼의 콜레이션(있는 경우)을 볼 수 있어요.

COLLATE 함수

COLLATE 함수는 입력 문자열 표현식에 지정된 콜레이션을 사용해요.

COLLATE( <expression> , '<collation_specification>' )

이 함수는 중위(infix) 표기법으로도 호출할 수 있어요.

<expression> COLLATE '<collation_specification>'

이 함수는 특정 연산(예: 정렬)에 특정 콜레이션을 명시적으로 지정할 때 특히 유용하며, 다음에도 사용할 수 있어요.

• 하위 쿼리의 SELECT 절에서 콜레이션을 허용해, 외부 쿼리의 지정된 컬럼에 대한 모든 연산이 콜레이션을 사용하도록 만들기.
• 지정된 콜레이션으로 CTAS를 사용해 테이블 만들기.

이 예제는 영어 대소문자 비구분 콜레이션으로 평가해요.

SELECT * FROM t1 WHERE COLLATE(col1 , 'en-ci') = 'Tango';

이 예제는 독일어(Deutsch) 콜레이션으로 결과를 정렬해요.

SELECT * FROM t1 ORDER BY COLLATE(col1 , 'de');

이 예제는 프랑스어 콜레이션을 사용하는 컬럼으로 테이블을 만들어요.

CREATE TABLE t2 AS SELECT COLLATE(col1, 'fr') AS col1 FROM t1;

이 예제는 중위 표기법을 사용해 프랑스어 콜레이션을 사용하는 컬럼으로 테이블을 만들어요.

CREATE TABLE t2 AS SELECT col1 COLLATE 'fr' AS col1 FROM t1;

COLLATION 함수

COLLATION 함수는 테이블 컬럼을 포함한 표현식이 사용하는 콜레이션 지정을 반환해요.

COLLATION( <expression> )

표현식에 콜레이션이 지정되지 않았다면 함수는 NULL을 반환해요.

일반적으로 컬럼 이름에 이 함수를 사용한다면, 테이블의 각 행마다 출력 행 하나를 얻지 않도록 DISTINCT를 사용하는 것이 좋아요. 예를 들어:

SELECT DISTINCT COLLATION(column1) FROM table1;

Note

이 함수는 콜레이션 지정만 반환하고 그 우선순위 수준은 반환하지 않아요. 우선순위에 대한 자세한 내용은 이 주제의 다중 문자열 연산에서의 콜레이션 우선순위를 참고해요.

콜레이션 지정(Collation specifications)

COLLATE 절(테이블 컬럼용)이나 COLLATE 함수(표현식용)를 사용할 때는, 컬럼/표현식에 사용되는 비교 논리를 결정하는 콜레이션 지정을 포함해야 해요.

콜레이션 지정은 하이픈(-)으로 구분된 하나 이상의 지정자(specifier) 문자열로 구성되며, 다음과 같은 형태예요.

'<specifier>[-<specifier> ...]'

다음 지정자가 지원돼요(자세한 내용은 이 주제의 지원되는 지정자 참고):

• 로케일(Locale)
• 대소문자 구분(Case-sensitivity)
• 악센트 구분(Accent-sensitivity)
• 구두점 구분(Punctuation-sensitivity)
• 첫 글자 선호(First-letter preference)
• 대소문자 변환(Case-conversion)
• 공백 정리(Space-trimming)

지정자는 대소문자를 구분하지 않으며, 사용된다면 항상 첫 번째여야 하는 로케일을 제외하고는 어떤 순서로든 될 수 있어요.

다음 섹션들은 콜레이션 지정에 대한 더 자세한 내용을 제공해요.

• 지정 예제
• 지원되는 지정자

지정 예제

콜레이션 지정 문자열의 몇 가지 예는 다음과 같아요.

• 'de': 독일어(Deutsch) 로케일.
• 'de-ci-pi': 독일어 로케일, 대소문자 비구분·구두점 비구분 비교.
• 'fr_CA-ai': 캐나다 프랑스어 로케일, 악센트 비구분 비교.
• 'en_US-trim': 미국 영어 로케일, 비교 전에 앞 공백과 뒤 공백이 정리됨.

콜레이션 지정에 빈 문자열을 지정할 수도 있어요(예: COLLATE '' 또는 COLLATE(col1, '')). 이는 콜레이션을 사용하지 않음을 나타내요.

지원되는 지정자

로케일(Locale):

언어별·국가별 규칙을 지정해요.

_language_ __country_ 형태의 언어 코드(필수)와 국가 코드(선택)로 구성된 유효한 로케일 문자열을 지원해요. 몇 가지 로케일 예시:

• en - English
• en_US - American English
• fr - French
• fr_CA - Canadian French

또한 utf8 의사 로케일(pseudo-locale)은 기본값인 유니코드 순서(Unicode ordering)를 지정해요. 자세한 내용은 이 주제의 UTF-8 또는 로케일 콜레이션 사용 시 정렬 차이를 참고해요.

로케일 지정자는 선택 사항이지만, 사용된다면 문자열의 첫 번째 지정자여야 해요.

Snowflake가 지원하는 로케일의 전체 목록은 Snowflake가 지원하는 콜레이션 로케일을 참고해요.

대소문자 구분(Case-sensitivity):

값을 비교할 때 대소문자를 고려할지 결정해요. 가능한 값:

• cs - 대소문자 구분 (기본값)
• ci - 대소문자 비구분

예를 들어:

콜레이션 지정	값	결과
'en-ci'	Abc = abc	True
'en-cs' / en	Abc = abc	False

악센트 구분(Accent-sensitivity):

악센트가 붙은 문자가 기본 문자와 같은 것으로 간주되는지, 다른 것으로 간주되는지 결정해요. 가능한 값:

• as - 악센트 구분 (기본값)
• ai - 악센트 비구분

예를 들어:

콜레이션 지정	값	결과	비고
'fr-ai'	E = É	True
'fr-as' / 'fr'	E = É	False
'en-ai'	a = ą	True	영어에서 이 문자들은 악센트 차이만 있는 것으로 취급되므로 악센트 비구분을 지정하면 값이 같은 것으로 비교돼요.
'pl-ai'	a = ą	False	폴란드어에서 이 문자들은 별개의 기본 문자로 취급되므로, 악센트 비구분을 지정하더라도 항상 같지 않은 것으로 비교돼요.
'pl-as' / 'pl'	a = ą	False
악센트 구분과 콜레이션의 규칙은 언어마다 달라요. 예를 들어 일부 언어에서는 콜레이션이 항상 악센트 구분이고, 악센트 비구분 콜레이션을 지정해서도 끌 수 없어요.

구두점 구분(Punctuation-sensitivity):

글자가 아닌 문자가 중요한지 결정해요. 가능한 값:

• ps - 구두점 구분.
• pi - 구두점 비구분.

기본값은 로케일별로 다르다는 점에 주의해요(즉, 구두점 구분을 지정하지 않으면 로케일별 규칙이 사용돼요). 대부분의 경우 그 규칙은 ps와 같아요.

예를 들어:

콜레이션 지정	값	결과	비고
'en-pi'	A-B-C = ABC	True
'en-ps'	A-B-C = ABC	False

첫 글자 선호(First-letter preference):

정렬할 때 대문자와 소문자 중 어느 것이 먼저 오는지 결정해요. 가능한 값:

• fl - 소문자가 먼저 정렬됨.
• fu - 대문자가 먼저 정렬됨.

기본값은 로케일별로 다르며(즉, 값을 지정하지 않으면 로케일별 순서가 사용돼요), 대부분의 경우 순서는 fl과 같아요.

또한 이 지정자는 동등성 비교에는 영향을 주지 않아요.

대소문자 변환(Case-conversion):

비교 전에 문자열을 소문자나 대문자로 변환하게 해요. 어떤 상황에서는 전체 로케일별 콜레이션보다 더 빠를 수 있어요. 가능한 값:

• upper - 비교 전에 문자열을 대문자로 변환.
• lower - 비교 전에 문자열을 소문자로 변환.

이 지정자에는 기본값이 없어요(즉, 값을 지정하지 않으면 두 변환 모두 발생하지 않아요).

공백 정리(Space-trimming):

비교 전에 문자열에서 앞/뒤 공백을 제거해요. 이 기능은 SQL CHAR 데이터 타입과 (극히 드문 모서리 경우를 제외하고) 의미상 동등한 비교를 수행하는 데 유용할 수 있어요.

가능한 값:

• trim - 비교 전에 앞·뒤 공백 모두 제거.
• ltrim - 비교 전에 앞 공백만 제거.
• rtrim - 비교 전에 뒤 공백만 제거.

이 지정자에는 기본값이 없어요(즉, 값을 지정하지 않으면 정리(trimming)가 수행되지 않아요).

예를 들어:

콜레이션 지정	값	결과	비고
'en-trim'	__ABC_ = ABC	True	이 예제에서는 밑줄 문자가 공백을 나타내요.
'en-ltrim'	__ABC_ = ABC	False
'en-rtrim'	__ABC_ = ABC	False
'en'	__ABC_ = ABC	False

콜레이션 구현 세부 사항

다음 섹션들은 콜레이션 지원에 대한 더 자세한 내용을 제공해요.

• 대소문자 비구분 비교
• UTF-8 또는 로케일 콜레이션 사용 시 정렬 차이
• 다중 문자열 연산에서의 콜레이션 우선순위
• 내장 함수에서 콜레이션의 제한적 지원
• 콜레이션 사용의 성능 영향
• 콜레이션 사용에 대한 추가 고려 사항

대소문자 비구분 비교

다음 섹션들은 대소문자 비구분 비교를 설명해요.

• 대문자 문자열과 원본 문자열 비교의 차이
• 문자 가중치(character weights)

대문자 문자열과 원본 문자열 비교의 차이

일부 언어에서는 두 개의 소문자 문자가 같은 해당 대문자를 가져요. 예를 들어 일부 언어는 점이 있는 소문자 I와 없는 소문자 I의 형태를 모두 지원해요(예: i와 ı). 문자열을 대문자로 강제하면 비교에 영향을 줘요.

다음 예제는 그 차이를 보여 줘요.

테이블 만들기:

CREATE OR REPLACE TABLE test_table (col1 VARCHAR, col2 VARCHAR);
INSERT INTO test_table VALUES ('ı', 'i');

데이터 쿼리:

SELECT col1 = col2,
       COLLATE(col1, 'lower') = COLLATE(col2, 'lower'),
       COLLATE(col1, 'upper') = COLLATE(col2, 'upper')
  FROM test_table;

+-------------+-------------------------------------------------+-------------------------------------------------+
| COL1 = COL2 | COLLATE(COL1, 'LOWER') = COLLATE(COL2, 'LOWER') | COLLATE(COL1, 'UPPER') = COLLATE(COL2, 'UPPER') |
|-------------+-------------------------------------------------+-------------------------------------------------|
| False       | False                                           | True                                            |
+-------------+-------------------------------------------------+-------------------------------------------------+

문자 가중치(Character weights)

Snowflake는 다음 콜레이션 지정을 지원해요.

• ICU (International Components for Unicode).
• Snowflake 특정 콜레이션 지정(예: upper와 lower).

ICU가 정의한 대소문자 비구분 비교 연산의 경우, Snowflake는 Unicode Collation Algorithm (UCA)을 따르며 유니코드 문자의 1차(primary)·2차(secondary) 가중치만 고려하고 3차(tertiary) 가중치는 고려하지 않아요. 3차 가중치에서만 다른 문자는 동일하게 취급돼요. 예를 들어 en-ci 콜레이션 지정을 사용하면 공백과 줄바꿈 없는 공백(non-breaking space)이 동일한 것으로 간주돼요.

UTF-8 또는 로케일 콜레이션 사용 시 정렬 차이

문자열은 항상 Snowflake 내부에 UTF-8로 저장되며, UTF-8이 지원하는 모든 언어의 어떤 문자든 표현할 수 있어요. 따라서 콜레이션을 지정하지 않으면 동작은 UTF-8 콜레이션(즉, 'utf8')과 같아요.

Snowflake에서 'utf8'과 'bin'은 동등한 콜레이션 지정이에요. 그러나 이 지정들은 단일 표현식에서 혼합될 수 없어요. 예를 들어 다음 쿼리는 오류를 반환해요.

SELECT 'abc' COLLATE 'bin' = 'abc' COLLATE 'utf8';

UTF-8 콜레이션은 문자의 알파벳 순서가 아니라 문자의 숫자 표현에 기반해요.

이는 각 ASCII 문자의 서수값으로 정렬하는 것과 유사하며, 대문자가 소문자보다 서수값이 낮다는 점을 주목하는 것이 중요해요:

A = 65 B = 66 ... a = 97 b = 98 ...

결과적으로:

• UTF-8 순서로 정렬하면 모든 대문자가 모든 소문자보다 먼저 반환돼요: A , B , … , Y , Z , … , a , b , … , y , z
• 반면 'en' 콜레이션 지정은 (UTF-8 내부 표현 대신) 알파벳순으로 정렬해서, A와 a가 모두 B와 b보다 먼저 반환돼요: a , A , b , B , …

또한 cs와 ci 대소문자 구분 지정자의 차이가 정렬에 영향을 줘요.

• cs(대소문자 구분)는 항상 같은 글자의 소문자 버전을 대문자 버전보다 먼저 반환해요. 예를 들어 'en-cs' 사용 시: a , A , b , B , …

대소문자 구분이 기본값이므로 'en-cs'와 'en'은 동등해요.

• ci(대소문자 비구분)는 글자의 대문자·소문자 버전을 서로에 대해 무작위로 반환하지만, 여전히 더 뒤 글자의 대문자·소문자 버전보다 먼저 반환해요. 예를 들어 'en-ci' 사용 시: A , a , b , B , …

일부 비알파벳 문자도 콜레이션 설정에 따라 다르게 정렬될 수 있어요. 다음 예제는 더하기 문자(+)와 빼기 문자(-)가 서로 다른 콜레이션 설정에 대해 다르게 정렬됨을 보여 줘요.

테이블 만들기:

CREATE OR REPLACE TABLE demo (
    no_explicit_collation VARCHAR,
    en_ci VARCHAR COLLATE 'en-ci',
    en VARCHAR COLLATE 'en',
    utf_8 VARCHAR collate 'utf8');
INSERT INTO demo (no_explicit_collation) VALUES
    ('-'),
    ('+');
UPDATE demo SET
    en_ci = no_explicit_collation,
    en = no_explicit_collation,
    utf_8 = no_explicit_collation;

데이터 쿼리:

SELECT MAX(no_explicit_collation), MAX(en_ci), MAX(en), MAX(utf_8)
  FROM demo;

+----------------------------+------------+---------+------------+
| MAX(NO_EXPLICIT_COLLATION) | MAX(EN_CI) | MAX(EN) | MAX(UTF_8) |
|----------------------------+------------+---------+------------|
| -                          | +          | +       | -          |
+----------------------------+------------+---------+------------+

다중 문자열 연산에서의 콜레이션 우선순위

둘 이상의 문자열에 대해 연산을 수행할 때, 서로 다른 문자열에 다른 콜레이션이 지정될 수 있어요. 적용할 콜레이션 결정은 각 입력에 콜레이션이 어떻게 지정되었는지와 각 지정자의 우선순위에 따라 달라져요.

세 가지 우선순위 수준이 있어요(높은 것부터 낮은 것까지):

함수(Function):

SQL 문에서 COLLATE 함수로 콜레이션을 지정.

컬럼(Column):

컬럼 정의에서 콜레이션을 지정.

없음(None):

주어진 표현식/컬럼에 콜레이션이 지정되지 않았거나, 빈 지정의 콜레이션이 사용됨(예: COLLATE(col1, '') 또는 col1 STRING COLLATE '').

사용할 콜레이션을 결정할 때 우선순위가 가장 높은 콜레이션 지정이 사용돼요. 같은 우선순위 수준으로 여러 콜레이션이 지정되면 그 값들이 비교되고, 같지 않으면 오류가 반환돼요.

예를 들어 다음 컬럼 수준 콜레이션 지정이 있는 테이블을 고려해요:

CREATE OR REPLACE TABLE collation_precedence_example(
  col1    VARCHAR,               -- equivalent to COLLATE ''
  col2_fr VARCHAR COLLATE 'fr',  -- French locale
  col3_de VARCHAR COLLATE 'de'   -- German locale
);

두 문자열을 비교하는 문에서 테이블이 사용되면 콜레이션은 다음과 같이 적용돼요.

• 이 비교는 col2_fr의 우선순위가 col1의 우선순위보다 높으므로 'fr' 콜레이션을 사용해요.

... WHERE col1 = col2_fr ...

• 이 비교는 문에 명시적으로 지정되어 col2_fr의 콜레이션보다 우선하므로 'en' 콜레이션을 사용해요.

... WHERE col1 COLLATE 'en' = col2_fr ...

• 이 비교는 같은 우선순위 수준에서 표현식들이 다른 콜레이션을 가지므로 오류를 반환해요.

... WHERE col2_fr = col3_de ...

• 이 비교는 col2_fr의 콜레이션이 제거되었으므로 'de' 콜레이션을 사용해요.

... WHERE col2_fr COLLATE '' = col3_de ...

• 이 비교는 같은 우선순위 수준에서 표현식들이 다른 콜레이션을 가지므로 오류를 반환해요.

... WHERE col2_fr COLLATE 'en' = col3_de COLLATE 'de' ...

명시적 콜레이션이 콜레이션 없음보다 우선순위가 높기 때문에, 빈 문자열을 지정하는 것(또는 콜레이션을 지정하지 않는 것)은 'utf8' 콜레이션을 명시적으로 지정하는 것과 다르다. 다음 코드 예제의 마지막 두 문이 그 차이를 보여 줘요.

예를 들어 다음 컬럼 수준 콜레이션 지정이 있는 테이블을 고려해요:

CREATE OR REPLACE TABLE collation_precedence_example2(
  s1 STRING COLLATE '',
  s2 STRING COLLATE 'utf8',
  s3 STRING COLLATE 'fr'
);

두 문자열을 비교하는 문에서 테이블이 사용되면 콜레이션은 다음과 같이 적용돼요.

• 이 비교는 s1에 콜레이션이 없고 'utf8'이 기본값이므로 'utf8'을 사용해요.

... WHERE s1 = 'a' ...

• 이 비교는 s1에 콜레이션이 없고 s2가 명시적 'utf8' 콜레이션을 가지므로 'utf8'을 사용해요.

... WHERE s1 = s2 ...

• 이 비교는 s1에 콜레이션이 없고 s3가 명시적 fr 콜레이션을 가지므로 명시적 콜레이션이 우선해 오류 없이 실행돼요.

... WHERE s1 = s3 ...

• 이 비교는 s2와 s3가 같은 우선순위 수준에서 다른 콜레이션을 지정하므로 오류를 일으켜요.

... WHERE s2 = s3 ...

002322 (42846): SQL compilation error: Incompatible collations: 'fr' and 'utf8'

내장 함수에서 콜레이션의 제한적 지원

콜레이션은 문자열 함수의 일부 부분집합에서만 지원돼요. 콜레이션을 구현할 것으로 합리적으로 기대될 수 있지만 아직 지원하지 않는 함수는 콜레이션과 함께 사용되면 오류를 반환해요. 이 오류 메시지는 COLLATE 함수를 호출할 때뿐 아니라, 컬럼을 만든 CREATE TABLE 또는 ALTER TABLE 문에서 콜레이션이 정의된 컬럼에 문자열 함수를 호출할 때도 표시돼요.

콜레이션을 지원하는 함수

다음 함수들은 콜레이션을 지원해요.

• [ NOT ] BETWEEN
• CASE
• CHARINDEX
• COALESCE
• CONCAT , ||
• CONTAINS
• DECODE
• ENDSWITH
• [ NOT ] EQUAL_NULL
• GREATEST
• IFF
• IFNULL
• [ NOT ] ILIKE
• ILIKE ANY (부분 지원)
• LEAST
• LEFT
• LENGTH, LEN (영향 없이 지원)
• [ NOT ] LIKE
• LIKE ALL (부분 지원)
• LIKE ANY (부분 지원)
• LISTAGG
• LPAD
• MAX
• MIN
• NULLIF
• NVL
• NVL2
• POSITION
• REPLACE
• RIGHT
• RPAD
• SPLIT
• SPLIT_PART
• STARTSWITH
• SUBSTR , SUBSTRING (영향 없이 지원)

이 중 일부 함수는 콜레이션 사용에 제한이 있어요. 자세한 내용은 각 함수의 문서를 참고해요.

이 목록은 시간이 지나면서 확장될 수 있어요.

주의

||(연결)과 LIKE 같은 일부 SQL 연산자와 술어는 함수로 구현되며(예: LIKE()와 CONCAT()로 함수로도 사용 가능), 술어나 연산자가 함수로 구현되고 그 함수가 콜레이션을 지원하지 않으면, 그 술어나 연산자도 콜레이션을 지원하지 않아요.

콜레이션 제한 사항도 참고해요.

콜레이션 사용의 성능 영향

콜레이션 사용은 다양한 데이터베이스 연산의 성능에 영향을 줄 수 있어요.

• 비교를 포함하는 연산이 더 느려질 수 있어요. 이는 단순 WHERE 절뿐 아니라 조인, 정렬, GROUP BY 연산 등에도 영향을 줄 수 있어요.
• WHERE 술어에서 일부 함수와 함께 사용되면 마이크로파티션 가지치기(pruning)가 덜 효율적일 수 있어요.
• 컬럼에 지정된 콜레이션과 다른 콜레이션을 WHERE 술어에 사용하면 가지치기 효율이 낮아지거나 가지치기가 완전히 제거될 수 있어요.

콜레이션 사용에 대한 추가 고려 사항

• 이름이 비슷함에도 불구하고 다음 콜레이션 함수는 서로 다른 결과를 반환한다는 점을 기억해요:
  • COLLATE는 사용할 콜레이션을 명시적으로 지정해요.
  • COLLATION은 명시적으로 지정되지 않았을 때 어떤 콜레이션이 사용되는지 보여 줘요.
• 콜레이션 지정이 있는 컬럼은 그 콜레이션의 로케일에서 오지 않은 문자를 사용할 수 있으며, 이는 정렬에 영향을 줄 수 있어요.

예를 들어 COLLATE 'en' 절로 컬럼을 만들면 컬럼의 데이터는 영어가 아닌 문자 É를 포함할 수 있어요. 이 상황에서 문자 É는 E 근처에 정렬돼요.

• 반드시 의미가 있는 것은 아닌 콜레이션 연산을 지정할 수 있어요.

예를 들어 폴란드어 데이터를 독일어 콜레이션을 사용해 프랑스어 데이터와 비교하도록 지정할 수 있어요.

SELECT ... WHERE COLLATE(French_column, 'de') = Polish_column;

그러나 Snowflake는 이 기능을 이런 방식으로 사용할 것을 권장하지 않아요. 예상치 못하거나 의도하지 않은 결과를 반환할 수 있기 때문이에요.

• 테이블 컬럼이 정의된 뒤에는 컬럼의 콜레이션을 변경할 수 없어요. 즉 CREATE TABLE 문으로 특정 콜레이션으로 컬럼을 만든 뒤에는 ALTER TABLE로 콜레이션을 변경할 수 없어요.

그러나 그 컬럼을 참조하는SELECT 문 같은 DML 문에서 다른 콜레이션을 지정할 수는 있어요.

• CREATE VIEW 명령으로 뷰를 만들면 뷰의 컬럼은 소스 테이블 컬럼의 콜레이션 지정을 상속해요.

Iceberg 테이블에서의 콜레이션 지원

콜레이션은 Snowflake 관리 Iceberg 테이블에서 지원돼요. COLLATE 절, COLLATE 함수, 콜레이션된 문자열 컬럼을 사용할 수 있어요. 필터링, 조인, 집계, 그리고 DESC TABLE과 GET_DDL() 같은 연산은 콜레이션된 컬럼의 콜레이션을 존중해요.

콜레이션은 Snowflake 특정 확장이에요. Iceberg 테이블 형식은 현재 사양에 콜레이션을 포함하지 않으므로, 콜레이션 메타데이터는 Snowflake에만 저장돼요. 같은 Iceberg 테이블을 읽는 다른 쿼리 엔진은 콜레이션을 인식하지 못해요.

콜레이션은 Snowflake 관리 Iceberg 테이블에만 적용돼요. 외부 관리 Iceberg 테이블이나 카탈로그 연결 데이터베이스의 테이블에서는 지원되지 않아요.

Iceberg 테이블의 기본 콜레이션

Snowflake 관리 Iceberg 테이블의 새 문자열 컬럼에 대한 기본 콜레이션을 설정하려면 ICEBERG_DEFAULT_DDL_COLLATION 파라미터를 사용해요. 이 파라미터는 계정, 데이터베이스, 스키마 또는 테이블 수준에서 설정할 수 있어요.

제한 사항

Snowflake 관리 Iceberg 테이블에서 콜레이션을 사용할 때 다음 제한 사항을 고려해요.

• 콜레이션된 컬럼에는 파티션 변환(partition transforms)을 사용할 수 없어요.
• 콜레이션된 컬럼이 있는 Snowflake 관리 Iceberg 테이블을 외부 관리 Iceberg 테이블로 변환할 수 없어요.
• Snowflake는 현재 콜레이션된 컬럼에 대한 lower·upper 경계를 Iceberg 매니페스트 파일에 쓰지 않아요.

ci와 upper / lower 사이의 차이

upper와 lower 콜레이션 지정은 문자열 비교와 정렬 중 ci 콜레이션 지정보다 더 나은 성능을 제공할 수 있어요. 그러나 upper와 lower는 ci와 약간 다른 효과를 가지는데, 다음 섹션들에서 설명해요.

• 너비(width), 공백, 스크립트 비교의 차이
• 무시 가능한(ignorable) 코드 포인트 처리의 차이
• 다른 코드 포인트로 표현되는 문자의 차이
• 단일 문자를 나타내는 코드 포인트 시퀀스의 차이
• 대소문자 변경이 여러 코드 포인트를 초래하는 경우의 차이
• 정렬 순서의 차이

너비, 공백, 스크립트 비교의 차이

문자열 비교 중 ci 콜레이션 지정은 문자의 서로 다른 시각적 표현이 여전히 같은 문자를 가리킬 수 있음을 인식하고 그에 따라 취급해요. 더 빠른 비교를 허용하기 위해 upper와 lower 콜레이션 지정은 문자의 서로 다른 시각적 표현을 같은 문자로 인식하지 않아요.

구체적으로 ci 콜레이션 지정은 다음 범주의 일부 차이를 무시하지만, upper와 lower 콜레이션 지정은 이들을 무시하지 않아요.

• 문자 너비
• 공백 유형
• 문자 스크립트

다음 섹션들은 이러한 차이를 보여 주는 예제를 포함해요.

Note

전각(full-width)·반각(half-width) 문자의 비교 동작은 로케일에 따라 달라질 수 있어요.

다른 너비의 문자 비교 예제

different_widths라는 테이블을 만들고 다른 너비의 문자를 포함하는 행을 삽입해요.

CREATE OR REPLACE TABLE different_widths(codepoint STRING, description STRING);

INSERT INTO different_widths VALUES
  ('a', 'ASCII a'),
  ('A', 'ASCII A'),
  ('ａ', 'Full-width a'),
  ('Ａ', 'Full-width A');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_widths;

+-------------+--------------------------+--------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION  |
|-------------+--------------------------+--------------|
| a           | U+0061                   | ASCII a      |
| A           | U+0041                   | ASCII A      |
| ａ          | U+FF41                   | Full-width a |
| Ａ          | U+FF21                   | Full-width A |
+-------------+--------------------------+--------------+

다음 쿼리는 ci 콜레이션 지정이 문자를 비교할 때 하나의 서로 다른 값을 찾음을 보여 줘요. upper와 lower 콜레이션 지정은 문자를 비교할 때 두 개의 서로 다른 값을 찾아요.

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_widths;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       4 |                  4 |                       1 |                        2 |                        2 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

ci 콜레이션 지정은 너비와 대소문자의 차이를 모두 무시하므로, 문자 사이에 차이가 없다고 봐요. upper와 lower 콜레이션 지정은 대소문자의 차이만 무시하므로, 반각 문자는 전각 문자와 다른 문자로 간주돼요.

반각 소문자 a는 반각 대문자 A와 같은 것으로 간주되고, 전각 소문자 a는 전각 대문자 A와 같은 것으로 간주돼요. 따라서 upper와 lower 콜레이션 지정은 두 개의 서로 다른 값을 찾아요.

다른 유형의 공백 비교 예제

different_whitespaces라는 테이블을 만들고 다른 유형의 공백이 있는 행을 삽입해요.

CREATE OR REPLACE TABLE different_whitespaces(codepoint STRING, description STRING);

INSERT INTO different_whitespaces VALUES
  (' ', 'ASCII space'),
  ('\u00A0', 'Non-breaking space'),
  (' ', 'Ogham space mark'),
  (' ', 'en space'),
  (' ', 'em space');

SELECT codepoint visual_char,
       'U+'  || TO_CHAR(unicode(codepoint), '0XXX')
       codepoint_representation, description
  FROM different_whitespaces;

+-------------+--------------------------+--------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION        |
|-------------+--------------------------+--------------------|
|             | U+0020                   | ASCII space        |
|             | U+00A0                   | Non-breaking space |
|             | U+1680                   | Ogham space mark   |
|             | U+2002                   | en space           |
|             | U+2003                   | em space           |
+-------------+--------------------------+--------------------+

다음 쿼리는 ci 콜레이션 지정이 공백을 비교할 때 하나의 서로 다른 값을 찾음을 보여 줘요. 즉 공백 사이에 차이가 없어요. upper와 lower 콜레이션 지정은 공백을 비교할 때 다섯 개의 서로 다른 값을 찾아, 모두 다르다는 뜻이에요.

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) NumDistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_whitespaces;

+---------+-----------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | NUMDISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+-----------------------+-------------------------+--------------------------+--------------------------|
|       5 |                     5 |                       1 |                        5 |                        5 |
+---------+-----------------------+-------------------------+--------------------------+--------------------------+

다른 스크립트의 문자 비교 예제

different_scripts라는 테이블을 만들고 다른 스크립트를 사용하는 문자를 포함하는 행을 삽입해요.

CREATE OR REPLACE TABLE different_scripts(codepoint STRING, description STRING);

INSERT INTO different_scripts VALUES
  ('1', 'ASCII digit 1'),
  ('¹', 'Superscript 1'),
  ('₁', 'Subscript 1'),
  ('①', 'Circled digit 1'),
  ('੧', 'Gurmukhi digit 1'),
  ('௧', 'Tamil digit 1');

SELECT codepoint VISUAL_CHAR,
       'U+'  || TO_CHAR(UNICODE(codepoint), '0XXX') codepoint_representation,
       description
  FROM different_scripts;

+-------------+--------------------------+------------------+
| VISUAL_CHAR | CODEPOINT_REPRESENTATION | DESCRIPTION      |
|-------------+--------------------------+------------------|
| 1           | U+0031                   | ASCII digit 1    |
| ¹           | U+00B9                   | Superscript 1    |
| ₁           | U+2081                   | Subscript 1      |
| ①           | U+2460                   | Circled digit 1  |
| ੧           | U+0A67                   | Gurmukhi digit 1 |
| ௧           | U+0BE7                   | Tamil digit 1    |
+-------------+--------------------------+------------------+

다음 쿼리는 ci 콜레이션 지정이 문자를 비교할 때 하나의 서로 다른 값을 찾음을 보여 줘요. 즉 문자 사이에 차이가 없어요. upper와 lower 콜레이션 지정은 문자를 비교할 때 여섯 개의 서로 다른 값을 찾아, 모두 다르다는 뜻이에요.

SELECT COUNT(*) NumRows,
       COUNT(DISTINCT UNICODE(codepoint)) DistinctCodepoints,
       COUNT(DISTINCT codepoint COLLATE 'en-ci') DistinctCodepoints_EnCi,
       COUNT(DISTINCT codepoint COLLATE 'upper') DistinctCodepoints_Upper,
       COUNT(DISTINCT codepoint COLLATE 'lower') DistinctCodepoints_Lower
  FROM different_scripts;

+---------+--------------------+-------------------------+--------------------------+--------------------------+
| NUMROWS | DISTINCTCODEPOINTS | DISTINCTCODEPOINTS_ENCI | DISTINCTCODEPOINTS_UPPER | DISTINCTCODEPOINTS_LOWER |
|---------+--------------------+-------------------------+--------------------------+--------------------------|
|       6 |                  6 |                       1 |                        6 |                        6 |
+---------+--------------------+-------------------------+--------------------------+--------------------------+

무시 가능한 코드 포인트 처리의 차이

Unicode Collation Algorithm은 콜레이션 요소(코드 포인트)가 무시 가능(ignorable)할 수 있다고 지정해요. 즉 문자열 비교와 정렬 중 코드 포인트가 고려되지 않는다는 뜻이에요.

• ci 콜레이션 지정에서는 이러한 코드 포인트가 무시돼요. 이로 인해 무시 가능한 코드 포인트를 검색하거나 교체하기가 어려울 수 있어요.
• upper와 lower 콜레이션 지정에서는 이러한 코드 포인트가 무시되지 않아요.

예를 들어 코드 포인트 U+0001은 무시 가능해요. 이 코드 포인트를 en-ci 콜레이션 지정으로 빈 문자열과 비교하면 U+0001이 무시되므로 결과는 TRUE예요.

SELECT '\u0001' = '' COLLATE 'en-ci';

+-------------------------------+
| '\U0001' = '' COLLATE 'EN-CI' |
|-------------------------------|
| True                          |
+-------------------------------+

반면 upper 또는 lower 콜레이션 지정을 사용하면 U+0001이 무시되지 않으므로 결과는 FALSE예요.

SELECT '\u0001' = '' COLLATE 'upper';

+-------------------------------+
| '\U0001' = '' COLLATE 'UPPER' |
|-------------------------------|
| False                         |
+-------------------------------+

마찬가지로 REPLACE 함수를 호출해 이 코드 포인트를 문자열에서 제거한다고 가정해요. en-ci 콜레이션 지정을 사용하면 U+0001이 무시되므로 함수는 코드 포인트를 제거하지 않아요.

다음 예제에서 보듯이 REPLACE 함수가 반환하는 문자열은 함수에 전달된 문자열과 같은 길이인데, 함수가 U+0001 문자를 제거하지 않기 때문이에요.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'en-ci', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        4 |
+-----------------+--------------------------+

반면 upper 또는 lower 콜레이션 지정을 사용하면 함수가 코드 포인트를 문자열에서 제거해 더 짧은 문자열을 반환해요.

SELECT
  LEN('abc\u0001') AS original_length,
  LEN(REPLACE('abc\u0001' COLLATE 'upper', '\u0001')) AS length_after_replacement;

+-----------------+--------------------------+
| ORIGINAL_LENGTH | LENGTH_AFTER_REPLACEMENT |
|-----------------+--------------------------|
|               4 |                        3 |
+-----------------+--------------------------+

다른 코드 포인트로 표현되는 문자의 차이

유니코드에서는 다른 코드 포인트 시퀀스가 같은 문자를 표현할 수 있어요. 예를 들어 Dialytika와 Tonos가 있는 그리스 소문자 Iota는 코드 포인트 U+0390의 조합(precomposed) 문자로 표현하거나, 분해된 문자의 U+03b9 U+0308 U+0301 코드 포인트 시퀀스로 표현할 수 있어요.

ci 콜레이션 지정을 사용하면 문자의 서로 다른 코드 포인트 시퀀스가 같은 문자로 취급돼요. 예를 들어 코드 포인트 U+0390과 코드 포인트 시퀀스 U+03b9 U+0308 U+0301은 동등하게 취급돼요.

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'en-ci';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'EN-CI' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

upper와 lower 콜레이션 지정의 성능을 개선하기 위해 시퀀스는 같은 방식으로 처리되지 않아요. 두 코드 포인트 시퀀스는 대문자나 소문자로 변환된 뒤 같은 이진 표현을 만들 때만 동등한 것으로 간주돼요.

예를 들어 U+0390 코드 포인트와 U+03b9 U+0308 U+0301 코드 포인트 시퀀스에 upper 지정을 사용하면 같은 문자로 취급되는 결과가 돼요.

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'upper';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'UPPER' |
|-------------------------------------------------|
| True                                            |
+-------------------------------------------------+

lower 지정을 사용하면 같지 않은 문자로 취급되는 결과가 돼요.

SELECT '\u03b9\u0308\u0301' = '\u0390' COLLATE 'lower';

+-------------------------------------------------+
| '\U03B9\U0308\U0301' = '\U0390' COLLATE 'LOWER' |
|-------------------------------------------------|
| False                                           |
+-------------------------------------------------+

이 차이는 100개가 넘는 복합 소문자 코드 포인트에 비해 합성 대문자 코드 포인트가 하나(U+0130)뿐이므로 lower보다 upper를 사용할 때 발생할 가능성이 더 낮아요.

단일 문자를 나타내는 코드 포인트 시퀀스의 차이

코드 포인트 시퀀스가 단일 문자를 나타내는 경우, ci 콜레이션 지정은 시퀀스가 단일 문자를 나타냄을 인식하고 시퀀스의 개별 코드 포인트를 일치시키지 않아요.

예를 들어 코드 포인트 시퀀스 U+03b9 U+0308 U+0301은 단일 문자(Dialytika와 Tonos가 있는 그리스 소문자 Iota)를 나타내요. U+0308과 U+0301은 U+03b9에 적용된 악센트를 나타내요.

ci 콜레이션 지정의 경우 CONTAINS 함수로 시퀀스 U+03b9 U+0308이 U+03b9나 U+0308을 포함하는지 확인하면, 시퀀스 U+03b9 U+0308이 단일 문자로 취급되므로 함수는 FALSE를 반환해요.

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'en-ci');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'EN-CI') |
|----------------------------------------------------|
| False                                              |
+----------------------------------------------------+

성능을 개선하기 위해 upper와 lower 지정은 이러한 시퀀스를 단일 문자로 취급하지 않아요. 위 예제에서 CONTAINS 함수는 TRUE를 반환하는데, 이 지정들이 코드 포인트 시퀀스를 별개의 문자로 취급하기 때문이에요.

SELECT CONTAINS('\u03b9\u0308', '\u03b9' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U03B9' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

SELECT CONTAINS('\u03b9\u0308', '\u0308' COLLATE 'upper');

+----------------------------------------------------+
| CONTAINS('\U03B9\U0308', '\U0308' COLLATE 'UPPER') |
|----------------------------------------------------|
| True                                               |
+----------------------------------------------------+

대소문자 변경이 여러 코드 포인트를 초래하는 경우의 차이

일부 합성 문자는 대문자 또는 소문자 버전이 코드 포인트 시퀀스로 표현돼요. 예를 들어 독일어 문자 ß의 대문자는 두 개의 S 문자 시퀀스(SS)예요.

ß와 SS가 동등함에도 불구하고, upper 콜레이션 지정을 사용하면 ß와 SS의 검색이 다른 결과를 반환해요. 대소문자 변환으로 생성된 시퀀스는 전체가 일치하거나 전혀 일치하지 않아요.

SELECT CONTAINS('ß' , 's' COLLATE 'upper');

+--------------------------------------+
| CONTAINS('SS' , 'S' COLLATE 'UPPER') |
|--------------------------------------|
| False                                |
+--------------------------------------+

SELECT CONTAINS('ss', 's' COLLATE 'upper');

+-------------------------------------+
| CONTAINS('SS', 'S' COLLATE 'UPPER') |
|-------------------------------------|
| True                                |
+-------------------------------------+

정렬 순서의 차이

upper와 lower 콜레이션 지정의 정렬은 ci 지정의 정렬과 다르게 동작해요.

• ci 지정을 사용하면 문자열이 콜레이션 키(collation key)로 정렬돼요. 일반적으로 콜레이션 키는 대소문자 구분, 악센트 구분, 로케일 등을 설명할 수 있어요.
• upper와 lower 지정을 사용하면 성능을 개선하기 위해 문자열이 코드 포인트로 정렬돼요.

예를 들어 ASCII 범위 내의 일부 문자(+와 - 같은)는 다르게 정렬돼요.

SELECT '+' < '-' COLLATE 'en-ci';

+---------------------------+
| '+' < '-' COLLATE 'EN-CI '|
|---------------------------|
| False                     |
+---------------------------+

SELECT '+' < '-' COLLATE 'upper';

+---------------------------+
| '+' < '-' COLLATE 'UPPER' |
|---------------------------|
| True                      |
+---------------------------+

또 다른 예로, 무시된 코드 포인트가 있는 문자열은 다른 순서로 정렬돼요.

SELECT 'a\u0001b' < 'ab' COLLATE 'en-ci';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'EN-CI' |
|-----------------------------------|
| False                             |
+-----------------------------------+

SELECT 'a\u0001b' < 'ab' COLLATE 'upper';

+-----------------------------------+
| 'A\U0001B' < 'AB' COLLATE 'UPPER' |
|-----------------------------------|
| True                              |
+-----------------------------------+

또한 이모지도 다르게 정렬돼요.

SELECT 'abc' < '❄' COLLATE 'en-ci';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'EN-CI' |
|-----------------------------|
| False                       |
+-----------------------------+

SELECT 'abc' < '❄' COLLATE 'upper';

+-----------------------------+
| 'ABC' < '❄' COLLATE 'UPPER' |
|-----------------------------|
| True                        |
+-----------------------------+

콜레이션 제한 사항

콜레이션에는 다음 제한 사항이 적용돼요.

• 콜레이션은 최대 64MB의 문자열에서만 지원돼요.
• UDF에서는 콜레이션이 지원되지 않아요.
• VARIANT, ARRAY, OBJECT 값의 문자열에서는 콜레이션이 지원되지 않아요.
• Clean room은 기본 콜레이션만 지원해요.

콜레이션은 최대 64MB의 문자열에서만 지원

Snowflake VARCHAR 데이터 타입은 최대 128MB의 문자열을 지원하지만, Snowflake는 결과 문자열이 64MB 이하일 때만 콜레이션을 지원해요. (일부 콜레이션 연산은 문자열을 길게 만들 수 있어요.)

UDF에서는 콜레이션이 지원되지 않음

Snowflake는 UDF(사용자 정의 함수)에서 콜레이션을 지원하지 않아요.

• UDF에서 콜레이션된 문자열 값을 반환할 수 없어요. 서버는 실제 반환 타입이 선언된 반환 타입과 호환되지 않는다고 보고해요.
• 콜레이션된 문자열 값을 UDF에 전달하면 콜레이션 정보가 전달되지 않아요. UDF는 문자열을 콜레이션되지 않은 문자열로 봐요.

VARIANT, ARRAY, OBJECT 값의 문자열에서는 콜레이션이 지원되지 않음

VARIANT, OBJECT, ARRAY 값 안에 저장된 문자열은 콜레이션 지정을 포함하지 않아요. 따라서:

• 이 값들의 비교는 항상 'utf8' 콜레이션을 사용해요.
• 콜레이션 지정이 있는 VARCHAR 값으로 ARRAY, OBJECT, VARIANT 값을 구성할 때 콜레이션 지정이 보존되지 않아요.
• ARRAY, OBJECT, VARIANT 안에 저장된 값을 추출하고 VARCHAR로 캐스트한 뒤 콜레이션 지정을 추가해 비교할 수는 있어요. 예를 들어:

COLLATE(VARIANT_COL:fld1::VARCHAR, 'en-ci') = VARIANT_COL:fld2::VARCHAR

Clean room은 기본 콜레이션만 지원

Clean room은 계정 수준에서 기본 콜레이션만 지원해요. SHOW PARAMETERS LIKE 'DEFAULT_DDL_COLLATION' IN ACCOUNT;를 실행해 확인할 수 있어요.

콜레이션 예제

다음 문은 각 컬럼에 다른 콜레이션을 사용하는 테이블을 만들어요.

CREATE OR REPLACE TABLE collation_demo (
  uncollated_phrase VARCHAR,
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en',
  spanish_phrase VARCHAR COLLATE 'es');

INSERT INTO collation_demo (
      uncollated_phrase,
      utf8_phrase,
      english_phrase,
      spanish_phrase)
   VALUES (
     'pinata',
     'pinata',
     'pinata',
     'piñata');

Note

콜레이션은 저장할 수 있는 문자 집합에 영향을 주지 않아요. Snowflake는 모든 UTF-8 문자를 지원해요.

테이블에 대한 다음 쿼리는 기대되는 값을 보여 줘요.

SELECT * FROM collation_demo;

+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------|
| pinata            | pinata      | pinata         | piñata         |
+-------------------+-------------+----------------+----------------+

다음 쿼리는 문자 ñ이 n과 일치하지 않으므로 일치를 찾지 못해요.

SELECT * FROM collation_demo WHERE spanish_phrase = uncollated_phrase;

+-------------------+-------------+----------------+----------------+
| UNCOLLATED_PHRASE | UTF8_PHRASE | ENGLISH_PHRASE | SPANISH_PHRASE |
|-------------------+-------------+----------------+----------------+
+-------------------+-------------+----------------+----------------+

콜레이션을 변경해도 관련 있지만 같지 않은 문자(예: ñ과 n)가 같은 것으로 취급되도록 강제하지 않아요.

CREATE OR REPLACE TABLE collation_demo1 (
  uncollated_phrase VARCHAR,
  utf8_phrase VARCHAR COLLATE 'utf8',
  english_phrase VARCHAR COLLATE 'en-ai',
  spanish_phrase VARCHAR COLLATE 'es-ai');

INSERT INTO collation_demo1 (
    uncollated_phrase,
    utf8_phrase,
    english_phrase,
    spanish_phrase)
  VALUES (
    'piñata',
    'piñata',
    'piñata',
    'piñata');

SELECT uncollated_phrase = 'pinata',
       utf8_phrase = 'pinata',
       english_phrase = 'pinata',
       spanish_phrase = 'pinata'
  FROM collation_demo1;

+------------------------------+------------------------+---------------------------+---------------------------+
| UNCOLLATED_PHRASE = 'PINATA' | UTF8_PHRASE = 'PINATA' | ENGLISH_PHRASE = 'PINATA' | SPANISH_PHRASE = 'PINATA' |
|------------------------------+------------------------+---------------------------+---------------------------|
| False                        | False                  | True                      | False                     |
+------------------------------+------------------------+---------------------------+---------------------------+

영어 문구만 True를 반환하는 이유는 다음과 같아요.

• 콜레이션되지 않은 비교는 악센트를 무시하지 않아요.
• utf8 콜레이션 비교는 악센트를 무시하지 않아요.
• en-ai와 es-ai 콜레이션 비교는 악센트를 무시하지만, 스페인어에서 ñ은 악센트가 있는 n이 아니라 개별 문자로 취급돼요.

다음 예제들은 정렬 순서에 대한 콜레이션의 효과를 보여 줘요.

INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');

SELECT spanish_phrase FROM collation_demo
  ORDER BY spanish_phrase;

+------------------+
| SPANISH_PHRASE   |
|------------------|
| Pinatubo (Mount) |
| pint             |
| Pinta            |
| piña colada      |
| piñata           |
+------------------+

다음 쿼리는 콜레이션을 'es'(스페인어)에서 'utf8'으로 변경해 값을 다른 순서로 반환해요.

SELECT spanish_phrase FROM collation_demo
  ORDER BY COLLATE(spanish_phrase, 'utf8');

+------------------+
| SPANISH_PHRASE   |
|------------------|
| Pinatubo (Mount) |
| Pinta            |
| pint             |
| piña colada      |
| piñata           |
+------------------+

이 예제는 COLLATION 함수를 사용해 컬럼 같은 표현식의 콜레이션을 보는 방법을 보여 줘요.

CREATE OR REPLACE TABLE collation_demo2 (
  c1 VARCHAR COLLATE 'fr',
  c2 VARCHAR COLLATE '');

INSERT INTO collation_demo2 (c1, c2) VALUES
  ('a', 'a'),
  ('b', 'b');

SELECT DISTINCT COLLATION(c1), COLLATION(c2) FROM collation_demo2;

+---------------+---------------+
| COLLATION(C1) | COLLATION(C2) |
|---------------+---------------|
| fr            | NULL          |
+---------------+---------------+

DESCRIBE TABLE을 사용해 테이블 컬럼의 콜레이션 정보를 볼 수도 있어요.

DESC TABLE collation_demo2;

+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+
| name | type                           | kind   | null? | default | primary key | unique key | check | expression | comment | policy name | privacy domain |
|------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------|
| C1   | VARCHAR(16777216) COLLATE 'fr' | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
| C2   | VARCHAR(16777216)              | COLUMN | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    | NULL        | NULL           |
+------+--------------------------------+--------+-------+---------+-------------+------------+-------+------------+---------+-------------+----------------+

더 알아보기 (Learn more)

• Snowflake가 지원하는 콜레이션 로케일 — 지원되는 로케일 전체 목록
• 문자열 데이터 타입 — 텍스트형 타입
• COLLATE 함수 — 콜레이션 적용 함수
• COLLATION 함수 — 콜레이션 조회 함수