Introduction
Here we are going to explore what a Snowflakes database is, look at steps for creating a Snowflakes trail account, and learn how to integrate a Mulesoft application with a Snowflakes database and do a walkthrough with an example of CRUD operation.
What is Snowflakes?
Snowflakes is a cloud-based data warehousing company which is provided as software-as-a-service (SaaS). It allows corporate users to store and analyse data using cloud-based hardware and software. Snowflake runs on cloud providers like AWS, GCP, Azure. It is a full relational database management system (RDBMS) built for the cloud and also ACID compliant and support standard SQL.
It serves a wide range of technology areas, including data integration, business intelligence, advanced analytics and security & governance. It provides support for programming languages like Go, Java, .Net etc.
Steps to Create Snowflakes Account Setup
We need to create the Snowflakes trail account and to create it Click Here.
After providing your details click continue and select the cloud provider for which the infrastructure is provided like AWS, GCP or Azure. Active the account using the activation sent to the registered email.
Once it is activated, login to the Snowflake account using the credentials. An instance will be created with a unique id and will be visible in the URL after the login.
Create a database in Snowflake from the home page. We can create a worksheet where we can run the SQL commands for all the database operations and tables like create, update, delete etc.
Create an employee table in SQL query in the worksheet and run it.
Procedure:
Connecting Snowflakes Database with Anypoint studio:
From the Anypoint studio create a new project and create a simple flow with HTTP listener component.
We can connect to the Snowflake database using the generic connection of database module configuration. All the database module components can be used with snowflake databases.
We can configure it by adding the following dependency to the project pom.xml file.
<dependency>
<groupId>net.snowflake</groupId>
<artifactId>snowflake-jdbc</artifactId>
<version>3.12.16</version>
</dependency>
In the database Configuration provide the details as given below
Connection type: Generic Connection.
Connection URL: jdbc: snowflake://
Driver Class: net.snowflake.client.jdbc.SnowflakeDriver
User Name: Username of snowflakes account.
Password: Password of snowflakes account.
After configuring above details test the connection and you get a popup saying the connection is successful then save the file.
Create a flow with a transform component in order to convert the payload into JSON format and to make necessary transformations if required to insert the data into snowflakes database.
Write the SQL query with query parameters in the insert component and map them with respective fields in query parameters section as below:
Now, send a post request from the client application (here am using Postman as my client application) to insert the data and once you get the success response check in the database table.
The output can be seen by previewing data of the table in the database.
Conclusion
As you can see the data is successfully inserted into the table from the application.
Now you know how to successfully integrate a Snowflake cloud database with the Mule application and create database operations through the connectors.
