Snowflake S3 Database Account

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 Accountbecause 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 propertiesas expressions that reference pipeline parameters.

Prerequisites

  • None

Limitations and Known Issues

  • 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


Snowflake S3 Database 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 value: N/A

Example: Snowflake_S3_Database_Account
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 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
Authentication Type Dropdown list Required. 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.
Example: Key Pair
Username String Appears when Password is selected. Specify the username to connect to the Snowflake database server..

Default value: N/A

Example: SW_User
Password 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.

Default value: N/A

Example: ll@98#*00w
Encrypted 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.

Learn more about configuring and generating a valid key pair.

Default value: N/A

Example: -----BEGIN PRIVATE KEY-----MIIE6TAbBgkqhkiG9w0BBQMwDgQILYPyennlX2bd8yX8 xOxGSGfvB+99+PmSlex0FmY9ov1J8H1H9Y3lJXXb

-----END PRIVATE KEY-----
Encrypted Private Key Passphrase String/Expression Appears when Key Pair is selected. Specify the encrypted passphrase. Specify the encrypted private key passphrase for authentication.

Default value: N/A

Example: Nki62lmWfFO75Xg04W+Fnlt9ejiMn4zPbkf/me Aq1R3X7CM/ORLuLms4vU70NjzNYo09SOUXhQmoqpEkQBvrrYuzdaQt5hvwh7vZQh WZ5L8+lTRoAneFtmd6LZ94AssA==
Database name String/Expression Required. Specify the Snowflake database to connect to.

Default value: N/A

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

Default: None

Example: testwh
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.
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.
S3 storage integration String/Expression 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.
URL Properties Use this field set to define additonal URL properties to use if any.
URL property name String

Specify the name of the URL property.

Default: None

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

Default: None

Example: 4
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

Generating a Valid Key Pair

  1. In a Linux terminal, run the following command to generate a Private Key stored in PKCS8 format:: openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8
  2. Generate a public key by referencing the previously generated private key rsa_key.p8 : openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
  3. Store the generated private key (rsa_key.p8) and public key (rsa_key.pub) in a safe location.
  4. In the Snowflake console, assign the public key to the required Snowflake user. Exclude the header and footer from the public key ALTER USER jsmith SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';.
  5. In the Snowflake console, verify the assignment of the public key to the required Snowflake user: DESC USER jsmith;
  6. Specify the entire Private Key and include the header and footer in the Private Key field of the Snowflake account.
Note: Snowflake supports multiple active keys for uninterrupted rotation. Learn more: Key Pair Authentication & Key Pair Rotation — Snowflake Documentation.