How to unload data from Snowflake in parquet format with lowercase column name?

Question

I have to unload the Snowflake data into external s3 location in the parquet format in the lowercase column name. By default it's coming as uppercase, is there a way of doing it?

Update: Following is the command to create view:

create view test_view as select 'col1','col2' from target_table;

Following is the copy command:

CREATE OR REPLACE FILE FORMAT dev."table_name" TYPE = 'parquet'
NULL_IF = ('NULL', 'null') COMPRESSION=SNAPPY;

COPY INTO @STAGING.DEV_EXTERNAL_STAGE/20200626/data/20200626/
FROM dev.table_name file_format = dev."table_name"
OVERWRITE=TRUE HEADER = TRUE;

Mike Walton · Answer 1 · 2020-06-30T22:01:13.780

0

Not sure if this works the way you'd want it to, but if you create a view over the table you are exporting that forces lowercase column names using quotes, then you could export from the view, instead of the table and that should hold into the parquet file. However, not sure if you'll get the quotes in there, as well, or not.

Your create view statement is just hard-coding strings, not creating columns with lowercase names. It should be like this:

create view test_view as select col1 as "col1", col2 as "col2" from target_table;

I also don't see any benefits from creating a file_format with explicit lowercase names. I used this:

CREATE OR REPLACE FILE FORMAT parquet_fmt TYPE = 'parquet'
NULL_IF = ('NULL', 'null') COMPRESSION=SNAPPY;

And then referenced the different file format in the COPY INTO statement and it created a file without issue. I am unsure as to how the column names are represented in that file, but give it a try.

edited Jun 30 '20 at 22:01

answered Jun 30 '20 at 18:47

Mike Walton

6,595
2
11
22

no that won't work, getting "SQL compilation error: Missing column specification" – Vishrant Jun 30 '20 at 19:04
Would you mind sharing your code for the view and the COPY INTO statement? Maybe you could edit your original question with those details? – Mike Walton Jun 30 '20 at 19:49
2

Adding additional details, including the commands run and their error messages, and possibly an example would allow folks like Mike the ability to better assist. – Rich Murnane Jun 30 '20 at 19:55
@Vishrant I updated my answer with more details. I did not receive any errors in creating the file. – Mike Walton Jun 30 '20 at 22:02

score 0 · Answer 2 · answered Oct 07 '20 at 22:14

In my use cases, simply using a select with quoted column names works for me. So perhaps replacing FROM dev.table_name with FROM (SELECT col1 as "col1", col2 AS col2 FROM coldev.table_name) as shown below may work?

CREATE OR REPLACE FILE FORMAT dev."table_name" TYPE = 'parquet'
NULL_IF = ('NULL', 'null') COMPRESSION=SNAPPY;

COPY INTO @STAGING.DEV_EXTERNAL_STAGE/20200626/data/20200626/

FROM (
SELECT col1 as "col1", col2 AS col2 FROM coldev.table_name
)

file_format = dev."table_name"
OVERWRITE=TRUE HEADER = TRUE;

How to unload data from Snowflake in parquet format with lowercase column name?

2 Answers2