Welcome To TechBrothersIT: Snowflake SQL Tutorial 123 - PUT Renames Your File And GET Hands It Back Still Zipped

Snowflake SQL Tutorial 123 - PUT Renames Your File And GET Hands It Back Still Zipped

Snowflake SQL · Episode 123

In this post we are going to cover PUT and GET in Snowflake.

PUT uploads a file from your own machine into a stage, and GET downloads one back out again. Neither of them is really SQL. There is no PUT running on the warehouse. It is a command your client interprets, and the first thing it does is rename your file.

We will start with one upload and read the report it gives back column by column, because that report is the whole post.

Every query below was run on a real Snowflake account, and every result you see is the output it gave back.

Episode 123 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's set up a stage and a file to work with
Let's upload the file with PUT
Now let's run the identical PUT again
Let's turn the compression off
Can we read the compressed one without mentioning gzip?
And can COPY INTO load it?
Let's upload several files at once
And one into my own user stage
Now let's bring a file back down with GET
Let's make the folder first and try again
What happens when GET matches nothing?
Can we read both of those files back off the stage?
Three ways PUT refuses, and one for GET
One last thing: two of Snowflake's own numbers disagree
And let's tidy up

Let's set up a stage and a file to work with

We need a named stage to upload into and a file format to read with. Both of those came up in the last post.

Setup

-- A stage and a file format to work with.
USE DATABASE DEMO_DB;
USE SCHEMA PUBLIC;

CREATE OR REPLACE STAGE STAGE_T123
    COMMENT = 'episode 123 probe stage';

CREATE OR REPLACE FILE FORMAT CSV_T123
    TYPE = CSV
    FIELD_DELIMITER = ','
    SKIP_HEADER = 0;

And the stage starts empty.

Query

LIST @STAGE_T123;

Result0 rows · 4 columns

name	size	md5	last_modified

We also need a file. Mine is a small CSV called t123_sales.csv, sitting in a folder next to the script, with six lines of sales in it.

About the file paths

The statements below say C:/your/folder where my own folder name used to be, which is what the shared demo script says too. Point them at a folder of your own before you run them. Two of the captured error messages further down still show the real folder, because an error pane on this site is printed exactly as Snowflake sent it and never edited.

Let's upload the file with PUT

PUT takes the local path first and the stage second. That is the whole statement.

Query

PUT 'file://C:/your/folder/t123_sales.csv' @STAGE_T123/t123/;

Result1 row · 8 columns · scroll →

Eight columns. Scroll the grid sideways for the status.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_sales.csv	t123_sales.csv.gz	90	112	NONE	GZIP	UPLOADED

Read that one row column by column, because it is the post.

source is t123_sales.csv. That is what I gave it.
target is t123_sales.csv.gz. That is what arrived.
source_size and target_size are different numbers.
source_compression is NONE and target_compression is GZIP.
status is UPLOADED, and message is empty.

We uploaded a .csv. The stage is holding a .csv.gz. Nobody asked for that.

Let's see it in the stage.

Query

LIST @STAGE_T123/t123/;

Result1 row · 4 columns

name	size	md5	last_modified
stage_t123/t123/t123_sales.csv.gz	112	1003f95780df89cacd3c4e3af8201942	Wed, 30 Sep 2026 11:04:14 GMT

Query

SELECT "name" AS THE_NAME_IN_THE_STAGE, "size" AS SIZE_IN_BYTES
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 2 columns

THE_NAME_IN_THE_STAGE	SIZE_IN_BYTES
stage_t123/t123/t123_sales.csv.gz	112

The name in the stage is the .gz one, and that is the name every COPY INTO and every stage read afterwards has to live with.

The first thing PUT does

AUTO_COMPRESS is on unless you turn it off. If you have ever wondered why the file in your stage has an extension you never typed, that is why.

Now let's run the identical PUT again

Same statement, same file, same stage. Nothing changed.

Query

PUT 'file://C:/your/folder/t123_sales.csv' @STAGE_T123/t123/;

Result1 row · 8 columns · scroll →

Scroll sideways for the status column.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_sales.csv	t123_sales.csv.gz	90	0	NONE	GZIP	SKIPPED

status is SKIPPED, and target_size is 0, because nothing was transferred.

PUT does not overwrite by default. It saw a file of that name already in the stage and stopped.

The one that actually costs people

Think about how that plays out. Somebody finds a bad row, fixes the file and re-uploads it. PUT reports SKIPPED. The script does not read that column and carries on. COPY INTO then loads the stage, which is still holding the old file. The load succeeds, the row count looks right, and the data is wrong. That status column is the entire warning, and it is printed every single time.

To replace it you have to ask.

Query

PUT 'file://C:/your/folder/t123_sales.csv' @STAGE_T123/t123/ OVERWRITE = TRUE;

Result1 row · 8 columns · scroll →

Scroll sideways for the status column.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_sales.csv	t123_sales.csv.gz	90	112	NONE	GZIP	UPLOADED

UPLOADED this time, with the same target size as the first run. And the stage still holds exactly one file, because it replaced it rather than adding to it.

Query

LIST @STAGE_T123/t123/;

Result1 row · 4 columns

name	size	md5	last_modified
stage_t123/t123/t123_sales.csv.gz	112	c67ed2a5f8942fdf78a2d870a39272fc	Wed, 30 Sep 2026 11:04:16 GMT

Let's turn the compression off

AUTO_COMPRESS = FALSE is how you stop it. Let's upload the same file into a different prefix with it turned off.

Query

PUT 'file://C:/your/folder/t123_sales.csv' @STAGE_T123/plain/ AUTO_COMPRESS = FALSE;

Result1 row · 8 columns · scroll →

Scroll sideways for the compression columns.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_sales.csv	t123_sales.csv	90	96	NONE	NONE	UPLOADED

The name stays. source and target are the same, and both compression columns say NONE.

Have a look at the two size columns though. The file got slightly bigger on the way up, with no compression anywhere in the statement. Hold on to that, because it comes back at the end of the post.

Query

LIST @STAGE_T123/plain/;

Result1 row · 4 columns

name	size	md5	last_modified
stage_t123/plain/t123_sales.csv	96	16a517e9476b0e1cf416ecdf374c1bdb	Wed, 30 Sep 2026 11:04:17 GMT

Query

SELECT "name" AS THE_NAME_IN_THE_STAGE, "size" AS SIZE_IN_BYTES
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 2 columns

THE_NAME_IN_THE_STAGE	SIZE_IN_BYTES
stage_t123/plain/t123_sales.csv	96

Now both prefixes at once, so we can see the two side by side.

Query

LIST @STAGE_T123/;

Result2 rows · 4 columns

name	size	md5	last_modified
stage_t123/plain/t123_sales.csv	96	16a517e9476b0e1cf416ecdf374c1bdb	Wed, 30 Sep 2026 11:04:17 GMT
stage_t123/t123/t123_sales.csv.gz	112	c67ed2a5f8942fdf78a2d870a39272fc	Wed, 30 Sep 2026 11:04:16 GMT

Query

SELECT "name" AS THE_NAME_IN_THE_STAGE, "size" AS SIZE_IN_BYTES
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "name";

Result2 rows · 2 columns

THE_NAME_IN_THE_STAGE	SIZE_IN_BYTES
stage_t123/plain/t123_sales.csv	96
stage_t123/t123/t123_sales.csv.gz	112

One .gz and one plain. The same file on my machine, uploaded twice, living under two different names.

Can we read the compressed one without mentioning gzip?

This is the question that matters, because we did not ask for the .gz and now we have to work with it.

Query

SELECT $1 AS SALE_ID, $2 AS REGION, $3 AS AMOUNT
FROM @STAGE_T123/t123/ (FILE_FORMAT => 'CSV_T123')
ORDER BY $1;

Result6 rows · 3 columns

SALE_ID	REGION	AMOUNT
1	north	100.00
2	north	250.50
3	south	75.25
4	east	90.00
5	west	420.00
6	west	55.00

Six rows, and the word GZIP appears nowhere in that query.

And the uncompressed one, for comparison.

Query

SELECT $1 AS SALE_ID, $2 AS REGION, $3 AS AMOUNT
FROM @STAGE_T123/plain/ (FILE_FORMAT => 'CSV_T123')
ORDER BY $1;

Result6 rows · 3 columns

SALE_ID	REGION	AMOUNT
1	north	100.00
2	north	250.50
3	south	75.25
4	east	90.00
5	west	420.00
6	west	55.00

The same six rows. Let's count them side by side rather than trust our eyes.

Query

SELECT (SELECT COUNT(*) FROM @STAGE_T123/t123/  (FILE_FORMAT => 'CSV_T123')) AS ROWS_FROM_THE_GZ,
       (SELECT COUNT(*) FROM @STAGE_T123/plain/ (FILE_FORMAT => 'CSV_T123')) AS ROWS_FROM_THE_PLAIN_ONE;

Result1 row · 2 columns

ROWS_FROM_THE_GZ	ROWS_FROM_THE_PLAIN_ONE
6	6

So a stage read decompresses for you and does not make a fuss about it.

And can COPY INTO load it?

Let's make a table shaped like the file and load the compressed one into it.

Query

CREATE OR REPLACE TABLE SALES_T123 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

Result1 row · 1 column

status
Table SALES_T123 successfully created.

Query

COPY INTO SALES_T123
FROM @STAGE_T123/t123/
FILE_FORMAT = (FORMAT_NAME = 'CSV_T123');

Result1 row · 10 columns · scroll →

The load report. Ten columns, so scroll sideways.

file	status	rows_parsed	rows_loaded	error_limit	errors_seen	first_error	first_error_line	first_error_character	first_error_column_name
stage_t123/t123/t123_sales.csv.gz	LOADED	6	6	1	0	NULL	NULL	NULL	NULL

LOADED, with the rows parsed and the rows loaded agreeing and no errors seen. The four first_error columns are nulls because there were none.

And again, no mention of .gz anywhere in the statement. The file name in the report has it, because that is the file's name, but we never had to tell COPY INTO anything about compression.

Query

SELECT SALE_ID, REGION, AMOUNT FROM SALES_T123 ORDER BY SALE_ID;

Result6 rows · 3 columns

SALE_ID	REGION	AMOUNT
1	north	100.00
2	north	250.50
3	south	75.25
4	east	90.00
5	west	420.00
6	west	55.00

Six rows in a real table. So on the way in, the .gz is Snowflake's business and not yours.

Hold on to the words “on the way in”, because that is not true on the way out.

Let's upload several files at once

PUT takes a wildcard in the local path.

Query

PUT 'file://C:/your/folder/t123_*.csv' @STAGE_T123/many/;

Result2 rows · 8 columns · scroll →

One row per file. Scroll sideways for the status.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_extra.csv	t123_extra.csv.gz	30	64	NONE	GZIP	UPLOADED
t123_sales.csv	t123_sales.csv.gz	90	112	NONE	GZIP	UPLOADED

Two rows in the report, one per file, both UPLOADED, and both renamed to .gz on the way.

Query

LIST @STAGE_T123/many/;

Result2 rows · 4 columns

name	size	md5	last_modified
stage_t123/many/t123_extra.csv.gz	64	e35462bf4156d9985520c2ec1f04b144	Wed, 30 Sep 2026 11:04:21 GMT
stage_t123/many/t123_sales.csv.gz	112	56b4123da5bf897ca5aa6d122f9d1566	Wed, 30 Sep 2026 11:04:21 GMT

Query

SELECT "name" AS THE_NAME_IN_THE_STAGE, "size" AS SIZE_IN_BYTES
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
ORDER BY "name";

Result2 rows · 2 columns

THE_NAME_IN_THE_STAGE	SIZE_IN_BYTES
stage_t123/many/t123_extra.csv.gz	64
stage_t123/many/t123_sales.csv.gz	112

Now let's read the whole prefix and ask which file each row came from.

Query

SELECT METADATA$FILENAME        AS THE_FILE,
       METADATA$FILE_ROW_NUMBER AS THE_ROW_IN_THAT_FILE,
       $1                       AS SALE_ID
FROM @STAGE_T123/many/ (FILE_FORMAT => 'CSV_T123')
ORDER BY THE_FILE, THE_ROW_IN_THAT_FILE;

Result8 rows · 3 columns

THE_FILE	THE_ROW_IN_THAT_FILE	SALE_ID
many/t123_extra.csv.gz	1	7
many/t123_extra.csv.gz	2	8
many/t123_sales.csv.gz	1	1
many/t123_sales.csv.gz	2	2
many/t123_sales.csv.gz	3	3
many/t123_sales.csv.gz	4	4
many/t123_sales.csv.gz	5	5
many/t123_sales.csv.gz	6	6

Eight rows out of two files. The sales file gave six and the extra file gave two, and METADATA$FILENAME says which is which.

And one into my own user stage

PUT works on @~ as well, which is the user stage from the last post.

Query

PUT 'file://C:/your/folder/t123_sales.csv' @~/t123/;

Result1 row · 8 columns · scroll →

Scroll sideways for the status column.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_sales.csv	t123_sales.csv.gz	90	112	NONE	GZIP	UPLOADED

Query

LIST @~/t123/;

Result1 row · 4 columns

name	size	md5	last_modified
t123/t123_sales.csv.gz	112	14356a0e545011a6f3428aaee7c37dfa	Wed, 30 Sep 2026 11:04:22 GMT

Renamed to .gz there too, and with no stage name on the front of the path. That is the difference the last post found between a named stage and the other two.

Now let's bring a file back down with GET

GET is the other direction. The stage comes first and the local folder second.

Refused

GET @STAGE_T123/many/ 'file://C:/your/folder/t123_downloaded/';

Query failed — Snowflake returned an error

253005: The local path is not a directory: C:/Users/ashah/Videos/Snowflake_SQL_Course/_run_on_my_pc/t123_downloaded/

It failed. The local path is not a directory.

So GET does not create the folder for you. I had not made t123_downloaded yet, and that is all it took.

Naming a single file rather than the whole prefix does not help, because the problem is the other end.

Refused

GET @STAGE_T123/plain/t123_sales.csv 'file://C:/your/folder/t123_downloaded/';

Query failed — Snowflake returned an error

253005: The local path is not a directory: C:/Users/ashah/Videos/Snowflake_SQL_Course/_run_on_my_pc/t123_downloaded/

Both of the GETs in that run failed the same way, so both of them stay in the post. This is exactly what you hit the first time you run a GET from a script on a machine that has not been set up.

Let's make the folder first and try again

I made the folder and ran this half again, in a stage of its own, with two files in it: one uploaded compressed and one uploaded plain.

Query

PUT 'file://C:/your/folder/t123_sales.csv' @STAGE_T123B/both/;

Result1 row · 8 columns · scroll →

Scroll sideways for the compression columns.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_sales.csv	t123_sales.csv.gz	90	112	NONE	GZIP	UPLOADED

Query

PUT 'file://C:/your/folder/t123_extra.csv' @STAGE_T123B/both/ AUTO_COMPRESS = FALSE;

Result1 row · 8 columns · scroll →

Scroll sideways for the compression columns.

source	target	source_size	target_size	source_compression	target_compression	status	message
t123_extra.csv	t123_extra.csv	30	32	NONE	NONE	UPLOADED

Query

LIST @STAGE_T123B/both/;

Result2 rows · 4 columns

name	size	md5	last_modified
stage_t123b/both/t123_extra.csv	32	0e9eb873e524fcaebf59748f50b0e6a0	Wed, 30 Sep 2026 12:10:52 GMT
stage_t123b/both/t123_sales.csv.gz	112	e364b49fcfc42c4a8dcb17417f200aa0	Wed, 30 Sep 2026 12:10:51 GMT

Now the same GET as before, with the folder there this time.

Query

GET @STAGE_T123B/both/ 'file://C:/your/folder/t123b_downloaded/';

Result2 rows · 4 columns

file	size	status	message
t123_extra.csv	30	DOWNLOADED
t123_sales.csv.gz	102	DOWNLOADED

Two rows, both DOWNLOADED. And one file on its own, named exactly.

Query

GET @STAGE_T123B/both/t123_extra.csv 'file://C:/your/folder/t123b_downloaded/';

Result1 row · 4 columns

file	size	status	message
t123_extra.csv	30	DOWNLOADED

Now the part I actually care about. Look at the file column and ask what landed on the disk.

One of them is t123_extra.csv. The other is t123_sales.csv.gz. Still compressed.

GET does not decompress

PUT compressed the file on the way up and GET handed it back compressed. A PUT then GET round trip does not give you back the file you started with. It gives you a .gz to deal with yourself.

What happens when GET matches nothing?

I expected zero rows for this one. A stage path with no files under it.

Refused

GET @STAGE_T123B/no_such_prefix/ 'file://C:/your/folder/t123b_downloaded/';

Query failed — Snowflake returned an error

253006: While getting file(s) there was an error: the file does not exist.

It is a refusal, not an empty result. While getting file(s) there was an error: the file does not exist.

That is worth filing away, because it is the opposite of what a stage SELECT does with a path that matches nothing, and the next post is where that comparison gets made.

And the missing folder again, on purpose, so that both outcomes sit together.

Refused

GET @STAGE_T123B/both/ 'file://C:/your/folder/t123b_definitely_not_there/';

Query failed — Snowflake returned an error

253005: The local path is not a directory: C:/Users/ashah/Videos/Snowflake_SQL_Course/_run_on_my_pc/t123b_definitely_not_there/

Can we read both of those files back off the stage?

Yes, and naming the file rather than the prefix works fine.

Query

SELECT $1 AS SALE_ID, $2 AS REGION, $3 AS AMOUNT
FROM @STAGE_T123B/both/t123_sales.csv.gz (FILE_FORMAT => 'CSV_T123B')
ORDER BY $1;

Result6 rows · 3 columns

SALE_ID	REGION	AMOUNT
1	north	100.00
2	north	250.50
3	south	75.25
4	east	90.00
5	west	420.00
6	west	55.00

Query

SELECT $1 AS SALE_ID, $2 AS REGION, $3 AS AMOUNT
FROM @STAGE_T123B/both/t123_extra.csv (FILE_FORMAT => 'CSV_T123B')
ORDER BY $1;

Result2 rows · 3 columns

SALE_ID	REGION	AMOUNT
7	north	11.00
8	south	22.00

Six rows out of the compressed file and two out of the plain one. Counted together:

Query

SELECT COUNT(*)                          AS ROWS_ACROSS_BOTH_FILES,
       COUNT(DISTINCT METADATA$FILENAME) AS FILES_THEY_CAME_FROM
FROM @STAGE_T123B/both/ (FILE_FORMAT => 'CSV_T123B');

Result1 row · 2 columns

ROWS_ACROSS_BOTH_FILES	FILES_THEY_CAME_FROM
8	2

Eight rows across two files, and the read did not care which of them was compressed.

Three ways PUT refuses, and one for GET

These are all ones you will meet, so it is worth having seen them. First, a stage that is not there.

Refused

PUT 'file://C:/your/folder/t123_sales.csv' @NO_SUCH_STAGE_T123/x/;

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Stage 'DEMO_DB.PUBLIC.NO_SUCH_STAGE_T123' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on STAGE DEMO_DB.PUBLIC.NO_SUCH_STAGE_T123.

does not exist or not authorized, which is the same sentence Snowflake uses when the object does exist and your role cannot see it. It will not tell you which.

Second, a local file that is not there.

Refused

PUT 'file://C:/your/folder/t123_does_not_exist.csv' @STAGE_T123/x/;

Query failed — Snowflake returned an error

253006: File doesn't exist: ['C:/Users/ashah/Videos/Snowflake_SQL_Course/_run_on_my_pc/t123_does_not_exist.csv']

File doesn't exist, and it names the path it looked for. I expected a refusal here but not that exact wording, so I left my guess in the demo file unmarked and the capture settled it.

Third, no local path at all. Two stage names, one on each side.

Refused

PUT @STAGE_T123/t123/ @STAGE_T123/x/;

Query failed — Snowflake returned an error

001020 (42601): SQL compilation error:
invalid source URL scheme for PUT command: @STAGE_T123/t123/. Only local file URL is allowed.

Only local file URL is allowed. PUT goes from your machine to a stage and that is the only direction it has. Stage to stage is a different command altogether.

And GET from a stage that is not there gives you the same sentence as the first of those, word for word.

Refused

GET @NO_SUCH_STAGE_T123/x/ 'file://C:/your/folder/t123_downloaded/';

Query failed — Snowflake returned an error

002003 (02000): SQL compilation error:
Stage 'DEMO_DB.PUBLIC.NO_SUCH_STAGE_T123' does not exist or not authorized. Your primary role ACCOUNTADMIN or one of your secondary roles must have at least one privilege granted on STAGE DEMO_DB.PUBLIC.NO_SUCH_STAGE_T123.

Which makes sense. Neither command gets as far as the file part if it cannot find the stage.

One last thing: two of Snowflake's own numbers disagree

I noticed this while writing the demo and I am not going to pretend I can explain it here.

The stage reports the compressed file at 112 bytes. GET reports 102 for the same file. The plain one is 32 on the stage and 30 coming down.

Both files, a few bytes apart, consistently. Both numbers are Snowflake's own output and both are on this page, in the LIST grids and the GET grids above.

I did not know why when I recorded this, and I said so rather than inventing a reason. Episode 124 collects enough of these pairs to answer it with arithmetic, so if it is bothering you, that is where the answer is.

And let's tidy up

The user stage is shared and permanent, so what we wrote to it comes out first.

Cleanup

-- Put the account back the way we found it.
-- The user stage is shared and permanent, so that REMOVE is the one that
-- actually matters.
REMOVE @~/t123/;
REMOVE @STAGE_T123/;

DROP STAGE       IF EXISTS STAGE_T123;
DROP FILE FORMAT IF EXISTS CSV_T123;
DROP TABLE       IF EXISTS SALES_T123;

Query

REMOVE @~/t123/;

Result1 row · 2 columns

name	result
t123/t123_sales.csv.gz	removed

Query

LIST @~/t123/;

Result0 rows · 4 columns

name	size	md5	last_modified

Then the named stage, which is holding everything we uploaded into it.

Query

REMOVE @STAGE_T123/;

Result4 rows · 2 columns

name	result
stage_t123/many/t123_sales.csv.gz	removed
stage_t123/many/t123_extra.csv.gz	removed
stage_t123/plain/t123_sales.csv	removed
stage_t123/t123/t123_sales.csv.gz	removed

Four files removed, which is the two prefixes from the first half plus the two the wildcard put there.

Query

LIST @STAGE_T123/;

Result0 rows · 4 columns

name	size	md5	last_modified

And the last query counts what is left behind.

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES
        WHERE RIGHT(TABLE_NAME, 5) = '_T123') AS PROBE_TABLES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STAGES
        WHERE RIGHT(STAGE_NAME, 5) = '_T123') AS PROBE_STAGES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILE_FORMATS
        WHERE RIGHT(FILE_FORMAT_NAME, 5) = '_T123') AS PROBE_FORMATS_LEFT,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 6 columns · scroll →

THE_DATABASE	THE_SCHEMA	PROBE_TABLES_LEFT	PROBE_STAGES_LEFT	PROBE_FORMATS_LEFT	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	0	0	10

Query

LIST @~/t123/;

Result0 rows · 4 columns

name	size	md5	last_modified

Nothing of mine in the user stage, no probe objects in the schema, and the demo database still reads. The second stage was taken down the same way.

Query

REMOVE @STAGE_T123B/;

Result2 rows · 2 columns

name	result
stage_t123b/both/t123_extra.csv	removed
stage_t123b/both/t123_sales.csv.gz	removed

Query

LIST @STAGE_T123B/;

Result0 rows · 4 columns

name	size	md5	last_modified

Query

SELECT CURRENT_DATABASE()                 AS THE_DATABASE,
       CURRENT_SCHEMA()                   AS THE_SCHEMA,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STAGES
        WHERE RIGHT(STAGE_NAME, 6) = '_T123B') AS PROBE_STAGES_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.FILE_FORMATS
        WHERE RIGHT(FILE_FORMAT_NAME, 6) = '_T123B') AS PROBE_FORMATS_LEFT,
       (SELECT COUNT(*) FROM INFORMATION_SCHEMA.STAGES
        WHERE RIGHT(STAGE_NAME, 5) = '_T123') AS ANYTHING_LEFT_FROM_EP123,
       (SELECT COUNT(*) FROM CUSTOMERS)   AS DEMO_DB_IS_READABLE;

Result1 row · 6 columns · scroll →

THE_DATABASE	THE_SCHEMA	PROBE_STAGES_LEFT	PROBE_FORMATS_LEFT	ANYTHING_LEFT_FROM_EP123	DEMO_DB_IS_READABLE
DEMO_DB	PUBLIC	0	0	0	10

Zero stages and zero file formats from the second run, nothing left from the first one, and the demo database still reads. Back where we started.

Key points

PUT is not SQL. It is a command your client interprets, and it takes a path on your own machine.
PUT renames your file. AUTO_COMPRESS is on unless you turn it off, so a .csv arrives in the stage as a .csv.gz.
With AUTO_COMPRESS = FALSE the name stays and the file gets slightly bigger instead.
A repeat PUT reports SKIPPED with a target size of 0. It does not overwrite by default.
That is the column to read in a script. Ignore it and a fixed file never reaches the stage, while the load that follows reports success.
OVERWRITE = TRUE replaces it, and the stage still holds one file.
A stage read decompresses a .gz with no special instruction, and COPY INTO loads one without the word GZIP anywhere in the statement.
PUT takes a wildcard and reports one row per file.
GET does not create the local folder: The local path is not a directory.
GET does not decompress either. The file on your disk after a round trip is still .csv.gz.
A GET that matches nothing is a refusal, not an empty result.
PUT refuses a missing stage, a missing local file, and a source that is not a local file url.
LIST and GET report different byte counts for the same file. Both are Snowflake's own numbers.

Next in this series

Episode 124 — querying a stage directly. @STAGE/path as a FROM target, what $1 really is, the metadata columns that tell you which file a row came from, and a PATTERN that matched nothing and said nothing about it.

Every query and every result grid on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The local folder in the code blocks is written as C:/your/folder, the same substitution the shared demo script makes; the captured error messages are printed exactly as Snowflake returned them and are not edited, so two of them show the real folder. The second half of the post is a second run of the demo in a stage of its own, because the first run's two GET statements both failed and that failure is part of the lesson. Cells marked empty are empty strings and cells reading NULL are real nulls. Nothing on this page is timed; the byte counts are Snowflake's own report columns, quoted as they came back, and no performance claims are made anywhere in this series. PUT and GET behaviour follows the Snowflake PUT documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 123 - PUT Renames Your File And GET Hands It Back Still Zipped

Let's set up a stage and a file to work with

Let's upload the file with PUT

Now let's run the identical PUT again

Let's turn the compression off

Can we read the compressed one without mentioning gzip?

And can COPY INTO load it?

Let's upload several files at once

And one into my own user stage

Now let's bring a file back down with GET

Let's make the folder first and try again

What happens when GET matches nothing?

Can we read both of those files back off the stage?

Three ways PUT refuses, and one for GET

One last thing: two of Snowflake's own numbers disagree

And let's tidy up

Key points

No comments:

Post a Comment