In this article

Overview

You can use this account type to connect Snowflake Snaps with data sources that use Snowflake S3 Database accounts.

note

If the Snap fails to connect to the database, it attempts to reconnect three times. 

You can validate an account connection when creating a Snowflake S3 Database Account, but not when creating a Snowflake S3 Dynamic Account because the account properties of a dynamic account are provided dynamically as pipeline parameters. The difference between the Snowflake S3 Database Account and Snowflake S3 Dynamic Account is that in the latter you can specify the account properties as expressions that reference pipeline parameters.

If the Snap fails to connect to the database, it attempts to reconnect three times. 

You can validate an account connection when creating a Snowflake S3 Database Account, but not when creating a Snowflake S3 Dynamic Account because the account properties of a dynamic account are provided dynamically as pipeline parameters. The difference between the Snowflake S3 Database Account and Snowflake S3 Dynamic Account is that in the latter you can specify the account properties as expressions that reference pipeline parameters.

Prerequisites

None.

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.

  • 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.

Account Settings

  • Asterisk ( * ): Indicates a mandatory field.

  • Suggestion icon ( (blue star) ): Indicates a list that is dynamically populated based on the configuration.

  • Expression icon ( (blue star) ): Indicates whether the value is an expression (if enabled) or a static value (if disabled). Learn more about Using Expressions in SnapLogic.

  • Add icon ( (blue star) ): Indicates that you can add fields in the field set.

  • Remove icon ( (blue star) ): Indicates that you can remove fields from the field set.

Field Name

Field Type

Field Dependency

Description

Label*

Default value: None
Example: Snowflake_S3_Databas e_Account

String

N/A

Specify a unique label for the account.

Account properties*

JDBC JARs

Use this field set to add a list of JDBC JAR files to be loaded. The Snowflake account has been tested with the JDBC driver version 3.14.0.


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*

Default value: None
Example: snowflake-jdbc-3.14.0.jar

String

N/A

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 JAR v3.14 file. Therefore, even if you do not provide a JDBC Driver, the account does not fail.

The Snowflake Snap Pack is bundled with the default Snowflake JDBC JAR v3.14 file. Therefore, even if you do not provide a JDBC Driver, the account does not fail.

Hostname*

Default value: None
Example: demo.snowflake.net

String

N/A

Specify the hostname of the Snowflake server to which you want to connect the new account.

Port Number*

Default value: 443 
Example: 332

Integer

N/A

Specify the port number associated with the Snowflake database server that you must use for this account.

Authentication Type*

Default Value: Password
Example: Key Pair

Dropdown list

N/A

Choose an authentication type to connect to the database. The available options are:

  • Password: Authenticates with the Username and Password credentials.

  • Key Pair: Authenticates using the Private Key and Encrypted Private Key Passphrase.

Username*

Default Value: N/A
Example: SW_User

String

N/A

Specify the username to connect to the Snowflake database server.

Password*

Default Value: N/A
Example: ll@98#*00w

String

Appears when you select Password for Authentication Type.

Specify the password associated with the username specified above. This will be used as the default password while retrieving connections.

Encrypted Private Key*

Default Value: N/A
Example: -----BEGIN PRIVATE KEY-----MIIE6TAbBgkqhkiG9w0BBQMwDgQILYPyennlX2bd8yX8
xOxGSGfvB+99+PmSlex0FmY9ov1J8H1H9Y3lJXXb

-----END PRIVATE KEY-----

String

Appears when you select Key Pair for Authentication Type.

Specify the key that you have generated for authentication. The key must include the generated header and footer.

note

The Private Key is read, decrypted if necessary, and included with properties to connect to Snowflake, where it is validated with its paired Public Key to authenticate the connection.

The Private Key is read, decrypted if necessary, and included with properties to connect to Snowflake, where it is validated with its paired Public Key to authenticate the connection.

Learn more about configuring and generating a valid key pair.

Encrypted Private Key Passphrase*

Default Value: N/A
Example: Nki62lmWfFO75Xg04W+Fnlt9ejiMn4zPbkf/me Aq1R3X7CM/ORLuLms4vU70NjzNYo09SO
UXhQmoqpEkQBvrrYuzdaQt5hvwh7vZQh WZ5L8+lTRoA
neFtmd6LZ94AssA==

String

Appears when you select Key Pair for Authentication Type.

Specify the passphrase for the Encrypted Private Key. Leave this field blank if the specified Private Key is not encrypted. However, we recommend that you use Encrypted Private Key.

Database name*

Default value: None
Example: TestDB

String

N/A

Specify the name of the database to which you want to connect.

Warehouse name*

Default value: None
Example: SW_WH

String

N/A

Specify the name of the warehouse to which you want to connect.

JDBC Driver Class

Default Value: net.snowflake.client.jdbc.SnowflakeDriver
Example: net.snowflake.client.jdbc.SnowflakeDriver

String

N/A

Specify the JDBC driver class to use.

S3 Bucket

Default Value: N/A
Example: sl-bucket-ca

String

N/A

Specify the name of the S3 bucket that you want to use for staging data to Snowflake. 

note

  • 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

  • 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

S3 Folder

Default Value: N/A
Example:  sl-bucket-cas3/test

String/Expression

N/A

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.

S3 Access-key ID

Default Value: N/A
Example: NAVRGGRV7EDCFVLKJH

String/Expression

N/A

Specify the S3 access key ID that you want to use for AWS authentication.

S3 Secret key

Default Value: N/A
Example: 2RGiLmL/6bCujkKLaRuUJHY9uSDEjNYr+ozHRtg

String/Expression

N/A

Specify the S3 secret key associated with the S3 Access-ID key. listed in the S3 Access-key ID field.

S3 AWS Token

Default Value: None
Example: AQoDYXdzEJr

String/Expression

N/A

Specify the S3 AWS Token to connect to private and protected Amazon S3 buckets. Note that only global Security Token Service (STS) regions are supported.

note

The temporary AWS Token is used when:

  • Data is staged in the S3 location.

  • Data is coming from the input view and the files are staged in an external staging location.

The temporary AWS Token is used when:

  • Data is staged in the S3 location.

  • Data is coming from the input view and the files are staged in an external staging location.

S3 Storage Integration

Default Value: N/A
Example: S3_Storage_Integration

String/Expression

N/A

Specify the predefined storage integration that is used to authenticate the Amazon S3 bucket hosting as an external stage.

note

  • When you provide a value in this field, the COPY command uses S3 Storage Integration instead of AWS credentials and triggers the VALIDATE command. Storage Integration is only used for the Snowflake COPY command.

  • When you do not provide a value in this field, the COPY command uses AWS credentials and does not trigger the VALIDATE command.

  • S3 Storage Integration value is necessary if you want to validate data after a bulk load or bulk insert operation.

For more information about creating Storage Integration, see Configuring a Snowflake Storage Integration to Access Amazon S3.

  • When you provide a value in this field, the COPY command uses S3 Storage Integration instead of AWS credentials and triggers the VALIDATE command. Storage Integration is only used for the Snowflake COPY command.

  • When you do not provide a value in this field, the COPY command uses AWS credentials and does not trigger the VALIDATE command.

  • S3 Storage Integration value is necessary if you want to validate data after a bulk load or bulk insert operation.

For more information about creating Storage Integration, see Configuring a Snowflake Storage Integration to Access Amazon S3.

Advanced properties

URL Properties

Use this field set to define additonal URL properties to use if any.

URL property name

Default value: None
Example : queryTimeout

String

N/A

Specify the name of the URL property.

note

Snowflake allows session parameters to be set as follows:
URL property name QUOTED_IDENTIFIERS_IGNORE_CASE = false; where the URL property value is set as false.

Session parameters enable you to customize Snowflake sessions and manage access control to various resources within a Snowflake account.

Snowflake allows session parameters to be set as follows:
URL property name QUOTED_IDENTIFIERS_IGNORE_CASE = false; where the URL property value is set as false.

Session parameters enable you to customize Snowflake sessions and manage access control to various resources within a Snowflake account.

URL property value

Default value: None
Example: 4

String

N/A

Specify the URL property value associated with the URL property name.

Batch size*


Default value: None
Example: 3

Integer

N/A

Specify the number of Snowflake queries that you want to execute at a time.

  • If the Batch Size is 1, the query is executed as-is, that is the Snap skips the batch (non-batch execution).

  • If the Batch Size is greater than 1, the Snap performs the regular batch execution.

Fetch size*

Default value: 100
Example: 12

Integer

N/A

Specify the number of rows a query must fetch for each execution.

note

Large values could cause the server to run out of memory.

Large values could cause the server to run out of memory.

Min pool size*

Default value: 3
Example: 0

Integer

N/A

Specify the minimum number of idle connections that you want the pool to maintain at a time. 

Max pool size*

Default value: 15
Example: 0

Integer

N/A

Specify the maximum number of connections that you want the pool to maintain at a time.

Max life time*

Default value: 60
Example: 50

Integer

N/A

Specify the maximum lifetime of a connection in the pool, in seconds.

note

  • Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit.

  • 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.

  • Ensure that the value you enter is a few seconds shorter than any database or infrastructure-imposed connection time limit.

  • 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.

Minimum value: 0
Maximum value: No limit

Idle Timeout*

Default value: 5
Example: 4

Integer

N/A

Specify the maximum amount of time in seconds that a connection is allowed to sit idle in the pool. 

note

0 indicates that idle connections are never removed from the pool.

0 indicates that idle connections are never removed from the pool.

Minimum value: 0
Maximum value: No limit

Checkout timeout*

Default value: 10000
Example: 9000

Integer

N/A

Specify the maximum time in 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 other than 0, the Snap displays an exception after the wait time has expired.

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 other than 0, the Snap displays an exception after the wait time has expired.

Minimum value: 0
Maximum value: No limit

Snap Pack History

Related Content