In addition to loading directly from files in external locations like AWS S3, Azure containers and Google Cloud Storage, Snowflake supports creating named external stages. These stages specifies where data files are stored (i.e. “staged”) so that the data in the files can be loaded into a table.
External Stages are recommended when you plan to load data regularly from the same location. The external stages also allows you to create external tables on top of the files present in their locations there by allowing you to query the data present in files using SQL. The external tables created on top of the data files can also be used to join with other Snowflake tables, create views and materialized views.
In this article let us discuss how to create External Stages in Snowflake.
Creating External Stages in Snowflake
External Stages can be created in following ways.
- Web Interface / SQL using Secret Access Keys of cloud provider.
- Configuring a Cloud Storage Integration.
The first method is the easiest way of creating an external stage in Snowflake but it involves supplying cloud storage credentials to connect to external location.
The second method is a recommended option which avoids the need to supplying cloud storage credentials when creating stages or loading data. It involves several steps in creating external Stages.
Creating External Stages using Web Interface
Follow below steps to create an external stage using Web Interface
- Navigate to Databases
- Select the Database in which you want to create an external stage.
- Go to Stages tab and click Create.
- Select the external cloud storage provider and click Next.
- Provide the details of stage name and the schema in which stage needs to be created.
- Provide the URL of the location and the secret access keys to connect.
- Click Finish to create an external stage.
The below example shows creating an external stage in Snowflake on Azure using Web Interface.
The external stages can also be created using SQL statements by passing cloud storage credentials as shown below.
use schema mydb.public;
-- Creating an external stage in Snowflake on Azure location
CREATE STAGE my_azure_stage
URL = 'azure://myazurespace.blob.core.windows.net/snowflake'
CREDENTIALS = (AZURE_SAS_TOKEN = '***************************');
-- Creating an external stage in Snowflake on AWS S3 location
CREATE STAGE my_s3_stage
URL = 's3://mybucket/snowflake/'
CREDENTIALS = (AWS_KEY_ID = '*********************' AWS_SECRET_KEY = '**************');Creating External Stages by configuring a Cloud Storage Integration
A storage Integration is a Snowflake object that stores a generated identity and access management (IAM) entity for your external cloud storage, along with an optional set of allowed or blocked storage locations in cloud storage providers (Amazon S3, Google Cloud Storage, or Microsoft Azure).
Cloud provider administrators in your organization grant permissions on the storage locations to the generated entity. This option allows users to avoid supplying credentials when creating stages or when loading or unloading data.
A single Storage Integration can support multiple external stages.
Follow below steps to create an external stage using Cloud Storage Integration.
Step-1: Creating Cloud Storage Integration
The following examples shows how to create Cloud Storage Integration.
Amazon S3
create storage integration s3_int
type = external_stage
storage_provider = s3
enabled = true
storage_aws_role_arn = '<iam_role>'
storage_allowed_locations = ('s3://bucket/path/', 's3://bucket/path2/');Snowflake recommends creating an IAM policy for Snowflake to access the S3 bucket. You can then create an IAM Role and attach the policy to the role and use the security credentials generated by AWS for the role to access files in the bucket.
Follow below links to create an IAM Policy and IAM Role in AWS for Snowflake
<iam_role> specifies the Amazon Resource Name (ARN) of the AWS IAM role you create using above steps.
Microsoft Azure
create storage integration azure_int
type = external_stage
storage_provider = azure
enabled = true
azure_tenant_id = '<tenant_id>'
storage_allowed_locations = ('azure://account.blob.core.windows.net/container/path/','azure://account.blob.core.windows.net/container/path2/');<tenant_id> is the ID for your Office 365 tenant that the allowed and blocked storage accounts belong to. A storage integration can authenticate to only one tenant, and so the allowed and blocked storage locations must refer to storage accounts that all belong this tenant.
To find your tenant ID, log into the Azure portal and click Azure Active Directory » Properties. The tenant ID is displayed in the Directory ID field.
Google Cloud Storage
create storage integration gcs_int
type = external_stage
storage_provider = gcs
enabled = true
storage_allowed_locations = ('gcs://bucket/path/','gcs://bucket/path2/');You can also specify locations that you want to block using storage_blocked_locations in the examples shown above.
Step-2: Retrieve the Snowflake Service Account/Client Name/IAM User for your Snowflake Account
Execute the DESCRIBE INTEGRATION command to retrieve the details of the service account that was created automatically for your Snowflake account.
DESC STORAGE INTEGRATION <integration_name>;<integration_name> is the name of the Cloud Storage Integration you created in Step 1.
Amazon S3
desc integration s3_int;Record the values in the following columns from the output result.
| Value | Description |
| STORAGE_AWS_IAM_USER_ARN | The AWS IAM user created for your Snowflake account. We provision a single IAM user for your entire Snowflake account. All S3 storage integrations use that IAM user. |
| STORAGE_AWS_EXTERNAL_ID | The external ID that is needed to establish a trust relationship. |
Microsoft Azure
desc integration azure_int;Record the values in the following columns from the output result.
| Value | Description |
| AZURE_CONSENT_URL | URL to the Microsoft permissions request page. |
| AZURE_MULTI_TENANT_APP_NAME | Name of the Snowflake client application created for your account. In a later step in this section, you will need to grant this application the permissions necessary to obtain an access token on your allowed storage locations. |
Google Cloud Storage
desc integration gcs_int;Record the values in the following columns from the output result.
| Value | Description |
| STORAGE_GCP_SERVICE_ACCOUNT | Cloud Storage service account created for your Snowflake account. We provision a single Cloud Storage service account for your entire Snowflake account. All Cloud Storage integrations use that service account. |
Step-3: Grant Snowflake permissions to Access to the Storage Locations
Depending on the Cloud Storage on which you wanted to create an external stage, the process to assign the permissions to access the storage locations is different.
Follow below links to grant Snowflake permissions to Access to the Storage Locations
- Grant the IAM User Permissions to Access AWS S3 Bucket Objects
- Grant Snowflake Access to the Azure Storage Locations
- Grant the Service Account Permissions to Access Google Cloud Bucket Objects
Step-4: Create an External Stage
Create an external stage that references the storage integration you created in Step 1 using the CREATE STAGE command.
Amazon S3
Creating an External Stage on AWS S3
create stage my_s3_stage
storage_integration = s3_int
url = 's3://bucket1/path1'
file_format = my_csv_format;Microsoft Azure
Creating an External Stage on Azure
create stage my_azure_stage
storage_integration = azure_int
url = 'azure://myaccount.blob.core.windows.net/container1/path1'
file_format = my_csv_format;Google Cloud Storage
Creating an External Stage on Google Cloud
create stage my_gcs_stage
url = 'gcs://mybucket1/path1'
storage_integration = gcs_int
file_format = my_csv_format;Related Articles:
