FRENDS: Getting Data From Snowflake and Send it to an XML Web Service

Part (2/2)

This is my attempt to create a simple process where I get Product data from Snowflake, process each of the records and then send it as an XML Web Service. It was a very straightforward experience, I found it very easy to create a simple process of course with help of resources available online.

When you log in, you will be presented a Dashboard that will show all the Successful and failed processes that have taken place. 

Definition of the Tabs Above

• API: APIs are entry points to Frends Processes where the actual integration logic is implemented. 
• Processes: Standard “unit” of integrations within Frends, meaning that all end to end integrations are called Processes, regardless of whether they are run on schedule or not. 
• Subprocesses: Are reusable components. Are called from the main level Processes
• Tasks: Are the building blocks of Processes and Subprocesses. Think of Tasks as a .NET or C# microservices that provide a piece of basic functionality such as reading files or calling APIs. 
• Environment Variables: Are reusable & centralized variables. For example, this is where you can define your connection parameters to Snowflake

Add Snowflake Connector

The vanilla demo environment doesn’t have the Snowflake connector, hence you need to get it.  Thankfully there are numerous connectors provided out of the box, hence you don’t really need to install it. 

Under Administration -> Select Tasks
Once there, select Search For Tasks
Search for Snowflake and then click on Import.

Once imported, you should see it in the list

Processes

You will see the list of all the processes that were created, activated and run.  

To create a new process -> Select Create New

Create a Process

Our objective is to create a simple process like this. 

When you create a new Process, you are presented with an open canvas. From there you drag and drop objects from the left to start creating an outline of the integration, so that you can plan your work ahead. Frends enables you to work in this sort of a process based approach.  You can visually draw out the solution you had in mind beforehand. 

Get that product information and then update some sort of a web shop interface.  Since we are getting the top 10 products, we need to loop through each of those products. 

This process will be triggered manually.

To add a new  Task, just highlight the Append Task.

Give the task a name, and in the drop down select ‘Snowflake ExecuteQuery’

You will need to define the connection parameters for Snowflake (or use them from environment variables if set up) and then write your SQL statement.

Once you have defined your connection parameters and SQL statement - you can run the test to view the result set.

The next task would be to loop through each of those top 20 records from the result of the previous step.  Access each product under a variable (product) within the loop (product) 

Variable: product
The ‘Get Product Data from Snowflake’ - is the previous Task.

The expression #result[Get Product Data from Snowflake].Data - will get the result set from the previous Task. Remember to put the .Data at the end as the data in the JSON file starts with “Data”  And also it will fail the validation. 

Last Task would be to Send the product as an XML to a Web Service.  For this particular task, select the HTTPRequest. 

Method: POST
Message: The #var.product is our variable followed by the columns we need.

Once done, your complete process would be as such.

Validate & Run the Process

Activate and Run the Process

You can check the status of the runs. Clicking on the ID will take you directly to that process for you to troubleshoot. 

The above process was just a simple process that I tried, the use cases are diverse and limitless as mentioned in the earlier section.  Below are just some more examples. The good thing is that organizations can plan ahead and visually create the flow/business process that you want to automate. And then dive in and configure each of the blocks.  

Extract Transform and Load 

In the following scenario, when a new Order table is created, the process will be triggered. It will read & parse the EDIFACT file after which it will continue to transform the file to a JSON message to be send to Partners via Web Service.

The Green - Extract Phase
Blue - Transform Phase
Purple - Load Phase
Orange - Error Handling

Business Process Automation

Frends can be used to create business process automations by utilizing the BPMN 2.0 based process modeling.  We first model out the process including decisions, business logic, loops, error handling, etc. After modelling the solution, we can configure the activity in the process sequence. 

File Transformation

And ofcourse file transformations, i.e converting CSV to XSLT. 

Resources

During my learning journey, I leveraged numerous resources: 

Fundamental Course : Link 

Documentation/knowledge base: Link

And some of their content on YouTube. 

Hope this has been helpful. 

Thanks for reading...