Snowflake REST SQL Connector

1 Introduction

The Snowflake REST SQL connector allows you to use data from Snowflake in your Mendix application and enrich your app with the capabilities that Snowflake provides.

1.1 Typical Use Cases

The Snowflake REST SQL connector provides a way to first setup key-pair authentication with an RSA key pair according to PKCS #8 standard, and then execute SQL statements on Snowflake via a REST call from within your Mendix application. These statements allow you to perform the following tasks:

• Read data from Snowflake
• Write data to Snowflake
• Trigger Snowflake Cortex ML functions
• Use Snowflake Cortex LLM functions

The current version of the connector supports the following actions:

• Authentication with an RSA key pair according to PKCS #8 standard
• Execution of single SQL statements
• Synchronous execution of calls

1.2 Prerequisites

The Snowflake REST SQL connector requires Mendix Studio Pro version 9.18.0 or above.

To use the Snowflake REST SQL connector, you must also install and configure the following modules from the Mendix marketplace:

• Community Commons - This module is a required dependency for the Snowflake REST SQL connector.
• Encryption - This module is a required dependency for the Snowflake REST SQL connector. The EncryptionKey constant must be set up in your application settings.

1.3 Licensing and Cost

This connector is available as a free download from the Mendix Marketplace, but the services in Snowflake to which is connects may incur a usage cost. For more information, refer to the Snowflake documentation.

Depending on your use case, your deployment environment, and the type of app that you want to build, you may also need a license for your Mendix app. For more information, refer to Licensing Apps.

2 Installation

Follow the instructions in How to Use Marketplace Content in Studio Pro to import the Snowflake REST SQL connector into your app.

3 Configuration

After you install the connector, you can find it in the App Explorer, in the SnowflakeRESTSQL section. The connector provides a domain model and several activities that you can use.

3.1 Configuring Snowflake Authentication

In order to use the capabilities of Snowflake in a Mendix app with the Snowflake REST SQL connector, an RSA key-pair authentication method must be used.

3.1.1 Configuring Key-pair Authentication in Snowflake

To configure RSA key-pair authentication for your account in Snowflake, perform the following steps:

1. Generate the private key.
2. Generate a public key.
3. Assign the public key to a Snowflake user.

For more details about each step, refer to the official Snowflake documentation.

3.1.2 Setting up the Key-pair Authentication in a Mendix App

To make it easier for users to configure the key-pair authentication in a Mendix app, the Snowflake REST SQL connector includes pages and microflows that you can simply drag and drop them into your own modules.

To configure the authentication, perform the following steps:

1. In the App Explorer, under the SnowflakeRESTSQL section, find the SNIPPET_SnowflakeConfiguration snippet and drag and drop it into a page in your module.

   

2. Assign the module role SnowflakeRESTSQL.Administrator to the application role that will be used to set up the configuration, so that the added logic will be usable.

3. Run the application and go to the page where you added the snippet.

4. Click New.

5. On the Connection details page, fill out all fields with the details of your Snowflake account. For more information, see ConnectionDetails.

6. Enter the passphrase and upload your private key file in .p8 format.

   

7. Click Save to save the connection, or click Save and test connection to generate a JSON Web Token (JWT) and validate your connection.

3.2 Configuring a Microflow for the Service

After you configure the authentication for Snowflake, you can implement the functions of the connector by using the provided activities in microflows. An extended microflow has been implemented and added to the Snowflake REST SQL connector as an example for users that would like to retrieve a list of objects from an existing table in Snowflake. In the SnowflakeRESTSQL module, see the ExampleImplementation microflow and the ExampleObject domain model entity to learn how the TransformResponsesToMxObjects operation can be used to easily convert the data received in HttpResponse objects into Mendix objects.

4 Technical Reference

To help you work with the Snowflake REST SQL connector, the following sections of this document list the available entities, enumerations, and activities that you can use in your application.

4.1 Domain Model

The domain model is a data model that describes the information in your application domain in an abstract way. For more information, see Domain Model.

4.1.1 ConnectionDetails

Name	Description
Name	An identifier of the connection inside the Mendix app. This property is not passed to Snowflake.
AccountURL	The unique account URL of the Snowflake account within your organization to connect to the Snowflake API. For more information, refer to the Snowflake documentation about account identifiers.
ResourcePath	The path to a resource in Snowflake API, for example, /api/v2/statements.
AccountIdentifier	A unique account identifier that identifies a Snowflake account within your organization, as well as throughout the global network of Snowflake-supported cloud platforms and cloud regions, for example, <orgname>-<account_name>. For more information, refer to the Snowflake documentation about account identifiers.
Username	The username with which you sign in to your Snowflake account.

4.1.2 PrivateKey

Name	Description
Passphrase	A passphrase which is used to encode and decode the private key file.

4.1.3 JWT

Name	Description
Token	Value of the JSON Web Token as a string.
ExpirationDate	Expiration date of the JSON Web Token.

4.1.4 Statement

Name	Description
SQLStatement	The SQL statement to execute.
Timeout	The amount of seconds after which the connection will be closed.
Database	The database to use.
Schema	The database schema to use, for example PUBLIC.
Warehouse	The warehouse to use for computations.
Role	The role to use to execute the SQL statement. The role sufficient permissions to execute the statement.

4.1.5 ResultSet

Name	Description
Code	Code that is returned from Snowflake as a response to the executed statement.
StatementHandle	Unique handle given to the statement that has been executed. It is saved as part of the ResultSet object. It can be used to retrieve the ResultSet object for each request.
Message	Message that is returned from Snowflake as a response to the executed statement.
NumRows	The amount of rows which will be returned by the executed statement. This is a sum of all the rows in the partitions.

4.1.6 PartitionInfo

Name	Description
RowCount	The number of rows within this partition. The sum of all PartitionInfo.RowCount corresponds to ResultSet.NumRows.

4.2 Activities

Activities define the actions that are executed in a microflow or a nanoflow.

4.2.1 ExecuteStatement

The ExecuteStatement activity allows you to execute a command in Snowflake using the SQL statement and the configuration details given in a Statement object. It requires a Statement object and returns a list of HttpResponse objects.

The input and output for this service are shown in the table below:

Input	Output
Statement	{HttpResponseList}

4.2.2 TransformResponsesToMxObjects

The TransformResponsesToMxObjects activity allows you to transform the list of HttpResponse objects into objects of the entity of your choice.

It requires a list of HttpResponse objects and the entity of the objects that you would like to create with the received information. It returns a list of Mendix objects of the entity given in the input.

Input	Output
HttpResponseList, EntityType	{EntityType}ObjectList

To showcase this, we have created an example entity in the domain model of the connector:

ExampleObject
ATTR_TXT (string)
ATTR_INT (integer)
ATTR_LONG (long)
ATTR_BOOL (boolean)
ATTR_DECI (decimal)
ATTR_ENUM (enumeration)
ParsedDate (date and time)

This entity is only an example. You must review properties such as the naming of the attributes, what datatypes they have or in which order they are added, and configure them according to your needs. This information is important after the entity has been decided on and the data will be received from a Snowflake account. The order in which you receive the columns from a Snowflake table, the name of these columns as well as the datatypes of these values must match the entity that you have selected.

For example, a table in Snowflake may contain multiple columns named column1, column2,.......,column8. To retrieve data from the column, create ExampleObject objects and display them on a page, you must execute an SQL statement that would retrieve the table columns with the name of my attributes and have the same datatypes. After making sure that the datatypes in Snowflake and Mendix match, you can execute a statement such as the one shown in the following example:

SELECT 
     column1 as ATTR_TXT,
     column2 as ATTR_INT,
     column3 as ATTR_LONG,
     column4 as ATTR_BOOL,
     column5 as ATTR_DECI,
     column6 as ATTR_ENUM
FROM your_table 

This statement returns data from a Snowflake table with the columns named as specified with the as **NewColumnName**" part of each line. If the attribute names, datatypes and their order match, the TransformResponsesToMxObjects activity automatically converts the retrieved data into Mendix objects.