Forum Discussion

davidm's avatar
davidm
New Contributor
6 years ago

Loading JSON to Snowflake

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

9 Replies

  • koryknick's avatar
    koryknick
    Employee
    5 years ago

    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's avatar
      neeraj_sharma
      New Contributor II
      4 years ago

      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's avatar
      fabriciocarboni
      New Contributor II
      2 years ago

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

  • koryknick's avatar
    koryknick
    Employee
    4 years ago

    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's avatar
    smanoharan
    Employee
    6 years ago

    @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's avatar
      davidm
      New Contributor
      6 years ago

      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's avatar
    davidm
    New Contributor
    6 years ago

    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's avatar
      janph
      New Contributor
      6 years ago

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

  • davidm's avatar
    davidm
    New Contributor
    5 years ago

    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.