โ04-28-2020 02:44 PM
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.
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
โ12-16-2020 12:22 PM
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.
โ12-16-2020 12:40 PM
You can push a JSON object into a Snowflake Variant datatype by first formatting it using JSON.stringify($json_object) in a Mapper expression.
โ03-09-2022 11:15 PM
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?
โ03-10-2022 04:32 AM
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.