Storing Image files in Snowflake database & presenting them via Tableau dashboards!
Storing un-structured Image files in Snowflake database from Azure blog.
This post lists end-to-end process for viewing web hosted images in Tableau in 6 easy steps. This process involves integration between Azure blob storage and Snowflake database. Below are high level step’s involved.
1) Create external BLOB storage to save image files in Azure cloud.
2) Create cloud storage integration in snowflake.
3) Grant snowflake access to storage location’s in Azure.
4) Create external stage in snowflake.
5) Create table in snowflake with image details.
6) Create dashboard to view images in Tableau.
Step 1:
Login to portal.azure.com and choose Storage Explorer (Preview) Subscriptions. Create a Blob Container for your project, right click on existing Blob containers as shown below and
Once Container is created, upload image file and copy Image url. This image url is required to create storage integration.
Step 2:
Create a Cloud Storage Integration in Snowflake. To create a storage Integration, We need to have ‘ACCOUNTADMIN’ credentials.
Step 3:
Grant Snowflake Access to the Storage Locations
Desc storage integration <integration_name> will give AZURE_MULTI_TENANT_APP_NAME, AZURE_CONSENT_URL to setup permission in further steps.
In a web browser, navigate to the URL in the AZURE_CONSENT_URL URL column. The page displays a Microsoft permissions request page and Click the Accept button.
Once Permission’s are approved provide assign roles to Snowflake service principal as shown below.
- Log into the Microsoft Azure portal and Navigate to Azure Services> Storage Accounts > Access Control (IAM) » Add role assignment > and choose ‘Storage Blob Data Contributor’ role for ‘SnowflakePACInt0563’ (Snowflake service principal. This should match with ‘AZURE_MULTI_TENANT_APP_NAME in desc storage integration
Step 4:
Create Stage.
Creating a stage that uses a storage integration created in previous steps. This stage should have USAGE privilege to integration.
Step 5:
Create snowflake table and view with image’s info as shown below.
Created table and inserted data to tabled EDWD.DB_POC.IMAGE_LOC.
Create view with pre-defined function’s provided by snowflake.
Step 6: Tableau
Create a dashboard in tableau.
Create data source
Create sheet or report with URL, MD5 as report rows.
Create a dashboard and use Action from Menu to create a url view in dashboard. 2. From the menu at the top of your dashboard, go to “Dashboard,” and then navigate to “Actions” in the dropdown menu.
3. Click on “Add Action” in the pop-up window and navigate to “URL” in the dropdown menu.
4. Give your action a useful name in the “Name” field.
5. Under “Run action on:” choose “Hover.” This indicates that the action occurs when the user mouses over a selection.
6. Click on the triangle to the right of the “URL” field, and select the name of the column from your data, in this example [URL], that the unique URLs reside in.
7. Click on “OK.”
Thanks for reading.
