教程：使用 COPY 从 Amazon S3 批量加载

简介

本教程介绍如何将数据从现有 Amazon Simple Storage Service (Amazon S3) 桶中的文件中加载到表中。在本教程中，您将学习如何进行以下操作：

  • 创建用于描述数据文件的命名文件格式。

  • 创建命名暂存区对象。

  • 将 S3 桶中的数据加载到 Snowflake 表中。

  • 解决数据文件中的错误。

本教程介绍如何加载 CSV 和 JSON 数据。

先决条件

本教程假设如下：

  • 您有一个配置为使用 Amazon Web Services (AWS) 的 Snowflake 账户以及具有授予创建数据库、表和虚拟仓库对象所需权限的角色的用户。

  • 您已经安装了 SnowSQL。

请参阅 20 分钟学会使用 Snowflake 以获得满足这些要求的说明。

Snowflake 在公共 Amazon S3 桶中提供示例数据文件以供本教程使用。但在开始之前，您需要为本教程创建数据库、表和虚拟仓库。这些是大多数 Snowflake 活动所需的基本 Snowflake 对象。

关于示例数据文件

Snowflake 提供暂存于公共 S3 桶中的示例数据文件。

备注

在常规使用中，您可以使用 AWS 管理控制台、AWS 命令​​行界面或等效的客户端应用程序暂存自己的数据文件。有关说明，请参阅 Amazon Web Services (https://aws.amazon.com/console/) 文档。

示例数据文件包含以下格式的示例联系信息：

  • 包含一个标题行和五条记录的 CSV 文件。字段分隔符是竖线字符 (|)。以下示例显示了一个标题行和一条记录：

    ID|lastname|firstname|company|email|workphone|cellphone|streetaddress|city|postalcode
6|Reed|Moses|Neque Corporation|eget.lacus@facilisis.com|1-449-871-0780|1-454-964-5318|Ap #225-4351 Dolor Ave|Titagarh|62631
    Copy

  • JSON 格式的单个文件包含一个数组和三个对象。下面是一个包含其中一个对象的数组示例：

    [
 {
   "customer": {
     "address": "509 Kings Hwy, Comptche, Missouri, 4848",
     "phone": "+1 (999) 407-2274",
     "email": "blankenship.patrick@orbin.ca",
     "company": "ORBIN",
     "name": {
       "last": "Patrick",
       "first": "Blankenship"
     },
     "_id": "5730864df388f1d653e37e6f"
   }
 },
]
    Copy

创建数据库、表和仓库

执行以下语句创建本教程所需的数据库、两个表（csv 和 json 数据表）和一个虚拟仓库。完成本教程后，您可以删除这些对象。

CREATE OR REPLACE DATABASE mydatabase;

CREATE OR REPLACE TEMPORARY TABLE mycsvtable (
     id INTEGER,
     last_name STRING,
     first_name STRING,
     company STRING,
     email STRING,
     workphone STRING,
     cellphone STRING,
     streetaddress STRING,
     city STRING,
     postalcode STRING);

CREATE OR REPLACE TEMPORARY TABLE myjsontable (
     json_data VARIANT);

CREATE OR REPLACE WAREHOUSE mywarehouse WITH
     WAREHOUSE_SIZE='X-SMALL'
     AUTO_SUSPEND = 120
     AUTO_RESUME = TRUE
     INITIALLY_SUSPENDED=TRUE;
Copy

请注意以下事项：

  • CREATE DATABASE 语句创建一个数据库。数据库自动包含一个名为“public”的架构。

  • CREATE TABLE 语句为 CSV 和 JSON 数据创建目标表。这些表是临时的，也就是说，它们仅在用户会话期间持续存在，并且对其他用户不可见。

  • CREATE WAREHOUSE 语句创建一个最初暂停的仓库。该语句还设置 AUTO_RESUME = true，这将在您执行需要计算资源的 SQL 语句时自动启动仓库。

第 1 步：创建文件格式对象

当您将数据文件从 S3 桶加载到表中时，您必须描述文件的格式并指定如何解释和处理文件中的数据。例如，如果要从 CSV 文件加载竖线分隔数据，则必须指定该文件使用带有竖线符号的 CSV 格式作为分隔符。

执行 COPY INTO <table> 命令时，请指定此格式信息。您可以将此信息指定为命令中的选项（例如 TYPE = CSV、FIELD_DELIMITER = '|' 等），也可以指定包含此格式信息的文件格式对象。您可以使用 CREATE FILE FORMAT 命令创建已命名的文件格式对象。

在此步骤中，您将创建文件格式对象，该对象用于描述供本教程使用的示例 CSV 和 JSON 数据的数据格式。

为 CSV 数据创建文件格式对象

执行 CREATE FILE FORMAT 命令以创建 mycsvformat 文件格式。

CREATE OR REPLACE FILE FORMAT mycsvformat
   TYPE = 'CSV'
   FIELD_DELIMITER = '|'
   SKIP_HEADER = 1;
Copy

其中：

  • TYPE = 'CSV' 指示源文件格式类型。CSV 是默认文件格式类型。

  • FIELD_DELIMITER = '|' 指示“|”字符是字段分隔符。默认值为“,”。

  • SKIP_HEADER = 1 指示源文件包含一个标题行。此 COPY 命令在加载数据时跳过这些标题行。默认值为 0。

为 JSON 数据创建文件格式对象

执行 CREATE FILE FORMAT 命令以创建 myjsonformat 文件格式。

CREATE OR REPLACE FILE FORMAT myjsonformat
  TYPE = 'JSON'
  STRIP_OUTER_ARRAY = TRUE;
Copy

其中：

  • TYPE = 'JSON' 指示源文件格式类型。

  • STRIP_OUTER_ARRAY = TRUE 指示 COPY 命令在将数据加载到表时排除根括号 ([])。

第 2 步：创建暂存区对象

暂存区指定数据文件的存储位置（即“暂存”），以便可以将文件中的数据加载到表中。命名的 外部暂存区 是由 Snowflake 管理的云存储位置。外部暂存区引用存储在 S3 桶中的数据文件。在本例中，我们将创建一个暂存区，该暂存区会引用完成本教程所需的示例数据文件。

如果希望多个用户或进程上传文件，则创建命名的外部暂存区很有用。如果您计划暂存数据文件以便仅由您自己加载，或仅加载到单个表中，则您可能更喜欢使用用户暂存区或表暂存区。 有关信息，请参阅 从 Amazon S3 批量加载。

在此步骤中，为不同类型的示例数据文件创建命名的暂存区。

为 CSV 数据文件创建暂存区

执行 CREATE STAGE 以创建 my_csv_stage 暂存区：

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat
  URL = 's3://snowflake-docs';
Copy

为 JSON 数据文件创建暂存区

执行 CREATE STAGE 以创建 my_json_stage 暂存区：

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat
  URL = 's3://snowflake-docs';
Copy

备注

在常规使用中，如果要创建指向私有数据文件的暂存区，则应引用由账户管理员（即具有 ACCOUNTADMIN 角色的用户）或具有全局 CREATE INTEGRATION 权限的角色使用 CREATE STORAGE INTEGRATION 创建的存储集成：

CREATE OR REPLACE STAGE external_stage
  FILE_FORMAT = mycsvformat
  URL = 's3://private-bucket'
  STORAGE_INTEGRATION = myint;
Copy

第 3 步：将数据复制到目标表中

执行 COPY INTO <table> 以将暂存数据加载到目标表中。

CSV

要从示例 CSV 文件加载数据，请执行以下操作：

  1. 首先从 mycsvtable 表中名为 contacts1.csv 的 /tutorials/dataloading/ 前缀（文件夹）内的某个文件加载数据。执行以下命令：

    COPY INTO mycsvtable
  FROM @my_csv_stage/tutorials/dataloading/contacts1.csv
  ON_ERROR = 'skip_file';
    Copy

    其中：

    • FROM 子句指定暂存数据文件的位置（暂存区名称后跟文件名）。

    • ON_ERROR = 'skip_file' 子句指定当 COPY 命令在文件中遇到错误时要执行的操作。在这种情况下，当该命令在文件中的任何记录中遇到数据错误时，将跳过该文件。如果未指定 ON_ERROR 子句，则默认情况下会使用 abort_statement，该语句会在文件中的任何记录出现第一个错误时中止 COPY 命令。

    COPY 命令返回一个结果，其中显示已复制的文件的名称和相关信息：

    +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                                                    | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts1.csv | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
    Copy

  2. 加载 mycsvtable 表中其余的暂存文件。

    以下示例使用模式匹配，将与正则表达式 .*contacts[1-5].csv 匹配的文件中的数据加载到 mycsvtable 表中。

    COPY INTO mycsvtable
  FROM @my_csv_stage/tutorials/dataloading/
  PATTERN='.*contacts[1-5].csv'
  ON_ERROR = 'skip_file';
    Copy

    其中， PATTERN 子句指定该命令应从与此正则表达式 .*contacts[1-5].csv 匹配的文件名中加载数据。

    COPY 命令返回一个结果，其中显示已复制的文件的名称和相关信息：

    +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
| file                                                    | status      | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                                                                                                                          | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts2.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
| s3://snowflake-docs/tutorials/dataloading/contacts3.csv | LOAD_FAILED |           5 |           0 |           1 |           2 | Number of columns in file (11) does not match that of the corresponding table (10), use file format option error_on_column_count_mismatch=false to ignore this error |                3 |                     1 | "MYCSVTABLE"[11]        |
| s3://snowflake-docs/tutorials/dataloading/contacts4.csv | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
| s3://snowflake-docs/tutorials/dataloading/contacts5.csv | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
+---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
    Copy

    请注意结果中的以下亮点：

    • contacts1.csv 中的数据将会忽略，因为您已成功加载数据。

    • 已成功加载以下文件中的数据：contacts2.csv、contacts4.csv 和 contacts5.csv。

    • 由于 2 个数据错误， contacts3.csv 中的数据已跳过。本教程的下一步将介绍如何验证和修复错误。

JSON

将 contacts.json 暂存数据文件加载到 myjsontable 表。

COPY INTO myjsontable
  FROM @my_json_stage/tutorials/dataloading/contacts.json
  ON_ERROR = 'skip_file';
Copy

COPY 返回一个结果，其中显示已复制的文件的名称和相关信息：

+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                                                    | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| s3://snowflake-docs/tutorials/dataloading/contacts.json | LOADED |           3 |           3 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
Copy

第 5 步：清理

恭喜，您已成功完成本教程。

教程清理（可选）

执行以下 DROP <object> 命令，将系统恢复到教程开始前的状态：

DROP DATABASE IF EXISTS mydatabase;
DROP WAREHOUSE IF EXISTS mywarehouse;
Copy

删除数据库会自动移除所有子数据库对象，例如表。

其他数据加载教程

语言: 中文