Categories:

File Functions

GET_PRESIGNED_URL

Generates the pre-signed URL to a staged file using the stage name and relative file path as inputs.

Access files in a stage using any of the following methods:

  • Navigate to the pre-signed URL directly in a web browser.

  • Retrieve a pre-signed URL in Snowsight. Click on the pre-signed URL in the results table.

  • Send the pre-signed URL in a request to the REST API for file support.

Note

When calling this function for files in an external stage that references Microsoft Azure cloud storage: This function returns output only when the Azure container that stores the blob object is accessed using a storage integration; querying the function fails if the container is accessed using a shared access signature (SAS) token you generate.

The GET_PRESIGNED_URL function requires Azure Active Directory authentication to create the user delegation SAS token. For this purpose, a storage integration object stores a generated service principal for your Azure cloud storage. The Snowflake service principal is granted a role that includes the Microsoft.Storage/storageAccounts/blobServices/generateUserDelegationKey permission (or action). Both the Storage Blob Data Reader and Storage Blob Data Contributor roles include this permission. For more information about this permission, see the Microsoft documentation.

For more information about accessing an Azure container, see Configuring an Azure Container for Loading Data.

Syntax

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

Arguments

stage_name

Name of the internal or external stage where the file is stored.

Note

If the stage name includes spaces or special characters, it must be enclosed in single quotes (e.g. '@"my stage"' for a stage named "my stage").

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).

Usage Notes

  • The stage where the files are stored must have server-side encryption only. For details, see CREATE STAGE.

  • This SQL function returns a value for any role that has the following privilege on the stage:

    External stage

    USAGE

    Internal stage

    READ

Output

Pre-signed URL of the staged file.

Examples

Querying the Function

SELECT GET_PRESIGNED_URL(@images_stage, 'us/yosemite/half_dome.jpg', 3600);

+================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================-------+
| GET_PRESIGNED_URL(@IMAGES_STAGE, 'US/YOSEMITE/HALF_DOME.JPG', 3600)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
|================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================-------|
| http://myaccount.s3.amazonaws.com/national_parks/us/yosemite/half_dome.jpg?X-Amz-Algorithm=AWS4-HMAC-SHA256&X-Amz-Credential=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxAus-west-xxxxxxxxxaws1_request&X-Amz-Date=20200625T162738Z&X-Amz-Expires=3600&X-Amz-Security-Token=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx-Amz-SignedHeaders=host&X-Amz-Signature=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx   |
+================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================================-------+

Loading Metadata for an Image File and Retrieving the Pre-signed URL

Use the API for your cloud storage service to generate a list of JSON documents that contain the metadata extracted from the images.

For example, suppose the JSON document for one bitmap image file is as follows:

{
  "file_url": "s3://photos/national_parks/us/yosemite/half_dome.jpg",
  "image_format": "jpeg",
  "dimensions": {"x" : 1024, "y" : 768},
  "tags":[
    "rock",
    "cliff",
    "valley"
  ],
  "dominant_color": "gray"
}

Create a table for the image metadata, load the metadata into the table, and generate the pre-signed URL for the image:

-- Create a table to store the file metadata

  CREATE TABLE images_table
  (
      file_url string,
      image_format string,
      dimensions_X number,
      dimensions_Y number,
      tags array,
      dominant_color string,
      relative_path string
  );

-- Load the metadata from the JSON document into the table.

COPY INTO images_table
  FROM
  (SELECT $1:file_url::STRING, $1:image_format::STRING, $1:size::NUMBER, $1:tags, $1:dominant_color::STRING, GET_RELATIVE_PATH(@images_stage, $1:file_url)
  FROM
  @images_stage/image_metadata.json)
  FILE_FORMAT = (type = json);

-- Create a view that queries the pre-signed URL for an image as well as the image metadata stored in a table.
CREATE VIEW image_catalog AS
(
  SELECT
   size,
   get_presigned_url(@images_stage, relative_path) AS presigned_url,
   tags
  FROM
    images_table
);