Step 3. Stage the Data Files

A Snowflake stage is a location in cloud storage that you use to load and unload data from a table. A stage enables loading data into a table and unloading data from a table. Snowflake supports:

  • Internal stages — Used to store data files internally within Snowflake. Each user and table in Snowflake gets an internal stage by default for staging data files.

  • External stages — Used to store data files externally in Amazon S3, Google Cloud Storage, or Microsoft Azure. If your data is already stored in these cloud storage services, you can use an external stage to load data in Snowflake tables.

In this tutorial, we upload the sample data files (downloaded in Prerequisites) to the internal stage for the emp_basic table that you created earlier. You use the PUT command to upload the sample data files to that stage.

Staging the Sample Data Files

Execute PUT in SnowSQL to upload local data files to the table stage provided for the emp_basic table you created.

put file://<file-path>[/\]employees0*.csv @sf_tuts.public.%emp_basic;
Copy

For example:

  • Linux or macOS

    PUT file:///tmp/employees0*.csv @sf_tuts.public.%emp_basic;
    Copy

  • Windows

    PUT file://C:\temp\employees0*.csv @sf_tuts.public.%emp_basic;
    Copy

Let’s take a closer look at the command:

  • file://<file-path>[/\]employees0*.csv specifies the full directory path and names of the files on your local machine to stage. Note that file system wildcards are allowed.

  • @<namespace>.%<table_name> indicates to use the stage for the specified table, in this case the emp_basic table.

The command returns the following result, showing the staged files:

+-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------+
| source          | target             | source_size | target_size | source_compression | target_compression | status   | message |
|-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------|
| employees01.csv | employees01.csv.gz |         360 |         287 | NONE               | GZIP               | UPLOADED |         |
| employees02.csv | employees02.csv.gz |         355 |         274 | NONE               | GZIP               | UPLOADED |         |
| employees03.csv | employees03.csv.gz |         397 |         295 | NONE               | GZIP               | UPLOADED |         |
| employees04.csv | employees04.csv.gz |         366 |         288 | NONE               | GZIP               | UPLOADED |         |
| employees05.csv | employees05.csv.gz |         394 |         299 | NONE               | GZIP               | UPLOADED |         |
+-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------+
Copy

Note that the PUT command compresses files by default using gzip, as indicated in the TARGET_COMPRESSION column.

Listing the Staged Files (Optional)

You can list the staged files using the LIST command.

LIST @sf_tuts.public.%emp_basic;
Copy

The following is an example result:

+--------------------+------+----------------------------------+------------------------------+
| name               | size | md5                              | last_modified                |
|--------------------+------+----------------------------------+------------------------------|
| employees01.csv.gz |  288 | a851f2cc56138b0cd16cb603a97e74b1 | Tue, 9 Jan 2018 15:31:44 GMT |
| employees02.csv.gz |  288 | 125f5645ea500b0fde0cdd5f54029db9 | Tue, 9 Jan 2018 15:31:44 GMT |
| employees03.csv.gz |  304 | eafee33d3e62f079a054260503ddb921 | Tue, 9 Jan 2018 15:31:45 GMT |
| employees04.csv.gz |  304 | 9984ab077684fbcec93ae37479fa2f4d | Tue, 9 Jan 2018 15:31:44 GMT |
| employees05.csv.gz |  304 | 8ad4dc63a095332e158786cb6e8532d0 | Tue, 9 Jan 2018 15:31:44 GMT |
+--------------------+------+----------------------------------+------------------------------+
Copy

Next: Step 4. Copy Data into the Target Table

Language: English