Snowflake File URLs: Securely Access and Share Staged Files

Spread the love

Contents hide

1. Introduction

2. Types of URLs in Snowflake to Access Staged Files

2.1. File URL

2.2. Scoped URL

2.3. Pre-signed URL

3. How to Generate URLs to Access Staged Files?

3.1. GET_STAGE_LOCATION

3.2. GET_RELATIVE_PATH

3.3. GET_ABSOLUTE_PATH

3.4. BUILD_STAGE_FILE_URL

3.5. BUILD_SCOPED_FILE_URL

3.6. GET_PRESIGNED_URL

4. Summary

1. Introduction

In Snowflake, files typically stored in internal or external stages, are accessible only to users with the necessary permissions. However, there are scenarios where you might need to share a staged file with a user who does not have direct access to the stage location. Additionally, you may want to download the staged files to a secure location for local storage and analysis.

To facilitate such use cases, Snowflake provides the ability to generate URLs for staged files, allowing controlled access to the data without exposing the entire stage. In this article, we will explore how to generate and use URLs in Snowflake to securely access and share staged data files.

2. Types of URLs in Snowflake to Access Staged Files

The following are the types of Snowflake URLs available to access the files in stage locations.

File URL
Scoped URL
Pre-signed URL

2.1. File URL

A File URL is a permanent URL that points directly to a file stored in a Snowflake stage. Unlike other URL types, it does not expire and remains accessible as long as the file exists, and the user has the necessary permissions.

Key Characteristics:

Permanent Access: The File URL does not have an expiration time, making it a persistent reference to the staged file.
Access Control: Any role with sufficient privileges on the stage can access the file using the File URL.
Generation Methods:
- Using the BUILD_STAGE_FILE_URL file function.
- Querying the directory table of the stage.
Access Methods:
- In Snowsight, if the active role of the user has appropriate privileges on the stage can click on the File URL in the query results tab to download the file directly.
- The file can be accessed programmatically using the File URL via the Snowflake REST API using a GET request.

2.2. Scoped URL

A Scoped URL is a time-limited URL that provides temporary access to a staged file. Unlike a File URL, a Scoped URL is designed for secure, short-term access and expires automatically after a defined period.

Key Characteristics:

Temporary Access: The Scoped URL is temporary and expires when the query result cache expires which is 24 hours currently.
Access Control: Only the user who generated the scoped URL can use the URL to access the referenced file.
Generation Methods: Using the BUILD_SCOPED_FILE_URL file function.
Access Methods:
- In Snowsight, the user who generated the scoped URL can click on the URL in the query results tab to download the file directly.
- The file can be accessed programmatically through the Scoped URL only by the user who generated it via the Snowflake REST API using a GET request

2.3. Pre-signed URL

A Pre-signed URL is a time-limited URL that provides temporary access to a staged file without the need of any additional authentication or authorization token. The files can be accessed via any web browser and expiration time is configurable.

Key Characteristics:

Temporary Access: Pre-signed URLs have a set expiration time which is configurable while generation, after which they become invalid.
Access Control: Any person with a pre-signed URL can access the file for the configured duration.
Generation Methods: Using the GET_PRESIGNED_URL file function.
Access Methods:
- In Snowsight, click on the URL in the query results tab to download the file directly.
- The file can be accessed via any web browser using the Pre-signed URL.

3. How to Generate URLs to Access Staged Files?

Snowflake provides following File Functions to generate URLs to access the files in the stage location.

BUILD_STAGE_FILE_URL
BUILD_SCOPED_FILE_URL
GET_PRESIGNED_URL

Additionally, the following file functions help in extracting details about the stage and files staged which would later be helpful generating the file URLs.

GET_STAGE_LOCATION
GET_RELATIVE_PATH
GET_ABSOLUTE_PATH

For the demonstration, we will use the files stored in the MY_EXT_STAGE stage which points to the AWS S3 bucket s3://te-aws-s3-bucket001/.

CREATE OR REPLACE STAGE MY_EXT_STAGE
URL = 's3://te-aws-s3-bucket001/'
STORAGE_INTEGRATION = AWS_STORAGE_INT;

The files are located in the Inbox folder inside the S3 bucket.

3.1. GET_STAGE_LOCATION

The GET_STAGE_LOCATION retrieves the URL of an external or internal named stage using the stage name as the input.

Syntax:

GET_STAGE_LOCATION( @<stage_name> )

The following query retrieves the URL of external stage MY_EXT_STAGE.

SELECT GET_STAGE_LOCATION(@MY_EXT_STAGE);

3.2. GET_RELATIVE_PATH

The GET_RELATIVE_PATH retrieves the relative path of a staged file from its location in the stage. The inputs required are the stage name and absolute file path.

The absolute file path represents the complete path of the file including the stage location.

Syntax:

GET_RELATIVE_PATH( @<stage_name> , '<absolute_file_path>' )

The following query retrieves the relative path of the file s3_emp_0.csv from the stage location of external stage MY_EXT_STAGE.

SELECT GET_RELATIVE_PATH(@MY_EXT_STAGE, 's3://te-aws-s3-bucket001/Inbox/s3_emp_0.csv');

3.3. GET_ABSOLUTE_PATH

The GET_ABSOLUTE_PATH retrieves the absolute path of a staged file. The inputs required are the stage name and the relative file path from the stage location.

Syntax:

GET_ABSOLUTE_PATH( @<stage_name> , '<relative_file_path>' )

The following query retrieves the absolute path of the file s3_emp_0.csv in the external stage MY_EXT_STAGE using its relative path.

SELECT GET_ABSOLUTE_PATH(@MY_EXT_STAGE, 'Inbox/s3_emp_0.csv');

ABSOLUTE_FILE_PATH = STAGE_LOCATION + RELATIVE_FILE_PATH

3.4. BUILD_STAGE_FILE_URL

The BUILD_STAGE_FILE_URL generates Snowflake file URL to a staged file using the stage name and relative file path as inputs.

Syntax:

BUILD_STAGE_FILE_URL( @<stage_name> , '<relative_file_path>' )

The following query generates a file URL of the file s3_emp_0.csv in the external stage MY_EXT_STAGE using its relative path.

SELECT BUILD_STAGE_FILE_URL(@MY_EXT_STAGE,'Inbox/s3_emp_0.csv');

3.5. BUILD_SCOPED_FILE_URL

The BUILD_SCOPED_FILE_URL generates Snowflake scoped file URL to a staged file using the stage name and relative file path as inputs.

Syntax:

BUILD_SCOPED_FILE_URL( @<stage_name> , '<relative_file_path>' )

The following query generates a scoped file URL of the file s3_emp_0.csv in the external stage MY_EXT_STAGE using its relative path.

SELECT BUILD_SCOPED_FILE_URL(@MY_EXT_STAGE,'Inbox/s3_emp_0.csv');

3.6. GET_PRESIGNED_URL

The GET_PRESIGNED_URL generates Snowflake pre-signed file URL to a staged file using the stage name and relative file path as inputs.

Optionally, the length of time in seconds can be passed as input after which the access to file expires.
The default value is 3600 seconds (60 minutes) and the maximum allowed expiration time is 604800 seconds (7 days).

Syntax:

GET_PRESIGNED_URL( @<stage_name> , '<relative_file_path>', , [ <expiration_time> ] )

The following query generates a pre-signed file URL of the file s3_emp_0.csv in the external stage MY_EXT_STAGE using its relative path which would expire after 5 minutes.

SELECT GET_PRESIGNED_URL(@MY_EXT_STAGE, 'Inbox/s3_emp_0.csv', 300);

4. Summary

Snowflake provides multiple ways to access staged files using URLs, enabling controlled and secure data access without exposing the entire stage. There are three types of URLs that offer different levels of access control and expiration policies.

File URLs are permanent and accessible as long as the user has the necessary permissions.
Scoped URLs provide temporary, user-specific access that expires after 24 hours.
Pre-signed URLs allow temporary access for a configurable duration and can be accessed without requiring additional authentication.

To generate these URLs, Snowflake offers built-in file functions such as