How To Load A Parquet File Into The Snowflake Table

May 23, 2022May 25, 2022 CS Electrical And Electronics

Hello guys, welcome back to my blog. Here in this article, I will discuss how to load a parquet file into the snowflake table, different ways to load a parquet file, commands to be used, etc.

If you have any electrical, electronics, and computer science doubts, then ask questions. You can also catch me on Instagram – CS Electrical & Electronics

Also, read:

How To Load A Parquet File Into The Snowflake Table

A parquet file is a small-sized column storage file. It helps to load the complex and large dataset into the Snowflake tables. In this blog, we will see are the commands used to load parquet files into the Snowflake tables. Also, you will better know the steps involved in loading parquet files into the Snowflake tables. Let’s see them in detail in this blog.

Introduction

Parquet files can be loaded into the snowflake tables in two steps. First, the file is uploaded in the internal stages, and then it is loaded into the Snowflake tables. The Parquet file format has many advantages over other formats such as CSV and JSON. Similarly, the Snowflake database tables have many features, such as data integrity and reduced space for data storage.

This blog will make you walk through loading parquet files into the Snowflake database tables. Reading this blog, you will also understand the benefits of Parquet and Snowflake files. Explore for Snowflake Tutorial for more information.

Table of Contents:

1. What is the Parquet file format
2. What are the advantages of the Parquet file format?
3. What are the Snowflake tables?
4. What are the advantages of the Snowflake tables?
5. What is the PUT command in SQL, and how is it used?
6. What is the COPY INTO command in SQL, and how is it used?
7. How to load parquet files into the Snowflake tables
8. Uploading Parquet files in Snowflake internal stages
9. Viewing the Uploaded files from the list
10. Loading Parquet files into the Snowflake tables
11. Conclusion

What is Parquet File Format

Parquet file format is an open-source and column storage file. The columnar storage helps to reduce the storage space significantly. So, large datasets such as structured data tables, images, videos, and documents can be stored using this file format. And queries are quickly processed when a Parquet file is used. As the parquet file has binary representation, it is independent of languages.

What are the Advantages of Parquet File Format

Supports Complex Data Types: The parquet is a small file format that can easily adapt to many systems. It supports complex data types and advanced nested data structures. Parquet files can process gigabytes of data due to their layout.
Compression Schemes: It provides efficient compression and encoding schemes. Each column will have the same data type so that compression can be done quickly. In addition to that, different data types can be compressed differently.
Cloud Storage: Parquet files can be saved in cloud storage using effective column-wise compression and encoding schemes according to different data types. As a result, it also reduces cloud data storage space and associated costs.
Data Performance: Data throughput and performance are typically improved when a parquet file reads data in columns. A method such as ‘Data Skipping’ helps fetch data in columns by skipping reading the row values. It minimizes latency time significantly.

What are the Snowflake Tables

The Snowflake tables are the database tables where each reference table is linked with many other reference tables. So, it forms a Snowflake structure that has many branches. The Snowflake tables use less space to store large datasets with high data protection and low data redundancy.

The Snowflake tables support creating transient tables and temporary tables in addition to permanent tables. Generally, data is not stored in transient and temporary tables for long periods. Temporary data is stored in the temporary tables within the session where it is created, and this data will remain there until the session is over. The moment the session is over, the temporary tables will be deleted by the file owner or snowflake.

The transient tables don’t have fail-safe protection, whereas a fail-safe mechanism protects permanent tables. Hence, the cost is reduced when the transient tables are used for data storage.

However, to know more information on this exciting platform and enhance your career growth in this field, Snowflake Online Training is very beneficial.

What are the Advantages of the Snowflake tables:

Snowflake tables reduce storage size and have low data redundancy because data is normalized here
Data can be easily maintained in Snowflake tables
Complex queries can be quickly processed
It provides high protection to data and supports many-to-many relationships.

What is the PUT Command in SQL and how is it used

The PUT command uploads data from a local directory to any Snowflake internal stages. And this command cannot be used to upload data in external stages. There are three internal stages in Snowflake warehouse named as Named internal stage, the Specified table stage, and the Current user stage.

Syntax of the PUT Command:

PUT file://<path_to_file>/<filename> internalStage

Where @<name> allows uploading a file into the Named internal stage

@%<tablename> allows uploading a file into the table stage

@~<username> allows uploading a file into the user stage

Examples for using the PUT command:

You can understand the use of the PUT command from the following examples.

1. Named Internal Stage:

If you want to upload the mydata1.csv file in the /tmp/data directory into the named internal stage my_int_stage, you can use the following command to carry out the same.

put file:///tmp/data/mydata.csv@my_int_stage;

2. Table Internal Stage:

Suppose you want to upload the orders_002.csv file in the /tmp/data directory into an internal table stage orderstiny1_ext, and prefer to disable the automatic data compression, then you shall use the below command to achieve the same.

put file:///tmp/data/orders_002.csv@%orderstiny1_ext auto_compress=false;

3. Current User Stage:

Suppose you want to upload the mydata1.csv file in the C:\tmp\data directory into an internal stage for the current user and prefer to enable the automatic data compression, then you shall use the below command to complete the same.

put file://c:\temp\data\mydata1.csv@~ auto_compress=true;

What is the COPY INTO Command in SQL and how is it used

This command loads files from internal and external stages into the existing Snowflake tables. Generally, the files are staged in the internal and external stages such as the named internal stage, table internal stage, current user stage, named external stage, and external locations.

Syntax of COPY INTO Command:

COPY INTO [<namespace>.]<table_name> FROM { internalStage | externalStage | externalLocation}

Examples for using the COPY INTO command:

Here, you can understand the use of the COPY INTO command from the following examples.

1. Named Internal Stage:

If you prefer to load a file from the Named Internal Stage into the Snowflake table, you can use the below command to accomplish the same.

copy into mytablefrom @my_int_stage;

2. Table Internal Stage:

The following command can upload a file from an internal table stage into the Snowflake table.

copy into mytablefile_format = (type = csv);

3. Current User Stage:

If you prefer to upload a file from a current user’s internal stage into the Snowflake table, you can use the following command.

copy into mytable from @~/stagedfile_format = (format_name = ‘mycsv’);

4. Named External Stage:

You can use the below command to upload a file from the named external stages that reference the locations such as Amazon S3, Google Cloud Storage, and Microsoft Azure into the Snowflake table.

copy into mycsvtableFrom @my_ext_stage/tutorials/dataloading/contacts1.csv;

5. External Location:

The files prefixed with data/files from the storage location such as Amazon S3, Google Cloud Storage, and Microsoft Azure are loaded in the Snowflake table using a Named my_csv_format file format. You can use the following command to load a file into the Snowflake table.

copy into mytableFrom s3://mybucket/data/files

How to load Parquet Files into the Snowflake Tables

Loading Parquet files into the Snowflake tables is done in two steps as follows;

1. A Parquet file is uploaded in the Snowflake warehouse as an internal stage using the PUT command.

2. Then, the Parquet file is loaded into the Snowflake table from the internal stage using the COPY INTO command.

load Parquet Files into the Snowflake Tables — **Figure 1: Loading Parquet files into the Snowflake tables**

Uploading Parquet files in Snowflake Internal Stages:

You shall use the PUT command to upload a Parquet file into Snowflake internal stages.

You can understand this better by going through the examples described below;

1. The following command is used to upload a Parquet file in the internal table EMP stage.

PUT file:///tmp/data1_0_0_0.snappy.parquet @%EMP;

After the uploading, you will get the result as follows;

URL: https://sparkbyexamples.com/snowflake/load-parquet-file-into-snowflake-table/

2. The following command is used to upload a Parquet file in an internal name stage.

PUT file:///apps/sparkbyexamples/data1_0_0_0.snappy.parquet @~;

Viewing the Uploaded Files from the List

The command ‘LIST’ is used to ensure whether the Parquet file is uploaded successfully in the internal table stage.

For example, the below command helps to verify the uploading in the internal EMP stage.

LIST @%EMP

Then, you will get the results as follows;

Loading Parquet Files into the Snowflake Tables

The command ‘COPY INTO’ loads the parquet file into the Snowflake database table. Before loading, it is essential that you have to create a table EMP that should have a column of type variant.

Accordingly, use the following command to create the table.

CREATE OR REPLACE TABLE EMP (PARQUET_RAW VARIANT)

Further, Loading of parquet files into the snowflake tables can be done in two ways as follows;

1. You can use the following command to load the Parquet file into the table.

COPY INTO EMP from (select $1 from @%EMP/data1_0_0_0.snappy.parquet)file_format = (type=PARQUET COMPRESSION=SNAPPY);

Then, you will get the results as follows;

2. In addition, you can use the following command to load the Parquet file into the table.

COPY INTO EMP from @%EMP/data1_0_0_0.snappy.parquetfile_format = (type=PARQUET COMPRESSION=SNAPPY);

Then, you will get the results as follows;

you can use the following command to load the Parquet file into the table.

Conclusion

Having read this article, you must have learned how to load parquet files into Snowflake tables. Also, we have briefed the various commands used in SQL to manage Parquet files and Snowflake tables. Here, we have well outlined the advantages of both parquet and snowflake tables. Last but not least, loading data in parquet file format reduces the storage size and speeds up the loading process.

Author Bio

Sai Priya Ravuri is a Digital Marketer, and a passionate writer, who is working with MindMajix, a top global online training provider. She also holds in-depth knowledge of IT and demanding technologies such as Business Intelligence, Machine Learning, Salesforce, Snowflake, Software Testing, QA, Data analytics, Project Management, ERP tools, etc.

This was about “How To Load A Parquet File Into The Snowflake Table”. I hope this article may help you all a lot. Thank you for reading.

Also, read: