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

Question

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

score 1 · Answer 1 · answered Jun 11 '20 at 05:13

1

Achieved using below code

SET curr_dt=(
  SELECT 
    '.*/' || 
    TO_VARCHAR(CURRENT_DATE(), 'YYYY/MM/DD') || 
    '/a\\.csv'
)

-- Referenced as a SQL variable in the PATTERN option
COPY INTO a_table
FROM @stage/ts/
(PATTERN=>$curr_dt);

answered Jun 11 '20 at 05:13

Kar

790
13
36

score 0 · Accepted Answer · answered Jun 10 '20 at 20:03

0

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'

answered Jun 10 '20 at 20:03

Greg Pavlik

10,089
2
12
29

thanks for the update, I have updated the original question. I want all same files falling under same date only those files need to be loaded. Below command gets data from all the dates files which I don't want `select $1,$2 from @stage/ts/ (file_format=>CSV_SKIP_HEADER,pattern=>'.*/file.*[.]csv'); ` – Kar Jun 10 '20 at 22:44

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

2 Answers2