The goal is to copy only the last few files from a stage to a table (based on date). I'm trying to find a straight forward way in snowflake to do that.

I know there's LIST command that give the list of files based on the pattern. But that is not useable (as in you can't use it in a select statement). There JS solutions for this scenario, which is a bit hacky, but is there anyway I can copy files based on filename/pattern and datemodified from stage to a table?

CodePudding user response：

You can use the pattern parameter to achieve this requirement where in you can select the specific set of files based on the criteria. Details and samples are here: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#syntax

Sample using pattern matching: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#loading-using-pattern-matching

CodePudding user response：

You could create a Snowpipe, with auto-ingest set to false, and use:

alter pipe <pipe_name> refresh PREFIX = ‘<path>’ MODIFIED_AFTER = <start_time>;

With MODIFIED_AFTER being a time stamp (in ISO-8601 format) of the oldest file you want to load based on the LAST_MODIFIED date (i.e. date when a file was staged).

See more info & example here https://docs.snowflake.com/en/sql-reference/sql/alter-pipe.html#alter-pipe

Note - The REFRESH functionality is not intended for regular use.

I think the only other option if you don't want to use Snowpipe, would be to use the 'FILES =' parameter to only ingest the specific files you want, you then have control to include the files manually only if you wish based on their last_modified date - but if you have a high volume of files, this is not viable (unless you do it programmatically, but then you're moving away from the straightforward solution you are after). https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html#optional-parameters