SnowSQL – Unload Snowflake table to Parquet file

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.

https://sfc-repo.snowflakecomputing.com/snowsql/bootstrap/index.html

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.

Reference

Snowflake reference

Happy Learning !!

NNK

SparkByExamples.com is a Big Data and Spark examples community page, all examples are simple and easy to understand and well tested in our development environment Read more ..

Leave a Reply

You are currently viewing SnowSQL – Unload Snowflake table to Parquet file