Querying mulitple DB's in the same job

[jensej]

Hello guys

I have about 50 postgresql databases that i work with.

The all have the same setup (Schema,Tables,Databasename,Username,PW etc.)

To access 5 of them i need to create a ssh tunnel and connect with localhost as Host.

For the rest the only thing that changes is the Host.

My goal is to be able to write one query that can make selects or mutations on all the databases with just one click.

I have succefully tested the tSshTunnel component to create tunnels.

What's the best way to do this?

List all of my 45 hostnames in a excel file and then somehow loop thruu that list and execute a query for every hostname?

What components and connections do i need to solve this?

Thanks

[Anonymous]

Hi

Read the hostnames from file and then iterate each hostname, assign the hostname value to context variable and use context variable on DB component, the Job looks like:

tFileInputExcel--main(row1)--tFlowToIterate--iteate--tJava--oncomponnetOK-->tDBinput--main-->other components

on tJava:

context.hostname=row1.hostname;

Can you try and let me know if it helps?

Regards

Shong

[jensej]

Hi @Shicong Hong​ 

Thanks for your help.

So i manage to make it work with this setup:

tFileInputDelimited -- main -- tFlowToIterate -- iterate -- tDbInput -- main -- tmap -- main -- tFileOutputExcel

Why do you use tJava and oncomponentOK? What do write inside the tJava component?

In tFileInputDelimited i list all my host and if i query select count(*) from users in tDbInput i get a Excel in the end with two columns Hostname and AmountUsers. The hostname i add in tmap.

Hostname AmountUsers

Server1 10

Server2 15

Server3 20

I still have a problem though.. If for example the hostname for one connection is wrong my whole job dies.

If i rename the Server2 to Serve2 so that the hostname is wrong and no connection is possible i want to continue to iterate on the next host and write some kind of message that the host returned an error.

Something like this.

Server1 10

Serve2 Error, no connection

Server3 20

How can i do this?

[Anonymous]

I assign the current hostname value to context variable on tJava, for example:

context.hostname=row1.hostname;

You can access the current hostname directly on DBInput as you did if you don't define context variable.

About your second question, please refer to this topic, you have the same requirement as tagliaferri1640766615.

Regards

Shong

[jensej]

Hi @Shicong Hong​ 

Ok im sorry but im not able to understand how i can solve my second question from your links.

Lets say i have 8 rows in InputServer with host and port for the db connection. If for example row 7 has wrong connection data i don't want the PGInput to throw an error and cancel the job. I want it to ignore that connection that didn't work and go on with the 8th row.

[Anonymous]

if you want to ignore the error and let the job continue to run, I have shared a tip that shows how to do it, go to the tip page

https://community.talend.com/s/tips

and see the tip I submit at Step, 27.

[jensej]

Hi @Shicong Hong​ 

My job now ignores the error and continues, thanks! Now I have another problem.

If my job can't establish a connection and catches an error I still want to be able to catch and write the error message.

I tried like this and then sent the "error" to the csv in tmap but it's always empty.

[jensej]

This message I want to catch and print