Step 5. Copy Data into the Target Tables¶
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 the file named contacts1.csv.gz into the mycsvtable table.
COPY INTO mycsvtable FROM @my_csv_stage/contacts1.csv.gz FILE_FORMAT = (FORMAT_NAME = mycsvformat) ON_ERROR = 'skip_file';Snowflake returns the following results indicating the data in
contacts1.csv.gzwas 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/contacts1.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
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';Snowflake returns the following results:
The data in
contacts1.csv.gzis ignored because you already loaded the data successfully.The data in the following files was loaded successfully:
contacts2.csv.gz
contacts4.csv.gz
contacts5.csv.gz
The data in
contacts3.csv.gzwas skipped due to 2 data errors. The next step in this tutorial addresses how to validate and fix the errors.+-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+ | 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 | +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
JSON¶
The following example loads data from the file named contacts.json.gz into the myjsontable table.
COPY INTO myjsontable FROM @my_json_stage/contacts.json.gz FILE_FORMAT = (FORMAT_NAME = myjsonformat) ON_ERROR = 'skip_file';
Snowflake returns the following results indicating the data in contacts1.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 | |------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | myjsontable/contacts.json.gz | LOADED | 3 | 3 | 1 | 0 | NULL | NULL | NULL | NULL | +------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
Next: Step 6. Resolve Data Load Errors Related to Data Issues