Snowflake

Copy
If you have Amazon Web Services (AWS) as your cloud provider and want to leverage S3 as your object storage, you will need to follow a few more steps to configure your Snowflake destination with a snowflake integration.

Detailed instructions can be found here

Configuring snowflake integration with AWS

1. Create a policy in AWS:

Replace <bucket> and <prefix> with your values in the JSON below. And create the policy with a name of your choice.

Copy
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:GetObjectVersion",
        "s3:DeleteObject",
        "s3:DeleteObjectVersion"
      ],
      "Resource": "arn:aws:s3:::<bucket>/<prefix>/*"
    },
    {
      "Effect": "Allow",
      "Action": "s3:ListBucket",
      "Resource": "arn:aws:s3:::<bucket>",
      "Condition": {
        "StringLike": {
          "s3:prefix": ["<prefix>/*"]
        }
      }
    }
  ]
}

2. Create a role and attach the above policy in AWS: - Create role of type Another AWS account - Enter your AWS account ID and enable Require External ID - For external ID enter a dummy ID such as 0000. We will modify this later on. - And attach the policy created in Step 1. - Give your role a name and keep the role ARN handy for the next step.
3. Create cloud storage integration in Snowflake

Copy
CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<iam_role>'
  STORAGE_ALLOWED_LOCATIONS = ('s3://<bucket>/<path>/', 's3://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('s3://<bucket>/<path>/', 's3://<bucket>/<path>/') ]

<integration_name> with an integration name of your choice.<iam_role> with the role ARN from the previous step.

Record the values: <integration_name>.

4. Retrieve the AWS IAM user for your Snowflake account as shown:

Copy
DESC INTEGRATION <integration_name>;

<integration_name> - The integration name used in the previous step.

Record the values - STORAGE_AWS_ROLE_ARN and STORAGE_AWS_EXTERNAL_ID .

5. Grant IAM user permission to access bucket objects in AWS
Choose the role created in Step 2 and edit the trust relationship with the following JSON.

Copy
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "",
      "Effect": "Allow",
      "Principal": {
        "AWS": "<snowflake_user_arn>"
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "<snowflake_external_id>"
        }
      }
    }
  ]
}

<snowflake_user_arn> is the STORAGE_AWS_ROLE_ARN in the previous step.<snowflake_external_id> is the STORAGE_AWS_EXTERNAL_ID in the previous step.
6. Grant integration access to role in Snowflake:

Copy
grant usage on integration <integration_name> to role <sf_role>

<integration_name> is the integration created in the Step 3.<sf_role> is the role in Snowflake you want to grant access to.

Detailed instructions can be found here.

Configuring snowflake integration with Azure

1. Create a storage account in Azure.

2. Create a container in the storage account created above by navigating to <storage_account> - Storage Explorer - Blob Containers - Create a Blob Container.

3. Create cloud storage integration in Snowflake:

   Copy
   CREATE STORAGE INTEGRATION <integration_name>
   TYPE = EXTERNAL_STAGE
   STORAGE_PROVIDER = AZURE
   ENABLED = TRUE
   AZURE_TENANT_ID = '<tenant_id>'
   STORAGE_ALLOWED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>/<path>/', 'azure://<account>.blob.core.windows.net/<container>/<path>/')
   [ STORAGE_BLOCKED_LOCATIONS = ('azure://<account>.blob.core.windows.net/<container>/<path>/', 'azure://<account>.blob.core.windows.net/<container>/<path>/') ]

   <tenant_id> - Get your tenant id by navigating to Azure Active Directory - Properties. The tenant ID is displayed in the Directory ID field.<account> - <storage_account> - Access keys - <storageAccountName>
4. Grant Snowflake access to the storage locations

Replace <integration_name> with the integration name created in the previous step.

Copy
DESC INTEGRATION <integration_name>;

And record the values AZURE_CONSENT_URL and AZURE_MULTI_TENANT_APP_NAME .
5. Navigate to the URL in the AZURE_CONSENT_URL obtained in the previous step and accept.

6. Grant snowflake access to the container created in Step 2: - Navigate to

   Azure Services - Storage Accounts and select the storage account created in Step 1. - Add role : Navigate toAccess Control (IAM) - Add Role Assignmentand select either Storage Blob Data Reader(Read Access) orStorage Blob Data Contributor (Read and Write Access) - AddAssign Access : Add Service Principal as the type of security principal to assign the role to. And search forAZURE_MULTI_TENANT_APP_NAME obtained in step 4.
7. Grant integration access to role in Snowflake:

Copy
grant usage on integration <integration_name> to role <sf_role>;

<integration_name> is the integration created in the Step 4.<sf_role> is the role in Snowflake you want to grant access to.

Detailed instructions can be found here.

Configuring Snowflake Integration with Google Cloud Platform

1. Create a Cloud Storage integration in Snowflake.

Copy
CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = GCS
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://<bucket>/<path>/', 'gcs://<bucket>/<path>/')

• <integration_name> is the name of the new integration being created
• <bucket> is the name of the Cloud Storage bucket that you createdabove
• <path> is an optional path that can be used to provide granular control over objects in the bucket
2. Retrieve the Cloud Storage Service Account for your Snowflake account. The following DESCRIBE command will retrieve the ID for the Cloud Storage service account that was created for your Snowflake account.

Copy
DESC STORAGE INTEGRATION <integration_name>;

• Where <integration_name> is the name of the integration you specified above in step 1.
• The output will be a table that has a property calledSTORAGE_GCP_SERVICE_ACCOUNT. Retrieve that property value.
• The value that should be retrieved will have the following format:service-account-id@<unique_string>.iam.gserviceaccount.com
3. Grant the Service Account Permissions to Access Bucket Objects
• Create a custom IAM role that has the permissions required to access the bucket and get objects.

Copy
1. Log into the Google Cloud Platform Console as a project editor
 2. From the home dashboard, choose IAM & admin » Roles.
 3. Click Create Role.
 4. Enter a name, and description for the custom role.
 5. Click Add Permissions.
 6. Filter the list of permissions, and add the following from the list below:
    | Permission Property Name |
    | :----------------------- |
    | <code class="inline-code">storage.buckets.get</code>    |
    | <code class="inline-code">storage.objects.get</code>    |
    | <code class="inline-code">storage.object.list</code>    |
    | <code class="inline-code">storage.objects.create</code> |
 7. Click Create

4. Assigning the Custom Role to the Cloud Storage Service Account
• Log into the Google Cloud Platform Console as a project editor.
• From the home dashboard, choose Cloud Storage » Browser:
• Select the checkbox of the bucket you would like to configure for access.
• Click SHOW INFO PANEL in the upper-right corner. The information panel for the bucket will slide out from the right.
• In the Add members field, search for the service account name from the DESCRIBE INTEGRATION output in Step 2: Retrieve the Cloud Storage Service Account for your Snowflake Account (in this topic).
• From the Select a role dropdown, select Storage » Custom » <role>, where <role> is the custom Cloud Storage role you created in Creating a Custom IAM Role (in this topic).
• Click the Add button. The service account name is added to the Storage Object Viewer role dropdown in the information panel.
5. Grant usage to an external (i.e. Cloud Storage) stage that references the integration you created.

   Copy
   GRANT USAGE ON INTEGRATION <integration_name> TO ROLE "RUDDER";