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.

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.

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.

References

• Snowflake SQL REST API
• Configure Snowflake OAuth for Custom Clients
• Snowflake CREATE SECURITY INTEGRATION (Snowflake OAuth)
• Snowflake DESCRIBE INTEGRATION
• Snowflake SYSTEM$SHOW_OAUTH_CLIENT_SECRETS

The article was originally posted at Boomi Community.