Need to be able to use a value from a db query in the key prefix of tS3List component

[Anonymous]

I have a Talend Job that does the following:

1) checks if a file exists in S3Bucket/key_prefix, I have the S3 bucket hard coded in the tS3List component, but I am using TalendDate.formatDate("yyyyMMdd",TalendDate.getCurrentDate()) as the key prefix value. This shows me YYYYMMDD of today.  The file that I am looking for has 20190218 in the file name.

2) if the file for today does exist in the bucket, i do an insert into a Redshift table (insert into log_table (job_id, file_name, insert_date, update_date, file_status) select 101, "Current_Key from the tLogRow", getdate(), getdate(), 'created'

3) if the file for today does not exist in the bucket, i do an insert into a Redshift table using a TDBRow component. (insert into log_table (job_id, file_name, insert_date, update_date, status) select 101, "I have to hard code this because the Current Key from LogRow is NULL, getdate(), getdate(), 'missing' 

Is there a way to query the redshift database and gather a value and use it in the key prefix of the tS3List in a subsequent job.  In other words, i will need to create a new job to go and find the MISSING file_name(s) and do another check for those so I can UPDATE the log_table with information as to when the files did actually show up.

Make sense?

Thanks in advance.

[vapukov]

Hi,

as for any database you can use tDBInput component to request any data

because:

• Redshift based on a PostgreSQL 8.2 (no generate_series) you could check ideas - https://stackoverflow.com/questions/17282276/using-sql-function-generate-series-in-redshift
• your filenames based on date you can use TO_CHAR for format string

use 2 above - prepare query (JOIN) for detect missed values, and then use tFlowToIterate for re-check all missed files

[Anonymous]

Thanks, I was able to get the values to show in the tLogRow_1 but how do I
get those values to iterate through an S3 Key Prefix?

[vapukov]

what is the problem?

you can use tFlowToIterate to save the value into a variable, then just use

(String)globalMap.get("variable_name") as prefix

[Anonymous]

Forgive me for my ignorance i am new to Talend and I am finding it to be
less than intuitive.

[vapukov]

it not a problem at all - just need to explain what is your problem, because it helps to understand, how is help to you to resolve them