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 is a table named RESERVATIONS that stores reservation details.
- Insert a single record into the Snowflake database.
- Insert multiple records into the Snowflake database.
- Query data from the Snowflake database.
- Delete data from the Snowflake database.
All operations are exposed via an API. The API with the context /snowflakeconnector
has 4 resources.
/insertReservation
: Insert a single reservation into 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.
- Create a database named
HOTEL_DB
in Snowflake. - Select the
PUBLIC
schema and create a table namedRESERVATIONS
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.
Set up the integration project¶
Follow the steps in the create integration project guide to set up the Integration Project.
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 | method |
---|---|
/insertReservation |
POST |
/getReservationInfo |
GET |
/insertReservationBatch |
POST |
/deleteReservation/{NICNUMBER} |
DELETE |
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 send the reservation payload in the request body.
-
In the API insequence add 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"/>
-
Add the
execute
operation from the SnowflakeConnector section.-
Then in the appearing window, you see an option to add a new connection. Click on
Add new connection
to initiate a new Snowflake Connection.- In the Connection Configurations section give a name for
Snowflake Connection
. - Provide your Snowflake Account Identifier in the
Account Identifier
text box. - Provide your Snowflake username in the
Username
text box. - Provide your Snowflake password in the
Password
text box. - Click Add.
- In the Connection Configurations section give a name for
-
Select the created connection for the
execute
operation. - 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 (?, ?, ?, ?, ?, ?, ?, ?, ?)
-
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>
-
-
Add the Respond Mediator to send back the response.
- /getReservationInfo¶
Using this resource users retrieve all records from table Reservations
of HOTEL_DB
.
-
Add the
query
operation from the SnowflakeConnector section.- Select the Snowflake connection configuration you created.
-
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>
-
Add the Property Mediator and set the Property name as
messageType
and the value asapplication/json
. This is added so that the response will be in JSON.<property name="messageType" scope="axis2" type="STRING" value="application/json"/>
- Add the Respond Mediator to send back the response.
- /insertReservationBatch¶
Using this resource users can insert multiple records into the table Reservations
of HOTEL_DB
. The user will send the bulk payload in the request body.
- In the API insequence add 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"/>
-
Add the
batchExecute
operation from the SnowflakeConnector section.- Select the Snowflake connection configuration you created.
- 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 (?, ?, ?, ?, ?, ?, ?, ?, ?)
-
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>
-
Add the Respond Mediator to send back the response.
- /deleteReservation/{NICNUMBER}¶
Using this resource users can delete a record in table Reservations
of HOTEL_DB
. The user will provide the NICNUMBER as a path parameter.
- In the API insequence add 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"/>
- Add the
execute
operation from the SnowflakeConnector section.- Select the Snowflake connection configuration you created.
- In the
Execute Query
text box, enter$ctx:deleteQuery
as an expression.<snowflake.execute configKey="SNOWFLAKE_CONNECTION"> <executeQuery>{$ctx:deleteQuery}</executeQuery> </snowflake.execute>
- Add the Property Mediator and set the Property name as
messageType
and the value asapplication/json
. This is added so that the response will be in JSON.<property name="messageType" scope="axis2" type="STRING" value="application/json"/>
- Add 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 name="payload" scope="default" type="STRING" expression="json-eval($)"/>
<snowflake.execute configKey="SNOWFLAKE_CONNECTION_1">
<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>
<faultSequence>
</faultSequence>
</resource>
<resource methods="GET" uri-template="/getReservationInfo">
<inSequence>
<snowflake.query configKey="SNOWFLAKE_CONNECTION_1">
<query>SELECT * FROM HOTEL_DB.PUBLIC.RESERVATIONS</query>
</snowflake.query>
<property name="messageType" scope="axis2" type="STRING" value="application/json"/>
<respond/>
</inSequence>
<faultSequence>
</faultSequence>
</resource>
<resource methods="POST" uri-template="/insertReservationBatch">
<inSequence>
<property expression="json-eval($)" name="payload" scope="default" type="STRING"/>
<snowflake.batchExecute configKey="SNOWFLAKE_CONNECTION_1">
<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>
<faultSequence>
</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_1">
<executeQuery>{$ctx:deleteQuery}</executeQuery>
</snowflake.execute>
<property name="messageType" scope="axis2" type="STRING" value="application/json"/>
<respond/>
</inSequence>
<faultSequence>
</faultSequence>
</resource>
</api>
Export integration logic as a carbon application¶
To export the project, please refer to the build and export the carbon application guide.
Get the project¶
You can download the ZIP file and extract the contents to get the project code.
You may need to update the values of Snowflake connection configurations before deploying and running this project.
Deployment¶
To deploy and run the project, please refer to the build and run guide.
You can further refer to the application deployed through the CLI tool. See the instructions on managing integrations from the CLI.
Test the resources¶
Let's test the API. Deploy the carbon application and start the Micro Integrator.
-
Insert Reservation Resource
- 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" }
- Invoke the API as shown below using the curl command.
Expected Response: You should get a response as below.
curl -H "Content-Type: application/json" --request POST --data @payload.json http://localhost:8290/snowflakeconnector/insertReservation
{ "operation":"execute", "isSuccessful":true, "message":"Rows affected : 1" }
- Create a file called
-
Insert Reservation Batch Resource
- 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" } ]
-
Invoke the API as shown below using the curl command.
Expected Response: You should get a response as below.curl -H "Content-Type: application/json" --request POST --data @payload.json http://localhost:8290/snowflakeconnector/insertReservationBatch
{ "operation":"batchExecute", "isSuccessful":true, "message":"Successfully executed 2 statements out of 2 statements." }
- Create a file called
-
Get Reservation Info Resource
- Invoke the API as shown below using the curl command.
Expected Response: You should receive a response as below.
curl -H "Content-Type: application/json" --request GET http://localhost:8290/snowflakeconnector/getReservationInfo
[ { "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" } ]
- Invoke the API as shown below using the curl command.
- Delete Reservation Resource
- Invoke the API as shown below using the curl command.
Expected Response: You should receive a response as below.
curl -H "Content-Type: application/json" --request DELETE http://localhost:8290/snowflakeconnector/deleteReservation/9876543210
{ "operation":"execute", "isSuccessful":true, "message":"Rows affected : 1" }
- Invoke the API as shown below using the curl command.
What's next¶
- To customize this example for your own scenario, see Snowflake Connector Configuration documentation for all operation details of the connector.