CREATE EXTERNAL TABLE

CREATE EXTERNAL TABLE

현재/지정한 스키마에 새 외부 테이블(external table)을 만들거나 기존 외부 테이블을 교체하는 명령이에요. 쿼리되면 외부 테이블은 지정된 외부 스테이지의 하나 이상의 파일 집합에서 데이터를 읽고 단일 VARIANT 컬럼으로 출력해요.

출처: 문서

본문

추가 컬럼을 정의할 수 있으며, 각 컬럼 정의는 이름, 데이터 타입, 선택적으로 NOT NULL/기본 키/외래 키 같은 제약 조건으로 구성돼요.

구문 (Syntax)

표현식에서 계산된 파티션:

CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  [ REFRESH_ON_CREATE =  { TRUE | FALSE } ]
  [ AUTO_REFRESH = { TRUE | FALSE } ]
  [ PATTERN = '<regex_pattern>' ]
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ AWS_SNS_TOPIC = '<string>' ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]

수동으로 추가/제거되는 파티션:

CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]

Delta Lake:

CREATE [ OR REPLACE ] EXTERNAL TABLE [IF NOT EXISTS]
  <table_name>
    ( [ <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ]
      [ inlineConstraint ]
      [ , <col_name> <col_type> AS <expr> | <part_col_name> <col_type> AS <part_expr> ... ]
      [ , ... ] )
  cloudProviderParams
  [ PARTITION BY ( <part_col_name> [, <part_col_name> ... ] ) ]
  [ WITH ] LOCATION = externalStage
  PARTITION_TYPE = USER_SPECIFIED
  FILE_FORMAT = ( { FORMAT_NAME = '<file_format_name>' | TYPE = { CSV | JSON | AVRO | ORC | PARQUET } [ formatTypeOptions ] } )
  [ TABLE_FORMAT = DELTA ]
  [ COPY GRANTS ]
  [ COMMENT = '<string_literal>' ]
  [ [ WITH ] ROW ACCESS POLICY <policy_name> ON (VALUE) ]
  [ [ WITH ] TAG ( <tag_name> = '<tag_value>' [ , <tag_name> = '<tag_value>' , ... ] ) ]
  [ WITH CONTACT ( <purpose> = <contact_name> [ , <purpose> = <contact_name> ... ] ) ]

여기서:

inlineConstraint ::=
  [ NOT NULL ]
  [ CONSTRAINT <constraint_name> ]
  { UNIQUE | PRIMARY KEY | [ FOREIGN KEY ] REFERENCES <ref_table_name> [ ( <ref_col_name> [ , <ref_col_name> ] ) ] }
  [ <constraint_properties> ]

cloudProviderParams (for Google Cloud Storage) ::=
  [ INTEGRATION = '<integration_name>' ]

cloudProviderParams (for Microsoft Azure) ::=
  [ INTEGRATION = '<integration_name>' ]

externalStage ::=
  @[<namespace>.]<ext_stage_name>[/<path>]

형식 유형 옵션(formatTypeOptions)은 FILE_FORMAT = ( TYPE = ... ) 형태로 지정하며, 파일 형식 유형(CSV/JSON/AVRO/ORC/PARQUET)에 따라 압축, 구분자, null 처리 등 다양한 옵션을 지원해요. 자세한 내용은 본문의 형식 유형 옵션 섹션을 참조하세요.

CREATE EXTERNAL TABLE … USING TEMPLATE 구문

CREATE [ OR REPLACE ] EXTERNAL TABLE <table_name>
  USING TEMPLATE <query>
  [ ... ]
  [ COPY GRANTS ]

반정형 데이터(Parquet, Avro, ORC, JSON, CSV)를 포함하는 스테이징된 파일 집합에서 파생된 컬럼 정의로 새 외부 테이블을 만들어요.

필수 매개변수

• <table_name>: 테이블의 식별자예요. 생성되는 스키마 내에서 고유해야 해요.
• [ WITH ] LOCATION = externalStage: 읽을 데이터 파일이 스테이징된 외부 스테이지와 선택적 경로를 지정해요. @[namespace.]ext_stage_name[/path]. 문자열 리터럴이나 SQL 변수는 지원되지 않아요. 전체 디렉터리 경로를 지정해야 하며 부분 경로(공유 접두사)는 안 돼요. 특정 파일 이름은 참조할 수 없어요 (파일 필터링에는 PATTERN 사용).
• FILE_FORMAT = ( FORMAT_NAME = 'file_format_name' ) 또는 FILE_FORMAT = ( TYPE = CSV | JSON | AVRO | ORC | PARQUET [ ... ] ): 스캔할 스테이징된 데이터 파일의 형식을 지정해요.
  • FORMAT_NAME = file_format_name: 기존 명명된 파일 포맷을 지정해요.
  • TYPE = ...: 스테이징된 데이터 파일의 형식 유형을 지정해요. 추가 형식별 옵션을 지정할 수 있어요. 기본값: TYPE = CSV.
  • FORMAT_NAME과 TYPE은 상호 배타적이에요.

선택 매개변수

• col_name: 컬럼 식별자를 지정해요.
• col_type: 컬럼의 데이터 타입을 지정해요. 컬럼의 expr 결과와 일치해야 해요.
• expr: 컬럼의 표현식을 지정해요. 외부 테이블 컬럼은 명시적 표현식으로 정의되는 가상 컬럼(virtual column)이에요. VALUE 컬럼이나 METADATA$FILENAME 의사 컬럼을 사용해 가상 컬럼을 추가해요.
  • VALUE: 외부 파일에서 단일 행을 나타내는 VARIANT 타입 컬럼. CSV의 경우 각 행을 컬럼 위치로 식별되는 요소가 있는 객체로 구조화해요 ({c1: <column_1_value>, c2: <column_2_value>, ...}).
  • METADATA$FILENAME: 외부 테이블에 포함된 각 스테이징된 데이터 파일의 이름(스테이지의 경로 포함)을 식별하는 의사 컬럼.
• CONSTRAINT ...: 테이블의 지정된 컬럼에 대한 인라인/아웃오브라인 제약 조건을 정의해요.
• REFRESH_ON_CREATE = { TRUE | FALSE }: 외부 테이블을 만든 직후 메타데이터를 한 번 자동 새로 고칠지 지정해요. TRUE면 생성 후 자동으로 한 번 새로 고쳐요. 지정된 위치에 약 100만 개 이상의 파일이 있으면 FALSE를 권장해요. 기본값: TRUE.
• AUTO_REFRESH = { TRUE | FALSE }: 지정된 스테이지에서 새/업데이트된 데이터 파일이 있을 때 외부 테이블 메타데이터 자동 새로 고침 트리거를 활성화할지 지정해요.
  • 수동 추가 파티션(PARTITION_TYPE = USER_SPECIFIED)과 S3 호환 저장소를 참조하는 외부 테이블에서는 TRUE가 지원되지 않아요.
  • 자동 새로 고침을 활성화하려면 저장소 위치에 대한 이벤트 알림(event notification)을 구성해야 해요 (클라우드 서비스별 지침 참조). 기본값: TRUE.
• PATTERN = 'regex_pattern': 일치시킬 외부 스테이지의 파일 이름/경로를 지정하는 정규 표현식 패턴 문자열이에요. 성능을 위해 많은 파일에 필터링하는 패턴은 피하세요.
• AWS_SNS_TOPIC = 'string': Amazon S3 스테이지에서 AUTO_REFRESH를 구성할 때 필요해요. S3 버킷의 SNS 토픽 ARN을 지정해요.
• TABLE_FORMAT = DELTA: 외부 테이블이 클라우드 저장소 위치의 Delta Lake를 참조함을 식별해요. Amazon S3, GCS, Microsoft Azure의 Delta Lake를 지원해요. (이 기능은 향후 릴리스에서 사용 중단될 예정이에요. Apache Iceberg™ 테이블 사용을 고려하세요.) _delta_log/ 디렉터리만 포함해야 해요. AWS_SNS_TOPIC과 PATTERN은 지원되지 않아요. REFRESH_ON_CREATE와 AUTO_REFRESH는 FALSE여야 해요.
• COPY GRANTS: CREATE OR REPLACE TABLE 변형으로 외부 테이블을 다시 만들 때 원본 테이블의 접근 권한을 보존해요. OWNERSHIP을 제외한 모든 권한을 복사해요.
• COMMENT = 'string_literal': 외부 테이블에 대한 주석을 지정해요.
• ROW ACCESS POLICY <policy_name> ON (VALUE): 테이블에 설정할 행 접근 폴리시를 지정해요. 외부 테이블에 적용할 때는 VALUE 컬럼을 지정해요.
• WITH DATA METRIC FUNCTION ( dmf_name ON ( col_name [ , ... ] ) [ , ... ] ): 생성 시 하나 이상의 데이터 메트릭 함수(DMF)를 외부 테이블과 연결해요.
• TAG ( tag_name = 'tag_value' [ , ... ] ): 태그 이름과 문자열 값을 지정해요.
• WITH CONTACT ( purpose = contact [ , ...] ): 새 객체를 하나 이상의 연락처와 연결해요.

파티셔닝 매개변수

• part_col_name col_type AS part_expr: 외부 테이블에 하나 이상의 파티션 컬럼을 정의해요. 파티션 컬럼은 METADATA$FILENAME 의사 컬럼의 경로/파일 이름 정보를 파싱하는 표현식으로 평가해야 해요.
• PARTITION_TYPE = USER_SPECIFIED: 외부 테이블의 파티션 유형을 사용자 정의로 지정해요. 외부 테이블 소유자는 ALTER EXTERNAL TABLE … ADD PARTITION 문으로 파티션을 수동으로 추가해야 해요. 파티션이 자동 추가되면 이 매개변수를 설정하지 마세요.
• [ PARTITION BY ( part_col_name [, ... ] ) ]: 평가할 파티션 컬럼을 지정해요. WHERE 절에 파티션 컬럼을 포함하면 Snowflake가 스캔할 데이터 파일 집합을 제한해요.

클라우드 공급자 매개변수 (cloudProviderParams)

• INTEGRATION = integration_name (Google Cloud Storage, Microsoft Azure): Google Pub/Sub 또는 Azure Event Grid 이벤트 알림으로 외부 테이블 메타데이터를 자동 새로 고치는 데 사용되는 알림 인티그레이션 이름을 지정해요. 자동 새로 고침 활성화에 필요해요.

형식 유형 옵션 (formatTypeOptions)

형식 유형 옵션은 테이블로 데이터를 로드하고 테이블에서 언로드하는 데 사용돼요. 파일 형식 유형(FILE_FORMAT = ( TYPE = ... ))에 따라 다음 옵션을 포함할 수 있어요:

• CSV: COMPRESSION(기본 AUTO), RECORD_DELIMITER(기본 개행), FIELD_DELIMITER(기본 쉼표), MULTI_LINE(기본 TRUE), SKIP_HEADER(기본 0), SKIP_BLANK_LINES(기본 FALSE), ESCAPE_UNENCLOSED_FIELD(기본 \\), TRIM_SPACE(기본 FALSE), FIELD_OPTIONALLY_ENCLOSED_BY(기본 NONE), NULL_IF(기본 \N), EMPTY_FIELD_AS_NULL(기본 TRUE), ENCODING(기본 UTF8).
• JSON: COMPRESSION(기본 AUTO), MULTI_LINE(기본 TRUE), ALLOW_DUPLICATE(기본 FALSE), STRIP_OUTER_ARRAY(기본 FALSE), STRIP_NULL_VALUES(기본 FALSE), REPLACE_INVALID_CHARACTERS(기본 FALSE).
• AVRO: COMPRESSION(기본 AUTO), REPLACE_INVALID_CHARACTERS(기본 FALSE).
• ORC: TRIM_SPACE(기본 FALSE), REPLACE_INVALID_CHARACTERS(기본 FALSE), NULL_IF(기본 \N).
• PARQUET: COMPRESSION(기본 AUTO), BINARY_AS_TEXT(기본 TRUE), REPLACE_INVALID_CHARACTERS(기본 FALSE).

각 옵션의 자세한 의미와 지원 값은 본문의 각 유형 섹션을 참조하세요. (ENCODING은 Big5, EUCKR, SHIFTJIS 등 다양한 문자 셋을 지원하며, Snowflake는 내부적으로 모든 데이터를 UTF-8로 저장해요.)

접근 제어 요구 사항

권한	객체	참고
CREATE EXTERNAL TABLE	스키마
CREATE STAGE	스키마	새 스테이지 생성 시 필요.
USAGE	스테이지	기존 스테이지 참조 시 필요.
USAGE	파일 포맷

사용법 참고 사항

• 외부 테이블은 외부(S3, Azure, GCS) 스테이지만 지원해요. 내부(Snowflake) 스테이지는 지원되지 않아요.
• 외부 테이블은 저장소 버전 관리(S3 versioning 등)를 지원하지 않아요.
• 복원이 필요한 보관용(archival) 클라우드 저장소 클래스(예: Glacier, Glacier Deep Archive, Azure Archive Storage)의 데이터에 접근할 수 없어요.
• Snowflake는 외부 테이블에서 무결성 제약 조건을 강제하지 않아요. 특히 NOT NULL을 강제하지 않아요.
• 외부 테이블은 다음 메타데이터 컬럼을 포함해요:
  • METADATA$FILENAME: 각 스테이징된 데이터 파일의 이름 (스테이지의 경로 포함).
  • METADATA$FILE_ROW_NUMBER: 각 레코드의 행 번호.
• 외부 테이블에 지원되지 않는 항목: 클러스터링 키, 복제(cloning), XML 형식의 데이터, Time Travel.
• OR REPLACE는 기존 외부 테이블에 DROP EXTERNAL TABLE을 수행한 다음 같은 이름으로 새 외부 테이블을 만드는 것과 동일해요. 원자적이에요.
• SELECT *는 항상 모든 일반/반정형 데이터가 variant 행으로 캐스팅된 VALUE 컬럼을 반환해요.
• OR REPLACE와 IF NOT EXISTS 절은 상호 배타적이에요.

예제 (Examples)

파티션 컬럼 표현식에서 자동으로 추가되는 파티션

데이터 파일이 logs/YYYY/MM/DD/HH24 구조로 구성된 예에서 s1이라는 외부 스테이지를 만든 뒤(경로 /files/logs/ 포함) 파티션된 외부 테이블을 만들어요:

CREATE EXTERNAL TABLE et1(
 date_part date AS TO_DATE(SPLIT_PART(metadata$filename, '/', 3)
 || '/' || SPLIT_PART(metadata$filename, '/', 4)
 || '/' || SPLIT_PART(metadata$filename, '/', 5), 'YYYY/MM/DD'),
 timestamp bigint AS (value:timestamp::bigint),
 col2 varchar AS (value:col2::varchar))
 PARTITION BY (date_part)
 LOCATION=@s1/logs/
 AUTO_REFRESH = true
 FILE_FORMAT = (TYPE = PARQUET)
 AWS_SNS_TOPIC = 'arn:aws:sns:us-west-2:001234567890:s3_mybucket';

외부 테이블 메타데이터를 새로 고쳐요:

ALTER EXTERNAL TABLE et1 REFRESH;

WHERE 절로 파티션 컬럼을 필터링해 쿼리해요:

SELECT timestamp, col2 FROM et1 WHERE date_part = to_date('08/05/2018');

수동으로 추가되는 파티션

PARTITION_TYPE = USER_SPECIFIED를 사용해 사용자 정의 파티션으로 외부 테이블을 만들어요:

create external table et2(
  col1 date as (parse_json(metadata$external_table_partition):COL1::date),
  col2 varchar as (parse_json(metadata$external_table_partition):COL2::varchar),
  col3 number as (parse_json(metadata$external_table_partition):COL3::number))
  partition by (col1,col2,col3)
  location=@s2/logs/
  partition_type = user_specified
  file_format = (type = parquet);

파티션 컬럼에 대한 파티션을 추가해요:

ALTER EXTERNAL TABLE et2 ADD PARTITION(col1='2022-01-24', col2='a', col3='12') LOCATION '2022/01';

외부 테이블의 구체화된 뷰

외부 테이블 컬럼의 서브쿼리를 기반으로 구체화된 뷰를 만들어요:

CREATE MATERIALIZED VIEW et1_mv
  AS
  SELECT col2 FROM et1;

감지된 컬럼 정의로 생성된 외부 테이블

INFER_SCHEMA로 스테이징된 파일에서 파생된 컬럼 정의로 외부 테이블을 만들어요:

CREATE EXTERNAL TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT('COLUMN_NAME',COLUMN_NAME, 'TYPE',TYPE, 'NULLABLE', NULLABLE, 'EXPRESSION',EXPRESSION))
    FROM TABLE(
      INFER_SCHEMA(
        LOCATION=>'@mystage',
        FILE_FORMAT=>'my_parquet_format'
      )
    )
  )
  LOCATION=@mystage
  FILE_FORMAT=my_parquet_format
  AUTO_REFRESH=false;

더 알아보기 (Learn more)

• ALTER EXTERNAL TABLE
• DROP EXTERNAL TABLE
• SHOW EXTERNAL TABLES
• DESCRIBE EXTERNAL TABLE
• CREATE STAGE
• CREATE | ALTER TABLE … CONSTRAINT
• CREATE ICEBERG TABLE (Delta files in object storage)
• 마스킹 폴리시와 외부 테이블, 행 접근 폴리시와 외부 테이블