topic Re: select ID and sql query (count query) from table and write ID and result of count query to target table in Talend Studio

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

DataEng112 — Fri, 15 Nov 2024 23:04:20 GMT

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!

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

Anonymous — Wed, 30 Mar 2022 00:40:05 GMT

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

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

DataEng112 — Wed, 30 Mar 2022 05:06:32 GMT

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

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

Anonymous — Wed, 30 Mar 2022 05:55:20 GMT

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.

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

DataEng112 — Wed, 30 Mar 2022 12:58:58 GMT

yes, thanks for pointing it out.

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

thank you!

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

DataEng112 — Wed, 30 Mar 2022 12:59:41 GMT

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

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