Design Job for Redshift

Four Stars

Design Job for Redshift

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 locationSmiley Wink
  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. 


Accepted Solutions
Highlighted
Employee

Re: Design Job for Redshift

Hi,

 

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

 

image.png

      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)

 

 

image.png

 

 

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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)

All Replies
Highlighted
Employee

Re: Design Job for Redshift

Hi,

 

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

 

image.png

      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)

 

 

image.png

 

 

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


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Six Stars

Re: Design Job for Redshift

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

Employee

Re: Design Job for Redshift

Hi Dave,

 

       Thanks for sharing the good news.

 

        Enjoy programming in Talend :-)

 

Warm Regards,

 

Nikhil Thampi


Warm Regards,
Nikhil Thampi
Please appreciate our members by giving Kudos for spending their time for your query. If your query is answered, please mark the topic as resolved :-)
Four Stars

Re: Design Job for Redshift

Hi Nikhil,

 

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

 

Thanks a million!!

 

Best Regards

roach758