How to Export Data from Snowflake: Simple Steps and Examples
To export data from Snowflake, use the
COPY INTO command to unload data from a table or query into external storage like Amazon S3 or Azure Blob Storage. This command lets you specify file format, compression, and destination path for the exported files.Syntax
The COPY INTO command exports data from Snowflake to an external location. You specify the source table or query, the destination path, and options like file format and compression.
- source: Table name or SQL query to export data from.
- location: External storage path (e.g., S3 bucket URL).
- file_format: Format of exported files (CSV, JSON, Parquet, etc.).
- compression: Optional compression type (GZIP, NONE, etc.).
sql
COPY INTO 's3://mybucket/export/' FROM my_table FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1) COMPRESSION = GZIP HEADER = TRUE;
Example
This example exports data from the customers table to an Amazon S3 bucket in compressed CSV files with headers.
sql
COPY INTO 's3://mybucket/exports/customers/' FROM customers FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' SKIP_HEADER = 1) COMPRESSION = GZIP HEADER = TRUE CREDENTIALS = (AWS_KEY_ID='your_aws_key' AWS_SECRET_KEY='your_aws_secret');
Output
Data exported successfully to s3://mybucket/exports/customers/ as compressed CSV files.
Common Pitfalls
- Not setting proper permissions on the external storage can cause export failures.
- Forgetting to specify
CREDENTIALSor using incorrect keys will block access. - Using incompatible file formats or missing required options like
FIELD_DELIMITERcan corrupt output files. - Not including
HEADER = TRUEwhen needed can make CSV files harder to read.
sql
/* Wrong: Missing credentials */ COPY INTO 's3://mybucket/exports/' FROM my_table FILE_FORMAT = (TYPE = CSV); /* Right: Include credentials */ COPY INTO 's3://mybucket/exports/' FROM my_table FILE_FORMAT = (TYPE = CSV) CREDENTIALS = (AWS_KEY_ID='key' AWS_SECRET_KEY='secret');
Quick Reference
| Option | Description |
|---|---|
| COPY INTO | Command to export data from Snowflake |
| FROM | Source table or query |
| 's3://bucket/path/' | Destination external storage path |
| FILE_FORMAT | Defines output file type and options |
| COMPRESSION | Optional compression type (GZIP, NONE) |
| HEADER | Include column headers in output (TRUE/FALSE) |
| CREDENTIALS | Access keys for external storage |
Key Takeaways
Use COPY INTO to export data from Snowflake to external storage like S3.
Always specify file format and compression options for correct output.
Provide valid credentials to access the external storage location.
Include HEADER = TRUE for CSV exports to add column names.
Check permissions on storage to avoid export failures.
