topic Re: Read tables with same structure from different DB and create one joining all the inputs in Talend Studio

Read tables with same structure from different DB and create one joining all the inputs

FGuijarro — Sat, 16 Nov 2024 01:12:21 GMT

Hi,

I´m trying to read 5 tables from DB1, DB2, DB3, DB4 and DB5.

All the tables are called with the same name AND all of them have the same structure

The output will be 1 table with all the content of the 5 input tables.

Can anyone help me, please?

Thank you

Re: Read tables with same structure from different DB and create one joining all the inputs

Anonymous — Fri, 30 Oct 2020 05:41:16 GMT

Are all DB connection information the same except database name? If so, I think you can write each database name on tForEach component and then iterate each database name, the job looks like: (take Mysql database for exxample)

tForEach--iterate--tMysqlInput--main--tUnite---tMysqlOutput

on tMysqlInput, set the database field with global variable:

((String)globalMap.get("tForeach_1_CURRENT_VALUE"))

tUnite: merge all the output from each database.

Regards

Shong

Re: Read tables with same structure from different DB and create one joining all the inputs

FGuijarro — Fri, 30 Oct 2020 10:41:26 GMT

Hi Shong,

Thanks for your answer.

Definetly it worked!!

I just have an issue because it seems there are some repeated records in the 5 table readed from the diferent databases with same primary key. So after run it, I received message

[statistics] connected

(conn=453) Duplicate entry '3510' for key 'PRIMARY'

(conn=453) Duplicate entry '488' for key 'PRIMARY'

(conn=453) Duplicate entry '486' for key 'PRIMARY'

[statistics] disconnected

Query used in tDBInput is:

"SELECT

`venta`.`id`,

`venta`.`cliente`,

`venta`.`fecha_exp`,

`venta`.`operacion`,

`venta`.`codigo_servicio`

FROM `venta`"

And database field contents: ((String)globalMap.get("tForeach_1_CURRENT_VALUE"))

Is there any option to copy every single row from the 5 tables (one from one different DB) although primary key could be the same in table_1 than in table_2?

Many thanks!

Re: Read tables with same structure from different DB and create one joining all the inputs

Anonymous — Mon, 02 Nov 2020 07:36:00 GMT

I think the error occurs when you trying to insert duplicated entry into target database, if you don't want this error, you can filter the duplicated records before inserting.

tForEach--iterate--tMysqlInput--main--tUnite--tUniqRow-tMysqlOutput

Re: Read tables with same structure from different DB and create one joining all the inputs

FGuijarro — Mon, 02 Nov 2020 12:44:25 GMT

Hi Shong,

Thanks for your answer. I checked records and DB had duplicated records by the end of each month so it would be a great idea to add an ammount to the first id of the next month for not to repeat ids when changing month.

Thanks!