Loading JSON to Snowflake

[davidm]

Hi,

Been trying to load json data into VARIANT data field in Snowflake. I want to avoid writing externally like an S3 bucket and then doing a bulk upload. I prefer to do it straight load to Snowflake.
I’ve tried the following snaps and it keeps failing.

• Snowflake Bulk Upload - fail
• Snowflake Bulk Merge - fail
• Snowflkae Insert - slow but it works (but had to remove all other fields )

My table is a mix of VARHCAR (3 columns) and 1 VARIANT.

Question, I have is what is the suitable SNAP for loading JSON into Snowflake?

Thanks,
David

[davidm]

Figure this out. The important thing in using Variant is that you want to store JSON as JSON and not as string. To do this, I basically need to convert Document to Binary and Binary to Document. Strange, but it works.

[koryknick]

You can push a JSON object into a Snowflake Variant datatype by first formatting it using JSON.stringify($json_object) in a Mapper expression.

[neeraj_sharma]

Thanks … I was facing the same issue, but why do we need to Stringify the JSON, why snowflake does not consume it as it is.
If we want to insert JSON in variant column we parse it into JSON, but here in snaplogic we are stringified it, why so?

[fabriciocarboni]

4 years now after this post, this trick helped me to solve this problem after 2 weeks losing my hair on that 😂

[koryknick]

In the background, the Bulk Load snap first writes the content to a file for Snowflake to ingest. So the stringify is basically like escaping quote characters in your data prior to sending the data to Snowflake.