Step 5. Resolve Data Load Errors¶
In the preceding step, the COPY INTO command skipped loading one of the files when it encountered the first error. You need to find all the errors and fix them. In this step, you use the VALIDATE function to validate the previous execution of the COPY INTO command and returns all errors.
Validate the Sample Data Files and Retrieve Any Errors¶
You first need the query ID associated with the COPY INTO command
that you previously executed. You then call the VALIDATE function,
specifying the query ID.
Retrieve the query ID.
Sign into Snowsight.
Make sure the role in Snowsight is the same as the role you are using in SnowSQL to run SQL statements for this tutorial.
Click Activity » Query History.
Select the row for the specific COPY INTO command to open the query information pane.
Copy the Query ID value.
Validate the COPY INTO command execution, represented by the query ID, and save errors to a new table named
save_copy_errors.In SnowSQL, execute the following command. Replace
query_idwith the Query ID value.CREATE OR REPLACE TABLE save_copy_errors AS SELECT * FROM TABLE(VALIDATE(mycsvtable, JOB_ID=>'<query_id>'));
Query the
save_copy_errorstable.SELECT * FROM SAVE_COPY_ERRORS;
The query returns the following results:
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+ | ERROR | FILE | LINE | CHARACTER | BYTE_OFFSET | CATEGORY | CODE | SQL_STATE | COLUMN_NAME | ROW_NUMBER | ROW_START_LINE | REJECTED_RECORD | |----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------| | 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 | mycsvtable/contacts3.csv.gz | 3 | 1 | 234 | parsing | 100080 | 22000 | "MYCSVTABLE"[11] | 1 | 2 | 11|Ishmael|Burnett|Dolor Elit Pellentesque Ltd|vitae.erat@necmollisvitae.ca|1-872|600-7301|1-513-592-6779|P.O. Box 975, 553 Odio, Road|Hulste|63345 | | Field delimiter '|' found while expecting record delimiter '\n' | mycsvtable/contacts3.csv.gz | 5 | 125 | 625 | parsing | 100016 | 22000 | "MYCSVTABLE"["POSTALCODE":10] | 4 | 5 | 14|Sophia|Christian|Turpis Ltd|lectus.pede@non.ca|1-962-503-3253|1-157-|850-3602|P.O. Box 824, 7971 Sagittis Rd.|Chattanooga|56188 | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------+------+-----------+-------------+----------+--------+-----------+-------------------------------+------------+----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+
The result shows two data errors in mycsvtable/contacts3.csv.gz:
Number of columns in file (11) does not match that of the corresponding table (10)In Row 1, a hyphen was mistakenly replaced with the pipe (
|) character, the data file delimiter, effectively creating an additional column in the record.
Field delimiter '|' found while expecting record delimiter '\n'In Row 5, an additional pipe (
|) character was introduced after a hyphen, breaking the record.
Fix the Errors and Load the Data Files Again¶
Fix the errors in the records manually in the
contacts3.csvfile in your local environment.Use the PUT command to upload the modified data file to the stage. The modified file overwrites the existing staged file.
Linux or macOS:
PUT file:///tmp/load/contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
Windows:
PUT file://C:\temp\load\contacts3.csv @my_csv_stage AUTO_COMPRESS=TRUE OVERWRITE=TRUE;
Copy the data from the staged files into the tables.
COPY INTO mycsvtable FROM @my_csv_stage/contacts3.csv.gz FILE_FORMAT = (FORMAT_NAME = mycsvformat) ON_ERROR = 'skip_file';
Snowflake returns the following results, indicating the data in contacts3.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 | |-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------| | mycsvtable/contacts3.csv.gz | LOADED | 5 | 5 | 1 | 0 | NULL | NULL | NULL | NULL | +-----------------------------+--------+-------------+-------------+-------------+-------------+-------------+------------------+-----------------------+-------------------------+
Verify the Loaded Data¶
Execute a SELECT query to verify that the data was loaded successfully.
CSV¶
SELECT * FROM mycsvtable;
The query returns the following results:
+----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+---------------------------------+------------------+------------+ | ID | LAST_NAME | FIRST_NAME | COMPANY | EMAIL | WORKPHONE | CELLPHONE | STREETADDRESS | CITY | POSTALCODE | |----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+---------------------------------+------------------+------------| | 6 | Reed | Moses | Neque Corporation | eget.lacus@facilisis.com | 1-449-871-0780 | 1-454-964-5318 | Ap #225-4351 Dolor Ave | Titagarh | 62631 | | 7 | Audrey | Franks | Arcu Eu Limited | eu.dui@aceleifendvitae.org | 1-527-945-8935 | 1-263-127-1173 | Ap #786-9241 Mauris Road | Bergen | 81958 | | 8 | Jakeem | Erickson | A Ltd | Pellentesque.habitant@liberoProinmi.ca | 1-381-591-9386 | 1-379-391-9490 | 319-1703 Dis Rd. | Pangnirtung | 62399 | | 9 | Xaviera | Brennan | Bibendum Ullamcorper Limited | facilisi.Sed.neque@dictum.edu | 1-260-757-1919 | 1-211-651-0925 | P.O. Box 146, 8385 Vel Road | Béziers | 13082 | | 10 | Francis | Ortega | Vitae Velit Egestas Associates | egestas.rhoncus.Proin@faucibus.com | 1-257-584-6487 | 1-211-870-2111 | 733-7191 Neque Rd. | Chatillon | 33081 | | 16 | Aretha | Sykes | Lobortis Tellus Justo Foundation | eget@Naminterdumenim.net | 1-670-849-1866 | 1-283-783-3710 | Ap #979-2481 Dui. Av. | Thurso | 66851 | | 17 | Akeem | Casey | Pharetra Quisque Ac Institute | dictum.eu@magna.edu | 1-277-657-0361 | 1-623-630-8848 | Ap #363-6074 Ullamcorper, Rd. | Idar-Oberstei | 30848 | | 18 | Keelie | Mendez | Purus In Foundation | Nulla.eu.neque@Aeneanegetmetus.co.uk | 1-330-370-8231 | 1-301-568-0413 | 3511 Tincidunt Street | Lanklaar | 73942 | | 19 | Lane | Bishop | Libero At PC | non@dapibusligula.ca | 1-340-862-4623 | 1-513-820-9039 | 7459 Pede. Street | Linkebeek | 89252 | | 20 | Michelle | Dickson | Ut Limited | Duis.dignissim.tempor@cursuset.org | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St. | Stornaway | 61290 | | 20 | Michelle | Dickson | Ut Limited | Duis.dignissim.tempor@cursuset.org | 1-202-490-0151 | 1-129-553-7398 | 6752 Eros. St. | Stornaway | 61290 | | 21 | Lance | Harper | Rutrum Lorem Limited | Sed.neque@risus.com | 1-685-778-6726 | 1-494-188-6168 | 663-7682 Et St. | Gisborne | 73449 | | 22 | Keely | Pace | Eleifend Limited | ante.bibendum.ullamcorper@necenim.edu | 1-312-381-5244 | 1-432-225-9226 | P.O. Box 506, 5233 Aliquam Av. | Woodlands County | 61213 | | 23 | Sage | Leblanc | Egestas A Consulting | dapibus@elementum.org | 1-630-981-0327 | 1-301-287-0495 | 4463 Lorem Road | Woodlands County | 33951 | | 24 | Marny | Holt | Urna Nec Luctus Associates | ornare@vitaeorci.ca | 1-522-364-3947 | 1-460-971-8360 | P.O. Box 311, 4839 Nulla Av. | Port Coquitlam | 36733 | | 25 | Holly | Park | Mauris PC | Vestibulum.ante@Maecenasliberoest.org | 1-370-197-9316 | 1-411-413-4602 | P.O. Box 732, 8967 Eu Avenue | Provost | 45507 | | 1 | Imani | Davidson | At Ltd | nec@sem.net | 1-243-889-8106 | 1-730-771-0412 | 369-6531 Molestie St. | Russell | 74398 | | 2 | Kelsie | Abbott | Neque Sed Institute | lacus@pede.net | 1-467-506-9933 | 1-441-508-7753 | P.O. Box 548, 1930 Pede. Road | Campbellton | 27022 | | 3 | Hilel | Durham | Pede Incorporated | eu@Craspellentesque.net | 1-752-108-4210 | 1-391-449-8733 | Ap #180-2360 Nisl. Street | Etalle | 84025 | | 4 | Graiden | Molina | Sapien Institute | sit@fermentum.net | 1-130-156-6666 | 1-269-605-7776 | 8890 A, Rd. | Dundee | 70504 | | 5 | Karyn | Howard | Pede Ac Industries | sed.hendrerit@ornaretortorat.edu | 1-109-166-5492 | 1-506-782-5089 | P.O. Box 902, 5398 Et, St. | Saint-Hilarion | 26232 | | 11 | Ishmael | Burnett | Dolor Elit Pellentesque Ltd | vitae.erat@necmollisvitae.ca | 1-872-600-7301 | 1-513-592-6779 | P.O. Box 975, 553 Odio, Road | Hulste | 63345 | | 12 | Ian | Fields | Nulla Magna Malesuada PC | rutrum.non@condimentumDonec.co.uk | 1-138-621-8354 | 1-369-126-7068 | P.O. Box 994, 7053 Quisque Ave | Ostra Vetere | 90433 | | 13 | Xanthus | Acosta | Tortor Company | Nunc.lectus@a.org | 1-834-909-8838 | 1-693-411-2633 | 282-7994 Nunc Av. | Belcarra | 28890 | | 14 | Sophia | Christian | Turpis Ltd | lectus.pede@non.ca | 1-962-503-3253 | 1-157-850-3602 | P.O. Box 824, 7971 Sagittis Rd. | Chattanooga | 56188 | | 15 | Dorothy | Watson | A Sollicitudin Orci Company | diam.dictum@fermentum.co.uk | 1-158-596-8622 | 1-402-884-3438 | 3348 Nec Street | Qu�bec City | 63320 | +----+-----------+------------+----------------------------------+----------------------------------------+----------------+----------------+---------------------------------+------------------+------------+
JSON¶
SELECT * FROM myjsontable;
The query returns the following results:
+-----------------------------------------------------------------+ | JSON_DATA | |-----------------------------------------------------------------| | { | | "customer": { | | "_id": "5730864df388f1d653e37e6f", | | "address": "509 Kings Hwy, Comptche, Missouri, 4848", | | "company": "ORBIN", | | "email": "blankenship.patrick@orbin.ca", | | "name": { | | "first": "Blankenship", | | "last": "Patrick" | | }, | | "phone": "+1 (999) 407-2274" | | } | | } | | { | | "customer": { | | "_id": "5730864d4d8523c8baa8baf6", | | "address": "290 Lefferts Avenue, Malott, Delaware, 1575", | | "company": "SNIPS", | | "email": "anna.glass@snips.name", | | "name": { | | "first": "Anna", | | "last": "Glass" | | }, | | "phone": "+1 (958) 411-2876" | | } | | } | | { | | "customer": { | | "_id": "5730864e375e08523150fc04", | | "address": "756 Randolph Street, Omar, Rhode Island, 3310", | | "company": "ESCHOIR", | | "email": "sparks.ramos@eschoir.co.uk", | | "name": { | | "first": "Sparks", | | "last": "Ramos" | | }, | | "phone": "+1 (962) 436-2519" | | } | | } | +-----------------------------------------------------------------+