3 Approaches To Connecting With Snowflake Using MuleSoft Connectors
Learn approaches to operating available MuleSoft connectors on the Snowflake platform and the benefits to prepare a connection with Snowflake as a source system.
Join the DZone community and get the full member experience.
Join For FreeUsing available MuleSoft connectors, there are three approaches that we can use to operate on the Snowflake platform:
- Using MuleSoft Snowflake Connector
- Using DataSource with MuleSoft Database connector
- With MuleSoft connector with database generic connection
All the above have their benefits in preparing a connection with Snowflake as a source system, which we will discuss in the sections below.
Maven Snowflake Driver Dependency
The below Maven dependency needs to be used in all three scenarios. It downloads the Snowflake driver in the project, which will be used to prepare a connection with the Snowflake instance.
<dependency>
<groupId>net.snowflake</groupId>
<artifactId>snowflake-jdbc</artifactId>
<version>3.13.18</version>
</dependency>
It provides classes that are used in the individual approach to creating connections.
Driver's class for (MuleSoft Connectors):
- DataSource configuration used from Driver: "bridges.SnowflakeBasicDataSource"
- Database generic and Snowflake connector : "net.snowflake.client.jdbc.SnowflakeDriver"
The properties that we provide in the configuration file are as follows:
snowflake:
url: "jdbc:snowflake://<instance>.snowflakecomputing.com/"
url_full : "jdbc:snowflake://<instance>.snowflakecomputing.com/?user=<username>&password=<password>&db=MULESOFT&schema=MONTREAL"
username : "<username>"
role : "ACCOUNTADMIN"
warehouse : "COMPUTE_WH"
schema : "MONTREAL"
database : "MULESOFT"
password: "<password>"
Using MuleSoft Snowflake Connector
Below is the example we will use to create a connection from the MuleSoft Snowflake connector. MuleSoft Snowflake connector is highly optimized and capable to perform the necessary configuration to perform authentication and operations with Snowflake.
<snowflake:snowflake-config name="Snowflake_Config" doc:name="Snowflake Config"
doc:id="9b7f68e3-785e-4dd8-a016-f13571033a43" >
<snowflake:snowflake-connection accountName="${snowflake.account_name}"
warehouse="COMPUTE_WH" database="MULESOFT" schema="MONTREAL"
user="${snowflake.username}" password="${snowflake.password}"
role="ACCOUNTADMIN" ></snowflake:snowflake>
</snowflake:snowflake-config>
Next, here is the flow which we have configured to validate the connection using Snowflake:
The operation above is to collect data from the Snowflake table based on the event, and executed as expected.
As we generate an event on Snowflake to validate Snowflake connector configurations, it performed the operation with success, as seen below.
Using Data Source With MuleSoft Database Connector
This is a flexible approach to perform configuration for required proxy/security parameters to create a connection with Snowflake. In several scenarios to communicate with enterprise resources, we need to configure certificates or routing parameters to prepare a connection with a platform that is lying under the secure zone. In such scenarios, it provides feasibility to configure required parameters which can be provided to the system to authenticate successfully.
Configure DataSource With Beans (Spring)
Below is the sample of code snippets that will be used to configure the DataSource. In the below sample, we are providing all needed values to the driver to make connections with the source (Snowflake). As stated above, we can provide certificates or security parameters based on demand.
<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:context="http://www.springframework.org/schema/context"
xsi:schemaLocation="
http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd">
<bean id="jdbcDataSource" class="bridges.SnowflakeBasicDataSource">
<property name="url" value="${snowflake.url}"></property>
<property name="user" value="${snowflake.username}"></property>
<property name="password" value="${snowflake.password}"></property>
<property name="role" value="${snowflake.role}"></property>
<property name="warehouse" value="${snowflake.warehouse}"></property>
<property name="schema" value="${snowflake.schema}"></property>
<property name="database" value="${snowflake.database}"></property>
</bean>
</beans>
The selection below shows how to import beans.xml file to the MuleSoft flow.
<spring:config name="Spring_Config" doc:name="Spring Config"
doc:id="3ffe0bc0-96b0-40f8-8f71-ab623a315ca5" files="beans.xml" />
The next section shows how to refer DataSource configured in the beans file in MuleSoft Database Connector.
<db:config name="Database_Config_DataSource" doc:name="Database Config"
doc:id="ea76a56f-65b0-4e33-bd79-00c0ea63e525" >
<db:data-source-connection dataSourceRef="jdbcDataSource"></db:data>
</db:config>
After completing the above configuration, now let's validate the connection. It works as expected with a successful result.
MuleSoft Connector With Database Generic Connection
This is one generic approach to performing a connection with any underlying database configuration. In the below code section, we need to provide the necessary parameters to create connections using the Snowflake driver. It works as simple as a JDBC connection, which we can see while writing code in Java.
For more information, we can check out MuleSoft Database Connector.
<db:config name="Database_Config_Generic" doc:name="Database Config"
doc:id="b5dbc440-91e3-434b-a835-86afe665ca33" >
<db:generic-connection url="${snowflake.url_full}"
driverClassName="net.snowflake.client.jdbc.SnowflakeDriver"
user="${snowflake.username}" password="${snowflake.password}"></db:generic>
</db:config>
Above, we can find the configuration parameters which need to provide to prepare generic connections with Snowflake.
Video Demo
Watch the demo below for more about MuleSoft integration with Snowflake.
Opinions expressed by DZone contributors are their own.
Comments