0

Need help on copy into pattern in snowpipe. facing challenge while using pattern for below file present in s3 bucket folder S3bucket/Folder/files

1)changes_tags.csv.12345
2)changes_tags.csv.987987
3)tags.csv.67587
4)tags.csv.9987

to load tags.csv file, in snowpipe copy into command using pattern=.*tags.csv.*, but it is loading changes_tags.csv file also.

created external stage at folderlevel,
CREATE OR REPLACE STAGE s3_stage
STORAGE_INTEGRATION = s3_int
ENCRYPTION          = ( TYPE = 'SNOWFLAKE_SSE' )
URL                 = 's3://bucketname/folder'
FILE_FORMAT         = csv_pipe_format;

please find below snowpipe command

create or replace pipe TAGS  auto_ingest=true as
COPY INTO CHANGE_TAGS(tag_id
tag_name ,
tag_value 
filename ,
file_rownum 
)
FROM (select $1,$2,$3,METADATA$FILENAME,METADATA$FILE_ROW_NUMBER from @s3_stage) 
PATTERN='.*tags.csv.*'
ON_ERROR = 'CONTINUE'
FILE_FORMAT =   (TYPE = 'CSV',FIELD_DELIMITER = ',',SKIP_BLANK_LINES = TRUE,FIELD_OPTIONALLY_ENCLOSED_BY = '"');
phenix
  • 1
  • 1
  • If your file name starts with tags,csv, then you can use ```pattern=tags.csv.*```. Not sure why you are prefixing .* when you dont want to consider anything before tags. – Pankaj Jun 29 '22 at 15:06
  • @pankaj, i have tried, but data not loaded into table. – phenix Jun 29 '22 at 15:22
  • How about ```pattern=.*/tags.csv.*``` ? The idea is to create an expression to just have file-name you need. You will also need to include '/' as part of expression and .* to include any path portion before your file-name. – Pankaj Jun 29 '22 at 16:13

1 Answers1

0

Refer below test-case, to show file exclusion/inclusion, using pattern -

  • Outputs are truncated for bravity

To upload file named - MOCK_DATA.csv -

copy into mock_data_tab from @sf_s3_stage pattern='.*/MOCK_DATA.*';

file status rows_parsed rows_loaded
s3://snowflakepr/csv/data/MOCK_DATA.csv LOADED 1001 1001

Changed file name and added a prefix, to test exclusion from copying -

list @sf_s3_stage;

name
s3://snowflakepr/csv/data/Prefix_MOCK_DATA.csv

Trying to copy -

copy into mock_data_tab from @sf_s3_stage pattern='.*/MOCK_DATA.*';

status
Copy executed with 0 files processed.

As can be seen from above no file gets copied.

If we use pattern .* as prefix it will pick all (not desired) -

copy into mock_data_tab from @sf_s3_stage pattern='.*MOCK_DATA.*';

file status rows_parsed rows_loaded
s3://snowflakepr/csv/data/Prefix_MOCK_DATA.csv LOADED 1001 1001
Pankaj
  • 2,692
  • 2
  • 6
  • 18