Picking up File everyday from S3 with different Timestamp

Highlighted
One Star

Picking up File everyday from S3 with different Timestamp

Hi All,
I have designed a Talend Job which will pick up a file from S3 location. I have written a copy command in tRedshiftRow component.Now the Copy Command looks like below:-
"
truncate Table A;
commit;
copy  Table A 
(
Column1
Column2
Column3
)
from 's3://*/*/*/*_2017-02-17_14-22-48.txt'
CREDENTIALS 'aws_access_key_id=AKIAI3JXC6EM5NEGKQ3A;aws_secret_access_key=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'  
DATEFORMAT 'YYYYMMDD' TIMEFORMAT 'YYYYMMDD HH:MISmiley FrustratedS' delimiter '|' IGNOREHEADER 1 IGNOREBLANKLINES ACCEPTINVCHARS ;

commit;
UPDATE Table A  SET crtd_by = '"+context.conn_X_RS_ECG_UserID+"', job_nm = '"+jobName+"';
commit;
UPDATE Table A SET load_typ = 'Refresh', load_ts = SYSDATE ;
commit;
"
The Filename everyday will be like x_2017_MM_DD HH-MM-SS.txt where x is like File Name.Can anyone please help me out
Highlighted
One Star

Re: Picking up File everyday from S3 with different Timestamp

Can anyone help regarding scenario
Highlighted
Forteen Stars

Re: Picking up File everyday from S3 with different Timestamp

hope Your aws keys - just example :-)

You can use tS3Connection -> tS3List for You bucket
then iterate over list
because (at least I was unsuccessful) Component understand only prefix (mean files begins from - including folder), it not accept wildcard such as *_2017_*
You can then filter this records by time stamp pattern
for example use runIf trigger with condition:
StringHandling.INDEX(((String)globalMap.get("tS3List_1_CURRENT_KEY")),TalendDate.formatDate("yyyy_MM_dd_HH",TalendDate.getCurrentDate()))!=-1


this trigger will execute all other steps for current iterated file if it name related to current Hour
of course You must adjust it:

for time difference
for schedule (daily, hourly, folders names, etc)

but it work
other way - use command line client for amazon, and tSystem (tSSH) component
for example we use this way for select from S3 most recent file
-----------

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog