Tutorial: Bulk loading from a local file system using COPY¶

Downloading the sample data files¶

For this tutorial you need to download the sample data files provided by Snowflake.

To download and unzip the sample data files:

1. Right-click the name of the archive file, data-load-internal.zip and save the link/file to your local file system.

2. Unzip the sample files. The tutorial assumes you unpacked files in to the following directories:

• Linux/macOS: /tmp/load

• Windows: C:\tempload

These data files include sample contact data in the following formats:

• CSV files that contain a header row and five records. The field delimiter is the pipe (|) character. The following example shows a header row and one record:

  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

• A single file in JSON format that contains one array and three objects. The following is an example of an array that contains one of the objects:

  [
   {
     "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

Creating the database, tables, and warehouse¶

Execute the following statements to create a database, two tables (for csv and json data), and a virtual warehouse needed for this tutorial. After you complete the tutorial, you can drop these objects.

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

The CREATE WAREHOUSE statement sets up the warehouse to be suspended initially. The statement also sets AUTO_RESUME = true, which starts the warehouse automatically when you execute SQL statements that require compute resources.

Creating a file format object for CSV data¶

Execute the CREATE FILE FORMAT command to create the mycsvformat file format.

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

Where:

• TYPE = 'CSV' indicates the source file format type. CSV is the default file format type.

• FIELD_DELIMITER = '|' indicates the ‘|’ character is a field separator. The default value is ‘,’.

• SKIP_HEADER = 1 indicates the source file includes one header line. The COPY command skips these header lines when loading data. The default value is 0.

Creating a file format object for JSON data¶

Execute the CREATE FILE FORMAT command to create the myjsonformat file format.

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

Where:

• TYPE = 'JSON' indicates the source file format type.

• STRIP_OUTER_ARRAY = TRUE directs the COPY command to exclude the root brackets ([]) when loading data to the table.

Creating a stage for CSV data files¶

Execute CREATE STAGE to create the my_csv_stage stage:

CREATE OR REPLACE STAGE my_csv_stage
  FILE_FORMAT = mycsvformat;

Note that if you specify the FILE_FORMAT option when creating the stage, it is not necessary to specify the same FILE_FORMAT option in the COPY command used to load data from the stage.

Creating a stage for JSON data files¶

Execute CREATE STAGE to create the my_json_stage stage:

CREATE OR REPLACE STAGE my_json_stage
  FILE_FORMAT = myjsonformat;

Staging the CSV sample data files¶

Execute the PUT command to upload the CSV files from your local file system.

• Linux or 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

Let us take a closer look at the command:

• file://<file-path>[/]contacts*.csv specifies the full directory path and names of the files on your local machine to stage. Note that file system wildcards are allowed.

• @my_csv_stage is the stage name where to stage the data.

• auto_compress=true; directs the command to compress the data when staging. This is also the default.

The command returns the following result, showing the staged files:

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

Staging the JSON sample data files¶

Execute the PUT command to upload the JSON file from your local file system to the named stage.

• Linux or 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

The command returns the following result, showing the staged files:

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

List the staged files (optional)¶

You can list the staged files by using the LIST command.

CSV¶

To load the data from the sample CSV files:

1. Start by loading the data from one of the files (contacts1.csv.gz). Execute the following:

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

   Copy

   Where:

   • The FROM clause specifies the location of the staged data file (stage name followed by the file name).

   • The ON_ERROR clause specifies what to do when the COPY command encounters errors in the files. By default, the command stops loading data when the first error is encountered; however, we’ve instructed it to skip any file containing an error and move on to loading the next file. Note that this is just for illustration purposes; none of the files in this tutorial contain errors.

   The COPY command returns a result showing the name of the file copied and related information:

   +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
   | 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. Load the rest of the staged files in the mycsvtable table.

   The following example uses pattern matching to load data from all files that match the regular expression .*contacts[1-5].csv.gz into the mycsvtable table.

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

   Copy

   Where the PATTERN clause specifies that the command should load data from the filenames matching this regular expression (.*employees0[1-5].csv.gz).

   The COPY command returns a result showing the name of the file copied and related information:

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

   Note the following highlights in the result:

   • The data in contacts1.csv.gz is ignored because you already loaded the data successfully.

   • The data in these files was loaded successfully: contacts2.csv.gz, contacts4.csv.gz, and contacts5.csv.gz.

   • The data in contacts3.csv.gz was skipped due to 2 data errors. The next step in this tutorial addresses how to validate and fix the errors.

JSON¶

Load the contacts.json.gz staged data file into the myjsontable table.

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

The COPY command returns a result showing the name of the file copied and related information:

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

Validate the sample data files and retrieve any errors¶

You first need the query ID associated with the COPY INTO command that you previously executed. You then call the VALIDATE function, specifying the query ID.

1. Retrieve the query ID.

   1. Sign in to Snowsight.

   2. Make sure the role in Snowsight is the same as the role you are using in SnowSQL to run SQL statements for this tutorial.

   3. Select Monitoring » Query History.

   4. Select the row for the specific COPY INTO command to open the query information pane.

   5. Copy the Query ID value.

2. Validate the COPY INTO command execution, represented by the query ID, and save errors to a new table named save_copy_errors.

   1. In SnowSQL, execute the following command. Replace query_id with the Query ID value.

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

      Copy

   2. Query the save_copy_errors table.

      SELECT * FROM SAVE_COPY_ERRORS;
      

      Copy

      The query returns the following results:

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

The result shows two data errors in mycsvtable/contacts3.csv.gz:

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

  In Row 1, a hyphen was mistakenly replaced with the pipe (|) character, the data file delimiter, effectively creating an additional column in the record.

  

• Field delimiter '|' found while expecting record delimiter 'n'

  In Row 5, an additional pipe (|) character was introduced after a hyphen, breaking the record.

  

Fix the errors and load the data files again¶

1. Fix the errors in the records manually in the contacts3.csv file in your local environment.

2. Use the PUT command to upload the modified data file to the stage. The modified file overwrites the existing staged file.

   • Linux or 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 the data from the staged files into the tables.

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

   Copy

Snowflake returns the following results, indicating the data in contacts3.csv.gz was loaded successfully.

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

Verify the loaded data¶

Execute a SELECT query to verify that the data was loaded successfully.

Tutorial clean up (optional)¶

Execute the following DROP <object> commands to return your system to its state before you began the tutorial:

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

Copy

Dropping the database automatically removes all child database objects such as tables.

Other data loading tutorials¶

• Snowflake in 20 minutes

• Tutorial: Bulk loading from Amazon S3 using COPY