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
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
The OAuth Refresh Token will only be valid for 90 days. After which user interaction is required to get new tokens assigned.
If a longer time is required, you may be able to contact Snowflake support to increase this timeout to 1 year: Snowflake OAuth FAQ
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;
Only run this if you need to use one of those roles as it can grant more access to the system than is wise.