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 pre-built Snaps and configure them. This diagram represents the integration of SnapLogic and Snowflake:

The data integration with Snowflake includes Snaps for bulk load, upsert, and unload in addition to standard CRUD (create, read, update, and delete) functionality. To connect to Snowflake, you have to configure the Snowflake S3 Dynamic Snap Account using the key steps suggested below.

Prerequisites

• Storage Integration in Snowflake.

Key Steps in Configuring Snowflake S3 Dynamic Account

Account Properties Section

• 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: S3 Access Keys

Advanced Properties Section

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

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

Account Properties

Specify the JDBC JAR details

To create a connection to the database, you must specify the JDBC JAR file details. By default, the Snowflake Snap Pack is bundled with the JDBC JAR V3.13.25 file to ensure that even when you do not provide a JDBC Driver, the account does not fail. To use a custom JAR file version, you must manually upload a JAR file using the database icon under Account Properties, as shown below:

Add more details 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 specify the Amazon Simple Storage Service (Amazon S3) Storage account details in the Account properties, the following configuration information from Snowflake is essential:

1. Snowflake Configurations

   1. Snowflake Access in AWS S3 Storage Account

   2. Secure Access to Cloud Storage

1 a Snowflake Access in AWS 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.

1 b Secure Access to Cloud Storage

There are two ways currently provided by Snowflake to configure Identity and access management (IAM), which allows you to read data from and write to an S3 bucket. The security and access management policies on the bucket should also allow Snowflake to access the bucket.

1. Configure Cloud Storage Integration in Snowflake.

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

Create a 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.

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 grant your instances access to the resources and then choose Next.

4. 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; perform the following steps:

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, specify the parametrized account details for pipeline parameters.

• S3 Access Key: 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 prevent the security of S3 resources and prevents unauthorized access. You can add details of the lifet ime 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

The URL properties are optional details that can be added by the user.

Specify details on 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