CODEWITHSUNDEEP
jsonamazon-s3nullsnowflake-cloud-data-platform
vbp13
vbp13

Reputation: 1190

snowflake read null from s3 json

This seems like it should be easy, but for the life of me on a Friday night...

I have a json file i'm reading from s3

{"name":"bob", "currentTime":"null"}

I created a stage in snowflake.

When I do,

Select $1:name, $2:currentTime 
from @myStage/mydocument

I get as expected

$1:name $2:currentTime

"bob" "null"

I have a snowflake table

create table test_bob
(
name varchar
,currentTime TIMESTAMP_NTZ
)

But when I do

Copy into test_bob
Select $1:name, $2:currentTime 
    from @myStage/mydocument

I get an error,

Failed to cast variant value "null" to TIMESTAMP_NTZ

I tried using NULL_IF as suggested here.

I tried using STRIP_NULL_VALUES as a file format

Upvotes: 0

Views: 253

Answers (1)

Mike Walton
Mike Walton

Reputation: 7369

It looks like you have a string of "null" rather than a null value. Did you try this?

Copy into test_bob
Select $1:name, NULLIF($2:currentTime::string,'null')::timestamp_ntz 
    from @myStage/mydocument

this should check the value of the string before trying to convert the json attribute object to a timestamp_ntz.

Upvotes: 1

Related Questions