Using SnowSQL COPY INTO statement you can download/unload the Snowflake table to Parquet file. Unloading a Snowflake table to the Parquet file is a two-step process.
- First use “
COPY INTO” statement, which copies the table into the Snowflake internal stage, external stage or external location. - Use “
GET” statement to download the file from the internal stage.
Pre-requisite
Install Snowflake CLI to run SnowSQL commands.
Below URL takes you to the Snowflake download index page, navigate to the OS you are using and download the binary and install.
Connect to Snowflake database and schema
syntax
user>snowsql -a accountname -u user -d dtabasename -s schemaname
for example, below Snowsql statement connects to account “oea82.us-east-1”, user “nkmr”, database “employee” and schema “public”.
user>snowsql -a oea82.us-east-1 -u nkmr -d employee -s public
Password:
* SnowSQL * v1.2.5
Type SQL statements or !help
nkmr#[email protected]>
Unload to Snowflake internal table stage in a Parquet file
Using SnowSQL COPY INTO statement, you can unload the Snowflake table to the internal table stage. In below example, we are unloading table EMP and stores at the internal table stage @%EMP/result/data
Use the file_format = (type = parquet) to export in a Parquet file format.
COPY INTO '@%EMP/result/data' from EMP file_format = (type = parquet);
+---------------+-------------+--------------+
| rows_unloaded | input_bytes | output_bytes |
|---------------+-------------+--------------|
| 5 | 122 | 115 |
+---------------+-------------+--------------+
1 Row(s) produced. Time Elapsed: 1.300s
As you see above, we have 5 records in the EMP table and all records have been unloaded. Now use GET to download the file.
Download the parquet file from the Snowflake internal table stage
Use GET snowSQL command to download the unloaded file from the internal table stage to the local file system. By default, all unloaded data files are compressed using gzip, unless compression is explicitly disabled or one of the other supported compression methods is explicitly specified.
GET @%EMP/result/data file://c:/tmp/;
data_0_0_0.snappy.parquet(0.00MB): [##########] 100.00% Done (0.197s, 0.01MB/s)
.
+----------------------------+------+------------+---------+
| file | size | status | message |
|----------------------------+------+------------+---------|
| data_0_0_0.snappy.parquet | 1445 | DOWNLOADED | |
+----------------------------+------+------------+---------+
1 Row(s) produced. Time Elapsed: 2.045s
nkmr#[email protected]>
This downloads a file data_0_0_0.snappy.parquet to /tmp directory.
Conclusion
In this article, you have learned first how to unload the Snowflake table into internal table stage in a Parquet file format using COPY INTO SnowSQL and then how to download the file to the local file system using GET.
Related Articles
- SnowSQL – Unload Snowflake table to Amazon S3
- SnowSQL – Unload Snowflake Table to CSV file
- Download Snowflake table to local WINDOWS | Linux | MAC
- Load CSV file into Snowflake Database table
- Snowflake – CREATE TABLE LIKE
- Snowflake SQL – SELECT INTO or similar
Reference
Happy Learning !!