Integrate With Snowflake Open Catalog

Introduction

This guide offers a detailed walkthrough for integrating StreamNative Cloud with Snowflake Open Catalog. It covers essential aspects such as configuring authentication, storage buckets, catalogs, and other key components. By following this guide, you will enable seamless interaction between StreamNative Cloud and Snowflake Open Catalog.

Setup Snowflake Open Catalog

Before initiating the integration of Snowflake Open Catalog with StreamNative Cloud, please ensure the following steps are completed. You can also watch this video to learn more about Preparing Snowflake Open Catalog Account

Step 1: Create Snowflake Standard Account:

Create a Snowflake Standard account. The homepage of a standard account will look as follows.

Step 2: Create Snowflake Open Catalog Account

To access the Snowflake Open Catalog console, a specialized Open Catalog account must be created. This account type is specifically designed for managing Open Catalog features and functionality.

Enter Admin → Accounts → Toggle → Create Snowflake Open Catalog Account

Configure the Snowflake Open Catalog

• Cloud: AWS
• Region: Region to place the Snowflake Open Catalog

[!NOTE] IMPORTANT: The Snowflake Open Catalog, s3 bucket, and StreamNative BYOC Ursa cluster should be in the same region.Snowflake Open Catalog doesn’t support cross-region buckets. To avoid costs associated with cross-region traffic, we highly recommend your s3 bucket and StreamNative BYOC Ursa cluster are in the same region.

Edition: any

Next, input a Snowflake Open Catalog Account Name, User Name,Password, and Email. This will create a new user for use specifically with the Snowflake Open Catalog Account.

Click Create Account. You will see the following if account creation is successful. We highly recommend taking a screenshot of this confirmation message. This Account URL will be used in later steps.

Click the Account URL, then sign into your open catalog account. You will enter the Snowflake Open Catalog console.

If you need the Account URL of your Snowflake Open Catalog Account in the future, navigate to Admin → Accounts → … → Manage URLs of your Snowflake Account.

Step 3. Setup storage bucket with permissions for StreamNative

Choose bucket location and grant access to StreamNative Cloud. You have two choices to setup a storage bucket.

[!NOTE] IMPORTANT:The Snowflake Open Catalog, s3 bucket, and StreamNative BYOC Ursa cluster should be in the same region.** Snowflake Open Catalog doesn’t support cross-region buckets. To avoid costs associated with cross-region traffic, we highly recommend your s3 bucket and StreamNative BYOC Ursa cluster are in the same region.

Option 1: Use your own bucket (recommended)

You need to create your own storage bucket, with the option to create a bucket path. When using your own bucket, the resulting path you will use for creation of the Snowflake Open Catalog will be as follows. The compaction folder will be created automatically by the StreamNative cluster.

s3://<your-bucket-name>/<your-bucket-path>/compaction

StreamNative will require access to this storage bucket. To grant access, execute the following Terraform module.

module "sn_managed_cloud" {
source = "github.com/streamnative/terraform-managed-cloud//modules/aws/volume-access?ref=v3.18.0"

external_id = "<your-organization-name>"
role = "<your-role-name>"
buckets = [
"<your-bucket-name>/<your-bucket-path>",
]

account_ids = [
"<your-aws-account-id>"
]
}

You can find your organization name in the StreamNative console, as shown below:

Before executing the Terraform module, you must define the following environment variables. These variables are used to grant you access to the AWS account where the S3 bucket is located.

export AWS_ACCESS_KEY_ID="<YOUR_AWS_ACCESS_KEY_ID>"
export AWS_SECRET_ACCESS_KEY="<YOUR_AWS_SECRET_ACCESS_KEY>"
export AWS_SESSION_TOKEN="<YOUR_AWS_SESSION_TOKEN>"

Run the Terraform module

terraform init
terraform plan
terraform apply

Option 2: Use StreamNative provided bucket

This process involves deploying the StreamNative BYOC Cloud Connection, Cloud Environment, and beginning the process of deploying the StreamNative BYOC Ursa Cluster to obtain the cluster id. StreamNative will automatically assign the necessary permissions to this bucket.

To proceed, you will need to first complete the steps for granting vendor access, creating a Cloud Connection, and setting up the Cloud Environment. Next, begin the process of deploying the StreamNative BYOC Ursa Cluster to obtain the cluster id. Step 1 of Create StreamNative BYOC Ursa Cluster below includes directions on obtaining the cluster id.

When using a StreamNative-provided bucket, the resulting path you will use for creation of the Snowflake Open Catalog will be as follows. The cloud environment id will be created during the deployment of the Cloud Environment. The cluster id is assigned when starting the cluster creation process in the StreamNative Console.

s3://<your-cloud-environement-id>/<your-cluster-id>/compaction

Step 4: Configure AWS Account for Snowflake Open Catalog Access

Create IAM policy and role for Snowflake Open Catalog Access.

In the AWS console, enter Access management → Policies → Create policy

Then choose the JSON format. Enter the rule as follows, replacing <your-bucket-name> and <your-bucket-path>

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

Click Next

Provide a policy name and click Create policy.

Create IAM Role

In the AWS console, enter Access management → Roles → Create role

• Trusted entity type: AWS account
• An AWS account: this account Enable External ID

Set External ID: training_test (will be used when creating catalog)

Click Next

Select the policy created in the previous step. Then click Next

Input a role name and click Create role.

View the detailed role information and record the ARN

This policy and role are used for Snowflake Open Catalog access to the s3 bucket.

Step 5: Create Snowflake Open Catalog

Create Snowflake Open Catalog

• Name: streamnative
• External: disabled
• Storage provider: S3
• Default base location:

User provided bucket:

s3://<your-bucket-name>/<your-bucket-path>/compaction

StreamNative provided bucket:

s3://<your-cloud-environement-id>/<your-cluster-id>/compaction

• Additional location: not configured
• S3 role ARN: arn copied from previous step
• External ID: external id created in previous step

Then click Create, you will see the catalog streamnative created

Then detail the catalog, here we need to record the value of the IAM user arn. The Snowflake Open Catalog will use this arn to access our AWS bucket.

Trust the Snowflake Open Catalog Iam user arn

In the AWS console, enter Access management → Roles, search for the role we created before.

Then click Trust relationships → edit trust policy

Change the value of Principal:AWS to the Snowflake Open Catalog IAM user arn

Then click Update policy and the Snowflake Open Catalog can access the bucket.

Step 6: Provide StreamNative Access to Snowflake Open Catalog

Our engine needs a connection to access the Snowflake Open Catalog, so we need to create one. We will later reuse this connection for Snowflake to access Snowflake Open Catalog.

• Name: streamnativeconnection
• Query Engine: not configured
• Create new principal role: enable
• Principal Role Name: streamnativeprincipal

Then click Create, and you will see a pane. Record the Client ID and Client Secret for this connection as <CLIENT ID>:<SECRET>. Our engine needs it to access the Snowflake Open Catalog.

We now have a Service Connection called streamnativeconnection linked to the Principal Role streamnativeprincipal.

Create a Snowflake Catalog Role

Enter catalogs → detail catalog pulsar → Roles → + Catalog Role

Name: streamnativeopencatalog

Privileges:

• NAMESPACE_CREATE
• NAMESPACE_LIST
• TABLE_CREATE
• TABLE_LIST
• TABLE_READ_DATA
• TABLE_WRITE_DAT
• TABLE_READ_PROPERTIES
• TABLE_WRITE_PROPERTIES
• NAMESPACE_READ_PROPERTIES
• NAMESPACE_WRITE_PROPERTIES

Click Create.

Then click Grant to Principal Role

• Catalog role to grant: streamnative_open_catalog_role
• Principal role to receive grant: streamnativeprincipal

Then click Grant

The catalog role streamnative_open_catalog_role now has the 10 required permissions on catalog streamnative. The catalog role streamnative_open_catalog_role is now linked to principal streamnativeprincipal.

We will resuse the connection when connecting Snowflake to Snowflake Open Catalog.

Create StreamNative BYOC Ursa Cluster

To proceed, you will need to first complete the steps for granting vendor access, creating a Cloud Connection, and setting up the Cloud Environment.Then you can begin the process of deploying the StreamNative BYOC Ursa Cluster. You can also watch this video to learn more about deploying the StreamNative BYOC Ursa Cluster.

Step 1: Create a StreamNative BYOC Ursa Cluster in StreamNative Cloud Console

In this section we create and set up a cluster in StreamNative Cloud. Login to StreamNative Cloud and click on ‘Create an instance and deploy cluster’

Click on Deploy BYOC

Enter Instance name, select your Cloud Connection, select URSA Engine and click on Cluster Location

Enter Cluster Name, select your Cloud Environment, select Multi AZ and click on Lakehouse Storage Configuration

To configure Storage Location there are two options

Option 1: Select Use Your Own Bucket (recommended) to choose your own storage bucket by entering the following details

• AWS role arn (created with terraform module)
• Region
• Bucket name
• Bucket path
• Confirm that StreamNative has been granted the necessary permissions to access your S3 bucket. The required permissions were granted by running a Terraform module.

Option 2: Select Use Existing BYOC Bucket to choose the bucket created by StreamNative

The UI will present you with the SN Bucket Location in this format to be used when creating the Snowflake Open Catalog.

s3://<your-cloud-environement-id>/<your-cluster-id>/compaction

e.g.

s3://aws-usw2-test-rni68-tiered-storage-snc/o-naa2l-c-vo06zqe-ursa/compaction

[!NOTE] IMPORTANT : If you are using the StreamNative provided bucket, do not close the browser while creating the catalog.** This will cause StreamNative to create a new cluster id. Once a catalog is created in Snowflake Open Catalog, the base location and additional locations cannot be changed. If the cluster id changes, you would need to create a new catalog.

To integrate with Snowflake Open Catalog, Enable Catalog Integration and select Snowflake Open Catalog.

• Warehouse: catalog created in Snowflake Open Catalog
• URI: Account URL when creating Snowflake Open Catalog. Append '/polaris/api/catalog' to the URI. Look at the screen shot below.
• Select Authentication Type/OAuth2: create a new secret in StreamNative using Snowflake Open Catalog Service Connection “<CLIENT ID>:<SECRET>”

Clicking Cluster Size will test the connection to the s3 bucket and the Snowflake Open Catalog.

Click Continue to begin sizing your cluster.

For this example, we deploy using the smallest cluster size. Click Finish to start deploying the StreamNative BYOC Ursa Cluster into your Cloud Environment.

When cluster deployment is complete, it will appear on the Organization Dashboard with a green circle.

The Lakehouse Storage configuration can be viewed by clicking on the Instance on the Organization Dashboard and selecting Configuration in the left pane.

Step 2: Produce Kafka messages to topic

Follow the creating and running a producer section to produce Kafka messages to a topic.

Step 3: Review s3 bucket

Navigate to the user provided or StreamNative provided s3 bucket. In this example the user provided bucket is s3://streamnativeopencatalog/test. A storage folder and compaction folder have been created by the cluster.

We published messages to multiple topics in the the public/default tenant/namespace. We see folders for the tenant, namespace, and each topic inside the compaction folder.

Inside each topic folder, we find partition and metadata folders.

Step 4: Verify Tables and Schema are Visible in Snowflake Open Catalog

Once the compaction folder has been created in the s3 bucket, we can verify the tables and schemas are visible in Snowflake Open Catalog. We can see the resulting topics created in streamnative/public/default with a registered schema.

Configure Snowflake to View Data from Snowflake Open Catalog

Querying a table in Snowflake Open Catalog using Snowflake requires completing the following from the Snowflake documentation. This video shows detailed queries for the above example.

Step 1: Create an external volume in Snowflake

Please refer to the Snowflake documentation here for the complete code samples for creating an external volume.

The video includes the following details from our example:

• When creating the new policy for Snowflake to access the s3 bucket, use root of the s3 bucket to avoid a list error when verifying storage access.
• When creating an external volume in Snowflake, for STORAGE_BASE_URL use the complete bucket path with s3://<>/<>/compaction.

Step 2: Create a catalog integration for Open Catalog

Please refer to the Snowflake documentation here for the complete code samples.

The video includes the following details from our example:

• The CATALOG_NAMESPACE refers to the tenant.namespace in our StreamNative Cluster. Since we published messages to public.default, use public.default as the CATALOG_NAMESPACE.
• We can resuse the <CLIENT ID>:<SECRET> for Snowflake Open Catalog to allow access for Snowflake. The <CLIENT ID> refers to OAUTH_CLIENT_ID and <SECRET> refers to OAUTH_CLIENT_SECRET.

You will need to create a new catalog integration for each tenant.namespace.

Step 3: Create an externally managed table

Please refer to the Snowflake documentation here for the complete code samples.

The video includes the following details from our example:

• A Snowflake Open Catalog warehouse.schema.table (e.g. streamnative.public.default.kafkaschematopic) is mapped to a Snowflake database.schema.table (e.g. training.public.kafkaschematopic)
• Use AUTO_REFRESH = TRUE; in CREATE ICEBERG TABLE to ensure new data is viewable in Snowflake.

You will need to create a new externally managed table for each topic.

Once completing these steps, you will be able to query the Iceberg Table registered in Snowflake Open Catalog through Snowflake.