Author: Radha Shraogi
What is Snowflake?
Snowflake is a cloud-based database and data warehouse solution 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 the 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 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 the table and vice versa
Create task: Create a new named external stage
Delete: Remove data from the 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 the Snowflake sample database named SNOWFLAKE_SAMPLE_DATA
We can achieve these functionalities using Mule by following two approaches.
Snowflake Connector Approach
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 the URL is in the 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 the 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 the Database.
To Configure it selects connection type: Generic Connector
In the URL part put the URL in the 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/