INFER_SCHEMA

INFER_SCHEMA

반정형 데이터를 담고 있는 스테이징 데이터 파일 집합에서 파일 메타데이터 스키마를 자동으로 감지하고 열 정의를 가져와요.

GENERATE_COLUMN_DESCRIPTION 함수는 INFER_SCHEMA 함수 출력을 기반으로, 스테이징 파일의 열 정의를 사용해 새 테이블, 외부 테이블 또는 뷰(적절한 CREATE <object> 명령 사용)를 더 쉽게 만들 수 있게 해요.

USING TEMPLATE 절과 함께 CREATE TABLE, CREATE EXTERNAL TABLE, 또는 CREATE ICEBERG TABLE 명령을 실행하면, 스테이징 파일의 열 정의를 기반으로 새 테이블이나 외부 테이블을 만들 수 있어요.

참고: 이 함수는 Apache Parquet, Apache Avro, ORC, JSON, CSV 파일을 지원해요.

출처: Snowflake SQL Reference - INFER_SCHEMA

본문

구문

INFER_SCHEMA(
  LOCATION => '{ internalStage | externalStage }'
  , FILE_FORMAT => '<file_format_name>'
  , FILES => ( '<file_name>' [ , '<file_name>' ] [ , ... ] )
  , IGNORE_CASE => TRUE | FALSE
  , MAX_FILE_COUNT => <num>
  , MAX_RECORDS_PER_FILE => <num>
  , KIND => '<kind_name>'
)

여기서:

internalStage ::=
    @[<namespace>.]<int_stage_name>[/<path>][/<filename>]
  | @~[/<path>][/<filename>]

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

인자

LOCATION => '...'

• 파일이 저장된 내부 또는 외부 스테이지의 이름이에요. 선택적으로 클라우드 스토리지 위치의 파일 대상을 하나 이상 포함할 수 있어요. 그렇지 않으면 INFER_SCHEMA 함수는 스테이지의 모든 하위 디렉터리의 파일을 스캔해요. @[namespace.]int_stage_name[/path][/filename] 파일이 지정한 명명된 내부 스테이지에 있음. @[namespace.]ext_stage_name[/path][/filename] 파일이 지정한 명명된 외부 스테이지에 있음. @~[/path][/filename] 파일이 현재 사용자의 스테이지에 있음. 이 SQL 함수는 명명된 스테이지(내부 또는 외부)와 사용자 스테이지만 지원해요. 테이블 스테이지는 지원하지 않아요.

FILES => ( 'file_name' [ , 'file_name' ] [ , ... ] )

• 반정형 데이터를 담고 있는 스테이징 파일 집합에서 하나 이상의 파일(쉼표로 구분) 목록을 지정해요. 파일은 명령에 지정된 Snowflake 내부 위치 또는 외부 위치에 이미 스테이징되어 있어야 해요. 지정한 파일 중 하나라도 찾을 수 없으면 쿼리가 중단돼요. 지정할 수 있는 최대 파일 이름 수는 1000개예요. 외부 스테이지(Amazon S3, Google Cloud Storage, Microsoft Azure)에 대해서만 파일 경로는 스테이지 정의의 URL과 해석된 파일 이름 목록을 연결해서 설정돼요. 그러나 Snowflake는 경로와 파일 이름 사이에 구분자를 암시적으로 삽입하지 않아요. 스테이지 정의의 URL 끝이나 이 매개변수에 지정된 각 파일 이름의 시작 부분에 구분자(/)를 명시적으로 포함해야 해요.

FILE_FORMAT => 'file_format_name'

• 스테이징 파일에 담긴 데이터를 설명하는 파일 포맷 객체의 이름이에요. 자세한 내용은 CREATE FILE FORMAT 문서를 참고하세요.

IGNORE_CASE => TRUE | FALSE

• 스테이지 파일에서 감지된 열 이름을 대소문자 구분으로 처리할지 여부를 지정해요. 기본값은 FALSE로, Snowflake가 열 이름을 가져올 때 알파벳 문자의 대소문자를 보존한다는 뜻이에요. 값을 TRUE로 지정하면 열 이름이 대소문자를 구분하지 않는 것으로 처리되고 모든 열 이름이 대문자로 가져와져요.

MAX_FILE_COUNT => num

• 스테이지에서 스캔할 최대 파일 수를 지정해요. 이 옵션은 파일 전체에 걸쳐 동일한 스키마를 가진 파일 수가 많을 때 권장돼요. 이 옵션으로는 어떤 파일이 스캔될지 결정할 수 없어요. 특정 파일을 스캔하려면 FILES 옵션을 대신 사용하세요.

MAX_RECORDS_PER_FILE => num

• 파일당 스캔할 최대 레코드 수를 지정해요. 이 옵션은 CSV와 JSON 파일에만 적용돼요. 큰 파일에는 이 옵션을 사용하는 것을 권장해요. 이 옵션은 스키마 감지 정확도에 영향을 줄 수 있어요.

KIND => 'kind_name'

• 스테이지에서 스캔할 수 있는 파일 메타데이터 스키마의 종류를 지정해요. 기본값은 STANDARD로, 스테이지에서 스캔할 수 있는 파일 메타데이터 스키마가 Snowflake 테이블용이고 출력이 Snowflake 데이터 타입이라는 뜻이에요. 값을 ICEBERG로 지정하면 스키마가 Apache Iceberg 테이블용이고 출력이 Iceberg 데이터 타입이에요. Parquet 파일을 추론해 Iceberg 테이블을 만들 때는 KIND => 'ICEBERG'로 설정하는 것을 강력히 권장해요. 그렇지 않으면 함수가 반환하는 열 정의가 올바르지 않을 수 있어요.

출력

이 함수는 다음 열을 반환해요.

Column Name	Data Type	Description
COLUMN_NAME	TEXT	스테이징 파일에 있는 열의 이름.
TYPE	TEXT	열의 데이터 타입.
NULLABLE	BOOLEAN	열의 행에 값 대신 NULL을 저장할 수 있는지 여부. 현재는 열의 추론된 null 여부가 스캔 집합의 다른 파일에는 적용되지 않고 한 데이터 파일에만 적용될 수 있음.
EXPRESSION	TEXT	$1:COLUMN_NAME::TYPE 형식의 열 표현식(주로 외부 테이블용). IGNORE_CASE가 TRUE로 지정되면 열의 표현식은 GET_IGNORE_CASE ($1, COLUMN_NAME)::TYPE 형식.
FILENAMES	TEXT	열을 담고 있는 파일들의 이름.
ORDER_ID	NUMBER	스테이징 파일에서의 열 순서.

사용 시 참고 사항

• CSV 파일의 경우 파일 포맷 옵션 PARSE_HEADER = [ TRUE | FALSE ]로 열 이름을 정의할 수 있어요. 옵션을 TRUE로 설정하면 첫 행의 헤더를 사용해 열 이름을 결정해요. 기본값 FALSE는 열 이름을 c*로 반환하며, *는 열의 위치예요. SKIP_HEADER 옵션은 PARSE_HEADER = TRUE와 함께 지원되지 않아요. PARSE_HEADER 옵션은 외부 테이블에서는 지원되지 않아요.
• CSV와 JSON 파일 모두에서 현재 지원되지 않는 파일 포맷 옵션은 DATE_FORMAT, TIME_FORMAT, TIMESTAMP_FORMAT이에요.
• JSON TRIM_SPACE 파일 포맷 옵션은 지원되지 않아요.
• JSON 파일의 과학 표기(예: 1E2)는 REAL 데이터 타입으로 가져와져요.
• 모든 타임스탬프 데이터 타입 변형은 시간대 정보 없이 TIMESTAMP_NTZ로 가져와져요.
• CSV와 JSON 파일 모두에서 모든 열이 NULLABLE로 식별돼요.
• KIND => 'STANDARD'와 KIND => 'ICEBERG' 모두에서, 스테이지의 지정된 파일에 중첩 데이터 타입이 담겨 있으면 첫 번째 중첩 수준만 지원되고 더 깊은 수준은 지원되지 않아요.
• Apache Iceberg™ version 3(v3) 테이블은 지원되지 않아요.

예시

Snowflake 열 정의

mystage 스테이지의 Parquet 파일에 대한 Snowflake 열 정의를 가져옵니다.

-- Create a file format that sets the file type as Parquet.
CREATE FILE FORMAT my_parquet_format
  TYPE = parquet;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| continent   | TEXT    | True     | $1:continent::TEXT  | geography/cities.parquet | 0        |
| country     | VARIANT | True     | $1:country::VARIANT | geography/cities.parquet | 1        |
| COUNTRY     | VARIANT | True     | $1:COUNTRY::VARIANT | geography/cities.parquet | 2        |
+-------------+---------+----------+---------------------+--------------------------+----------+

앞의 예시와 비슷하지만, mystage 스테이지에서 단일 Parquet 파일을 지정합니다.

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/geography/cities.parquet'
      , FILE_FORMAT=>'my_parquet_format'
      )
    );

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| continent   | TEXT    | True     | $1:continent::TEXT  | geography/cities.parquet | 0        |
| country     | VARIANT | True     | $1:country::VARIANT | geography/cities.parquet | 1        |
| COUNTRY     | VARIANT | True     | $1:COUNTRY::VARIANT | geography/cities.parquet | 2        |
+-------------+---------+----------+---------------------+--------------------------+----------+

IGNORE_CASE를 TRUE로 지정해 mystage 스테이지의 Parquet 파일에 대한 Snowflake 열 정의를 가져옵니다. 반환된 출력에서 모든 열 이름이 대문자로 가져와집니다.

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage'
      , FILE_FORMAT=>'my_parquet_format'
      , IGNORE_CASE=>TRUE
      )
    );

+-------------+---------+----------+----------------------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION                             | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+---------------------------------------------|----------+
| CONTINENT   | TEXT    | True     | GET_IGNORE_CASE ($1, CONTINENT)::TEXT  | geography/cities.parquet | 0        |
| COUNTRY     | VARIANT | True     | GET_IGNORE_CASE ($1, COUNTRY)::VARIANT | geography/cities.parquet | 1        |
+-------------+---------+----------+---------------------+---------------------------------------------+----------+

mystage 스테이지의 JSON 파일에 대한 Snowflake 열 정의를 가져옵니다.

-- Create a file format that sets the file type as JSON.
CREATE FILE FORMAT my_json_format
  TYPE = json;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/json/'
      , FILE_FORMAT=>'my_json_format'
      )
    );

+-------------+---------------+----------+---------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE          | NULLABLE | EXPRESSION                | FILENAMES                | ORDER_ID |
|-------------+---------------+----------+---------------------------+--------------------------|----------+
| col_bool    | BOOLEAN       | True     | $1:col_bool::BOOLEAN      | json/schema_A_1.json     | 0        |
| col_date    | DATE          | True     | $1:col_date::DATE         | json/schema_A_1.json     | 1        |
| col_ts      | TIMESTAMP_NTZ | True     | $1:col_ts::TIMESTAMP_NTZ  | json/schema_A_1.json     | 2        |
+-------------+---------------+----------+---------------------------+--------------------------+----------+

스테이징된 JSON 파일에서 감지된 스키마를 사용해 테이블을 만듭니다.

CREATE TABLE mytable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage/json/',
          FILE_FORMAT=>'my_json_format'
        )
      ));

참고: ARRAY_AGG(OBJECT_CONSTRUCT())에 *를 사용하면 반환된 결과가 128MB보다 크면 오류가 발생할 수 있어요. 더 큰 결과 집합에는 * 사용을 피하고, 쿼리에 필요한 열인 COLUMN NAME, TYPE, NULLABLE만 사용하는 것을 권장해요. WITHIN GROUP (ORDER BY order_id)를 사용할 때는 선택 열 ORDER_ID를 포함할 수 있어요.

mystage 스테이지의 CSV 파일에 대한 열 정의를 가져오고 MATCH_BY_COLUMN_NAME을 사용해 CSV 파일을 로드합니다.

-- Create a file format that sets the file type as CSV.
CREATE FILE FORMAT my_csv_format
  TYPE = csv
  PARSE_HEADER = true;

-- Query the INFER_SCHEMA function.
SELECT *
  FROM TABLE(
    INFER_SCHEMA(
      LOCATION=>'@mystage/csv/'
      , FILE_FORMAT=>'my_csv_format'
      )
    );

+-------------+---------------+----------+---------------------------+--------------------------+----------+
| COLUMN_NAME | TYPE          | NULLABLE | EXPRESSION                | FILENAMES                | ORDER_ID |
|-------------+---------------+----------+---------------------------+--------------------------|----------+
| col_bool    | BOOLEAN       | True     | $1:col_bool::BOOLEAN      | json/schema_A_1.csv      | 0        |
| col_date    | DATE          | True     | $1:col_date::DATE         | json/schema_A_1.csv      | 1        |
| col_ts      | TIMESTAMP_NTZ | True     | $1:col_ts::TIMESTAMP_NTZ  | json/schema_A_1.csv      | 2        |
+-------------+---------------+----------+---------------------------+--------------------------+----------+

-- Load the CSV file using MATCH_BY_COLUMN_NAME.
COPY INTO mytable FROM @mystage/csv/
  FILE_FORMAT = (
    FORMAT_NAME= 'my_csv_format'
  )
  MATCH_BY_COLUMN_NAME=CASE_INSENSITIVE;

Iceberg 열 정의

mystage 스테이지의 Parquet 파일에 대한 Iceberg 열 정의를 가져옵니다.

-- Create a file format that sets the file type as Parquet.
  CREATE OR REPLACE FILE FORMAT my_parquet_format
    TYPE = PARQUET
    USE_VECTORIZED_SCANNER = TRUE;

-- Query the INFER_SCHEMA function.
SELECT *
FROM TABLE(
  INFER_SCHEMA(
    LOCATION=>'@mystage'
    , FILE_FORMAT=>'my_parquet_format'
    , KIND => 'ICEBERG'
    )
  );

출력:

+-------------+---------+----------+---------------------+--------------------------+----------+
| COLUMN_NAME | TYPE    | NULLABLE | EXPRESSION          | FILENAMES                | ORDER_ID |
|-------------+---------+----------+---------------------+--------------------------|----------+
| id          | INT     | False    | $1:id::INT          | sales/customers.parquet   | 0       |
| custnum     | INT     | False    | $1:custnum::INT     | sales/customers.parquet   | 1       |
+-------------+---------+----------+---------------------+--------------------------+----------+

스테이징된 Parquet 파일에서 감지된 스키마를 사용해 Apache Iceberg™ 테이블을 만듭니다.

 -- Create a file format that sets the file type as Parquet.
 CREATE OR REPLACE FILE FORMAT my_parquet_format
   TYPE = PARQUET
   USE_VECTORIZED_SCANNER = TRUE;

-- Create an Iceberg table.
CREATE ICEBERG TABLE myicebergtable
  USING TEMPLATE (
    SELECT ARRAY_AGG(OBJECT_CONSTRUCT(*))
    WITHIN GROUP (ORDER BY order_id)
      FROM TABLE(
        INFER_SCHEMA(
          LOCATION=>'@mystage',
          FILE_FORMAT=>'my_parquet_format',
          KIND => 'ICEBERG'
        )
      ))
... {rest of the ICEBERG options}
;

참고: ARRAY_AGG(OBJECT_CONSTRUCT())에 *를 사용하면 반환된 결과가 128MB보다 크면 오류가 발생할 수 있어요. 더 큰 결과 집합에는 * 사용을 피하고, 쿼리에 필요한 열인 COLUMN NAME, TYPE, NULLABLE만 사용하는 것을 권장해요. WITHIN GROUP (ORDER BY order_id)를 사용할 때는 선택 열 ORDER_ID를 포함할 수 있어요.

더 알아보기

• Table functions — 테이블 함수 모음
• GENERATE_COLUMN_DESCRIPTION — 열 정의 생성
• CREATE FILE FORMAT — 파일 포맷 생성