select ID and sql query (count query) from table and write ID and result of count query to target table

Question

My source table has query id and a sql query. Using Talend I need to run this CUSTOM_SQL query against the database and load a target table with the counts.

source table:

 QUERY_ID|CUSTOM_SQL                                                      |
--------+----------------------------------------------------------------+
       1|select count(1) as ROW_COUNT from SYSTEM_PRIVILEGE_MAP          |
       2|select count(1) as ROW_COUNT from OGIS_SPATIAL_REFERENCE_SYSTEMS|
       3|select count(1) as ROW_COUNT from SDO_COORD_SYS                 |
       4|select count(1) as ROW_COUNT from SDO_COORD_REF_SYS             |
       5|select count(1) as ROW_COUNT from SDO_PREFERRED_OPS_SYSTEM      |
       6|select count(1) as ROW_COUNT from SDO_TIN_PC_SYSDATA_TABLE      |

expected output in target table:

QUERY_ID|QUERY_RESULT |
--------+-------------+
       1|290          |
       2|322          |
       3|784          |
       4|8484         |
       5|743          |

I created a job that looks as follows but it is not complete:

tdbInput -> tFlowIterate -> tDBInput -> tMap -> tDBOutput

With the above design I'm able to run the CUSTOM_SQL, capture the result from tDBInput, but unable capture and propagate the QUERY_ID.

How do I propagate both query_id and the query result in one row to the target table. What components should I use?

Please note that each CUSTOM_SQLs always return one row and one column. So this is a very specific usecase.

I simplified my scenario by using some dummy data.

I will appreciate any help on this. Thank you!

Answer 1

With your first tDBInput component, make sure you extract both QUERY_ID and CUSTOM_SQL (select QUERY_iD,CUSTOM_SQL from source_table) : you should get in global variables of your tFlowToIterate two variables (something like ((String)globalMap.get("row1.QUERY_ID")) , with row1 being the name of the flow between tDBInput and tFlowToIterate). You can also check in "outline" view if those variables appear under tFlowToIterate_1 component.

Then in tMap, you can just access this query_id global variable (with the above syntax) and push it to your target table.