topic Re: Picking up File everyday from S3 with different Timestamp in Talend Studio

Picking up File everyday from S3 with different Timestamp

_AnonymousUser — Sat, 16 Nov 2024 10:02:40 GMT

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:MI S' 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

Re: Picking up File everyday from S3 with different Timestamp

_AnonymousUser — Thu, 02 Mar 2017 05:57:55 GMT

Can anyone help regarding scenario

Re: Picking up File everyday from S3 with different Timestamp

vapukov — Thu, 02 Mar 2017 06:35:40 GMT

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