Step 3. Copy data into the target table¶
Execute COPY INTO <table> to load staged data into the target tables.
CSV¶
To load the data from the sample CSV files:
Start by loading the data from one of the files in the
/tutorials/dataloading/prefix (folder) namedcontacts1.csvin themycsvtabletable. Execute the following:COPY INTO mycsvtable FROM @my_csv_stage/tutorials/dataloading/contacts1.csv ON_ERROR = 'skip_file';
Where:
The
FROMclause specifies the location of the staged data file (stage name followed by the file name).The
ON_ERROR = 'skip_file'clause specifies what to do when the COPY command encounters errors in the files. In this case, when the command encounters a data error on any of the records in a file, it skips the file. If you do not specify an ON_ERROR clause, the default isabort_statement, which aborts the COPY command on the first error encountered on any of the records in a 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 | |---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | s3://snowflake-docs/tutorials/dataloading/contacts1.csv | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | +---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
Load the rest of the staged files in the
mycsvtabletable.The following example uses pattern matching to load data from files that match the regular expression
.*contacts[1-5].csvinto themycsvtabletable.COPY INTO mycsvtable FROM @my_csv_stage/tutorials/dataloading/ PATTERN='.*contacts[1-5].csv' ON_ERROR = 'skip_file';
Where the
PATTERNclause specifies that the command should load data from the filenames matching this regular expression.*contacts[1-5].csv.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 | |---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------| | 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 | +---------------------------------------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
Note the following highlights in the result:
The data in
contacts1.csvis ignored because you already loaded the data successfully.The data in these files was loaded successfully:
contacts2.csv,contacts4.csv, andcontacts5.csv.The data in
contacts3.csvwas 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 staged data file into the myjsontable table.
COPY INTO myjsontable FROM @my_json_stage/tutorials/dataloading/contacts.json ON_ERROR = 'skip_file';
The COPY 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 |
|---------------------------------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------|
| 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