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;
For example:
Linux or macOS
PUT file:///tmp/employees0*.csv @sf_tuts.public.%emp_basic;
Windows
PUT file://C:\temp\employees0*.csv @sf_tuts.public.%emp_basic;
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 theemp_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 | | +-----------------+--------------------+-------------+-------------+--------------------+--------------------+----------+---------+
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;
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 | +--------------------+------+----------------------------------+------------------------------+