python-3.xamazon-s3snowflake-cloud-data-platformsnowflake-tasksnowflake-pipe

Merge/Load same files in multiple folders in S3 into Snowflake


Need help on how to achieve in loading the same file available in multiple folders into the snowflake table. Is it possible to achieve using Snowflake Copy Command?

Say in a bucket I have .. All the files of a.csv which are only under the current date example here 2020/06/09 will go into a_table

2020 -- is the year followed by month 06 followed by date which is 09

bucket_a
   ts
     ts_folder
         2020
           06
             08
                a.csv
                b.csv
                c.csv
                d.csv
             09
                a.csv
                b.csv
                c.csv
                d.csv

     ts_folder2
         2020
           06
             08
                a.csv
                b.csv
                c.csv
                d.csv
             09
                a.csv
                b.csv
                c.csv
                d.csv

Solution

  • The Snowflake COPY INTO statement is will recursively crawl through the subdirectories in the stage, so all you need to do is add a pattern parameter to your COPY INTO statement, something like this:

    pattern = 'a\_date\.csv'