Step 4. Copy Data into the Target Tables¶
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 (
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';
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 | +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
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 themycsvtable
table.COPY INTO mycsvtable FROM @my_csv_stage FILE_FORMAT = (FORMAT_NAME = mycsvformat) PATTERN='.*contacts[1-5].csv.gz' ON_ERROR = 'skip_file';
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 | +-----------------------------+-------------+-------------+-------------+-------------+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------------------+-----------------------+-------------------------+
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
, andcontacts5.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 |
+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+