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
One Star

Re: Picking up File everyday from S3 with different Timestamp

Can anyone help regarding scenario
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 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads