Search code examples
amazon-s3snowflake-cloud-data-platformsnowpipe

Can snowpipe pick up files from within sub-folders?

I'm looking to try to pick up all parquet files from an s3 bucket that have been placed into partitioned sub-folders by date.

In the past I've used snowpipe with a sort of 1-1 relationship, one sub-folder to one table; but I would be interested to know if it is possible to crawl over partitioned data to a single table.

Many thanks!

Solution

  • Short answer: Yes!

    With COPY INTO you can load a particular file, a whole folder or all sub-folders within a certain folder. All you need to do is to adjust your path accordingly. Just mention the path in the FROM clause and all subfolders will be copied.

    copy into mytable
from @my_stage/your_main_folder/;

    Docs: https://docs.snowflake.com/en/sql-reference/sql/copy-into-table.html

    Edit: There are variations possible. Also the stage itself can point to a certain main folder already and you do not need to extend the COPY INTO path.