Configure Snowflake REST API with OAuth 2 in Boomi

Snowflake exposes multiple APIs to interact with your data warehouse. The Snowflake REST API is one of the common APIs used to connect with Snowflake. This article will outline how to create a Snowflake Integration and configure the HTTP connection within Boomi.

Snowflake Configuration

Create Snowflake Custom Security Integration

First, a Snowflake Integration needs to be created. Execute the following Snowflake SQL command to create an Integration. boomi-account-id needs to be populated with your Boomi Account Id.

CREATE OR REPLACE SECURITY INTEGRATION OAUTH_BOOMI
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://platform.boomi.com/account/<boomi-account-id>/oauth2/callback'
  OAUTH_ENFORCE_PKCE = FALSE
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
  PRE_AUTHORIZED_ROLES_LIST = ('USERADMIN')
  BLOCKED_ROLES_LIST = ('SYSADMIN')
;

Table 1. Snowflake SECURITY INTEGRATION Parameters.

Parameters	Descriptions
TYPE	OAUTH is the default value
ENABLED	TRUE sets the Integration as active
OAUTH_CLIENT	CUSTOM is for Custom Integrations
OAUTH_CLIENT_TYPE	CONFIDENTIAL is used for clients that can securely store a secrete. Boomi can store a secrete securely
OAUTH_REDIRECT_URI	The redirect URI is the Boomi OAuth callback URL. The Boomi Account Id needs to be populated.
OAUTH_ENFORCE_PKCE	FALSE disables PKCE. Boomi only supports Authorization Code Flow without PKCE.
OAUTH_ISSUE_REFRESH_TOKENS	TRUE allows for a refresh token to be used
OAUTH_REFRESH_TOKEN_VALIDITY	7776000 is the default value. Range 3600 (1 hr) to 7776000 (90 days)
PRE_AUTHORIZED_ROLES_LIST	Users that are pre-authorized. Not required.
BLOCKED_ROLES_LIST	Users that are blocks. ACCOUNTADMIN, ORGADMIN, and SECURITYADMIN are blocked by default. Not required

IMPORTANT NOTE: OAUTH_REFRESH_TOKEN_VALIDITY will set how often someone will need to re-authenticate within Boomi.

Additional details can be found in Snowflake’s documentation.

Snowflake Integration Credentials and Configuration

Execute the following Snowflake SQL command to retrieve the Snowflake Integration credentials. The Snowflake Integration credentials will be used to configure the HTTP connection within Boomi.

The DESCRIBE statement will be used to obtain the Authorization and Token endpoint. OAUTH_AUTHORIZATION_ENDPOINT and OAUTH_TOKEN_ENDPOINT are the endpoints that will be used to configure the HTTP connection within Boomi. The statement can also be helpful to obtain information about the Integration.

DESCRIBE INTEGRATION OAUTH_BOOMI;

Figure 1. Snowflake DESCRIBE INTEGRATION results.

Execute the following Snowflake SQL command to retrieve the Snowflake Integration credentials. The Snowflake Integration credentials will be used to configure the HTTP connection within Boomi.

The Select statement will return OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET. Those will be used within Boomi’s HTTP connector.

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_BOOMI');
-- The integration name is case sensitive and needs to be all caps

Figure 2. Snowflake SYSTEM$SHOW_OAUTH_CLIENT_SECRETS results.

Boomi HTTP Configuration

Boomi HTTP Connection

The Boomi HTTP Connection will be used to configure the connection values used to connect to Snowflake. Use the values from the Snowflake Integration to configure the HTTP connection. Once populated, click save, and then click on Generate next to Access Token. A new browser window will open and then supply the User account credentials.

Figure 3. Boomi HTTP Connection configuration.

Table 2. Boomi HTTP Connection Parameters.

Parameters	Value
URL	Snowflake’s Base URL. Same as Base URL of OAUTH_AUTHORIZATION_ENDPOINT
Authentication Type	OAuth 2.0
Grant Type	Authorization Code
Client ID	Value from OAUTH_CLIENT_ID
Client Secret	Value from OAUTH_CLIENT_SECRET
Authorization Endpoint	Value from OAUTH_AUTHORIZATION_ENDPOINT
Access Token URL	Value from OAUTH_TOKEN_ENDPOINT
Refresh Authorization Scheme	Basic Authorization

Boomi HTTP Operation

The Boomi HTTP Operation will be configured to execute a SQL statement. There are additional REST API endpoints that can be found in Snowflake’s documentation.

The HTTP Operation is set up as a SEND, which means it expects an input document and a response can be returned if Return HTTP Responses is checked. The following is an example document that is being sent to the REST API and contains a SQL query.

{
  "statement": "SELECT * FROM ACCOUNT.PUBLIC.ACCOUNT_NAME;"
}

Figure 4. Boomi HTTP Operation configuration.

Table 3. Boomi HTTP Operation Parameters.

Parameters	Value
Content Type	application/json
HTTP Method	POST
Return HTTP Responses	Checked
Request Headers: Accept	/
Resource Path	api/v2/statements

References

The article was originally posted at Boomi Community.