Loading JSON to Snowflake

Question

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

koryknick · Answer

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

koryknick · Answer

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.

smanoharan · Answer

@davidm You don’t have to write files to an external S3 or Azure storage for using the Snowflake Bulk Load Snap. What is the complete failure message and stack trace that you got from Snowflake Bulk Load.

davidm · Answer

I’m getting this error:
No column values found in input document to load into SF database table.
Resolution:
All columns which do not have default values have to be specified in the input document.
Reason:
The input to the SF Bulk Load snap should contain the column values to load into SF database.
Hide Details…
Snowflake - Bulk Load[58b7a2d080b28239d980b31f_6d77b238-ce5f-4f6b-86cc-be57f461663c – 1e79a01b-9983-4bdb-a2ed-135917bc152f]
`com.snaplogic.snap.api.SnapDataException: No column values found in input document to load into SF database table.
at com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:393)
at com.snaplogic.snaps.snowflake.BulkLoad.execute(BulkLoad.java:420)
at com.snaplogic.cc.snap.common.SnapRunnableImpl.executeSnap(SnapRunnableImpl.java:768)
at com.snaplogic.cc.snap.common.SnapRunnableImpl.execute(SnapRunnableImpl.java:550)
at com.snaplogic.cc.snap.common.SnapRunnableImpl.doRun(SnapRunnableImpl.java:834)
at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:400)
at com.snaplogic.cc.snap.common.SnapRunnableImpl.call(SnapRunnableImpl.java:116)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
at java.base/java.util.concurrent.FutureTask.run(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base/java.lang.Thread.run(Unknown Source)
Caused by: com.snaplogic.api.ExecutionException: No column values found in input document to load into SF database table.
at com.snaplogic.snaps.snowflake.BulkLoad.storeInputDocument(BulkLoad.java:550)
at com.snaplogic.snaps.snowflake.BulkLoad.processDocument(BulkLoad.java:484)
at com.snaplogic.snaps.sql.SimpleSqlSnap.process(SimpleSqlSnap.java:384)
… 12 more
Reason: The input to the SF Bulk Load snap should contain the column values to load into SF database.
Resolution: All columns which do not have default values have to be specified in the input document.
Error Fingerprint[0] = efp:com.snaplogic.snaps.sql.8mD-ksio
Error Fingerprint[1] = efp:com.snaplogic.snaps.snowflake.IT5dMonA`
pool-4-thread-11817
`com.snaplogic.cc.snap.common.ThreadDetails: prio=4 Id=74189 TIMED_WAITING on java.util.concurrent.SynchronousQueue$TransferStack@773794a
at java.base@11.0.5/jdk.internal.misc.Unsafe.park(Native Method)
-  waiting on java.util.concurrent.SynchronousQueue$TransferStack@773794a
at java.base@11.0.5/java.util.concurrent.locks.LockSupport.parkNanos(Unknown Source)
at java.base@11.0.5/java.util.concurrent.SynchronousQueue$TransferStack.awaitFulfill(Unknown Source)
at java.base@11.0.5/java.util.concurrent.SynchronousQueue$TransferStack.transfer(Unknown Source)
at java.base@11.0.5/java.util.concurrent.SynchronousQueue.poll(Unknown Source)
at java.base@11.0.5/java.util.concurrent.ThreadPoolExecutor.getTask(Unknown Source)
at java.base@11.0.5/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base@11.0.5/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
…
at java.base@11.0.5/jdk.internal.misc.Unsafe.park(Native Method)
at java.base@11.0.5/java.util.concurrent.locks.LockSupport.parkNanos(Unknown Source)
at java.base@11.0.5/java.util.concurrent.SynchronousQueue$TransferStack.awaitFulfill(Unknown Source)
at java.base@11.0.5/java.util.concurrent.SynchronousQueue$TransferStack.transfer(Unknown Source)
at java.base@11.0.5/java.util.concurrent.SynchronousQueue.poll(Unknown Source)
at java.base@11.0.5/java.util.concurrent.ThreadPoolExecutor.getTask(Unknown Source)
at java.base@11.0.5/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
at java.base@11.0.5/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
at java.base@11.0.5/java.lang.Thread.run(Unknown Source)
Error Fingerprint[0] = efp:jdk.internal.misc.rn-dMs9v`

davidm · Answer

Please note, I’m going to JIRA Search snap. Using JQL, I am able to return json documents and bulk uploading them to Snowflake using Snowflake Bulk Upload. Format Type: JSON.  The table we are loading contains only 1 column that is data type VARIANT.
Please note: I did try adding JSON Formatter and JSON Parser before loading it to Snowflake. It only worked for Snowflake Insert but instead of VARIANT (JSON) it was looking at JIRA as text.

Forum Discussion

Loading JSON to Snowflake

9 Replies

Recent Discussions

Pagination and nextCursor in header

Javascript to promote top level lists

Google Sheets Subscribe questions

Basic string transformations not working

Can we generate XML file in pretty print format using native snapLogic snaps?