Loading JSON to Snowflake
![davidm davidm](https://iaudz84835.lithium.com/legacyfs/online/avatars/2X/1/16509b136d888cec4551cf35060b96508e1ba7d7.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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.
- 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 davidm](https://iaudz84835.lithium.com/legacyfs/online/avatars/2X/1/16509b136d888cec4551cf35060b96508e1ba7d7.png)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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.
![koryknick koryknick](https://iaudz84835.lithium.com/legacyfs/online/avatars/2X/5/5ea37fb6350d2e33bb56e62dfe1e05b4fc4133fc.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎05-15-2024 08:47 AM
4 years now after this post, this trick helped me to solve this problem after 2 weeks losing my hair on that 😂
![koryknick koryknick](https://iaudz84835.lithium.com/legacyfs/online/avatars/2X/5/5ea37fb6350d2e33bb56e62dfe1e05b4fc4133fc.jpeg)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎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.
![](/skins/images/A5F8D4F9CE9DD11234D129690E6DC828/responsive_peak/images/icon_anonymous_message.png)