Picking up File everyday from S3 with different Timestamp

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
Thirteen 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
-----------

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.