About Snowflake and its Integration With MuleSoft

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:

  • 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.

  • 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/

 

We use cookies on this site to enhance your user experience. For a complete overview of how we use cookies, please see our privacy policy.