카테고리:: 테이블 함수

INFER_SCHEMA¶

반정형 데이터가 포함된 일련의 스테이징된 데이터 파일에서 파일 메타데이터 스키마를 자동으로 감지하고, 열 정의를 검색합니다.

GENERATE_COLUMN_DESCRIPTION 함수는 INFER_SCHEMA 함수 출력을 기반으로 하여 스테이징된 파일의 열 정의를 기반으로 새 테이블, 외부 테이블 또는 뷰(적절한 CREATE <오브젝트> 명령 사용) 생성을 단순화합니다.

USING TEMPLATE 절이 있는 CREATE TABLE, CREATE EXTERNAL TABLE 또는 CREATE ICEBERG TABLE 명령을 사용하여 INFER_SCHEMA 함수 출력에서 파생된 열 정의를 사용해 새 테이블 또는 외부 테이블을 만들 수 있습니다.

참고

이 함수는 Apache Parquet, Apache Avro, ORC, JSON 및 CSV 파일을 지원합니다.

구문¶

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>'
)

Copy

여기서:

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

Copy

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

Copy

인자¶

LOCATION => '...'

파일이 저장되는 내부 또는 외부 스테이지의 이름입니다. 선택적으로 클라우드 저장소 위치에 있는 하나 이상의 파일에 대한 경로를 포함합니다. 그렇지 않은 경우, INFER_SCHEMA 함수는 스테이지의 모든 하위 디렉터리에 있는 파일을 검색합니다.

`@[namespace.]int_stage_name[/path][/filename]`	파일이 지정된 명명된 내부 스테이지에 있습니다.
`@[namespace.]ext_stage_name[/path][/filename]`	파일이 지정된 명명된 외부 스테이지에 있습니다.
`@~[/path][/filename]`	파일이 현재 사용자의 스테이지에 있습니다.

참고

이 SQL 함수는 명명된 스테이지(내부 또는 외부) 및 사용자 스테이지만 지원합니다. 테이블 스테이지는 지원하지 않습니다.

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

반정형 데이터를 포함하는 스테이징된 파일 세트에서 하나 이상의 파일(쉼표로 구분) 목록을 지정합니다. Snowflake 내부 위치나 명령에 지정된 외부 위치에 파일이 이미 스테이징되어 있어야 합니다. 지정된 파일 중 찾을 수 없는 파일이 있으면 쿼리가 중단됩니다.

지정할 수 있는 파일 이름의 최대 개수는 1,000개입니다.

참고

외부 스테이지(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	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*로 반환되며 여기서 *는 열의 위치입니다. PARSE_HEADER = TRUE인 경우 SKIP_HEADER 옵션은 지원되지 않습니다.
- 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' 모두 스테이지의 지정된 파일에 중첩된 데이터 타입이 포함된 경우 첫 번째 수준의 중첩만 지원되며 더 깊은 수준은 지원되지 않습니다.

예¶

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        |
+-------------+---------+----------+---------------------+--------------------------+----------+

Copy

이전 예와 유사하지만, 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        |
+-------------+---------+----------+---------------------+--------------------------+----------+

Copy

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        |
+-------------+---------+----------+---------------------+---------------------------------------------+----------+

Copy

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        |
+-------------+---------------+----------+---------------------------+--------------------------+----------+

Copy

스테이징된 JSON 파일에서 감지된 스키마를 사용하여 테이블을 생성합니다.

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

Copy

참고

ARRAY_AGG(OBJECT_CONSTRUCT())`에 대해 :code:`*`를 사용하면 반환된 결과가 128MB보다 큰 경우 오류가 발생할 수 있습니다. 더 큰 결과 세트의 경우 :code:`* 를 사용하지 않고 쿼리에 대해 필수 열 COLUMN NAME, TYPE 및 NULLABLE`만 사용하는 것이 좋습니다. :code:`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;

Copy

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'
    )
  );

Copy

출력:

+-------------+---------+----------+---------------------+--------------------------+----------+
| 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}
;

Copy

참고

ARRAY_AGG(OBJECT_CONSTRUCT())`에 대해 :code:`*`를 사용하면 반환된 결과가 128MB보다 큰 경우 오류가 발생할 수 있습니다. 더 큰 결과 세트의 경우 :code:`* 를 사용하지 않도록 하고 쿼리에 대해 필수 열 COLUMN NAME, TYPE 및 NULLABLE 만 사용하는 것이 좋습니다. WITHIN GROUP (ORDER BY order_id) 을 사용할 때 선택적 열 ORDER_ID 를 포함할 수 있습니다.