CODEWITHSUNDEEP
snowflake-cloud-data-platformsnowflake-schema
Vishrant
Vishrant

Reputation: 16688

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

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;

Upvotes: 0

Views: 2708

Answers (2)

jmuhlenkamp
jmuhlenkamp

Reputation: 2160

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;

Upvotes: 0

Mike Walton
Mike Walton

Reputation: 7369

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.

Upvotes: 0

Related Questions