Snowflake Connector Example

The Snowflake Connector allows you to access the Snowflake JDBC Driver API from an integration sequence. This provides functionalities to execute a set of standard Snowflake DDL, DML and query commands. You can use the connector to perform query, execute and batch execute operations on Snowflake databases.

What you'll build

This example demonstrates how to use the Snowflake Connector for querying, inserting, batch inserting and deleting data in a Snowflake database. Let's consider a use-case involving a hotel database for managing reservations. Within the hotel database, there exists a table named RESERVATIONS responsible for storing reservation details.

1. Insert a single record to the Snowflake database.
2. Insert multiple records to the Snowflake database.
3. Query data from the Snowflake database.
4. Delete data from the Snowflake database.

All operations are exposed via an API. The API with the context /snowflakeconnector has 5 resources.

• /insertReservation : Insert a single reservation to the Snowflake database.
• /insertReservationBatch : Insert a batch of reservations to the Snowflake database.
• /getReservationInfo : Retrieve all the reservations from the Snowflake database.
• /deleteReservation : Delete a reservation from the Snowflake database.

Before you begin

Before you begin, you must have a valid Snowflake account. To use the Snowflake database, you must have a valid Snowflake account. To create a snowflake account, please visit the official Snowflake website and complete the registration process. Once registered you will obtain a username and password with which you can log in to your Snowflake account, and the account identifier which is the unique identifier for your Snowflake account within your business entity and the Snowflake network.

1. Create a database named HOTEL_DB in Snowflake.
2. Select the PUBLIC schema and create a table named RESERVATIONS with the following columns.

create table RESERVATIONS (
NICNUMBER String,
FIRSTNAME String,
LASTNAME String,
CHECKIN String,
CHECKOUT String,
ADULTS INT,
CHILDREN INT,
ROOMTYPE String,
SPECIALREQUESTS String
)

If you do not want to configure this yourself, you can simply get the project and run it.

Configure the connector in WSO2 Integration Studio

Connectors can be added to integration flows in WSO2 Integration Studio. Once added, the operations of the connector can be dragged onto your canvas and added to your sequences.

Import the connector

Follow these steps to set up the Integration Project and the Connector Exporter Project.

1. Open WSO2 Integration Studio and create an Integration Project.

2. Right-click the project that you created and click on Add or Remove Connector -> Add Connector. You will get directed to the WSO2 Connector Store.

3. Search for the specific connector required for your integration scenario and download it to the workspace.

4. Click Finish, and your Integration Project is ready. The downloaded connector is displayed on the side palette with its operations.

5. You can drag and drop the operations to the design canvas and build your integration logic.

6. Right click on the created Integration Project and select New -> Rest API to create the REST API.

Add integration logic

First, create a REST API called SnowflakeConnectorApi in your project.

Name	Context
SnowflakeConnectorApi	/snowflakeconnector

Create the following resources in the 'SnowflakeConnectorApi' REST API.

uri-template
/insertReservation
/getReservationInfo
/insertReservationBatch
/deleteReservation

Let's add the operations to the resources in the SnowflakeConnectorApi API.

- /insertReservation

Users can utilize this resource to insert a single record into the Snowflake database. The user will be sending the reservation payload in the request body.

1. In the API insequence drag and drop the Property Mediator to extract the payload from the request body. Let's store the payload in a property named payload. xml <property expression="json-eval($)" name="payload" scope="default" type="STRING"/>

2. Drag and drop the execute operation from the SnowflakeConnector section.

3. Double click the operation. It will show you the properties section.
4. In the General Section of the properties, click on the + button next to Snowflake Connection.
   1. In the Connection Configurations section give a name for Snowflake Connection.
   2. Provide your Snowflake Account Identifier in the Account Identifier text box.
   3. Provide your Snowflake username in the Username text box.
   4. Provide your Snowflake password in the Password text box.
   5. Click Finish.
5. In the Execute Query text box, enter the following query.

   INSERT INTO HOTEL_DB.PUBLIC.RESERVATIONS (NICNUMBER, FIRSTNAME, LASTNAME, CHECKIN, CHECKOUT, ADULTS, CHILDREN, ROOMTYPE, SPECIALREQUESTS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
   

6. For the Payload text box, enter $ctx:payload as an expression.

   <snowflake.execute configKey="SNOWFLAKE_CONNECTION">
     <executeQuery>INSERT INTO HOTEL_DB.PUBLIC.RESERVATIONS (NICNUMBER, FIRSTNAME, LASTNAME, CHECKIN, CHECKOUT, ADULTS, CHILDREN, ROOMTYPE, SPECIALREQUESTS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)</executeQuery>
     <payload>{$ctx:payload}</payload>
   </snowflake.execute>
   

   3. Drag and drop the Respond Mediator to send back the response.

- /getReservationInfo

Using this resource users retrieve all records from table Reservations of HOTEL_DB.

1. Drag and drop the query operation from the SnowflakeConnector section.
2. Double-click the operation to view its properties section.
3. In the 'General' section of the properties, select the Snowflake connection configuration you created.

4. In the Sql Query text box, enter the following query.

   SELECT * FROM HOTEL_DB.PUBLIC.RESERVATIONS
   

   <snowflake.query configKey="SNOWFLAKE_CONNECTION">
      <query>SELECT * FROM HOTEL_DB.PUBLIC.RESERVATIONS</query>
   </snowflake.query>
   

5. Drag and drop the Property Mediator and set the Property name as messageType and the value as application/json. This is added so that the response will be in JSON.

    <property name="messageType" scope="axis2" type="STRING" value="application/json"/>
   

6. Drag and drop the Respond Mediator to send back the response.

- /insertReservationBatch

Using this resource users can insert multiple records into table Reservations of HOTEL_DB. The user will be sending the bulk payload in the request body.

1. In the API insequence drag and drop the Property Mediator to extract the payload from the request body. Let's store the payload in a property called payload.

     <property expression="json-eval($)" name="payload" scope="default" type="STRING"/>
   

2. Drag and drop the batchExecute operation from the SnowflakeConnector section.
3. Double-click the operation to view its properties section.
4. In the 'General' section of the properties, select the Snowflake connection configuration you created.
5. In the Execute Query text box, enter the following query.

   INSERT INTO HOTEL_DB.PUBLIC.RESERVATIONS (NICNUMBER, FIRSTNAME, LASTNAME, CHECKIN, CHECKOUT, ADULTS, CHILDREN, ROOMTYPE, SPECIALREQUESTS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
   

6. For the Payload text box, enter $ctx:payload as an expression.

   <snowflake.batchExecute configKey="SNOWFLAKE_CONNECTION">
      <executeQuery>INSERT INTO HOTEL_DB.PUBLIC.RESERVATIONS (NICNUMBER, FIRSTNAME, LASTNAME, CHECKIN, CHECKOUT, ADULTS, CHILDREN, ROOMTYPE, SPECIALREQUESTS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)</executeQuery>
      <payload>{$ctx:payload}</payload>
   </snowflake.batchExecute>
   

   5. Drag and drop the Respond Mediator to send back the response.

- /deleteReservation

Using this resource users can delete a record in table Reservations of HOTEL_DB. The user will provide the NICNUMBER as a path parameter.

1. In the API insequence drag and drop the Property Mediator to construct the delete query. Let's store the query in a property named deleteQuery.

     <property expression="fn:concat('DELETE FROM HOTEL_DB.PUBLIC.RESERVATIONS WHERE NICNUMBER=',get-property('uri.var.NICNUMBER'))" name="deleteQuery" scope="default" type="STRING"/>
   

2. Drag and drop the execute operation from the SnowflakeConnector section.
3. Double-click the operation to view its properties section.
4. In the 'General' section of the properties, select the Snowflake connection configuration you created.
5. In the Execute Query text box, enter $ctx:deleteQuery as an expression.

   <snowflake.execute configKey="SNOWFLAKE_CONNECTION">
     <executeQuery>{$ctx:deleteQuery}</executeQuery>
   </snowflake.execute>
   

6. Drag and drop the Property Mediator and set the Property name as messageType and the value as application/json. This is added so that the response will be in JSON.

    <property name="messageType" scope="axis2" type="STRING" value="application/json"/>
   

7. Drag and drop the Respond Mediator to send back the response.

The resources are now ready to be tested. The API source should resemble the following. Expand to see.

<?xml version="1.0" encoding="UTF-8"?>
<api context="/snowflakeconnector" name="SnowflakeConnectorApi" xmlns="http://ws.apache.org/ns/synapse">
   <resource methods="POST" uri-template="/insertReservation">
      <inSequence>
         <property expression="json-eval($)" name="payload" scope="default" type="STRING"/>
         <snowflake.execute configKey="SNOWFLAKE_CONNECTION">
            <executeQuery>INSERT INTO HOTEL_DB.PUBLIC.RESERVATIONS (NICNUMBER, FIRSTNAME, LASTNAME, CHECKIN, CHECKOUT, ADULTS, CHILDREN, ROOMTYPE, SPECIALREQUESTS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)</executeQuery>
            <payload>{$ctx:payload}</payload>
         </snowflake.execute>
         <respond/>
      </inSequence>
      <outSequence/>
      <faultSequence/>
   </resource>
   <resource methods="GET" uri-template="/getReservationInfo">
      <inSequence>
         <snowflake.query configKey="SNOWFLAKE_CONNECTION">
            <query>SELECT * FROM HOTEL_DB.PUBLIC.RESERVATIONS</query>
         </snowflake.query>
         <property name="messageType" scope="axis2" type="STRING" value="application/json"/>
         <respond/>
      </inSequence>
      <outSequence/>
      <faultSequence/>
   </resource>
   <resource methods="POST" uri-template="/insertReservationBatch">
      <inSequence>
         <property expression="json-eval($)" name="payload" scope="default" type="STRING"/>
         <snowflake.batchExecute configKey="SNOWFLAKE_CONNECTION">
            <executeQuery>INSERT INTO HOTEL_DB.PUBLIC.RESERVATIONS (NICNUMBER, FIRSTNAME, LASTNAME, CHECKIN, CHECKOUT, ADULTS, CHILDREN, ROOMTYPE, SPECIALREQUESTS) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)</executeQuery>
            <payload>{$ctx:payload}</payload>
         </snowflake.batchExecute>
         <respond/>
      </inSequence>
      <outSequence/>
      <faultSequence/>
   </resource>
   <resource methods="DELETE" uri-template="/deleteReservation/{NICNUMBER}">
      <inSequence>
         <property expression="fn:concat('DELETE FROM HOTEL_DB.PUBLIC.RESERVATIONS WHERE NICNUMBER=',get-property('uri.var.NICNUMBER'))" name="deleteQuery" scope="default" type="STRING"/>
         <snowflake.execute configKey="SNOWFLAKE_CONNECTION">
            <executeQuery>{$ctx:deleteQuery}</executeQuery>
         </snowflake.execute>
         <property name="messageType" scope="axis2" type="STRING" value="application/json"/>
         <respond/>
      </inSequence>
      <outSequence/>
      <faultSequence/>
   </resource>
</api>

Exporting Integration Logic as a CApp

CApp (Carbon Application) is the deployable artifact on the integration runtime. Let us see how we can export integration logic we developed into a CApp along with the connector.

Creating Connector Exporter Project

To bundle a Connector into a CApp, a Connector Exporter Project is required.

1. Navigate to File -> New -> Other -> WSO2 -> Extensions -> Project Types -> Connector Exporter Project.
   

   

2. Enter a name for the Connector Exporter Project.

3. In the next screen select, Specify the parent from workspace and select the specific Integration Project you created from the dropdown.
   

4. Now you need to add the Connector to Connector Exporter Project that you just created. Right-click the Connector Exporter Project and select, New -> Add Remove Connectors -> Add Connector -> Add from Workspace -> Connector

5. Once you are directed to the workspace, it displays all the connectors that exist in the workspace. You can select the relevant connector and click Ok.
   

   

Creating a Composite Application Project

To export the Integration Project as a CApp, a Composite Application Project needs to be created. Usually, when an Integration project is created, this project can be created as part of that project by Integration Studio. If not, you can specifically create it by navigating to File -> New -> Other -> WSO2 -> Distribution -> Composite Application Project.

Exporting the Composite Application Project

1. Right-click the Composite Application Project and click Export Composite Application Project.
   

   

2. Select an Export Destination where you want to save the .car file.

3. In the next Create a deployable CAR file screen, select both the created Integration Project and the Connector Exporter Project to save and click Finish. The CApp is created at the specified location provided at the previous step.
   

   

Get the project

You can download the ZIP file and extract the contents to get the project code.

Tip

You may need to update the values of Snowflake connection configurations before deploying and running this project.

Deployment

Follow these steps to deploy the exported CApp in the integration runtime.

Deploying on Micro Integrator

You can copy the composite application to the <PRODUCT-HOME>/repository/deployment/server/carbonapps folder and start the server. Micro Integrator will be started and the composite application will be deployed.

You can further refer the application deployed through the CLI tool. See the instructions on managing integrations from the CLI.

Click here for instructions on deploying on WSO2 Enterprise Integrator 6

1. You can copy the composite application to the <PRODUCT-HOME>/repository/deployment/server/carbonapps folder and start the server.

2. WSO2 EI server starts and you can login to the Management Console https://localhost:9443/carbon/ URL. Provide login credentials. The default credentials will be admin/admin.

3. You can see that the API is deployed under the API section.

Testing the resources

Let's test the API. Deploy the carbon application and start the Micro Integrator.

1. Insert Reservation Resource

   1. Create a file called payload.json with the following payload.

      {
          "NICNUMBER": "9876543210",
          "FIRSTNAME": "Alice",
          "LASTNAME": "Johnson",
          "CHECKIN": "2023-10-01",
          "CHECKOUT": "2023-10-05",
          "ADULTS": 1,
          "CHILDREN": 0,
          "ROOMTYPE": "Single",
          "SPECIALREQUESTS": "Quiet room"
      }
      

   2. Invoke the API as shown below using the curl command.

      curl -H "Content-Type: application/json" --request POST --data @payload.json http://localhost:8290/snowflakeconnector/insertReservation
      

      Expected Response: You should get a response as below.

      {
        "operation":"execute",
        "isSuccessful":true,
        "message":"Rows affected :  1"
      }
      

2. Insert Reservation Batch Resource

3. Create a file called payload.json with the following payload.

   [
     {
        "NICNUMBER": "2345678901",
        "FIRSTNAME": "Emma",
        "LASTNAME": "Williams",
        "CHECKIN": "2024-06-01",
        "CHECKOUT": "2024-06-05",
        "ADULTS": "1",
        "CHILDREN": "0",
        "ROOMTYPE": "Single",
        "SPECIALREQUESTS": "Late check-in"
      },
      {
         "NICNUMBER": "1234567890",
         "FIRSTNAME": "Bob",
         "LASTNAME": "Smith",
         "CHECKIN": "2023-10-01",
         "CHECKOUT": "2023-10-05",
         "ADULTS": 2,
         "CHILDREN": 1,
         "ROOMTYPE": "Double",
         "SPECIALREQUESTS": "Extra bed"
      }
   ]
   

   1. Invoke the API as shown below using the curl command.

      curl -H "Content-Type: application/json" --request POST --data @payload.json http://localhost:8290/snowflakeconnector/insertReservationBatch
      

      Expected Response: You should get a response as below. json { "operation":"batchExecute", "isSuccessful":true, "message":"Successfully executed 2 statements out of 2 statements." }

4. Get Reservation Info Resource

5. Invoke the API as shown below using the curl command.

   curl -H "Content-Type: application/json" --request GET http://localhost:8290/snowflakeconnector/getReservationInfo
   

   Expected Response: You should receive a response as below.

   [
     {
       "NICNUMBER":"9876543210",
       "FIRSTNAME":"Alice",
       "LASTNAME":"Johnson",
       "CHECKIN":"2023-10-01",
       "CHECKOUT":"2023-10-05",
       "ADULTS":"1",
       "CHILDREN":"0",
       "ROOMTYPE":"Single",
       "SPECIALREQUESTS":"Quiet room"
     },
     {
       "NICNUMBER":"2345678901",
       "FIRSTNAME":"Emma",    
       "LASTNAME":"Williams",
       "CHECKIN":"2024-06-01",
       "CHECKOUT":"2024-06-05", 
       "ADULTS":"1",
       "CHILDREN":"0",
       "ROOMTYPE":"Single",
       "SPECIALREQUESTS":"Late check-in"
     },
     {
       "NICNUMBER":"1234567890",
       "FIRSTNAME":"Bob",
       "LASTNAME":"Smith",
       "CHECKIN":"2023-10-01",
       "CHECKOUT":"2023-10-05",
       "ADULTS":"2",
       "CHILDREN":"1",
       "ROOMTYPE":"Double",
       "SPECIALREQUESTS":"Extra bed"
     }
   ]
   

6. Delete Reservation Resource
   1. Invoke the API as shown below using the curl command.

      curl -H "Content-Type: application/json" --request DELETE http://localhost:8290/snowflakeconnector/deleteReservation/9876543210
      

      Expected Response: You should receive a response as below.

      {
        "operation":"execute",
        "isSuccessful":true,
        "message":"Rows affected :  1"
      }
      

What's Next

• To customize this example for your own scenario, see Snowflake Connector Configuration documentation for all operation details of the connector.