类别：

表函数

INFER_SCHEMA

自动检测一组包含半结构化数据的暂存数据文件中的文件元数据架构，并检索列定义。

GENERATE_COLUMN_DESCRIPTION 函数基于 INFER_SCHEMA 函数输出构建，用以简化基于暂存文件的列定义创建新表、外部表或视图（使用适当的 CREATE <object> 命令）的过程。

可以执行带有 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 内部位置或外部位置。如果找不到任何指定的文件，查询将被中止。

可以指定的最大文件名数为 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，而不是值。目前，推断出的列的可空性可以应用于扫描集中的一个数据文件，但不能应用于其他数据文件。

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

检索 mystage 暂存区中 Parquet 文件的 Snowflake 列定义，并将 IGNORE_CASE 指定为 TRUE。在返回的输出中，所有列名都以大写字母形式进行检索。

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

备注

如果返回的结果大于 16MB，使用 ARRAY_AGG(OBJECT_CONSTRUCT()) 的 * 可能会导致错误。建议避免使用 * 导致较大的结果集，而仅将所需的列 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

备注

如果返回的结果大于 16MB，则在 ARRAY_AGG(OBJECT_CONSTRUCT()) 中使用 * 可能会导致错误。对于较大的结果集，我们建议避免使用 *，只使用必需的列、COLUMN NAME、TYPE 和:code:NULLABLE 进行查询。使用 ORDER_ID 时可以包含可选列 WITHIN GROUP (ORDER BY order_id)。

语言: 中文