...
| Table of Contents | ||||
|---|---|---|---|---|
|
Overview
SnapLogic and Snowflake simplify streamline cloud-based data integration and warehousing via the cloud using standard functionality and other Snaps. To connect the Snaps to multiple data sources and analytics to with their comprehensive set of features and Snaps. By utilizing the 12 Snowflake Snaps and configuring them, you can easily connect Snaps to various data sources and enable analytics within the Snowflake cloud data warehouse solution, use the 12 Snowflake Snaps and configure them. This diagram represents the integration of . The following diagram illustrates the integration between SnapLogic and Snowflake:.
...
Prerequisites
Storage Integration in Snowflake
...
Key Steps in Configuring the Snowflake S3 Dynamic
...
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.
...
account
For the Snowflake S3 Dynamic account to successfully authorize, create, and configure the details for the account as shown in the workflow.
...
...
Step 1: Configure the Snowflake database details
To establish a connection to the Snowflake database, it is necessary to have the JDBC JAR file. The configure the following details:
JDBC JAR: By default, the Snowflake Snap Pack comes bundled with the JDBC JAR V3.13.
...
28 file
...
, 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.
Database details: Specify the Hostname, Port Number, Authentication Type, Password, Database Name, Warehouse name, and JDBC Driver class. For more information, refer to Snowflake Dynamic Accounts.
...
Step 2: Configure the Amazon S3 Storage details
Configure the following:
S3 Bucket: The Amazon S3 bucket where Snowflake creates the required data files. This bucket must reside in the same region as the cluster. Learn more: Creating an S3 Stage | Snowflake Documentation
S3 Access Key ID: The Access Key ID serves as authentication for requests made to Amazon S3. Learn more: AWS Access Key ID.
S3 AWS token: AWS generates a temporary security token that includes the user's permissions and the token's expiration time. 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 lifespan under the Advanced properties section.
S3 Storage Integration: Specify the S3 Storage Integration for Snowflake to be used for staging data instead of using the AWS Access-key ID and the S3 Secret key. This value is necessary for validating data after a bulk load or bulk insert operation. Learn more: Managing access keys for IAM users - AWS Identity and Access Management
To configure the S3 account details, you must have the following:
Access to 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
...
An AWS administrator
...
of your organization
...
must provide all the Snowflake access to your Amazon S3 storage account. Learn more
...
...
Access to Cloud Storage
...
: Snowflake offers the following two methods for configuring Identity and Access Management (IAM) for reading data to
...
and writing data from an S3
...
bucket
...
.
Configure Cloud Storage Integration in Snowflake: Set up the
...
cloud storage integration within Snowflake to establish
...
a connection between Snowflake and the S3 bucket.
Configure an AWS IAM User: It involves the following:
...
...
You must ensure that the S3 bucket's security and access management policies permit access to Snowflake.
Configure Cloud Storage Integration in the Snowflake platform
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: Option 1: Configuring a Snowflake Storage Integration to Access Amazon S3 | Snowflake Documentation
Create an AWS IAM User (Role)
Log in to the AWS console. Open the IAM console, navigate to Access Management > Roles,and click the Create role button.
Select the AWS service as the Trusted entity type, and EC2 as the Use case, and click Next.
In the Add permissions policies page, select all or required policies that allow your instances access to the resources and then choose Next.
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.
Review the details and click Create role.
...
Assign permissions to the IAM user
...
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:GetBucketLocations3:GetObjects3:GetObjectVersions3:ListBucket
Learn more: Configure Secure Access to Cloud Storage.
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:
Log in to the AWS Management Console and Select Identity & Access Management (IAM).
In your AWS console, click Policies and select the policy attached to the role from the list in the table.
Click the JSON tab, specify the policy details in the JSON editor, and click Review Policy.
Review the policy summary, add a name and, optionally, a description for this policy, and select Create policy.
...
Step 3: Configure the Advanced properties
Specify URL properties: Add any needed optional URL properties.
Specify values for additional properties such as Batch size, Fetch size, Minimum and Maximum pool size. Refer to the Snowflake S3 Dynamic Account for additional information.
Step 4: Configure the pipeline parameters for the Snowflake pipeline
Based on the above configurations, configure the pipeline parameters for the Snowflake pipeline.
Configuring the account with parameterized settings - Passing account details like username, password from pipeline parameters.
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
| Info |
|---|
The Amazon S3 bucket where Snowflake writes the output files must reside in the same region as the cluster. Learn more at https://docs.snowflake.com/en/user-guide/data-load-s3-create-stage. |
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.
...
...
Click the Edit Pipeline Properties
icon in the SnapLogic Designer toolbar.In the Edit Pipeline dialog box, define the Key-Value Parameters as shown below:
Enable the expressions for all the dynamic fields and select the parameters for each dynamic field as applicable:
Click Apply.
Demonstration
Watch the following video to understand how to configure the Snowflake S3 Dynamic Account.
...
...
Related content