CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Gavin Wilson
Gavin Wilson

Reputation: 532

Copying data from AWS S3 Parquet file - limiting to just a few rows

(Submitting on behalf of a Snowflake user - searching for any advice available...)

While copying data from AWS S3 Parquet file, I'm looking to select just a few rows based on a where condition to copy to Snowflake. I've reviewed the following link (understanding that the copy transformation should allow me to add a filter clause while selecting data from stage file):

https://docs.snowflake.net/manuals/user-guide/script-data-load-transform-parquet.html

However, from the example I see, it helps select a few columns not a few rows. I am looking to filter the rows based on a condition to load.

It has previously been recommended that the filter can be applied in the same way by applying the “Where” clause, but it doesn't seem to be a suitable solution for the user.

Any recommendations?

Upvotes: 0

Views: 663

Answers (2)

Shashi Kiran
Shashi Kiran

Reputation: 11

Copy statement only supports simple select statements without where clause. Only option to filter the rows during load is to use limit and offset values. 

create or replace file format sf_tut_parquet_format type = 'parquet';
create or replace stage sf_tut_stage1 file_format = sf_tut_parquet_format;
create or replace table prqnull1(data variant);
copy into prqnull1 (data) from (select * from @sf_tut_stage1 limit 3 offset 2);

The table contains parquet data constructed using below data.

cutoff_date,name
20081123, Ann
20080913, Beck
20100315, Carren
20110817, Dave
20161219, Emily

Upvotes: 1

Mike Walton
Mike Walton

Reputation: 7369

As far as I know, that isn't possible. You can limit which columns get loaded, but not which records get loaded. I don't see anything in the documentation link provided that suggests that you can use a WHERE clause. And the COPY INTO documentation also does not offer that ability.

https://docs.snowflake.net/manuals/sql-reference/sql/copy-into-table.html#type-parquet

Upvotes: 0

Related Questions