사용자 정의 타입

사용자 정의 타입 (User-defined types)

Snowflake에서 기존 데이터 타입을 기반으로 정의하는 사용자 정의 타입(user-defined types)을 다루는 문서예요. 사용자 정의 타입은 스키마 수준 객체로, 열 정의·함수·프로시저 정의·캐스트 표현식 등 타입을 사용할 수 있는 모든 곳에서 사용돼요. 필요한 권한, 일반 사용 참고 사항, 캐스팅과 예제를 확인할 수 있어요.

출처: Snowflake SQL Reference

본문

기존 Snowflake 데이터 타입을 기반으로 하는 새로운 데이터 타입인 사용자 정의 타입(user-defined types) 을 정의할 수 있어요. 예를 들어 사람의 나이를 위한 열을 정의하고, 소수점 이하 자릿수 없이 최대 세 자리 숫자만 포함하도록 값을 제한하고 싶다고 가정해 봐요. NUMBER(3,0)에 해당하는 age라는 데이터 타입을 정의할 수 있어요.

사용자 정의 타입은 스키마 수준 객체로, 열 정의, 함수·프로시저 정의, 캐스트 표현식을 포함해 타입을 사용할 수 있는 모든 곳에서 사용될 수 있어요.

사용자 정의 타입은 스키마 유지 관리를 단순화하고 데이터 품질을 개선할 수 있어요. 사용자 정의 타입을 한 번 정의하고 여러 객체에서 사용할 수 있어요.

또한 사용자 정의 타입을 사용해 관련 데이터 필드를 여러 열이나 테이블을 쓰는 대신 단일 논리 열로 묶을 수도 있어요. 예를 들어 거리 주소, 도시, 주, 우편번호 필드를 가진 정형 OBJECT 타입인 주소용 데이터 타입을 정의할 수 있어요.

사용자 정의 타입에 필요한 권한 (Privileges required for user-defined types)

스키마에서 사용자 정의 타입을 만들려면 그 스키마에 대해 CREATE TYPE 권한이 부여된 역할을 사용해야 해요.

자세한 내용은 사용자 정의 타입의 접근 제어 요구 사항을 참조하세요.

사용자 정의 타입에 대한 일반 사용 참고 사항 (General usage notes for user-defined types)

  • 사용자 정의 타입의 정의를 변경하려면, 그것을 drop하고 다시 만들어요.

사용자 정의 타입의 정의를 변경하면:

  • 그 타입을 사용하는 테이블 열에 대해 직접 연산하는 SQL 문(SELECT 문과 DML 문 포함)이 오류를 반환할 수 있어요. 그러나 그 타입을 사용하는 테이블 열에 직접 연산하지 않는 SQL 문은 정상적으로 실행돼요. 예를 들어 테이블에 typed_column이라는 사용자 정의 타입 열이 있고, SELECT 문이 SELECT 목록에서 다른 열을 지정한다면 SELECT 문은 정상적으로 실행돼요. 문제를 해결하려면 기본 Snowflake 타입을 사용하도록 SQL 문을 수정할 수 있어요.

  • 그 타입을 사용하는 함수와 저장 프로시저에 대한 호출은 오류를 반환해요. 문제를 해결하려면 함수와 저장 프로시저를 drop하고 다시 만들어요.

  • ALTER TABLE … ALTER COLUMN 명령은 열의 데이터 타입을 사용자 정의 타입에서 호환되는 Snowflake 데이터 타입으로, 또는 Snowflake 데이터 타입에서 사용자 정의 타입으로 변경할 수 있어요.

  • OBJECT_CONSTRUCT 함수나 OBJECT 상수를 사용해 사용자 정의 타입 열에 삽입할 객체를 만들 때는 결과를 사용자 정의 타입으로 캐스팅해요.

예제는 테이블 열에 사용자 정의 타입 사용하기를 참조하세요.

  • 집합 연산자(set operators)(예: UNION, INTERSECT, EXCEPT)나 조건부 표현식 함수(conditional expression functions)(예: CASE, IFF, COALESCE, NVL 등)가 사용자 정의 타입의 값으로 해석되는 표현식을 평가할 때, Snowflake는 피연산자의 기본 타입을 사용해 공통 타입을 결정해요. 기본적으로 결과의 데이터 타입은 이 기본 타입이에요. 결과가 사용자 정의 타입의 값이 되도록 하려면 최종 표현식을 사용자 정의 타입으로 명시적으로 캐스팅해요.

사용자 정의 타입이 집합 연산이나 조건부 표현식 함수에 사용될 때 다음 규칙이 적용돼요.

  • 사용자 정의 타입은 기본 타입과 구별되지만, 표현식 타입 해석에서는 공통 타입을 찾기 위해 기본 타입으로 강제 변환(coerce)돼요.
  • 분기나 피연산자가 단일 Snowflake 타입(예: VARCHAR 또는 NUMBER)으로 해석되면 그것이 결과 타입이 돼요.
  • 사용자 정의 타입을 보존하거나 사용자 정의 타입의 값인 결과를 만들려면 CAST(_expr_ AS {user-defined type}) 또는 _expr_ ::{user-defined type}로 전체 표현식을 캐스팅해요.
  • 호환되지 않는 기본 타입(예: VARCHAR와 NUMBER)은 일반 강제 변환 규칙(coercion rules)을 따라요. 공통 기본 타입이 없으면 오류가 반환돼요.

예제는 사용자 정의 타입과 함께 집합 연산자 및 조건부 표현식 함수 사용하기를 참조하세요.

  • 함수 오버로딩(overloading)에 사용자 정의 타입과 호환되는 Snowflake 데이터 타입을 함께 사용하는 것은 허용돼요. 즉 함수 인자 타입에 사용자 정의 타입을 지정할 수 있고, 같은 이름의 함수 인자 타입에 호환되는 Snowflake 데이터 타입을 지정할 수 있어요.

  • SQL 사용자 정의 함수(UDF) 또는 Snowflake Scripting 저장 프로시저의 RETURN 타입으로 사용자 정의 타입이 지정되면, 반환 값은 UDF나 저장 프로시저 본문에서 사용자 정의 타입으로 명시적으로 캐스팅되어야 해요.

  • SQL이 아닌 다른 언어(Python 또는 Java 등)로 작성된 UDF나 프로시저의 인자나 반환 값으로 사용자 정의 타입이 사용되면, 사용자 정의 타입은 기본 타입과 동일하게 취급돼요.

  • 스키마 진화(Schema evolution)는 사용자 정의 타입에서 지원되지 않아요.

사용자 정의 타입 캐스팅 (Casting user-defined types)

사용자 정의 타입은 데이터 타입 변환을 지원하며, 명시적 캐스팅과 암시적 캐스팅(강제 변환)을 모두 포함해요.

  • 사용자 정의 타입으로/으로부터의 명시적 캐스팅
  • 사용자 정의 타입의 강제 변환

사용자 정의 타입으로/으로부터의 명시적 캐스팅 (Explicit casting to and from user-defined types)

사용자 정의 타입 값은 기본 타입의 값과 동일한 데이터 타입으로 캐스팅될 수 있어요. 예를 들어 NUMBER 타입을 기반으로 하는 age라는 사용자 정의 타입을 만들어요.

CREATE TYPE age AS NUMBER(3,0);

값이 사용자 정의 타입의 기본 타입으로 캐스팅될 수 있으면 그 값을 사용자 정의 타입으로 캐스팅할 수 있어요. 예를 들어 값 10은 NUMBER 타입으로 캐스팅될 수 있으므로, 그 값을 age 타입으로 캐스팅할 수 있어요.

SELECT 10::age;

사용자 정의 타입의 기본 타입이 어떤 데이터 타입으로 캐스팅될 수 있으면, 사용자 정의 타입 값도 그 데이터 타입으로 캐스팅될 수 있어요. 예를 들어 NUMBER 값은 VARCHAR 타입으로 캐스팅될 수 있으므로, 사용자 정의 타입 age의 값 10은 VARCHAR 타입으로 캐스팅될 수 있어요.

SELECT 10::age::VARCHAR;

사용자 정의 타입의 강제 변환 (Coercion of user-defined types)

사용자 정의 타입 값은 기본 타입으로 강제 변환돼요. 따라서 모든 연산에서 기본 타입과 동일하게 동작해요. 예를 들어 NUMBER 타입을 기반으로 하는 age라는 사용자 정의 타입과, age 타입의 두 열을 가진 테이블을 만들어요.

CREATE TYPE age AS NUMBER(3,0);

CREATE TABLE test_age_udf(a age, b age);

테이블에 값을 삽입해요.

INSERT INTO test_age_udf VALUES (10, 20);

다음 예제는 테이블 값에 덧셈 연산을 수행하며, Snowflake는 연산을 완료하기 위해 age 값을 NUMBER 타입의 값으로 강제 변환해요. 이 예제는 SYSTEM$TYPEOF 함수를 사용해 결과의 데이터 타입을 보여줘요.

SELECT a + b AS result,
       SYSTEM$TYPEOF(a + b) AS type
  FROM test_age_udf;

+--------+------------------+
| RESULT | TYPE             |
|--------+------------------|
|     30 | NUMBER(4,0)[SB1] |
+--------+------------------+

사용자 정의 데이터 타입 예제 (Examples for user-defined data types)

다음 예제는 사용자 정의 타입을 사용하는 방법을 보여줘요.

  • 테이블 열에 사용자 정의 타입 사용하기
  • 사용자 정의 타입과 함께 집합 연산자 및 조건부 표현식 함수 사용하기

테이블 열에 사용자 정의 타입 사용하기 (Using a user-defined type for a table column)

다음 예제에서 address라는 사용자 정의 타입을 만들고, 테이블에서 그 타입을 사용해요.

  1. 주소 정보를 저장하기 위해 정형 OBJECT 타입을 기반으로 하는 사용자 정의 타입을 만들어요.
CREATE TYPE address AS OBJECT(
  street VARCHAR(100),
  city VARCHAR(50),
  state_abbr CHAR(2),
  zip_code CHAR(10)
);
  1. 주소를 포함한 고객 정보를 저장하는 테이블을 만들어요.
CREATE TABLE customers_udt_test (
  cust_id VARCHAR NOT NULL,
  cust_name VARCHAR(100),
  cust_address address
);
  1. 테이블에 행을 삽입하고, OBJECT 상수를 address 타입으로 캐스팅해 cust_address 열의 값을 지정해요.
INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
 '1000',
 'Example1 Inc',
 {
   'street': '101 Snow Street',
   'city': 'San Francisco',
   'state_abbr': 'CA',
   'zip_code': '94102'
 }::address;
  1. 테이블에 행을 삽입하고, OBJECT_CONSTRUCT 함수를 호출하고 반환 값을 address 타입으로 캐스팅해 cust_address 열의 값을 지정해요.
INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
 '1001',
 'Example2 Inc',
 OBJECT_CONSTRUCT(
   'street', '555 Polar Bear Street',
   'city', 'New York',
   'state_abbr', 'NY',
   'zip_code', '10001'
 )::address;
  1. 테이블에 행을 삽입하고, OBJECT 상수를 address 타입의 기본 타입인 OBJECT 타입으로 캐스팅해 cust_address 열의 값을 지정해요. 보통은 OBJECT 상수를 사용자 정의 타입으로 캐스팅하는 것이 더 쉽지만, 이 예제는 OBJECT 상수가 사용자 정의 타입으로 강제 변환된다는 것을 보여줘요.
INSERT INTO customers_udt_test (cust_id, cust_name, cust_address)
  SELECT
 '1002',
 'Example3 Inc',
 {
   'street': '909 Flake Street',
   'city': 'Seattle',
   'state_abbr': 'WA',
   'zip_code': '98109'
 }::OBJECT(
      street VARCHAR(100),
      city VARCHAR(50),
      state_abbr CHAR(2),
      zip_code CHAR(10));
  1. 삽입된 행을 표시하려면 테이블을 쿼리해요.
SELECT * FROM customers_udt_test;

+---------+--------------+--------------------------------------+
| CUST_ID | CUST_NAME    | CUST_ADDRESS                         |
|---------+--------------+--------------------------------------|
| 1000    | Example1 Inc | {                                    |
|         |              |   "street": "101 Snow Street",       |
|         |              |   "city": "San Francisco",           |
|         |              |   "state_abbr": "CA",                |
|         |              |   "zip_code": "94102"                |
|         |              | }                                    |
| 1001    | Example2 Inc | {                                    |
|         |              |   "street": "555 Polar Bear Street", |
|         |              |   "city": "New York",                |
|         |              |   "state_abbr": "NY",                |
|         |              |   "zip_code": "10001"                |
|         |              | }                                    |
| 1002    | Example3 Inc | {                                    |
|         |              |   "street": "909 Flake Street",      |
|         |              |   "city": "Seattle",                 |
|         |              |   "state_abbr": "WA",                |
|         |              |   "zip_code": "98109"                |
|         |              | }                                    |
+---------+--------------+--------------------------------------+
  1. 테이블을 쿼리하고 콜론 연산자를 사용해 address 데이터의 zip_code 값만 표시해요.
SELECT cust_id,
    cust_name,
    cust_address:zip_code
  FROM customers_udt_test;

+---------+--------------+-----------------------+
| CUST_ID | CUST_NAME    | CUST_ADDRESS:ZIP_CODE |
|---------+--------------+-----------------------|
| 1000    | Example1 Inc | 94102                 |
| 1001    | Example2 Inc | 10001                 |
| 1002    | Example3 Inc | 98109                 |
+---------+--------------+-----------------------+

사용자 정의 타입과 함께 집합 연산자 및 조건부 표현식 함수 사용하기 (Using set operators and conditional expression functions with user-defined types)

집합 연산자나 조건부 표현식 함수가 Snowflake 타입과 사용자 정의 타입의 값을 평가할 때, 타입은 단일 타입으로 호환되고 강제 변환 가능해야 해요. 결과 출력은 사용자 정의 타입으로 명시적으로 캐스팅되지 않는 한 Snowflake 기본 타입이에요. 자세한 내용은 사용자 정의 타입에 대한 일반 사용 참고 사항을 참조하세요.

이 섹션의 예제는 다양한 기본 타입을 가진 여러 사용자 정의 타입과 함께 집합 연산자와 조건부 표현식을 사용해요. 먼저 다양한 기본 타입을 가진 여러 사용자 정의 타입을 만들어요.

CREATE TYPE us_zipcode AS VARCHAR;
CREATE TYPE uk_postcode AS VARCHAR;
CREATE TYPE positive_integer AS INTEGER;
CREATE TYPE positive_number AS NUMBER;

다음 쿼리는 IFF 함수를 호출해요. 이 호출은 us_zipcode 사용자 정의 타입의 값과 호환되는 Snowflake 타입의 값을 평가해요. 이 쿼리는 SYSTEM$TYPEOF 함수를 사용해 결과가 Snowflake 기본 타입 VARCHAR임을 보여줘요.

SELECT IFF(TRUE, '90210'::us_zipcode, '10006') AS result,
       SYSTEM$TYPEOF(IFF(TRUE, '90210'::us_zipcode, '10006')) AS type;

+--------+-------------------------+
| RESULT | TYPE                    |
|--------+-------------------------|
| 90210  | VARCHAR(134217728)[LOB] |
+--------+-------------------------+

다음 쿼리는 이전 쿼리와 같지만 결과를 us_zipcode 사용자 정의 타입으로 캐스팅해요.

SELECT IFF(TRUE, '90210'::us_zipcode, '10006')::us_zipcode AS result,
       SYSTEM$TYPEOF(IFF(TRUE, '90210'::us_zipcode, '10006')::us_zipcode) AS type;

+--------+-------------------------------+
| RESULT | TYPE                          |
|--------+-------------------------------|
| 90210  | MYDB.MYSCHEMA.US_ZIPCODE[LOB] |
+--------+-------------------------------+

다음 쿼리는 서로 다르지만 호환되는 사용자 정의 타입을 평가하고 Snowflake 기본 타입의 값을 반환하는 CASE 표현식을 포함해요.

SELECT CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS result,
       SYSTEM$TYPEOF(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END) AS type;

+----------+-------------------------+
| RESULT   | TYPE                    |
|----------+-------------------------|
| SW1A 0AA | VARCHAR(134217728)[LOB] |
+----------+-------------------------+

다음 쿼리는 이전 쿼리와 같지만 결과를 uk_postcode 사용자 정의 타입으로 캐스팅해요.

SELECT CAST(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS uk_postcode) AS result,
       SYSTEM$TYPEOF(CAST(CASE
         WHEN TRUE THEN 'SW1A 0AA'::uk_postcode
           ELSE '90210'::us_zipcode
         END AS uk_postcode)) AS type;

+----------+--------------------------------------------+
| RESULT   | TYPE                                       |
|----------+--------------------------------------------|
| SW1A 0AA | MYDB.MYSCHEMA.UK_POSTCODE[LOB]             |
+----------+--------------------------------------------+

다음 쿼리는 서로 다르지만 호환되는 사용자 정의 타입을 평가하고 Snowflake 기본 타입의 값을 반환하는 COALESCE 표현식을 포함해요.

SELECT COALESCE(
        5::positive_integer,
        10::positive_number) AS result,
      SYSTEM$TYPEOF(COALESCE(
        5::positive_integer,
        10::positive_number)) AS type;

+--------+-------------------+
| RESULT | TYPE              |
|--------+-------------------|
|      5 | NUMBER(38,0)[SB1] |
+--------+-------------------+

다음 쿼리는 이전 쿼리와 같지만 결과를 positive_number 사용자 정의 타입으로 캐스팅해요.

SELECT CAST(COALESCE(
        5::positive_integer,
        10::positive_number
      ) AS positive_number) AS result,
      SYSTEM$TYPEOF(CAST(COALESCE(
        5::positive_integer,
        10::positive_number
      ) AS positive_number)) AS type;

+--------+------------------------------------+
| RESULT | TYPE                               |
|--------+------------------------------------|
|      5 | MYDB.MYSCHEMA.POSITIVE_NUMBER[SB1] |
+--------+------------------------------------+

더 알아보기 (Learn more)