About Snowflake and its Integration With MuleSoft

Author: Radha Shraogi

What is a Snowflake?

• Cloud based Database.
• Data warehouse solution.
• Data Cloud powered by an advanced data platform provided as Software as a Service(SaaS).

Benefits of Snowflake:

• There is no hardware (virtual or physical) to select, install, configure or manage.
• Ongoing maintenance, management, upgrade, and tuning are handled by Snowflake.

Steps to create an account in Snowflake:

• Sign Up on Snowflake trial account: https://trial.snowflake.com/

• Select Snowflake edition as Enterprise and Cloud Provider as Amazon Web Services (AWS).

• You will get an email from Snowflake to activate your Snowflake account.

• Set the username and password and will get an individual URL for your account.

• Login into the Snowflake and portal looks like below.

• Snowflake provides some sample databases, listed on the left of the portal.
• In the middle section, we can write a query that we want to execute.

List of operations available through snowflake connector in Anypoint Studio

Bulk delete: Delete multiple rows at a time
Bulk insert: Insert multiple rows at a time
Bulk update: Update multiple rows at a time
Copy into location: Upload data from the table into one or more files
Copy into the table: Load data from the file to the existing table
Create pipe: Create a new pipe for defining the COPY INTO statement
Create stage: Create a new stage to use for loading data from files to table and vice versa
Create task: Create a new named external stage
Delete: Remove data from table
Execute DDL: DDL command use to manipulate objects in Snowflake
Execute Script: Execute a SQL script
Insert: Insert the row into the table
Insert multi table: Insert a row into multiple tables
Merge: Insert, update or delete the value in a table based on values in the subquery
Select: Fetch the records from the table
Stored procedure: Invoke the stored procedure on the database
Update: Update specific row with new value

Steps to integrate Snowflake with MuleSoft:

Let’s take the use case to fetch data from Snowflake sample database named SNOWFLAKE_SAMPLE_DATA

We can achieve these functionalities using Mule by following two approaches.

1. Snowflake Connector Approach
2. Database Generic Connector Approach

1. Snowflake Connector Approach

Add Snowflake Connector to your Mule project from Exchange.

• In Mule Palette, click (X) Search in Exchange.
• In Add Dependencies to Project, type snowflake in the search field.
• Click Snowflake Connector in Available modules.
• Click Add and Finish.

Add the HTTP Listener in the pallet and configure it.

Add the Select from Snowflake that we added from Exchange.

Configure the connector configuration

• Account Name: Account Name is provided in the URL that you got through Gmail from Snowflake
  ex: If URL is in format: “https://abc.snowflakecomputing.com/”
  Then Account Name is abc
• Warehouse: Name of the virtual Snowflake warehouse that you are going to use.
• Database: Name the Snowflake database. Here I am using the sample database named SNOWFLAKE_SAMPLE_DATA
• Schema: Name the Schema
• User: Write the UserID of your Snowflake Account.
• Password: Write the password of your Snowflake Account.

Add the JDBC driver

• Click on Configure.
• Select Add Maven Dependency.
• Configure as follow.

• Click on Finish and it will download the dependency.

Click on Test Connection and verify whether it is successful or not.

Add a SQL Query to fetch the data.

Add a Transform message to transform payload into JSON format.

Add a Logger to get a Log message on Console.

Run the flow to get the results.

• Postman: Hit the URL http://localhost:8081/get_snow

• Console

2. Database Generic Connector Approach

• Add the HTTP Listener in the pallet and configure it.
• Add a select component of Database.
• To Configure it select connection type : Generic Connector
• In URL part put the URL in following format:
  jdbc:snowflake://https://<AccountURL>/?user=<UserID>&password=<Password>&db=<Database>&schema=<Schema>

• Add the Driver name: net.snowflake.client.jdbc.SnowflakeDriver.
• Add username and password.
• Check on the Test Connection button and check whether it is successful or not.
• Add the query in the SQL Query section of select.
  SELECT TOP 10 * FROM CUSTOMER
• Add a transform message to convert it into JSON data.
• Add a logger to get a log message on the console.

• Run the flow and get the results.

Reference:

For more information on Snowflake: https://docs.snowflake.net/manuals/