カテゴリ:: テーブル関数

INFER_SCHEMA¶

半構造化データを含む一連のステージングデータファイル内のファイルメタデータスキーマを自動的に検出し、列定義を取得します。

GENERATE_COLUMN_DESCRIPTION 関数は、 INFER_SCHEMA 関数の出力に基づいて構築され、ステージングされたファイルの列定義に基づいて、新しいテーブル、外部テーブル、またはビュー（適切な CREATE <オブジェクト> コマンドを使用）の作成を簡素化します。

CREATE TABLE、 CREATE EXTERNAL TABLE、または CREATE ICEBERG TABLE コマンドを USING TEMPLATE 句とともに実行すると、 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 => '...'

ファイルが保存される内部ステージまたは外部ステージの名前。必要に応じて、クラウドストレージの場所にある1つ以上のファイルへのパスを含めます。それ以外の場合、 INFER_SCHEMA 関数は、ステージにあるすべてのサブディレクトリ内のファイルをスキャンします。

`@[namespace.]int_stage_name[/path][/filename]`	ファイルは指定された名前付き内部ステージにあります。
`@[namespace.]ext_stage_name[/path][/filename]`	ファイルは指定された名前付き外部ステージにあります。
`@~[/path][/filename]`	ファイルは現在のユーザーのステージにあります。

注釈

この SQL 関数は、名前付きステージ（内部または外部）とユーザーステージのみをサポートします。テーブルステージには対応していません。

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

半構造化データを含むステージングされたファイルのセット内にある1つ以上のファイル（コンマで区切られた）のリストを指定します。ファイルは、コマンドで指定された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	TEXT	ステージングされたファイルの列の名前。
TYPE	TEXT	列のデータ型。
NULLABLE	BOOLEAN	列の行が、値の代わりに NULL を保存できるかどうかを指定します。現在、列の推定NULL可能性は、スキャンされたセット内の1つのデータファイルに適用できますが、他のファイルには適用できません。
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' の両方で、ステージで指定されたファイルにネストされたデータ型が含まれている場合、ネストの最初のレベルのみがサポートされ、それ以上のレベルはサポートされません。

例¶

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()) に * を使用すると、返される結果が 16MB よりも大きいとエラーになる場合があります。大きな結果セットの場合は * の使用を避け、クエリにはクエリに必要な列、 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;

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

ステージングされた JSON ファイルから検出されたスキーマを使用して 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()) に対して * を使用すると、返される結果が 16MB より大きいとエラーになる場合があります。大きなクエリ結果セットでは * の使用を避け、必要な列、 COLUMN NAME、 TYPE、 NULLABLE のみをクエリに使用することをお勧めします。 WITHIN GROUP (ORDER BY order_id) を使用する場合は、オプションの列 ORDER_ID を含めることができます。