S3 Load Component: Looking for the exact Pattern syntax

Matillion ETL
1

Environment:

Snowflake on AWS

Matillion 1.51.5

 

Hello,

I have csv files in a S3 bucket.

Filenames are in the format name_09_16_2021_uuid

 

I am able to load all the files in the folder using the pattern

.*

 

I am trying to load all files starting with name

I tried the pattern name.*  but it doesn't work 

 

The final goal is to load the previous day files.

So I am looking for a pattern something like *${date}* 

 

Can anyone tell me what is the exact pattern syntax when using wildcards.

Thanks

2

Hi @MichelCaissie​,

Agree those patterns can be surprisingly tricky to get working!

The first thing I'd say is get it working without using variables first. At least it will remove one of the possible complications (more information in this article).

Then secondly, because it's Snowflake, set the "Force Load" option true. Snowflake has some valuable smarts which make it silently ignore files it has already loaded. That's great in production but it can make development more tricky.. you run the exact same load twice and it only loads the file once!

Finally, it does all boil down to a COPY command, so keep the Snowflake reference to hand.

What I normally do - perhaps from force of habit - is use the folder both in the S3 Object Prefix and in the Pattern. So to load all the files named 19-something in SomeFolder under TheBucket, I would set

S3 Object Prefix: s3://TheBucket/SomeFolder/

Pattern: SomeFolder/19.*

Hope that's helpful.

Ian

3

Thank you @ian.funnel

Adding the folder in the pattern did the trick.

S3 Object Prefix: s3://TheBucket/SomeFolder/

Pattern: SomeFolder/name.*

Actually, it works if the S3 Object Prefix only points to the bucket.

But it seams the Pattern must absolutely have the full path.

So either

S3 Object Prefix: s3://TheBucket/SomeFolder/

Pattern: SomeFolder/name.*

or

S3 Object Prefix: s3://TheBucket/

Pattern: SomeFolder/name.*

works.

And it works with the variable

Pattern: SomeFolder/.*${date}.*

And thanks for confirming what I suspected regarding the Force Load option.

I figured it out, but not after wasting time trying to understand why the loading was working on a new table but not after deleting the entries in the table.

Thanks again!

Michel