Snowflake Load Local CSV File using COPY and Example

Post author:Vithal S
Post last modified:February 4, 2020
Post category:Snowflake
Reading time:5 mins read

Many organizations use flat files such as CSV or TSV files to offload large tables. Managing flat files such as CSV is easy and it can be transported by any electronic medium. In this article, we will check how to load or import local CSV file into Snowflake using COPY command with some examples.

Snowflake Load Local CSV File using COPY and Example

Load Local CSV File using Snowflake COPY Command

There are a couple of methods that you can use to load a csv file present in your local system.

Following are the methods.

Use SnowSQL command Line Interface
Use Snowflake Web Interface

Now, let us check these methods in details.

SnowSQL command Line Interface to import Local CSV to Snowflake Table

You can use the COPY command to import the CSV file which is located on S3 location or in your local directory. If your CSV file is located in local system, then Snowsql command line interface option will be easy.

You can use the following steps to load a local CSV file to Snowflake table.

Create Table Structure on Snowflake
Copy Local File to Snowflake Stage path using PUT command
Import CSV file using the COPY command

Create a Table Structure on Snowflake

The first step is to create a table on Snowflake. We will load the sample CSV file into this table.

For example, consider below table structure.

create table sample_csv(
id int, 
name varchar(20), 
dept int
);

Copy Local File to Snowflake Stage path using PUT command

This is an important step. You must upload the local file to a Snowflake stage area using PUT command.

Login to SnowSQL and execute PUT command.

For example, consider following PUT command to upload local file to a Snowflake stage area.

> put file:///home/snowflake/Sample_file.csv @~/staged;

Import CSV file using Snowflake COPY command

Now, the file is in stage area. You can simply use the COPY command to load CSV file to a table.

For example, consider following COPY command to load CSV file.

COPY INTO sample_csv FROM '@~/staged/Sample_file.csv.gz' FILE_FORMAT = ( TYPE = CSV, COMPRESSION = GZIP);

Finally, check the table for loaded records.

select * from sample_csv;
+------------+------+-------+
|         ID | NAME |  DEPT |
|------------+------+-------|
| 1234567890 | aaa  | 32114 |
|  987654123 | bbb  | 45789 |
| 1122445521 | ccc  | 75896 |
| 7845951656 | ddd  | 45795 |
| 5645798965 | xyz  | 45564 |
| 4547858551 | vvd  | 84654 |
+------------+------+-------+

Load Local CSV file to Snowflake Using Web Interface

This method is very easy and you can use when file size is comparatively small.

You can follow Snowflake official tutorials to use standard web interface to load local files to Snowflake table.