Snowflake - getting 'Error parsing JSON' while using the Copy command from S3 to snowflake

Question

i'm trying to copy gz files from my S3 directory to Snowflake.

i created a table in snowflake (notice that the 'extra' field is defined as 'Variant')

CREATE TABLE accesslog
(
	loghash VARCHAR(32) NOT NULL,
	logdatetime TIMESTAMP,
	ip VARCHAR(15),
	country VARCHAR(2),
	querystring VARCHAR(2000),
	version VARCHAR(15),
	partner INTEGER,
	name VARCHAR(100),
	countervalue DOUBLE PRECISION,
	username VARCHAR(50),
	gamesessionid VARCHAR(36),
	gameid INTEGER,
	ingameid INTEGER,
	machineuid VARCHAR(36),
	extra variant,
	ingame_window_name VARCHAR(2000),
	extension_id VARCHAR(50)
    
);

i used this copy command in snowflake:

copy  INTO accesslog
	FROM s3://XXX
    pattern='.*cds_201911.*'
    CREDENTIALS = (
        aws_key_id='XXX',
        aws_secret_key='XXX')
    FILE_FORMAT=(
        error_on_column_count_mismatch=false
    	FIELD_OPTIONALLY_ENCLOSED_BY = '"'
        TYPE = CSV 
        COMPRESSION = GZIP
        FIELD_DELIMITER = '\t'
        )
        ON_ERROR =  CONTINUE

I run it, and got this result (i got many error lines, this is an example to one) snowflake result snowflake result -more

a17589e44ae66ffb0a12360beab5ac12	2019-11-01 00:08:39	155.4.208.0	SE		0.136.0	3337	game_process_detected	0	OW_287d4ea0-4892-4814-b2a8-3a5703ae68f3	e9464ba4c9374275991f15e5ed7add13		765	19f030d4-f85f-4b85-9f12-6db9360d7fcc	[{"Name":"file","Value":"wowvoiceproxy.exe"},{"Name":"folder","Value":"C:\\Program Files (x86)\\World of Warcraft\\_retail_\\Utils\\WowVoiceProxy.exe"}]

can you please tell me what cause this error? thanks!

Hans Henrik Eriksen Hans Henrik Eriksen · Accepted Answer · 2019-12-17T08:57:21

I'm guessing;

The 'Error parsing JSON' is certainly related to the extra variant field.

The JSON looks fine, but there are potential problems with the backslashes \.
If you look at the successfully loaded lines, have the backslashes been removed?

This can (maybe) happen if you have STAGE settings involving escape characters.

The \\Utils substring in the Windows path value can then trigger a Unicode decode error, eg.
Error parsing JSON: hex digit is expected in \U???????? escape sequence, pos 123

UPDATE:
It turns out you have to turn off escape char processing by adding the following to the FILE_FORMAT:

ESCAPE_UNENCLOSED_FIELD  = NONE

The alternative is to doublequote fields or to doubly escape backslash, eg. C:\\\\Program Files.

Snowflake - getting 'Error parsing JSON' while using the Copy command from S3 to snowflake

1 Answers