0 0
Read Time:2 Minute, 9 Second

During this post we will talk about Configure Snowflake Storage Integration with AZURE. Currently I have started learning Snowflake in conjunction with Azure. Very first thing comes how to create secure integration between SF and Azure. As part of learning process we have established a storage integration so that we can connect with AZURE without exposing the credentials.

To start with, we created Container called snowbucket in Azure and upload the feed file.

In snowflake:

create or replace storage integration azure_int
type = external_stage
storage_provider = azure
enabled = true
azure_tenant_id = '8bfd4cdf-17b0-4e3e-8d6f-XXXXXXXXXX'
storage_allowed_locations = ('azure://sachinazurelearning.blob.core.windows.net/snowbucket');

To get Tenant_id:

Go to Azure Active Directory and copy the value:

Tenant

Get the Bucket name:

Bucket Name

Once the Storage Integration created successfully:

desc integration azure_int;

Azure Integration

Click on the URL and it will authenticate you.

Copy the AZURE_MULTI_TENANT_APP_NAME ,will use at later stage.

Now Create Stage and File format.

create or replace file format demo_db.public.my_csv_format

type = csv field_delimiter = ',' skip_header = 1 null_if = ('NULL', 'null') ;

create or replace stage demo_db.public.az_stage
storage_integration = azure_int
url = 'azure://sachinazurelearning.blob.core.windows.net/snowbucket'
file_format = my_csv_format;

Try to access the stage and will get following error.

Stage Access Error

To resolve this we need to assign Roles (IAM) to the user.

Go To the Bucket–> IAM–> Add Role Assignment

Role Assignment

After Next, Click on Select Members and locate the AZURE_MULTI_TENANT_APP_NAME

Add Role

Review and Assign.

Just wait for next 5-6 minutes to reflect the changes.

List Stage

Now we have established Snowflake Storage Integration with AZURE.

Create table and run the copy command.

create or replace table cust_tbl (
CUST_NUM varchar(8) null,
CUST_STAT varchar(255) null,
CUST_BAL number(20) null,
INV_NO varchar(10) null,
INV_AMT number(10) null,
CRID VARCHAR(4),
SSN VARCHAR(10),
phone number(10),
Email VARCHAR(50)
);

copy into cust_tbl
from @demo_db.public.az_stage
on_error = CONTINUE;

Table Query

Average Rating

5 Star
0%
4 Star
0%
3 Star
0%
2 Star
0%
1 Star
0%

Leave a Reply

Your email address will not be published. Required fields are marked *