How to Unload Data from Snowflake: Syntax and Examples
To unload data from Snowflake, use the
COPY INTO command to export query results or table data to external locations like cloud storage. Specify the target location, file format, and options within the command to control the output.Syntax
The COPY INTO command exports data from Snowflake to an external location. Key parts include:
COPY INTO <location>: Destination path or stage where data will be unloaded.FROM <table_or_query>: Source table or query to export data from.FILE_FORMAT = (TYPE = <format> ...): Defines the output file format like CSV or JSON.HEADER = TRUE/FALSE: Whether to include column headers in output files.MAX_FILE_SIZE = <bytes>: Maximum size of each output file.
sql
COPY INTO 's3://mybucket/data/' FROM my_table FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' HEADER = TRUE) MAX_FILE_SIZE = 50000000;
Example
This example unloads data from the sales table to an S3 bucket in CSV format with headers included.
sql
COPY INTO 's3://mybucket/sales_data/' FROM sales FILE_FORMAT = (TYPE = CSV FIELD_DELIMITER = ',' HEADER = TRUE) MAX_FILE_SIZE = 100000000;
Output
Statement executed successfully.
Common Pitfalls
Common mistakes when unloading data include:
- Not setting up the external stage or storage permissions correctly, causing access errors.
- Forgetting to specify
FILE_FORMAT, which leads to default formats that may not match your needs. - Ignoring file size limits, which can cause many small files or failures.
- Not including
HEADER = TRUEwhen headers are needed for clarity.
sql
/* Wrong: Missing FILE_FORMAT causes default format issues */ COPY INTO 's3://mybucket/data/' FROM sales; /* Right: Specify file format and header */ COPY INTO 's3://mybucket/data/' FROM sales FILE_FORMAT = (TYPE = CSV HEADER = TRUE);
Quick Reference
| Parameter | Description | Example |
|---|---|---|
| COPY INTO | Command to unload data | COPY INTO 's3://bucket/path/' |
| FROM | Source table or query | FROM my_table |
| FILE_FORMAT | Output file format and options | FILE_FORMAT = (TYPE = CSV HEADER = TRUE) |
| HEADER | Include column headers | HEADER = TRUE |
| MAX_FILE_SIZE | Max size per output file in bytes | MAX_FILE_SIZE = 50000000 |
Key Takeaways
Use the COPY INTO command to unload data from Snowflake to external storage.
Always specify FILE_FORMAT to control output file type and structure.
Ensure external storage permissions and stages are properly configured.
Include HEADER = TRUE if you want column names in your output files.
Set MAX_FILE_SIZE to manage output file sizes and avoid too many small files.
