가상 컬럼

가상 컬럼 (Virtual Columns)

가상 컬럼(virtual column)은 테이블에 저장되는 대신 표현식(expression)으로부터 계산된 값을 담는 컬럼이에요. 그 표현식은 같은 테이블의 다른 컬럼을 참조하거나, 결정적(deterministic) 시스템 정의 함수를 사용할 수 있습니다. 컬럼이 조회되면 Snowflake는 쿼리 시점에 그 표현식으로 값을 계산해요.

출처: Snowflake SQL Reference

본문

가상 컬럼은 뷰의 파생 컬럼(derived column)과 비슷한 목적을 갖지만 테이블 자체에 존재해요. 그래서 계산된 값만 노출하면 되는 경우, 가상 컬럼이 있는 테이블 하나로 테이블-뷰 쌍을 대체할 수 있습니다. 저장 비용이 없고(값이 쿼리 시점에 계산됨) 쿼리에서 별도 객체를 거치지 않고 직접 참조할 수 있죠.

구문 (Syntax)

가상 컬럼을 정의하려면 CREATE TABLE 또는 ALTER TABLE 문에 표현식이 있는 AS 절을 포함하세요.

CREATE OR REPLACE TABLE <table> ( <col_name> <col_type> [ GENERATED ALWAYS ] AS ( <expr> ) [ VIRTUAL ] )

ALTER TABLE <table> ADD [ COLUMN ] <col_name> <col_type> [ GENERATED ALWAYS ] AS ( <expr> ) [ VIRTUAL ]

AS ( expr )는 컬럼이 가상임을 나타내고 값을 계산하는 데 쓰는 표현식을 정의해요. 선택적인 GENERATED ALWAYS 접두사와 VIRTUAL 키워드는 ANSI SQL 구문으로 Snowflake가 호환성을 위해 받아들이는 것이며, 어느 쪽도 컬럼 동작을 바꾸지 않아요. 컬럼 데이터 타입은 필수이며 표현식의 추론된 타입과 호환되어야 합니다.

사용 참고 사항

허용되는 표현식

가상 컬럼 표현식은 리터럴, 연산자, 결정적 시스템 정의 함수를 지원해요. 함수가 결정적이라면 같은 입력에 대해 항상 같은 결과를 반환한다는 뜻이에요. 예:

• 유효: col1 * 2
• 유효: SUBSTR(col1, 1, 4) (SUBSTR는 결정적 함수)

가상 컬럼은 같은 테이블의 다른 가상 컬럼도 참조할 수 있어요. 단, 그 가상 컬럼들이 테이블 정의에서 더 앞에 정의되어 있어야 합니다.

• 유효: CREATE OR REPLACE TABLE t (c NUMBER, c1 NUMBER AS (c + 1), c2 NUMBER AS (c1 + 1));
• 무효: CREATE OR REPLACE TABLE t (c NUMBER, c1 NUMBER AS (c2 + 1), c2 NUMBER AS (c + 1)); (c1이 아직 정의되지 않은 c2를 참조)

금지된 표현식

가상 컬럼 정의에는 다음 표현식 유형이 허용되지 않아요.

• 비결정적 함수: 같은 입력에도 호출마다 출력이 달라질 수 있는 함수. 예: RANDOM, CURRENT_TIMESTAMP, CURRENT_DATE, UUID_STRING.
• 집계 함수: SUM, AVG, COUNT 같은 함수. 가상 컬럼은 행마다 평가되며 집계를 지원하지 않아요.
• 윈도우 함수: OVER 절을 사용하는 함수.
• 서브쿼리: 중첩된 SELECT 문.
• 사용자 정의 함수(UDF): SQL, JavaScript, 외부 UDF 포함.
• 바인드 변수: 위치 기반(?), 숫자(:1), 이름 기반(:value) 바인드 파라미터.
• 세션 변수: SET으로 설정된 변수.
• 위치 컬럼 참조: $1, $2 등을 사용하는 참조.

참고: $1 의사 컬럼(VALUE)은 외부 테이블 가상 컬럼에서는 허용돼요.

• 기본값 참조: DEFAULT 값을 가진 컬럼은 가상 컬럼 표현식에서 참조할 수 없고, 가상 컬럼에 기본값을 할당할 수도 없어요.

데이터 타입 요구 사항

가상 컬럼에 선언된 데이터 타입은 표현식의 추론된 타입과 호환되어야 해요. Snowflake는 다음 규칙을 강제합니다.

• 숫자 타입: 선언된 스케일(scale)이 표현식 스케일과 정확히 일치해야 하고, 선언된 정밀도(precision)는 표현식 정밀도보다 크거나 같아야 해요.
• 문자열 타입: 선언된 길이가 추론된 길이보다 크거나 같아야 하고, 콜레이션(collation)은 정확히 일치해야 해요.
• 타임스탬프·시간 타입: 선언된 소수 초 정밀도가 표현식 정밀도와 정확히 일치해야 해요.

참고: 외부 테이블의 가상 컬럼에 대해서는 Snowflake가 선언된 기본 타입이 추론된 표현식 타입과 일치하는지만 확인하며, 정밀도·스케일·길이는 강제하지 않아요.

또한 기본 컬럼에 대한 ALTER TABLE ... MODIFY COLUMN은, 변경으로 기본 컬럼의 타입이 의존하는 가상 컬럼과 호환되지 않게 되면 차단됩니다.

제약 조건

가상 컬럼에는 NOT NULL과 CHECK 제약 조건을 설정할 수 없어요. 이는 인라인 컬럼 정의(CREATE TABLE, ALTER TABLE ADD COLUMN)와 생성 후 수정(ALTER TABLE MODIFY, ALTER TABLE ADD CONSTRAINT) 모두에 적용됩니다. 가상 컬럼에는 DEFAULT 값을 할당할 수도 없어요.

참고: NOT NULL은 외부 테이블 가상 컬럼에서는 허용돼요.

어느 제약 조건이든 설정하려 하면 오류가 반환됩니다.

컬럼 의존성과 DROP COLUMN

테이블의 어떤 가상 컬럼이 드롭할 컬럼에 의존한다면 ALTER TABLE ... DROP COLUMN은 차단돼요. 여기에는 다음이 포함됩니다.

• 가상 컬럼이 참조하는 기본 컬럼 드롭.
• 다른 가상 컬럼이 참조하는 가상 컬럼 드롭.

연쇄 의존성은 완전히 보호됩니다. 예를 들어 가상 컬럼 b가 가상 컬럼 a에 의존한다면, a나 a가 참조하는 기본 컬럼 중 무엇을 드롭해도 차단돼요. 이 동작은 외부 테이블에도 적용됩니다.

추가 제한 사항

• 가상 컬럼은 테이블의 클러스터링 키로 설정할 수 없어요.
• 가상 컬럼이 모든 테이블 유형에서 지원되는 것은 아니에요. 자세한 내용은 특정 테이블 유형의 문서를 참고하세요.

알려진 문제 (Known issues)

가상 컬럼 표현식이 NULL 값을 비-NULL 값으로 바꾸고, 그 가상 컬럼을 외부 조인(outer join)에 사용하면, 조인의 보존(preserved) 쪽에서 매칭되지 않은 행이 가상 컬럼에 대해 NULL 대신 비-NULL 값을 반환할 수 있어요. 이 알려진 문제는 표현식이 COALESCE 같은 것으로 NULL 입력에 기본값을 대체할 때 발생합니다.

예를 들어 가상 컬럼이 COALESCE(<column>, 'none')으로 정의된 경우, LEFT JOIN 후에 보존 쪽에서 매칭이 없는 행에 대해 다른 테이블의 컬럼이 NULL임에도 그 가상 컬럼이 'none'을 반환할 수 있어요.

해결 방법: 가상 컬럼 정의에서 COALESCE(또는 유사한 로직)를 제거하고, 필요한 곳(컬럼 목록, SELECT 문, WHERE 술어 등)에서 적용하세요.

-- t1: 가상 컬럼이 NULL 색을 문자열 'none'으로 대체함.
CREATE OR REPLACE TEMPORARY TABLE t1 (
  id            INT,
  color         STRING,
  color_dark    STRING AS (COALESCE('dark ' || color, 'none'))
);
INSERT INTO t1 (id, color) VALUES (0, null), (1, 'red'), (2, 'blue'), (3, 'green');

-- t2: 조인의 보존 쪽. 행 1, 3은 t1과 매칭되고; 행 5는 t1에 매칭이 없음.
CREATE OR REPLACE TEMPORARY TABLE t2 (
  id INT
);
INSERT INTO t2 (id) VALUES (1), (3), (5);

-- 행 5는 SELECT 기본값 대신 color_dark에 대해 'none'을 잘못 반환할 수 있음.
SELECT t2.id, t1.color, COALESCE(t1.color_dark, 'dark none')
FROM t2
LEFT JOIN t1 ON t1.id = t2.id;

-- 해결책: 가상 컬럼은 연결만 하고, NULL 색은 t1에서 NULL로 유지됨.
CREATE OR REPLACE TEMPORARY TABLE t1 (
  id            INT,
  color         STRING,
  color_dark    STRING AS ('dark ' || color)
);
INSERT INTO t1 (id, color) VALUES (0, null), (1, 'red'), (2, 'blue'), (3, 'green');

-- 행 5: 조인 후 color_dark는 NULL이므로 COALESCE가 SELECT에서 'dark none' 기본값을 적용함.
SELECT t2.id, t1.color, COALESCE(t1.color_dark, 'dark none')
FROM t2
LEFT JOIN t1 ON t1.id = t2.id;

오류 참조 (Error reference)

가상 컬럼 작업에서 반환될 수 있는 SQL 컴파일 오류는 다음과 같습니다.

오류 코드	원인	예시	오류 메시지
001482	선언된 가상 컬럼 타입이 추론된 표현식 타입과 호환되지 않음	CREATE OR REPLACE TABLE t (i NUMBER(10,2), j NUMBER(5,0) AS (i));	Data type of virtual column does not match the data type of its expression for column 'J'.
011201	가상 컬럼 표현식에 비결정적·집계·윈도우 함수 사용	CREATE OR REPLACE TABLE t (i INT, j INT AS (RANDOM()));	Invalid usage of non-deterministic RANDOM function in J virtual column definition.
011202	가상 컬럼 표현식에 서브쿼리 사용	CREATE OR REPLACE TABLE t (i INT, j INT AS ((SELECT 1)));	Invalid usage of subquery in J virtual column definition.
011203	가상 컬럼 표현식에 바인드 변수 사용	CREATE OR REPLACE TABLE t (i INT, j INT AS (?));	Invalid usage of bind variable in J virtual column definition.
011204	가상 컬럼 표현식에 세션 변수 사용	CREATE OR REPLACE TABLE t (i INT, j INT AS ($myvar));	Invalid usage of session variable in J virtual column definition.
011205	가상 컬럼 표현식에 위치 컬럼 참조 사용	CREATE OR REPLACE TABLE t (i INT, j INT AS ($1));	Invalid usage of column positional reference in J virtual column definition.
011206	기본 컬럼에 대한 ALTER TABLE MODIFY COLUMN이 의존하는 가상 컬럼과 비호환하게 만듦	ALTER TABLE t MODIFY COLUMN i TYPE NUMBER(10,4);	Cannot change column I datatype. The dependent J virtual column has datatype NUMBER(10,2)...
011207	가상 컬럼에 NOT NULL 또는 CHECK 제약 조건 설정	CREATE OR REPLACE TABLE t (i INT, j INT AS (i * 2) NOT NULL);	Cannot set NOT NULL constraint on virtual column J.
011208	가상 컬럼이 의존하는 컬럼에 대한 DROP COLUMN	ALTER TABLE t DROP COLUMN i;	Cannot drop column 'I' because virtual column J depends on it.

가상 컬럼 식별하기

DESC TABLE 명령의 출력에서 KIND 컬럼은 컬럼이 표준 컬럼인지 가상 컬럼인지 식별해요. EXPRESSION 컬럼은 가상 컬럼 정의를 보여줍니다.

CREATE OR REPLACE TABLE x (i INT, j INT AS (i * i));

DESC TABLE x;

+------+--------------+---------+-------+---------+-------------+------------+-------+------------+---------+
| name | type         | kind    | null? | default | primary key | unique key | check | expression | comment |
|------+--------------+---------+-------+---------+-------------+------------+-------+------------+---------|
| I    | NUMBER(38,0) | COLUMN  | Y     | NULL    | N           | N          | NULL  | NULL       | NULL    |
| J    | NUMBER(38,0) | VIRTUAL | Y     | NULL    | N           | N          | NULL  | I * I      | NULL    |
+------+--------------+---------+-------+---------+-------------+------------+-------+------------+---------+

또한 SHOW COLUMNS 명령으로 테이블의 컬럼 메타데이터를 가져올 수 있어요. kind 컬럼은 가상 컬럼에 대해 VIRTUAL_COLUMN을, expression 컬럼은 가상 컬럼 정의를 보여줍니다.

INFORMATION_SCHEMA.COLUMNS 뷰를 조회할 수도 있으며, KIND 컬럼으로 가상 컬럼만 직접 필터링할 수 있어요.

SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, DATA_TYPE, EXPRESSION, KIND
  FROM INFORMATION_SCHEMA.COLUMNS
  WHERE TABLE_NAME = 'X'
    AND KIND = 'VIRTUAL_COLUMN';

예제 (Examples)

다음 예제는 컬럼 i의 값을 제곱하는 가상 컬럼 j가 있는 테이블을 만들어요.

CREATE OR REPLACE TABLE x (i INT, j INT AS (i * i));

INSERT INTO x VALUES (2);

SELECT * FROM x;

+---+---+
| I | J |
|---+---|
| 2 | 4 |
+---+---+

다음 예제는 CTAS 문에 인라인으로 정의된 가상 컬럼 tax가 있는 테이블을 만들어요. 가상 컬럼은 컬럼 수에서 제외되므로, SELECT 절은 비-가상 컬럼(vendor_name, vendor_city, cost)에 대한 값만 제공하면 됩니다.

-- 기본 테이블 만들기
CREATE OR REPLACE TABLE vendors (
  id    INT,
  name  VARCHAR(255) DEFAULT NULL,
  phone VARCHAR(100) DEFAULT NULL,
  city  VARCHAR(255),
  zip   VARCHAR(10)  DEFAULT NULL
);

INSERT INTO vendors (id, name, phone, city, zip)
VALUES
  (1, 'Acme Corporation',    '1-619-437-8889', 'San Diego',     '22434'),
  (2, 'Soylent Corporation', '1-650-249-5198', 'San Francisco', '94115'),
  (3, 'Initech',             '1-323-859-3954', 'Los Angeles',   '90001');

CREATE OR REPLACE TABLE sales (
  vendor_id  INT,
  order_date DATE,
  cost       NUMBER
);

INSERT INTO sales (vendor_id, order_date, cost)
VALUES
  (3, CURRENT_DATE(),  45.50),
  (2, CURRENT_DATE() - INTERVAL '1 day', 132.00),
  (1, CURRENT_DATE() - INTERVAL '2 days', 115.75),
  (3, CURRENT_DATE(), 205.00);

-- CTAS를 사용해 테이블 만들기
CREATE OR REPLACE TABLE today_sales (
  vendor_name VARCHAR(255),
  vendor_city VARCHAR(255),
  cost        NUMBER(16,3),
  tax         NUMBER(16,3) AS ((cost * 0.075)::NUMBER(16,3))
)
AS SELECT a.name, a.city, b.cost
   FROM vendors a LEFT JOIN sales b ON a.id = b.vendor_id
   WHERE order_date = CURRENT_DATE();

SELECT * FROM today_sales;

더 알아보기 (Learn more)

• CREATE TABLE
• ALTER TABLE
• 외부 테이블