Snowflake S3 OAuth2 Account

Overview

You can use this account type to connect Snowflake Snaps with data sources that use a Snowflake S3 OAuth2 account. Snowflake OAuth uses Snowflake’s built-in OAuth service to provide OAuth-based authentication.

Prerequisites

Create a Security Integration in Snowflake to generate a client ID and a client secret. Learn more about generating a Client ID and a Client Secret in Snowflake.

Limitations and Known Issues

Note:
  • If an S3 bucket is specified in the SnapLogic Snowflake Account, the S3 credentials are validated as follows:
    • The S3 access-key ID and S3 secret key specified are used to create an S3 connection.
      • If the S3 access-key ID and S3 secret key are not specified, the Snap uses the IAM role instead.
      • If the Snap is not able to write to the S3 bucket, validation ends with an error stating that the Snap is unable to write to the specified S3 bucket.
    • If the Snap is able to write to (but not delete from) the specified S3 bucket, validation ends with an error indicating that the configuration is not able to delete from the S3 bucket.
    • The S3 AWS token is also validated if specified.
      • Note that only global Security Token Service (STS) regions are supported.
  • If an S3 bucket isn’t specified in the SnapLogic Account, no validation of S3 credentials occurs.

When refreshing the access token, the Snowflake API prevents you from getting a new refresh token as the refresh tokens are short lived with a validity of 90 days (7776000 seconds).

Solution: To get a new token after every 90 days you must reauthorize your Snowflake account for the token to be valid for the next 90 days. We recommend you to set the oauth_refresh_token_validity to 7776000 seconds as shown below when creating the Security Integration in Snowflake.

alter integration <integration name> 
set oauth_refresh_token_validity = 7776000;

Account settings


Snowflake S3 oAuth2 account

Legend:
  • Expression icon (): Allows using JavaScript syntax to access SnapLogic Expressions to set field values dynamically (if enabled). If disabled, you can provide a static value. Learn more.
  • SnapGPT (): Generates SnapLogic Expressions based on natural language using SnapGPT. Learn more.
  • Suggestion icon (): Populates a list of values dynamically based on your Snap configuration. You can select only one attribute at a time using the icon. Type into the field if it supports a comma-separated list of values.
  • Upload : Uploads files. Learn more.
Learn more about the icons in the Snap settings dialog.
Field / Field set Type Description
Label String

Required. Specify a unique label for the account.

Default: None

Example: SnowflakeOauth2Account_Test
Client ID String

Required. Specify the OAuth Client ID (to be used for token request) that you obtain from the Snowflake Console. Learn more about How to generate OAuth Client ID and Client secret.

Default value: N/A

Example: GZxuj932klnbue8=
Client secret String

Required. Specify the OAuth Client secret that you obtain from the Snowflake Console.

Default value: N/A

Example: !tz@wld*(687
Access token String

Required. Auto-generated upon account authorization. The access token is used to make API requests on behalf of the user associated with the client ID.

Default value: N/A

Example: <Encrypted>
Refresh token String

Auto-generated upon account authorization. The token used to refresh the access token.

To access the API beyond the lifetime of a single access token, your application can obtain a refresh token. The application stores the refresh token for future use and automatically refreshes the access token before it expires.

Default value: N/A

Example: encrypted
Access token expiration Integer Auto-generated upon account authorization. The number of seconds after which the access token expires.
Note: We recommend you to set the oauth_refresh_token_validity to 7776000 seconds when creating the Security Integration in Snowflake as this is the maximum time Snowflake allows for obtaining refresh tokens.

Default value: N/A

Example: 6541
Header authenticated Checkbox Select this checkbox if the endpoint uses bearer header authentication.

Default status: Deselected
OAuth2 authorization endpoint String Required. Specify the endpoint in this format https://<account_identifier>.snowflakecomputing.com/oauth/authorize to authorize the application. Account identifier is the full name of your account that is provided by Snowflake.

Default value: N/A

Example: https://myaccount.snowflakecomputing.com/oauth/authorize
OAuth2 token endpoint String Required. Specify the OAuth2 token in this format https://<account_identifier>.snowflakecomputing.com/oauth/token-request to get the access token.

Default value: N/A

Example: https://myaccount.snowflakecomputing.com/oauth/token-request
Grant type Dropdown list Select one of the following Grant types for authorization:
  • Password: Obtains access token using your login credentials (username and password). When selected, it populates the following fields:
    • Username: Enter the username of the account type.
    • Password: Enter the password of the account type.
  • authorization_code: Authentication using credentials (username and password), which return to the client through a redirect URL. The application then receives the authorization code from the URL and uses it to request an access token.
  • client_credentials: Obtains an access token for the client ID and client secret through the token endpoint URL.

Default value: N/A

Example: client_credentials
Username String Specify the username to connect to the Snowflake database server.
Note: Appears when you select Password for Authentication Type.
Password String Specify the password associated with the username specified above. This will be used as the default password while retrieving connections.
Note: Appears when you select Password for Authentication Type.
Token endpoint config Use this field to define custom properties for the OAuth2 token endpoint. Depending on the request parameters, this endpoint returns access tokens or refresh tokens.
Token endpoint parameter String

Specify the parameter for the token endpoint.

Default value: N/A

Example: redirect_uri
Token endpoint parameter value String

Specify the value for the token endpoint parameter.

Default value: N/A

Example:
  • https://elastic.snaplogic.com/api/1/snowflake/admin/oauth2callback/snowflake
  • https://elastic.snaplogic.com/api/1/snowflake/admin/oauth2callback/snowflake"
Authorization endpoint config Use this fieldset to define custom properties for the OAuth2 authentication endpoint.
Note: You can define scopes in this field set and limit the authorization to a custom role. For example, scope=session:role:R1. Learn more.
Authentication parameter String

Specify the parameter for OAuth2 authentication.

Default value: N/A

Example: redirect_uri
Authentication parameter value String

Specify the value for the OAuth2 authentication parameter.

Default value: N/A

Example: https://elastic.snaplogic.com/api/1/snowflake/admin/oauth2callback/snowflake
Auto-refresh token Checkbox Select this checkbox to enable auto-refresh of the access token before it expires.

Default status: Deselected
JDBC JARs Use this field set to add a list of JDBC JAR files to be loaded. By default, the Snowflake account is bundled with the JDBC driver version 3.24.2. However, you can add a custom JAR file. Click + to add a new row for each JDBC JAR file. Add each JAR file in a separate row. See Downloading the JDBC driver for more information about JDBC drivers and downloading the appropriate driver for your account.
JDBC Driver String

Specify the fully-qualified name of the JDBC driver class to be used for connecting to the server.

Note: The Snowflake Snap Pack is bundled with the default Snowflake JDBC driver v3.24.2. Therefore, even if you do not provide a JDBC Driver, the account does not fail.

Default value: N/A

Example: snowflake-jdbc-3.24.2.jar
Hostname String/Expression Required. Required. Specify the hostname of the Snowflake server to connect the new account.

Default value: N/A

Example: demo.snowflake.net
Port Number Integer/Expression Required. Specify the port number associated with the Snowflake database server that you must use for this account.

Default value: 443

Example: 332
Database name String/Expression Required. Specify the Snowflake database to connect to.

Default value: N/A

Example: snapsdb
Warehouse name String/Expression Required. Specify the name of the warehouse to which you want to connect.

Default: None

Example: SL_WH
JDBC driver class String Specify the JDBC driver class to use.

Default value: net.snowflake.client.jdbc.SnowflakeDriver

Example: net.snowflake.client.jdbc.SnowflakeDriver
S3 bucket String Specify the name of the S3 bucket that you want to use for staging data to Snowflake.

  • If you want to delete the temporary files from the S3 Bucket, we recommend you assign the delete object permission policy to delete the files. Learn how to assign delete object permission to an S3 user in AWS S3.

  • If you do not want to delete the temporary files, you can add an error view to the Snap and run the pipeline

Default: None

Example: sl-bucket-ca
S3 folder String/Expression Specify the relative path to a folder in the S3 bucket listed in the S3 Bucket field. This is used as a root folder for staging data to Snowflake.

Default: None

Example: sl-bucket-cas3/test
S3 access-key ID String/Expression Specify the S3 access key ID that you want to use for AWS authentication.

Default: None

Example: NAVRGGRV7EDCFVLKJH
S3 secret key String/Expression Specify the S3 secret key associated with the S3 Access-ID key listed in the S3 Access-key ID field.

Default: None

Example: 2RGiLmL/6bCujkKLaRuUJHY9uSDEjNYr+ozHRtg
S3 AWS token String/Expression Specify the S3 AWS Token to connect to private and protected Amazon S3 buckets.
Note: Only global Security Token Service (STS) regions are supported.

Default: None

Example: AQoDYXdzEJr
S3 storage integration String/Expression Specify the S3 Storage Integration for Snowflake to be used for staging data instead of using AWS Acces-key ID and S3 Secret key. This value is necessary for validating data after a bulk load or bulk insert operation.
URL Properties Use this field set to define additonal URL properties to use if any.
Note: Granting roles

You can grant roles to the SnapLogic Snowflake account by using the role parameter in the URL Properties field set. Ensure that the roles are predefined in your Snowflake account before configuring the SnapLogic Snowflake account.

See Roles and Grant Roles for details.
URL property name String Specify the name of the URL property.

Default: None

Example: queryTimeout
URL property value String Specify the URL property value associated with the URL property name.

Default: None

Example: 4
Container String/Expression Specify the name of the Azure storage blob container that you want to use for hosting files.

Default: None

Example: Container1
Path String/Expression Specify the location of the folder in the container listed above where you want to host files.

Default: None

Example: System Generated
Shared Access Signature (SAS) token method Dropdown list Specify the method of supplying the SAS token to the Snaps. You can choose between the following two options:
  • User Supplied: Choose this option if you intend to manually enter the shared access token signature.
    Note: If you opt for the User Supplied option, then you need to ensure that your tokens are valid whenever the pipeline is run; else, the pipelines will fail. For more information, see Generating a SAS Token in Snowflake documentation.
  • System Generated: Choose this option if you want Snaps to generate and use the SAS tokens as and when required.
Default value: User Supplied
User token String/Expression Specify the shared access token that you want to use to access the Azure storage blob folder specified in the Path above. You can get a valid SAS token from the Azure portal.
Note: This property is applicable only when you choose User Supplied in the Shared Access Token Signature Method field above.

Default: None
Client side encryption Dropdown list Select either of the following options to encrypt the blob before uploading to Microsoft Azure:
  • None: Indicates that you do not want to use client-side encryption.

  • Custom_Key: Indicates that you want to use a custom key to access the storage blob.

Default: None

Example: Custom_Key
Custom Key String/Expression Specify the custom key that you want to use to access the Azure storage blob. This property is applicable only when you select Custom_Key in the Client side encryption field above. The key should be a 128- or 256-bit Base64-encoded key.
Batch size Integer/Expression Specify the number of statements that you want to execute at a time.
  • Select queries are not batched.
  • Using a large batch size could use up the JDBC placeholder limit of 2100.

Default value: 50

Example: 40
Fetch size Integer/Expression Specify the number of rows you want a query to fetch during each execution.
Note: Large values could cause the server to run out of memory.

Default value: 100

Example: 200
Min pool size Integer/Expression Specify the minimum number of idle connections that you want the pool to maintain at a time.

Default value: 100

Example: 200
Max pool size Integer/Expression Specify the maximum number of connections that you want the pool to maintain at a time.
Note: Snowflake Bulk Load/Bulk Upsert/S3 Upsert Snap requires a minimum of 2 connections per Snap in a pipeline. For example, if a pipeline has a Snowflake Bulk Load Snap and an S3 Upsert Snap, then the pool size must be greater than or equal to 4 for successful execution.
  • Minimum value: 0
  • Maximum value: No limit

Default value: 15

Example: 40
Max lifetime (minutes) Integer/Expression Specify the maximum lifetime of a connection in the pool. Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit. A value of 0 indicates an infinite lifetime, subject to the Idle Timeout value. An in-use connection is never retired. Connections are removed only after they are closed.

Default value: 60

Example: 50
Idle timeout (minutes) Integer/Expression Specify the maximum amount of time a connection is allowed to sit idle in the pool. A value of 0 indicates that idle connections are never removed from the pool.

Default value: 5

Example: 4
Checkout timeout (milliseconds) Integer/Expression Specify the number of milliseconds you want the system to wait for a connection to become available when the pool is exhausted.
Note: If you provide 0, the Snap waits infinitely until the connection is available. Therefore, we recommend you not to specify 0 for Checkout Timeout. For any other value, the system throws an exception after the wait time has expired.
Default value: 10000
Example: 9000