教程：使用 COPY 从本地文件系统批量加载¶

下载示例数据文件¶

在本教程中，您需要下载 Snowflake 提供的示例数据文件。

要下载并解压缩示例数据文件，请执行以下操作：

1. 右键点击归档文件的名称，data-load-internal.zip，并将链接/文件保存到本地文件系统。

2. 解压缩示例文件。本教程假设已将文件解压到以下目录：

• Linux/macOS：/tmp/load

• Windows：C:\tempload

这些数据文件包括以下格式的示例联系人数据：

• 包含一个标题行和五条记录的 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 a database. A database automatically includes a schema named 'public'.

CREATE OR REPLACE DATABASE mydatabase;

/* Create target tables for CSV and JSON data. The tables are temporary, meaning they persist only for the duration of the user session and are not visible to other users. */

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

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

Copy

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

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

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

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

其中：

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

其中：

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

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

为 CSV 数据文件创建暂存区¶

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

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat;

请注意，如果在创建暂存区时指定 FILE_FORMAT 选项，则无需在用于从暂存区加载数据的 COPY 命令中指定相同的 FILE_FORMAT 选项。

为 JSON 数据文件创建暂存区¶

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

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat;

暂存 CSV 示例数据文件¶

执行 PUT 命令以从本地文件系统上传 CSV 文件。

• Linux 或 macOS

  PUT file:///tmp/load/contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts*.csv @my_csv_stage AUTO_COMPRESS=TRUE;
  

  Copy

让我们仔细看看命令：

• file://<file-path>[/]contacts*.csv 指定本地计算机上要转移至暂存区的文件完整目录路径和名称。注意，允许使用文件系统通配符。

• @my_csv_stage 是暂存数据的暂存区名称。

• auto_compress=true; 指示命令在暂存时压缩数据。这也是默认设置。

该命令返回以下结果，显示暂存文件：

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts1.csv | contacts1.csv.gz |         694 |         506 | NONE               | GZIP               | UPLOADED |         |
| contacts2.csv | contacts2.csv.gz |         763 |         565 | NONE               | GZIP               | UPLOADED |         |
| contacts3.csv | contacts3.csv.gz |         771 |         567 | NONE               | GZIP               | UPLOADED |         |
| contacts4.csv | contacts4.csv.gz |         750 |         561 | NONE               | GZIP               | UPLOADED |         |
| contacts5.csv | contacts5.csv.gz |         887 |         621 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

暂存 JSON 示例数据文件¶

执行 PUT 命令将本地文件系统中的 JSON 文件上传到指定的暂存区。

• Linux 或 macOS

  PUT file:///tmp/load/contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

• Windows

  PUT file://C:\temp\load\contacts.json @my_json_stage AUTO_COMPRESS=TRUE;
  

  Copy

该命令返回以下结果，显示暂存文件：

+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source        | target           | source_size | target_size | source_compression | target_compression | status   | message |
|---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| contacts.json | contacts.json.gz |         965 |         446 | NONE               | GZIP               | UPLOADED |         |
+---------------+------------------+-------------+-------------+--------------------+--------------------+----------+---------+

列出暂存文件（可选）¶

您可以使用 LIST 命令列出暂存文件。

CSV¶

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

1. 首先从其中一个文件 (contacts1.csv.gz) 加载数据。执行以下命令：

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts1.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

   其中：

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

   • ON_ERROR 子句指定当 COPY 命令在文件中遇到错误时要执行的操作。默认情况下，该命令在遇到第一个错误时停止加载数据；但是，我们指示它跳过任何包含错误的文件并继续加载下一个文件。请注意，这仅用于说明目的；本教程中的任何文件均不包含错误。

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

   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   | file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
   |-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts1.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   

   Copy

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

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

   COPY INTO mycsvtable
     FROM @my_csv_stage
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     PATTERN='.*contacts[1-5].csv.gz'
     ON_ERROR = 'skip_file';
   

   Copy

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

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

   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   | file                        | status      | rows_parsed | rows_loaded | error_limit | errors_seen | first_error                                                                                                                                                          | first_error_line | first_error_character | first_error_column_name |
   |-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------|
   | mycsvtable/contacts2.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts3.csv.gz | 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]        |
   | mycsvtable/contacts4.csv.gz | LOADED      |           5 |           5 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   | mycsvtable/contacts5.csv.gz | LOADED      |           6 |           6 |           1 |           0 | NULL                                                                                                                                                                 |             NULL |                  NULL | NULL                    |
   +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
   

   Copy

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

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

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

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

JSON¶

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

COPY INTO myjsontable
  FROM @my_json_stage/contacts.json.gz
  FILE_FORMAT = (FORMAT_NAME = myjsonformat)
  ON_ERROR = 'skip_file';

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

+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                         | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| myjsontable/contacts.json.gz | LOADED |           3 |           3 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

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

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

1. 检索查询 ID。

   1. 登录 Snowsight。

   2. 确保 Snowsight 中的角色与 SnowSQL 中用于运行本教程 SQL 语句的角色相同。

   3. 选择 Monitoring » Query History。

   4. 选择特定 COPY INTO 命令行以打开查询信息窗格。

   5. 复制 Query ID 值。

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

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

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

      Copy

   2. 查询 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                  |
      +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
      

      Copy

结果显示 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 行中，在连字符之后引入了一个额外的管道 (|) 字符，打破了记录。

  

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

1. 在本地环境的 contacts3.csv 文件中手动修复记录中的错误。

2. 使用 PUT 命令将修改后的数据文件上传到暂存区。修改后的文件将覆盖现有暂存文件。

   • Linux 或 macOS：

     PUT file:///tmp/load/contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

   • Windows：

     PUT file://C:\temp\load\contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
     

     Copy

3. 将暂存文件中的数据复制到表中。

   COPY INTO mycsvtable
     FROM @my_csv_stage/contacts3.csv.gz
     FILE_FORMAT = (FORMAT_NAME = mycsvformat)
     ON_ERROR = 'skip_file';
   

   Copy

Snowflake 返回以下结果，指示 contacts3.csv.gz 中的数据已成功加载。

+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
| file                        | status | rows_parsed | rows_loaded | error_limit | errors_seen | first_error | first_error_line | first_error_character | first_error_column_name |
|-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| mycsvtable/contacts3.csv.gz | LOADED |           5 |           5 |           1 |           0 |        NULL |             NULL |                  NULL |                    NULL |
+-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+

Copy

验证加载的数据¶

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

教程清理（可选）¶

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

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

Copy

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

其他数据加载教程¶

• 20 分钟学会使用 Snowflake

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