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;
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.
