Unloading into Google Cloud Storage¶
If you already have a Google Cloud Storage account and use Cloud Storage buckets for storing and managing your files, you can make use of your existing buckets and folder paths when unloading data from Snowflake tables. This topic describes how to use the COPY command to unload data from a table into a Cloud Storage bucket. You can then download the unloaded data files to your local file system.
As illustrated in the diagram below, unloading data into a Cloud Storage bucket is performed in two steps:
Use the COPY INTO <location> command to copy the data from the Snowflake database table into one or more files in a Cloud Storage bucket bucket. In the command, you specify a named external stage object that references the Cloud Storage bucket (recommended) or you can choose to unload directly to the bucket by specifying the URI and security credentials (if required) for the bucket.
Regardless of the method you use, this step requires a running, current virtual warehouse for the session. The warehouse provides the compute resources to write rows from the table.
Use the interfaces/tools provided by Google to download the files from the Cloud Storage bucket.
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:
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: 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='gcs://mybucket/unload' CREDENTIALS=(GCS_SERVICE_ACCOUNT_KEY_BASE64='xxxx') FILE_FORMAT = my_csv_unload_format;
Note that the GCS_SERVICE_ACCOUNT_KEY_BASE64 value used in this example is for illustration purposes only.
Unloading Data to the Named Stage¶
Use the COPY INTO <location> command to unload data from a table into a Cloud Storage bucket 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 Cloud Storage bucket. A
d1filename prefix is applied to the files:
COPY INTO @my_ext_unload_stage/d1 from mytable;
Use the tools provided by Cloud Storage to retrieve the objects (i.e. files generated by the command) from the bucket.
Unloading Data Directly into a Cloud Storage bucket¶
Use the COPY INTO <location> command to unload data from a table directly into a specified Cloud Storage bucket. This option works well for ad hoc unloading, when you aren’t planning regular data unloading with the same table and bucket parameters.
You must specify the URI for the Cloud Storage bucket and the security credentials for accessing the bucket in the COPY command.
The following example unloads all the rows in the
mytabletable into one or more files with the folder path prefix
unload/in a Cloud Storage bucket:
COPY INTO gcs://mybucket/unload/ from mytable credentials = (GCS_SERVICE_ACCOUNT_KEY_BASE64='xxxx');
Use the Cloud Storage console (or equivalent client application) to retrieve the objects (i.e. files generated by the command) from the bucket.