Authors: Prathamesh Chavan, Siva Mani Subrahmanya Hari Vamsi Pullipudi
& Vishal Chandra
Introduction
Mulesoft can also be used as an ETL tool to extract the data from different sources to Snowflake. We can do some transformation as well and then load it to Snowflake. This blog gives a brief description of how to extract and load the data from the SQL server to Snowflake using mule4.
Prerequisites
Install Mule4
MuleSoft account credentials
Snowflake account credentials
SQL server credentials
In Mule Palette Exchange, install the Snowflake connector and database connector.
Truncate and Load approach
The overall pipeline used to extract and load the data from On-prem systems(SQL server) to Snowflake is given below.
Components Used
1. Scheduler
This component can be used to schedule the flows based on the requirement.
It supports Fixed Frequency and also Cron Expressions.
It will trigger the flow at a fixed frequency, which can be configured for days, hours, milliseconds, minutes, and seconds.
If cron jobs are used, cron expression and time zone should be provided. The flow will be triggered based on the cron job.
2. Database Select Component
This component can be used to connect to the database and extract the source data from the SQL server by providing the details like hostname, server name, password, and type of connection. The SQL source connection can also be tested in the connector configuration.
In the SQL Query Text box, we can provide the custom SQL query to fetch the data which is required to load into Snowflake.
3. Transform Message
This component helps in converting the resultset(payload), which is generated in the above step to JSON.
Left-hand side, the mappings of the column names in the source are visible.
4. Snowflake Bulk Insert
In the Connector configuration, configure Snowflake URL, username, password, database name, schema name and role should be provided. Testing the Snowflake connection can be done in connector configuration.
Connect to Snowflake and Load the data into Snowflake tables.
Using this component, we can load data into a Snowflake staging table.
5. Snowflake Execute Script
Connect to Snowflake and execute the SQL script, which is present in the properties folder in Mule.
This script which we use, will insert data from the staging table to the raw table and truncate the staging table.
6. Logger
Using this component, success messages and error messages can be displayed in the console when the Mulesoft flow is executed.
Conclusion
The data loading can be done successfully if we create a similar flow by using the above components in Mulesoft. More customizations can be done in the flow based on the requirement.