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

Question

(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?

Shashi Kiran Shashi Kiran · Accepted Answer · 2019-10-29T07:00:56

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

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

2 Answers