RE: Snowflake Copy Command is throwing an error when there is a backslash present as part of the data.

If there is any ‘BACKSLASH’ in the data, the COPY command will mess the data and may end in error for some cases.

For example,

Below is the scripts Diyotta generated automatically to communicated with Snowflake per our configuration. And job will be wrong in 4th step which is ‘COPY’ command.

SQL –

USE DATABASE TEST_DB;

USE SCHEMA REPFW;

USE WAREHOUSE “SNF-IT-ETL-DEVELOPER-WH”;

COPY INTO TEST_DB.REPFW.base_ecomm_dimension_product_current_im (retail_banner_cd,product_num,product_join_key,product_nm,product_style_cd,product_style_desc,product_fineline_cd,product_fineline_desc,product_sub_category_cd,product_sub_category_desc,product_category_cd,product_category_desc,product_lob_nm,product_division_nm,product_season_desc,load_ts,process_instance_name) FROM (SELECT REPLACE(REPLACE($1,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($2,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($3,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($4,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($5,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($6,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($7,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($8,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($9,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($10,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($11,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($12,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($13,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($14,chr(05),chr(10)),chr(06),chr(13)),REPLACE(REPLACE($15,chr(05),chr(10)),chr(06),chr(13)),$16,REPLACE(REPLACE($17,chr(05),chr(10)),chr(06),chr(13)) FROM @~/tmp/TGT_base_ecomm_dimension_product_current_im_63211_6)  ON_ERROR=’ABORT_STATEMENT’  PURGE=FALSE  RETURN_FAILED_ONLY=FALSE  ENFORCE_LENGTH=TRUE  TRUNCATECOLUMNS=FALSE  FORCE=TRUE  LOAD_UNCERTAIN_FILES=FALSE FILE_FORMAT=( type=’CSV’ COMPRESSION=’AUTO’ ERROR_ON_COLUMN_COUNT_MISMATCH=FALSE FIELD_DELIMITER=’\u0001′ DATE_FORMAT=’AUTO’ TIME_FORMAT=’AUTO’  TIMESTAMP_FORMAT=’AUTO’  BINARY_FORMAT=’UTF-8′ NULL_IF=’NULL’  EMPTY_FIELD_AS_NULL=FALSE  VALIDATE_UTF8=FALSE )  ;

remove @~/tmp/TGT_base_ecomm_dimension_product_current_im_63211_6;

 

 

 

 

 

JohnGS Advanced User Asked on August 3, 2020 in Diyotta Studio.
Add Comment
1 Answers

Hi John

we can specify the escape character in the load/extract properties as shown below

Added “Escape Unenclosed Field” option in Snowflake load properties for Bulk load
Use this option is used to specify a character as the escape character for unenclosed field values. Accepts common escape sequences, octal values, or hex values. Default value is backslash (\\). Specify the value as NONE if no character should be used to escape.

In your case you need to add the  backslash (\\) and then try to run the job.

RE: Snowflake Copy Command is throwing an error when there is a backslash present as part of the data.

Expert Answered on August 10, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.