0
0
During the last post we discuss about the DIRECTORY tables. We have seen how the directory table helps to retrieve the snowflake hosted file URL for each file present in stage.In continuation of the same we will discuss about three key functions being use with Directory tables. These functions are used to generate the URL and grant the access based on their authorizations.
BUILD_STAGE_FILE_URL:
Generates a Snowflake-hosted file URL to a staged file using the stage name and relative file path as inputs.
That is, the file URL does not expire.
When users send a file URL to the REST API to access files, Snowflake performs the following actions:
- Authenticate the user.
- Verify that the role has sufficient privileges on the stage that contains the file.
- Redirect the user to the staged file in the cloud storage service.
BUILD_STAGE_FILE_URL( @<stage_name> , '<relative_file_path>' )
select BUILD_STAGE_FILE_URL( @EXT_CSV_STAGE , RELATIVE_PATH) as presigned_url FROM DIRECTORY( @EXT_CSV_STAGE )
COPY the URL and search in browser:
To download or access a file, users send the file URL in a GET request to the REST API endpoint along with the authorization token.
Now to download the file from generated URL, need to follow the below steps:
- Generate the Public key and private key file using openssl.
- Alter the user having access to STAGE.
alter user sachinsnowpro set rsa_public_key= <<rsa public key generated in first step>>
- Generate my JWT token:
snowsql --private-key-path rsa_key.p8 --generate-jwt -a mk72610.us-east-2.aws -u sachinsnowpro
- Copy the token from command line.
- Generate the CURL command.
curl -L -i -X GET \
-H "Authorization: Bearer <<JWT TOKEN from previous step>>" \
-H "X-Snowflake-Authorization-Token-Type: KEYPAIR_JWT" \
-H "Accept: */*" \
-H "User-Agent: myApplicationName/xx1.0" \
"<file_url from the select BUILD_STAGE_FILE_URL statement>
Unstructured File will be downloaded to the folder on your local machine.
BUILD_SCOPED_FILE_URL:
BUILD_SCOPED_FILE_URL:
A scoped URL is encoded and permits access to a specified file for a limited period of time.
BUILD_SCOPED_FILE_URL( @<stage_name> , ‘<relative_file_path>’ )
Benefits:
This give scoped access to data files to specific roles in the same account via a view that retrieves scoped URLs. Only roles that have privileges on the view can access the files.
So in case if we need to provide the access to unstructured data for specific roles then BUILD_SCOPED_FILE_URL is being used w.r.t VIEW. Consider the scenario, when we need to providing unstructured data to other accounts via a share, we can create the secure view with BUILD_SCOPED_FILE_URL.
CREATE SECURE VIEW BUILD_URL
AS
SELECT BUILD_SCOPED_FILE_URL( @EXT_CSV_STAGE , RELATIVE_PATH) as presigned_url
FROM DIRECTORY( @EXT_CSV_STAGE );
Grant select on all views in schema DEMO_DB.public to role SUPER_ROLE;
Go to Snowsight with the SUPER_USER and run the query on view
SELECT * FROM BUILD_URL;
GET_PRESIGNED_URL :
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
Benefit:
Download or access files without authenticating into Snowflake or passing an authorization token. Pre-signed URLs are open; any user or application can directly access or download the files.
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).
SELECT
get_presigned_url(@EXT_CSV_STAGE, RELATIVE_PATH) as presigned_url
FROM DIRECTORY( @EXT_CSV_STAGE )
File will be downloaded without any CURL command or Secure View creation.
