Design Job for Redshift

[Anonymous]

I have complex design, well complex for a beginner.

1. I need to run a query that will return a varying number of rows depending on when its ran. (easy part...)
2. The results will be a table (call it db_info) with 2 columns. 1: schema_name and 2: table_name
3. I need to run another query using the schema_name and table_name from db_info as variables and write the results to a table or file. Preferably a table. ex.: (select count(*), location from @schema_name.@table_name group by location
4. I then need the next row in the db_info tbl passing the schema_name and table_name as the next variables until last row appending the table or file.

The goal is to gather # of records by location for each table in each schema, without have to specify each schema and its tables, as they may change daily.

Any guidance is greatly appreciated. 

[Anonymous · Accepted solution]

Hi,

     Could you please try the below steps? Join your DB_Info table to a tFlowtoIterate component (as shown below).

      Connect the output of it to a new tDBInput component but instead of hard coding values in schema and table name, give Control+ Space to get the list. Select the schema name and table name under tFlowtoIterate component (as shown below)

Hope the reply has helped to resolve your issue. Please mark the the topic as closed if the issue is resolved. Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi Nikhil

You are a superstar!  Thanks.  The changes you have advised seemed have now worked.  We are going into testing now the job works and should I have any further questions on it I'll come back.  For now we're all good.

Thanks

Dave

[Anonymous]

Hi Dave,

       Thanks for sharing the good news.

        Enjoy programming in Talend 🙂

Warm Regards,

Nikhil Thampi

[Anonymous]

Hi Nikhil,

You are indeed a superstar! The process you put forward works like a charm. 

Thanks a million!!

Best Regards

roach758