Snowflake 'GET': (VARCHAR(16777216), VARCHAR(12))

Question

I have loaded a JSON file on to a Snowflake stage.
Now my goal is to COPY the contents of the file into a relational table.

Table is defined as have either varchar or Boolean columns.

{
  "requestRefid": "W2W8P",
  "requestid": "kki8786f1b-03eb",
  "requestTypes": [
    "Do not sell it"
  ],
  "subjectTypes": [
    "Current customer"
  ],
  "firstName": "Dan",
  "lastName": "Murrary",
  "email": "[email protected]",
  "phone": "410000869",
  "emailValidation": true,
  "phoneValidation": true,
  "message": "Confirm",
}

Here is the COPY statement that I am using:

copy into TEST."PUBLIC".REQUESTS(REQUESTREFID, REQUESTID, FIRSTNAME, LASTNAME, EMAIL, PHONE, EMAILVALIDATION, PHONEVALIDATION, IDVALIDATION, MESSAGE, CHANNEL)
   from (select $1:requestRefid, $1:requestid, $1:firstName, $1:lastName, $1:email, $1:phone, $1:emailValidation, $1:phoneValidation, $1:idValidation, $1:message, $1:channel
   from @sf_tut_stage/sample.json t);

Here is the error that I get:

SQL Error [1044] [42P13]:

SQL compilation error: error line 2 at position 18

Invalid argument types for function 'GET': (VARCHAR(16777216), VARCHAR(12))

SQL compilation error: error line 2 at position 18

Invalid argument types for function 'GET': (VARCHAR(16777216), VARCHAR(12))

SQL compilation error: error line 2 at position 18

Invalid argument types for function 'GET': (VARCHAR(16777216), VARCHAR(12))

I am able to query the contents of the JSON file in the stage using following query:

select $1
   from @sf_tut_stage/sample.json;

What am I doing wrong?

I also tried add following to the copy statement:

file_format = (format_name = SF_TUT_CSV_FORMAT));

but no luck.

What is the right way to write this statement so it can load items within $1 to individual relational table columns.

if you run your select query from the stage while extracting columns from JSON do you get the same error? if so, can you narrow it down to a column that is causing the issue? — David Garrison

David Garrison David Garrison · Accepted Answer · 2020-01-11T00:05:39

Your code is trying to use the : syntax to extract a value from the source, but it's still just a varchar, which doesn't allow that syntax. Try this using PARSE_JSON so that SnowFlake knows that it's JSON and can apply that syntax appropriately.

copy into TEST."PUBLIC".REQUESTS(REQUESTREFID, REQUESTID, FIRSTNAME, LASTNAME, EMAIL, PHONE, EMAILVALIDATION, PHONEVALIDATION, IDVALIDATION, MESSAGE, CHANNEL)
from (
    select
        PARSE_JSON($1):requestRefid,
        PARSE_JSON($1):requestid,
        PARSE_JSON($1):firstName,
        PARSE_JSON($1):lastName,
        PARSE_JSON($1):email,
        PARSE_JSON($1):phone,
        PARSE_JSON($1):emailValidation,
        PARSE_JSON($1):phoneValidation,
        PARSE_JSON($1):idValidation,
        PARSE_JSON($1):message,
        PARSE_JSON($1):channel
    from @sf_tut_stage/sample.json t
 );

Snowflake 'GET': (VARCHAR(16777216), VARCHAR(12))

2 Answers