About Snowflake and its integration with MuleSoft

Snowflake is a cloud-based database and data warehouse solution powered by an advanced data platform provided as software as a service (SaaS). You don’t need to select, install, configure or manage any hardware (virtual or physical). Snowflake handles all ongoing maintenance, management, upgrades and tuning.

To create an account in Snowflake:

• Sign up for a Snowflake trial account: https://trial.snowflake.com/
  
• Select the Snowflake edition as Enterprise and the Cloud Provider as Amazon Web Services (AWS).
  
• You’ll receive an email from Snowflake to activate your Snowflake account.
  
• Set the username and password and then you’ll receive an individual URL for your account.

• Log in into the Snowflake and portal — it should look like the following image:
  

Snowflake provides some sample databases, which are listed on the left side of the portal. In the middle section, you can write a query that we’ll 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 into tables and vice versa
• Create task: Create a new named external stage
• Delete: Remove data from a table
• Execute DDL: The DDL command used to manipulate objects in Snowflake
• Execute Script: Execute a SQL script
• Insert: Insert a row into a 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 a specific row with a new value
  

Integrating Snowflake with MuleSoft

This section describes how to retrieve data from a Snowflake sample database — named SNOWFLAKE_SAMPLE_DATA.

You can use Mule to achieve these functionalities by following one of two approaches:

1. Snowflake Connector approach
2. Database Generic Connector approach

Snowflake Connector approach

1. Add a 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
   • In Available modules, click Snowflake Connector
   • Click Add and Finish
2. Add the HTTP Listener in the pallet and configure it
3. Add the selection from Snowflake that we added in Exchange
4. Configure the connector configuration:
   • Account Name: In the URL you received via the email from Snowflake — for example, if the URL is https://abc.snowflakecomputing.com/, then the Account Name is “abc”
   • Warehouse: Name of the virtual Snowflake warehouse that you’re going to use
   • Database: Name of the Snowflake database — in this case, the name of the sample database: SNOWFLAKE_SAMPLE_DATA
   • Schema: Name the schema
   • User: The UserID of your Snowflake account
   • Password: The password you chose for your Snowflake account
   
5. Add the JDBC driver:
   • Click Configure
   • Select Add Maven Dependency

     The configuration should look like this:

     
   • Click Finish and the dependency will download
6. Click Test Connection and verify whether or not it’s successful

7. Add a SQL query to fetch the data

8. Add a Transform Message to transform the payload into JSON format

9. Add a Logger to get a Log Message on the Console

10. Run the flow to get the results
    • Postman at http://localhost:8081/get_snow
    

• Console

Database Generic Connector approach

1. Add the HTTP Listener in the pallet and configure it:
   • Go to add a select component of the database:
     • To configure it, select connection type: Generic Connector
     • Use the following format for the URL:

        jdbc:snowflake://https://<AccountURL>/?user=<UserID>&password=<Password>&db=<Database>&schema=<Schema>

       
2. Add the Driver name: net.snowflake.client.jdbc.SnowflakeDriver
3. Add username and password
4. Click Test Connection and check whether or not it’s successful
5. Add the query in the SQL Query section of select — SELECT TOP 10 * FROM CUSTOMER
6. Add a Transform Message to convert it into JSON data
7. Add a Logger to get a Log Message on the Console

8. Run the flow and get the results

Reference:

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

— By Radha Shraogi