5

Connecting With Snowflake Using MuleSoft Connector - DZone Integration

 2 years ago
source link: https://dzone.com/articles/3-approaches-to-connecting-with-snowflake-using-mulesoft-connectors
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
neoserver,ios ssh client

Using 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:

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

Generate an event on Snowflake

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>
Database Config

After completing the above configuration, now let's validate the connection. It works as expected with a successful result.

Successful result of validating the connection

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>
Database_Config_Generic

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.


report

Recommend

About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK