教程：使用 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

Create the database, tables, and warehouse¶

执行以下语句创建本教程所需的数据库、两个表（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 语句时自动启动仓库。

创建文件格式对象¶

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

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

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

Create a file format object for CSV data¶

执行 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。

Create a file format object for JSON data¶

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

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

Copy

其中：

TYPE = 'JSON' 指示源文件格式类型。
STRIP_OUTER_ARRAY = TRUE 指示 COPY 命令在将数据加载到表时排除根括号 ([])。

创建命名暂存区对象。¶

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

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

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

Create a stage for CSV data files¶

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

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

Copy

Create a stage for JSON data files¶

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

将数据复制到目标表中¶

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

CSV¶

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

首先从 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 |
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

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

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

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 INTO 命令在遇到第一个错误时跳过加载其中一个文件。您需要找到所有错误。在此步骤中，您将使用 VALIDATE 函数对之前执行的 COPY INTO 命令进行验证，并返回所有错误。

验证示例数据文件并检索所有错误¶

首先，需要检索与之前执行的 COPY INTO 命令关联的查询 ID。然后，调用 VALIDATE 函数，指定查询 ID。

检索查询 ID。
1. Sign in to Snowsight.
2. 确保 Snowsight 中的角色与 SnowSQL 中用于运行本教程 SQL 语句的角色相同。
3. In the navigation menu, select Monitoring » Query History.
4. 选择特定 COPY INTO 命令行以打开查询信息窗格。
5. 复制 Query ID 值。

验证由查询 ID 表示的 COPY INTO 命令执行，并将错误保存到名为 save_copy_errors 的新表中。

在 SnowSQL 中，执行以下命令。将 query_id 替换为 Query ID 值。

CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>'));

Copy

查询 save_copy_errors 表。

SELECT * FROM SAVE_COPY_ERRORS;

Copy

查询将返回以下结果：

+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| ERROR                                                                                                                                                                | FILE                                | LINE | CHARACTER | BYTE_OFFSET | CATEGORY |   CODE | SQL_STATE | COLUMN_NAME                   | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD                                                                                                                                     |
|----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------|
| 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 | mycsvtable/contacts3.csv.gz         |    3 |         1 |         234 | parsing  | 100080 |     22000 | "MYCSVTABLE"[11]              |          1 |              2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 |
| Field delimiter '|' found while expecting record delimiter '\n'                                                                                                      | mycsvtable/contacts3.csv.gz         |    5 |       125 |         625 | parsing  | 100016 |     22000 | "MYCSVTABLE"["POSTALCODE":10] |          4 |              5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188                  |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+

结果显示 mycsvtable/contacts3.csv.gz 中的两个数据错误：

Number of columns in file (11) does not match that of the corresponding table (10)

在第 1 行中，连字符被错误地替换为管道 (|) 字符，即数据文件分隔符，从而有效地在记录中创建了一个附加列。
Field delimiter '|' found while expecting record delimiter 'n'

在第 5 行中，在连字符之后引入了一个额外的管道 (|) 字符，打破了记录。

修复错误并重新加载数据文件¶

在常规使用中，您将手动修复有问题的记录，并将它们写入新的数据文件。然后，将修复的数据文件暂存到 S3 桶，并尝试从这些文件中重新加载数据。在本教程中，您使用的是由 Snowflake 提供且并未更正的示例数据。

验证加载的数据¶

执行 SELECT 语句，以验证数据是否已成功加载。

CSV¶

SELECT * FROM mycsvtable;

Copy

查询将返回以下结果：

+----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------+
| ID | LAST_NAME | FIRST_NAME | 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 |
|  7 | Audrey    | Franks     | Arcu Eu Limited                  | eu.dui@aceleifendvitae.org             | 1-527-945-8935 | 1-263-127-1173 | Ap #786-9241 Mauris Road       | Bergen           |      81958 |
|  8 | Jakeem    | Erickson   | A Ltd                            | Pellentesque.habitant@liberoProinmi.ca | 1-381-591-9386 | 1-379-391-9490 | 319-1703 Dis Rd.               | Pangnirtung      |      62399 |
|  9 | Xaviera   | Brennan    | Bibendum Ullamcorper Limited     | facilisi.Sed.neque@dictum.edu          | 1-260-757-1919 | 1-211-651-0925 | P.O. Box 146, 8385 Vel Road    | Béziers          |      13082 |
| 10 | Francis   | Ortega     | Vitae Velit Egestas Associates   | egestas.rhoncus.Proin@faucibus.com     | 1-257-584-6487 | 1-211-870-2111 | 733-7191 Neque Rd.             | Chatillon        |      33081 |
| 16 | Aretha    | Sykes      | Lobortis Tellus Justo Foundation | eget@Naminterdumenim.net               | 1-670-849-1866 | 1-283-783-3710 | Ap #979-2481 Dui. Av.          | Thurso           |      66851 |
| 17 | Akeem     | Casey      | Pharetra Quisque Ac Institute    | dictum.eu@magna.edu                    | 1-277-657-0361 | 1-623-630-8848 | Ap #363-6074 Ullamcorper, Rd.  | Idar-Oberstei    |      30848 |
| 18 | Keelie    | Mendez     | Purus In Foundation              | Nulla.eu.neque@Aeneanegetmetus.co.uk   | 1-330-370-8231 | 1-301-568-0413 | 3511 Tincidunt Street          | Lanklaar         |      73942 |
| 19 | Lane      | Bishop     | Libero At PC                     | non@dapibusligula.ca                   | 1-340-862-4623 | 1-513-820-9039 | 7459 Pede. Street              | Linkebeek        |      89252 |
| 20 | Michelle  | Dickson    | Ut Limited                       | Duis.dignissim.tempor@cursuset.org     | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St.                 | Stornaway        |      61290 |
| 20 | Michelle  | Dickson    | Ut Limited                       | Duis.dignissim.tempor@cursuset.org     | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St.                 | Stornaway        |      61290 |
| 21 | Lance     | Harper     | Rutrum Lorem Limited             | Sed.neque@risus.com                    | 1-685-778-6726 | 1-494-188-6168 | 663-7682 Et St.                | Gisborne         |      73449 |
| 22 | Keely     | Pace       | Eleifend Limited                 | ante.bibendum.ullamcorper@necenim.edu  | 1-312-381-5244 | 1-432-225-9226 | P.O. Box 506, 5233 Aliquam Av. | Woodlands County |      61213 |
| 23 | Sage      | Leblanc    | Egestas A Consulting             | dapibus@elementum.org                  | 1-630-981-0327 | 1-301-287-0495 | 4463 Lorem Road                | Woodlands County |      33951 |
| 24 | Marny     | Holt       | Urna Nec Luctus Associates       | ornare@vitaeorci.ca                    | 1-522-364-3947 | 1-460-971-8360 | P.O. Box 311, 4839 Nulla Av.   | Port Coquitlam   |      36733 |
| 25 | Holly     | Park       | Mauris PC                        | Vestibulum.ante@Maecenasliberoest.org  | 1-370-197-9316 | 1-411-413-4602 | P.O. Box 732, 8967 Eu Avenue   | Provost          |      45507 |
|  1 | Imani     | Davidson   | At Ltd                           | nec@sem.net                            | 1-243-889-8106 | 1-730-771-0412 | 369-6531 Molestie St.          | Russell          |      74398 |
|  2 | Kelsie    | Abbott     | Neque Sed Institute              | lacus@pede.net                         | 1-467-506-9933 | 1-441-508-7753 | P.O. Box 548, 1930 Pede. Road  | Campbellton      |      27022 |
|  3 | Hilel     | Durham     | Pede Incorporated                | eu@Craspellentesque.net                | 1-752-108-4210 | 1-391-449-8733 | Ap #180-2360 Nisl. Street      | Etalle           |      84025 |
|  4 | Graiden   | Molina     | Sapien Institute                 | sit@fermentum.net                      | 1-130-156-6666 | 1-269-605-7776 | 8890 A, Rd.                    | Dundee           |      70504 |
|  5 | Karyn     | Howard     | Pede Ac Industries               | sed.hendrerit@ornaretortorat.edu       | 1-109-166-5492 | 1-506-782-5089 | P.O. Box 902, 5398 Et, St.     | Saint-Hilarion   |      26232 |
+----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+--------------------------------+------------------+------------+

JSON¶

SELECT * FROM myjsontable;

Copy

查询将返回以下结果：

+-----------------------------------------------------------------+
| JSON_DATA                                                       |
|-----------------------------------------------------------------|
| {                                                               |
|   "customer": {                                                 |
|     "_id": "5730864df388f1d653e37e6f",                          |
|     "address": "509 Kings Hwy, Comptche, Missouri, 4848",       |
|     "company": "ORBIN",                                         |
|     "email": "blankenship.patrick@orbin.ca",                    |
|     "name": {                                                   |
|       "first": "Blankenship",                                   |
|       "last": "Patrick"                                         |
|     },                                                          |
|     "phone": "+1 (999) 407-2274"                                |
|   }                                                             |
| }                                                               |
| {                                                               |
|   "customer": {                                                 |
|     "_id": "5730864d4d8523c8baa8baf6",                          |
|     "address": "290 Lefferts Avenue, Malott, Delaware, 1575",   |
|     "company": "SNIPS",                                         |
|     "email": "anna.glass@snips.name",                           |
|     "name": {                                                   |
|       "first": "Anna",                                          |
|       "last": "Glass"                                           |
|     },                                                          |
|     "phone": "+1 (958) 411-2876"                                |
|   }                                                             |
| }                                                               |
| {                                                               |
|   "customer": {                                                 |
|     "_id": "5730864e375e08523150fc04",                          |
|     "address": "756 Randolph Street, Omar, Rhode Island, 3310", |
|     "company": "ESCHOIR",                                       |
|     "email": "sparks.ramos@eschoir.co.uk",                      |
|     "name": {                                                   |
|       "first": "Sparks",                                        |
|       "last": "Ramos"                                           |
|     },                                                          |
|     "phone": "+1 (962) 436-2519"                                |
|   }                                                             |
| }                                                               |
+-----------------------------------------------------------------+

清理¶

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

教程清理（可选）¶

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

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

Copy

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

其他数据加载教程¶