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.
As illustrated in the diagram above, unloading data into an Azure container is performed in two, separate steps:
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.
Download the files from the Azure container.
Use the interfaces/tools provided by Microsoft Azure to get the data files.
The instructions in this set of topics assume you have read Preparing to Unload Data and have created a named file format, if desired.
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¶
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_stagestage to unload all the rows in the
mytabletable into one or more files into the Azure container. A
d1filename prefix is applied to the files:
COPY INTO @my_ext_unload_stage/d1 from mytable;
Use the tools provided by Azure to retrieve the objects (i.e. files generated by the command) from the container.