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

[DataEng112]

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!

[Anonymous]

QUERY_ID is read from the first tdbInput, then, add a new column in the output table of tMap, set its expression as:

(Integer)globalMap.get("row1.QUERY_ID")

//this expression is used to get the current QUERY_ID, row1 is the row label before tFlowToIterate 

Regards

Shong

[DataEng112]

Thanks Shong for quick response.

I'm getting null value for QUERY_ID in the output if I set the expression in tMap as

(Integer)globalMap.get("row1.QUERY_ID") 

I tried ((Integer)globalMap.get("QUERY_ID")) as well but got null

Below is the log. Based on the log I think it is not able to get the QUERY_ID from row1

Please suggest. Thank you! 

..

..

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_1 - Executing the query: '

select

QUERY_ID,

USER_DEFINED_SQL

from

  QUERIES_TABLE 

'.

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_1 - Retrieving records from the database.

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_1 - Retrieving the record 1.

[DEBUG]: dw.table_counts_0_1.table_counts - tFlowToIterate_1 - Current iteration is: 1.

[DEBUG]: dw.table_counts_0_1.table_counts - tLogRow_1 - Start to work.

..

..

..

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_1 - Retrieving the record 114.

[DEBUG]: dw.table_counts_0_1.table_counts - tFlowToIterate_1 - Current iteration is: 114.

[DEBUG]: dw.table_counts_0_1.table_counts - tLogRow_1 - Start to work.

[DEBUG]: dw.table_counts_0_1.table_counts - tLogRow_1 - Parameters:BASIC_MODE = false | TABLE_PRINT = true | VERTICAL = false | PRINT_CONTENT_WITH_LOG4J = true | 

[DEBUG]: dw.table_counts_0_1.table_counts - tMap_2 - Start to work.

[DEBUG]: dw.table_counts_0_1.table_counts - tMap_2 - Parameters:LINK_STYLE = AUTO | TEMPORARY_DATA_DIRECTORY = | ROWS_BUFFER_SIZE = 2000000 | CHANGE_HASH_AND_EQUALS_FOR_BIGDECIMAL = true | 

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_3 - Start to work.

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_3 - Parameters:USE_EXISTING_CONNECTION = true | CONNECTION = tDBConnection_1 | TABLE = "" | QUERYSTORE = "" | QUERY = ((String)globalMap.get("user_defined_sql")) | IS_CONVERT_XMLTYPE = false | USE_CURSOR = false | TRIM_ALL_COLUMN = false | TRIM_COLUMN = [{TRIM=false, SCHEMA_COLUMN=METRIC_VALUE}] | NO_NULL_VALUES = false | UNIFIED_COMPONENTS = tOracleInput | 

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_3 - Executing the query: 'select count(1) as METRIC_VALUE from CUSTOMERS'.

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_3 - Retrieving records from the database.

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_3 - Retrieving the record 1.

[DEBUG]: dw.table_counts_0_1.table_counts - tMap_2 - Outputting the record 1 of the output table 'output'.

[INFO ]: dw.table_counts_0_1.table_counts - tLogRow_1 - Content of row 1: 216808|null

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_3 - Retrieved records count: 1 .

[DEBUG]: dw.table_counts_0_1.table_counts - tDBInput_3 - Done.

[DEBUG]: dw.table_counts_0_1.table_counts - tMap_2 - Written records count in the table 'output': 1.

[DEBUG]: dw.table_counts_0_1.table_counts - tMap_2 - Done.

.------------+--------.

|   tLogRow_1   |

|=-----------+-------=|

|METRIC_VALUE|QUERY_ID|

|=-----------+-------=|

|216808   |null  |

'------------+--------'

screenshots of the mapping

 

  

 

[Anonymous · Accepted solution]

change it to

(Integer)globalMap.get("query_id") or (Integer)globalMap.get("row1.query_id") and let me know if it works, I see you customize the global variable rather than using the default ones.

[DataEng112]

yes, thanks for pointing it out.

((Integer)globalMap.get("query_id")) worked.

thank you!

[DataEng112]

fyi (Integer)globalMap.get("row1.query_id") didnt work.

(Integer)globalMap.get("query_id") worked.