2
0
During this post we will discuss about DIRECTORY and GET_PRESIGNED_URL two key properties in snowflake. Recently my friend Vivek has given me scenario where he has to download files from Stage using Snowflake Web UI. As per the ask by client, instead of downloading the File from respective cloud provider UI framework. They want File to be downloaded from Snowflake worksheet only. Consider the real time scenario source system has feed the file to Snowflake external stage. Later on, this file to be consume in Snowflake database using COPY command. Parallel Snowflake Users wants to download and access the file at their location as well. Due to compliance issue, they were not authorized to login to the cloud provider (AWS in our case). Snowflake has handled this requirement by two important properties.
- DIRECTORY Table
- GET_PRESIGNED_URL
Directory tables store a catalog of staged files in cloud storage. Roles with sufficient privileges can query a directory table to retrieve file URLs to access the staged files, as well as other metadata
Generates the pre-signed URL to a staged file using the stage name and relative file path as inputs. We can access files in a stage by navigate to the pre-signed URL directly in a web browser.
GET_PRESIGNED_URL( @<stage_name> , ‘<relative_file_path>‘ , [ <expiration_time> ] )
stage_name : External stage where the file present.
relative_file_path Path and filename of the file relative to its location in the stage.
expiration_time :Length of time (in seconds) after which the short term access token expires. Default value: 3600 (60 minutes).
create or replace stage mystage
url='s3://autorefreshexternalbucket/'
storage_integration = s3_int
directory = (enable = true);
alter stage mystage refresh;
SELECT * FROM DIRECTORY( @mystage );
SELECT
get_presigned_url(@mystage, 'Invoice1.csv') as presigned_url FROM DIRECTORY( @mystage ) where relative_path = 'Invoice1.csv';
Copy the URL and paste in the browser:
It will start downloading the file on your local drive.
Therefore, Using combination of DIRECTORY and GET_PRESIGNED_URL we can download file from Snowflake worksheet. However, This is new for me and will share my findings in upcoming post.
