How to work with Snowflake "insert Bulk" block

[MartijnWanders]

Hi all,

I created a REST connection with QAA and his works fine. I want to insert the data into a snowflake table. The only two options I have are "insert record" and "insert bulk". Per record is pretty slow (current case) and want to see how "insert bulk" works. My feeling tells me that is maybe faster.

The "Do Query" block is only to create or replace a new table in Snowflake.

I tried the "Loop batch" and "transform list" but I can not create something that can be working. 

Below is a screenshot from the current set-up. This is with the "insert bulk" but due the single loop it behaves like the "insert record".

I hope somebody can help because I can not find much information about this.

May thanks!

[AfeefaTk]

Hi @MartijnWanders 

For the 'Insert Bulk' endpoint, the data in the 'Values' field should be a list of JSON objects and not a single json object

Json object should be passed within a list

In the screenshot you have shared it seems you are using loop block which will insert one record during each iteration which will not work with the 'Insert Bulk' endpoint since it expects a list of records

In order to make this work, you can initialize a list variable, map items from the "Call URL" block to the list variable, and map the list variable to the "Values" field of the 'Insert Bulk' endpoint

I have created demo automation for you.

Please find the attached screenshot and json workspace below

Please let us know if that works for you as expected

Refer to the below articles which might be useful here

https://community.qlik.com/t5/Knowledge/Snowflake-How-to-get-started-with-Snowflake-in-Automations/t...

https://community.qlik.com/t5/Knowledge/How-to-construct-JSON-list-in-an-automation/ta-p/1783736

Thanks!!

[MartijnWanders]

Hi AfeefaTK,

Thank you for your response. I saw your demo application and I see the logic. In your case you list app's in a loop and then send it to Snowflake. But how does this works with an API call with multiple fields?

In the attachment I have a demo application. You can use the URL because it's demo data. I'm struggling how to get all columns into the variable. Do you place the whole object into the variable?

Thank you for your help!

[AfeefaTk]

Hi @MartijnWanders 

I have added a loop block to loop over all the items from the "Call URL" block and add each item to the "url" list variable

Attaching the workspace below

Will that work for you?

Thanks!!

[MartijnWanders]

Hi AfeefaTK,

Thank you for your quick response. Ok, I tried and get a message:

Are my parameters from the variable and Snowflake block ok? 

[AfeefaTk · Accepted solution]

Hi @MartijnWanders 

I noticed that column names are case sensitive

The column names in the snowflake table appear to be in CAPITAL LETTER

Could you please also capitalize the column names passing through the automation

I have done this in demo automation

Please always initialize the variable by setting the value to empty at the beginning of the automation

Refer to the attached json workspace and let me know the test results

Thanks!!

[MartijnWanders]

Hi AfeefaTk,

This seem to work perfectly and much faster. Thank you so much for your help!