Step 3. Copy Data Into the Target Table¶
Execute COPY INTO <table> to load your staged data into the target table.
The following examples include the ON_ERROR = 'skip_file' parameter value. If the command encounters a data error on any of the records, it skips the file. If you do not specify an ON_ERROR value, the default is ON_ERROR = 'abort_statement', which aborts the COPY command on the first error encountered on any of the records in a file.
Note
Loading data into tables requires a warehouse. If you created a warehouse by following the instructions in the prerequisites, skip to the next section. If you are using a warehouse that is not configured to auto resume, execute ALTER WAREHOUSE to resume the warehouse. Note that starting the warehouse could take up to five minutes.
ALTER WAREHOUSE mywarehouse RESUME;
CSV¶
The following example loads data from a single file in the /tutorials/dataloading/ prefix (folder) named contacts1.csv into the mycsvtable table.
COPY INTO mycsvtable FROM @my_csv_stage/tutorials/dataloading/contacts1.csv ON_ERROR = 'skip_file';Snowflake returns the following results. The data in
contacts1.csvis loaded successfully.+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | 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 | +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
The following example uses pattern matching to load data from files that match the regular expression .*contacts[1-5].csv into the mycsvtable table.
COPY INTO mycsvtable FROM @my_csv_stage/tutorials/dataloading/ PATTERN='.*contacts[1-5].csv' ON_ERROR = 'skip_file';Snowflake returns the following results:
The data in
contacts1.csvis ignored because we already loaded the data successfully.The data in the following files is loaded successfully:
contacts2.csv
contacts4.csv
contacts5.csv
The data in
contacts3.csvis skipped due to 2 data errors. The next step in this tutorial addresses the errors.+---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+ | 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 | +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
JSON¶
COPY INTO myjsontable FROM @my_json_stage/tutorials/dataloading/contacts.json ON_ERROR = 'skip_file';
Snowflake returns the following results. The data in contacts.json is loaded successfully.
+---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+ | 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 | +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
Next: Step 4. Resolve Data Load Errors Related to Data Issues