Snowflake SQL Query

Executes SQL queries against a Snowflake database and returns the result set as a JSON object.

Revision History

1.0.0.0 Initial release
1.0.0.1 Improved error handling on Connection Secrets

Properties

Connection

Type: Connection Input

Url
Type: String
The URL for your Snowflake acount. Can be retrieved by viewing account details in the Snowflake application.

AccountIdentifier
Type: String
The account identifier for your Snowflake acount. Can be retrieved by viewing account details in the Snowflake application.

UserName
Type: String
The user name for the account you want to log in as. Can be retrieved by viewing account details in the Snowflake application, or under Users in the Admin section.

Database
Type: String
The name of the database you want to connect to.

Schema optional
Type: String
The name of a schema you want to limit the connection to. This is optional.

ClientSecrets
Type: Password
The OAuth client secrets JSON object is used to authenticate the login. See Activate OAuth section for more details on how to get this.

Role optional
Type: String
The role you want to use for the connection. This is not required as the user name's role will be used if no other role supplied here.

AccessToken
Type: Password
OAuth Access Token

RefreshToken
Type: Password
OAuth Refresh Token

Query

Type: Multiline Text Input
The SQL query text

Result

Type: Json Output
The Result Property

RowsAffected

Type: Int32 Output
This return the number of rows returned by SQL.

#Remarks

Use this connector to execute SQL queries against a Snowflake database.

SQL Parameters via Custom Properties

Flowgear Custom Properties can be added to this Node to make SQL parameters available in the Query Property. Use of a Custom Property is shown in the linked example workflow (see Examples below).

Other Nodes for SQL databases

If you are updating values in a table based on keys, consider using Snowflake SQL Table Update instead of crafting update statements.

If you need to integrate with a non-Microsoft SQL database, refer to Integrating with SQL Databases for additional information and connection strings.

Activate OAuth

You will need to activate OAuth on your Snowflake account to be able to use this connector. To activate OAuth you must create a security integration.
To do this you must navigate to the SQL Worksheet (accessed by clicking on the + in the left menu) and create a new SQL Worksheet.

You will then have to run the following command in that worksheet

CREATE OR REPLACE SECURITY INTEGRATION 
"Flowgear"
TYPE = OAUTH
OAUTH_CLIENT = CUSTOM
OAUTH_CLIENT_TYPE = "CONFIDENTIAL"
OAUTH_REDIRECT_URI = "https://app.flowgear.net/r/auth"
OAUTH_ISSUE_REFRESH_TOKENS = true
ENABLED = true;

This will create a security integration on your account that can then be used to create a connection. You will note that the creation does not supply you with the client secrets needed. To get those you must run a separate command.

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS( 'Flowgear' )

You can then copy the value returned from that as is and paste it in the ClientSecrets. The object will be parsed to get the client id/secret needed for OAuth.

By default, Snowflake prevents the ACCOUNTADMIN, ORGADMIN, GLOBALORGADMIN, and SECURITYADMIN roles from authenticating. To allow these roles to authenticate you will need to run the follow command.

ALTER ACCOUNT SET OAUTH_ADD_PRIVILEGED_ROLES_TO_BLOCKED_LIST = false;