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

[smanoharan]

@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]

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]

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.

[janph]

have you tried to enable the “Load empty strings” setting in the Snowflake Bulk Load