CODEWITHSUNDEEP
snowflake-cloud-data-platformazure-data-factorydata-ingestion
Rohith
Rohith

Reputation: 11

Data ingestion to Snowflake using Azure data factory

I am getting the below error while loading the data from my database using Azure Data factory to Snowflake. When I checked in my database at the particular point where the error has occurred, I have a column(data type: string) with data which has '`' included in it. Could any one pls help to load the data without any errors?

"Message": "ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [22000] Found character '`' instead of field delimiter ','\n File 'staging/398e91a4-8147-4668-96ee-6645c73ae895/SnowflakeImportCopyCommand/data_398e91a4-8147-4668-96ee-6645c73ae895_e9a92264-b3ee-4cbe-a358-1ffeac10c3b3.txt',

Upvotes: 0

Views: 624

Answers (1)

KarthikBhyresh-MT
KarthikBhyresh-MT

Reputation: 5044

The most common Copy errors include skipping records when field delimiter is enclosed within the field value resulting in message. Quotes in delimited records are normally used to allow the data to carry delimiters and newline characters within them, but require you to escape the quotes themselves, i.e. the record ought to be in the following form to be read properly by a regular reader implementation (notice the double double-quotes within the data):

User ID 
karthik ""ab2""

When loading data, specifies the escape character for enclosed fields. Specify the character used to enclose fields by setting FIELD_OPTIONALLY_ENCLOSED_BY.

Refer: CREATE FILE FORMAT

FIELD_OPTIONALLY_ENCLOSED_BY = 'character' ` NONE

Definition: Character used to enclose strings. Value can be NONE, single quote character ('), or double quote character ("). To use the single quote character, use the octal or hex representation (0x27) or the double single-quoted escape (''). When a field contains this character, escape it using the same character. For example, if the value is the double quote character and a field contains the string A "B" C, escape the double quotes as follows:

A ""B"" C

Default: NONE

Example: The field delimiter is enclosed within the field value and occurs in all rows for a specified field (E.g. "EmailTypeToSend"). A potential solution would be to use the REGEXP function to replace the character.

Data:

Reference #,EmailTypeToSend,Date Sent
11111-000000,`CS_`XXXX`,`2013-02-24 09:11:35`

Error Message:

No error message, the record skipped.

Solution:

COPY INTO <table_name>
FROM
(
  SELECT
    $1,
    regexp_replace($2,'^\`|\`$') ,regexp_replace($3,'^\`|\`$')
  FROM<stage>
)
FILE_FORMAT = (TYPE = 'csv' RECORD_DELIMITER = ',' SKIP_HEADER = 1) ON_ERROR='SKIP_FILE_5
FILES = ('<file>')

;

Validation When there are a bunch of files loaded and only a few of them are processed, running the below query against COPY_HISTORY (https://docs.snowflake.net/manuals/sql-reference/functions/copy_history.html) will provide details into which files have been rejected. At which time, modified before attempting to load again.

Data:

COLUMN1`COLUMN2`COLUMN3
S8518B5`M *P`NK 800-653-8000CADGCVF`Entertainment
S8518B5`SCHG4FS`Entertainment

Error Message:

The data has delimiter “`” in the value which also happens to be a field delimiter.

Query:

select * from table(information_schema.copy_history
       (table_name=>'TABLE NAME', start_time=> dateadd(hours, -5, current_timestamp())));

Applies to: Copy, CSV, Data Load

References: copy-into-table, copy_history, copy-data-from-and-to-snowflake-with-azure-data-factory, create-file-format

Syntax:

REGEXP_REPLACE( string, pattern [, replacement_string [, start_position [, nth_appearance [, match_parameter ] ] ] ] )

Note: You could use TRIM(col, '"') but it may also remove away actual quotes appearing at the beginning or end of the real data.

Upvotes: -1

Related Questions