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
FROMclause specifies the location of the staged data file (stage name followed by the file name).The
ON_ERRORclause 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
mycsvtabletable.The following example uses pattern matching to load data from all files that match the regular expression
.*contacts[1-5].csv.gzinto themycsvtabletable.COPY INTO mycsvtable FROM @my_csv_stage FILE_FORMAT = (FORMAT_NAME = mycsvformat) PATTERN='.*contacts[1-5].csv.gz' ON_ERROR = 'skip_file';
Where the
PATTERNclause 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.gzis 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.gzwas 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 |
+------------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+