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 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.
- 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 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
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:
- 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.
For more information on Snowflake: https://docs.snowflake.net/manuals/