In this Article

Overview

SnapLogic and Snowflake simplify data integration and warehousing via the cloud using standard functionality and other Snaps. To connect the Snaps to multiple data sources and analytics to the Snowflake cloud data warehouse solution, use the 12 Snowflake Snaps and configure them. This diagram represents the integration of SnapLogic and Snowflake:

Prerequisites

• Storage Integration in Snowflake.

Key Steps in Configuring Snowflake S3 Dynamic Account

Account Properties

To connect to Snowflake, you can configure the Snowflake S3 Dynamic Snap Account using the following key steps.

• Specify JDBC JAR details: Add details on JDBC Jars, Hostname, Port Number, Authentication Type, Password, Database Name, Warehouse name, and JDBC Driver class.

• Specify Amazon S3 Storage details: Add details on S3 Access Key, S3 Bucket, S3 Folder, S3 Access-key ID, S3 Secret key, and S3 AWS Token.

Specify the JDBC JAR details

To establish a connection to the database, it is necessary to have the JDBC JAR file. The Snowflake Snap Pack comes bundled with the JDBC JAR V3.13.25 file by default, ensuring successful authentication even if you do not provide a specific JDBC driver. If you prefer to use a custom JAR file version, you can manually upload it by accessing the database icon under Account Properties, as shown below:

Configure additional settings related to the JDBC in the Account Properties section. For more information, refer to Snowflake Dynamic Accounts.

Specify Amazon Simple Storage Service (Amazon S3) Storage details

To configure the S3 account details, you must have the following:

• Snowflake Access in Amazon S3 Storage Account: Verify that the Snowflake user has appropriate access to the Amazon S3 Storage account.

• Secure Access to Cloud Storage

Snowflake Access in Amazon S3 Storage Account

You would need an AWS administrator from your organization to provide all the Snowflake access to your Amazon S3 storage account. Learn more at Virtual Private Cloud Ids for Snowflake Account.

Secure Access to Cloud Storage

Snowflake offers two methods for configuring Identity and Access Management (IAM) to facilitate reading and writing data from an S3 bucket. You must ensure that the bucket's security and access management policies permit Snowflake's access.

1. Configure Cloud Storage Integration in Snowflake: Set up the necessary cloud storage integration within Snowflake to establish the connection between Snowflake and the S3 bucket.

2. Configure an AWS IAM User: Create an AWS IAM user with the appropriate permissions to access the S3 bucket. This includes granting the necessary read and write permissions to facilitate data interaction between Snowflake and the S3 bucket.

Configure Cloud Storage Integration in Snowflake

To use the storage integrations for Snowflake, an administrator must provide all the necessary IAM user permissions in the AWS account. Learn more at https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration

Create an AWS IAM User (Role)

1. Log in to the AWS console. Open the IAM console, navigate to Access Management > Roles, and click the Create role button.

2. 

   Select the AWS service as the Trusted entity type, and EC2 as the Use case, and click Next.

   

3. In Add permissions policies page, select all or required policies that allow your instances access to the resources and then choose Next.

4. Optional. Add tags for resources because this is optional. Then choose Next. Specify a Role name and description in the Name review and create page.

5. Review the details and click Create role.

Configure an AWS IAM user with the required permissions to access the S3 bucket

The following permissions are required to access the S3 bucket and folder. Learn more at Configure Secure Access to Cloud Storage.

• s3:GetBucketLocation

• s3:GetObject

• s3:GetObjectVersion

• s3:ListBucket

Create an IAM Policy

Configure access permissions for Snowflake in your AWS Management Console so that you can use an S3 bucket to load and unload data; To create an IAM policy:

1. Log in to the AWS Management Console and Select Identity & Access Management (IAM).

2. In your AWS console, click Policies and select the policy attached to the role from the list in the table.

3. Click the JSON tab, specify the policy details in the JSON editor, and click Review Policy.

4. Review the policy summary, add a name and, optionally, a description for this policy, and select Create policy. 

Based on the above configurations, configure the pipeline parameters for the Snowflake pipeline.

• S3 Access Key: During the account setup process, an authentication request is used. The Access Key ID serves as authentication for requests made to Amazon S3. This is the authentication request used while setting up the account and requires the user to add details for the Access key id, which authenticates the requests for the Amazon S3.

• S3 AWS token: AWS generates a temporary security token that includes the user's permissions and the expiration time of the token. These temporary security tokens have a limited lifespan and preserve the security of S3 resources, and prevent unauthorized access. You can add details of the life time under the Advanced Properties section.

• S3 Storage Integration: Specify the S3 Storage Integration for Snowflake to be used for staging data instead of using AWS Access-key ID and S3 Secret key. This value is necessary for validating data after a bulk load or bulk insert operation. Learn more at https://docs.aws.amazon.com/IAM/latest/UserGuide/id_credentials_access-keys.html

Advanced Properties

• Specify URL properties: (This is optional as additional details need to be added here)

• Specify other details related to batch size and instance specifications.

Specify URL properties

• The URL properties are optional details that you can add as needed.

Specify other details

• Specify values for Batch size, Fetch size, Minimum and Maximum pool size, Maximum life time, Idle timeout and Checkout timeout in this section. For detailed information, refer to Snowflake S3 Dynamic Account.

Related Content

• Configuring a Snowflake Storage Integration to Access Amazon S3

• Getting Started with SnapLogic