Home > Back-end >  Get the list of files from a Snowflake stage?
Get the list of files from a Snowflake stage?

Time:07-01

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

  • Related