Skip to main content

Snowflake Component

Snowflake is a cloud data platform. Use the Snowflake component to access and update data in a Snowflake Database.

Component key: snowflake

Description

Snowflake is a cloud data platform. Use the Snowflake component to access and update data in a Snowflake Database.

Connections

Snowflake Key Pair Authentication

Set up key-pair authentication. For more information refer to the following guide.

As part of this process, you must:

  1. Generate a public-private key pair. The generated private key should be in a file (e.g. named rsa_key.p8).
  2. Assign the public key to your Snowflake user. After you assign the key to the user, run the DESCRIBE USER command. In the output, the RSA_PUBLIC_KEY_FP property should be set to the fingerprint of the public key assigned to the user.
InputNotesExample
Account Identifier
string
/ Required
accountIdentifier
You can find the account identifier in the organization's account panel https://docs.snowflake.com/en/user-guide/admin-account-identifier
 
Passphrase
password
/ Required
passphrase
The passphrase for the provided private key (Required).
 
Private Key
text
/ Required
privateKey
The private key for the Snowflake Key Pair Authentication.
 
Snowflake Username
string
/ Required
username
Your Snowflake username.
MY_ACCOUNT.API_DEMO_USER

Snowflake OAuth 2.0

Snowflake will use OAuth 2.0 for authentication and making API calls on behalf of customers.

Follow these steps to set up an OAuth integration on your snowflake. More information for these steps can be found here https://docs.snowflake.com/en/user-guide/oauth-custom

  1. Login to Snowflake and create a worksheet to set a new integration.
  2. Create a new security integration by copying the following SQL statement into Snowflake and selecting the Play button at the top right of the screen. Make sure to change the INTEGRATIONNAME to something you will remember
CREATE SECURITY INTEGRATION
INTEGRATIONNAME
TYPE = OAUTH
OAUTH_CLIENT = CUSTOM
OAUTH_REDIRECT_URI = 'https://oauth2.prismatic.io/callback'
OAUTH_CLIENT_TYPE = 'PUBLIC'
  1. Run The following SQL statement to get your Auth URL, Token URL, and Client ID. Change INTEGRATIONNAME to the name you used during the create statement.
DESCRIBE INTEGRATION INTEGRATIONNAME
  1. Run the following SQL statement to get your Client Secret.

    a. Its important that you use the secret listed as AUTH_CLIENT_SECRET from the return in Prismatic

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('INTEGRATIONNAME')
  1. Run the following SQL statement to enable the Integration.
ALTER SECURITY INTEGRATION INTEGRATIONNAME SET ENABLED = TRUE
  1. Enter your Authorization URL, Token URL, Client ID, and Client Secret into Prismatic.

    a. Once attempting to authenticate its important to note that users with the roles of ACCOUNTADMIN, SECURITYADMIN, and ORGADMIN will receive an “invalid consent request error” message as snowflake blocks these roles by default. We suggest authenticating with a different user without these roles assigned.

InputNotes
Authorize URL
string
/ Required
authorizeUrl
The OAuth 2.0 Authorization URL for the API
Client ID
string
/ Required
clientId
Client Identifier of your app for the API
Client Secret
password
/ Required
clientSecret
Client Secret of your app for the API
Headers
string
Key Value List
headers
Additional header to supply to authorization requests
Scopes
string
scopes
Space separated OAuth 2.0 permission scopes for the API
Token URL
string
/ Required
tokenUrl
The OAuth 2.0 Token URL for the API

Actions

Execute SQL

Executes one or more SQL statements in your Snowflake DB. | key: executeSql

InputDefaultNotesExample
Account Locator
string
/ Required
accountLocator
You can find the account locator in the organization's account panel or https://docs.snowflake.com/en/user-guide/admin-account-identifier#finding-the-region-and-locator-for-an-account
xy12345
Bindings
code
bindings
Values of bind variables in the SQL statement. Bindings should have this format:
Database
string
database
Database in which the statement should be executed.
TESTDB
Debug Request
boolean
debug
false
Enabling this flag will log out the current request.
 
Number of statements to execute
string
nStatementsToExecute
0 Indicates that a variable number of statements can be included in the request.
1
Parameters
code
parameters
Session parameters that you want to set for this request. Parameters should have this format:
Role
string
role
Role to use when executing the statement.
TESTROLE
Schema
string
schema
Schema in which the statement should be executed.
TESTSCHEMA
Poll for asynchronous results
boolean
shouldPoll
If true, action will handle polling for results on queries that take > 45 seconds to execute, If false, action will return immediately after executing the query.
 
Connection
connection
/ Required
snowflakeConnectionInput
 
 
 
Snowflake Identifier URL
string
/ Required
snowflakeUrl
The Snowflake URL for you account. Has to have the format: https://<account-name>.snowflakecomputing.com
https://myorg-account123.snowflakecomputing.com
SQL statements to run
code
/ Required
sqlInput
 
 
Timeout
string
timeout
Timeout in seconds for statement execution. If the execution of a statement takes longer than the specified timeout, the execution is automatically canceled.
60
Warehouse
string
warehouse
Warehouse to use when executing the statement.
TESTWH

Get Statement Handle

Retrieve the current status of a executed statement from Snowflake. | key: getStatementHandle

InputDefaultNotesExample
Account Locator
string
/ Required
accountLocator
You can find the account locator in the organization's account panel or https://docs.snowflake.com/en/user-guide/admin-account-identifier#finding-the-region-and-locator-for-an-account
xy12345
Debug Request
boolean
debug
false
Enabling this flag will log out the current request.
 
Partition
string
partition
The partition number to retrieve.
1
Connection
connection
/ Required
snowflakeConnectionInput
 
 
 
Snowflake Identifier URL
string
/ Required
snowflakeUrl
The Snowflake URL for you account. Has to have the format: https://<account-name>.snowflakecomputing.com
https://myorg-account123.snowflakecomputing.com
Statement Handle ID
string
/ Required
statementHandleId
The ID of the statement handle.
01234567-89ab-cdef-0123-456789abcdef