Unloading into Microsoft Azure

If you already have a Microsoft Azure account and use Azure containers for storing and managing your files, you can make use of your existing containers and folder paths when unloading data from Snowflake tables.

This topic describes how to use the COPY INTO location command to unload data from a table into an external stage that points to an Azure container. You can then download the unloaded data files to your local file system.

Unloading data to Azure

As illustrated in the diagram above, unloading data into an Azure container is performed in two, separate steps:

Step 1:

Use the COPY INTO location command to copy the data from the Snowflake database table into one or more files in an Azure container.

Use a stage object that references the Azure container. The stage object specifies the URL and security credentials for the Azure container where the files are written.

This step requires an active virtual warehouse. The warehouse provides the compute resources to write rows from the table.

Step 2:

Download the files from the Azure container.

Use the interfaces/tools provided by Microsoft Azure to get the data files.

Note

The instructions in this set of topics assume you have read Preparing to Unload Data and have created a named file format, if desired.

Tip

Before you begin, you may also want to read Data Unloading Considerations for best practices, tips, and other guidance.

In this Topic:

Configuring an Azure Container for Unloading Data

For Snowflake to write to an Azure container, you must generate a shared access signature (SAS) token for your storage access account. For configuration instructions, see Configuring an Azure Container for Loading Data.

Unloading Data into an External Stage

External stages are named database objects that provide the greatest degree of flexibility for data unloading. Because they are database objects, privileges for named stages can be granted to any role.

You can create an external named stage using either the web interface or SQL:

Web Interface:Click on Databases » db_name » Stages
SQL Command:CREATE STAGE

Creating a Named Stage

The following example creates an external stage named my_ext_unload_stage with a folder path named unload. The stage references the named file format object called my_csv_unload_format that was created in Preparing to Unload Data:

CREATE OR REPLACE STAGE my_ext_unload_stage
  URL='azure://myaccount.blob.core.windows.net/unload'
  CREDENTIALS=(AZURE_SAS_TOKEN='?sv=2016-05-31&ss=b&srt=sco&sp=rwdl&se=2018-06-27T10:05:50Z&st=2017-06-27T02:05:50Z&spr=https,http&sig=bgqQwoXwxzuD2GJfagRg7VOS8hzNr3QLT7rhS8OFRLQ%3D')
  ENCRYPTION=(TYPE='AZURE_CSE' MASTER_KEY = 'kPxX0jzYfIamtnJEUTHwq80Au6NbSgPH5r4BDDwOaO8=')
  FILE_FORMAT = my_csv_unload_format;

Note that the AZURE_SAS_TOKEN and MASTER_KEY values used in this example are for illustration purposes only.

Unloading Data to the Named Stage

  1. Use the COPY INTO location command to unload data from a table into an Azure container using the external stage.

    The following example uses the my_ext_unload_stage stage to unload all the rows in the mytable table into one or more files into the Azure container. A d1 filename prefix is applied to the files:

    COPY INTO @my_ext_unload_stage/d1 from mytable;
    
  2. Use the tools provided by Azure to retrieve the objects (i.e. files generated by the command) from the container.