From Thursday, July, 9, 3:00 PM Pacific,
our Community site will be in
read-only mode
through Sunday, July 12th.
Thank you for your patience.

How to share a temporary table across jobs?

Highlighted
Six Stars

How to share a temporary table across jobs?

Hi,

I want to create a temporary table (in Postgres) that I want to share across two jobs (see screenshot). However when I create a database connection, it's not recognized by any of the jobs (see second screenshot) as the component list is empty. In another forum, I found out that you can add the connection in the dynamic settings as an item (see 3rd screenshot), but this still lead to an error (4th screenshot). Anyone have any suggestions?

 

As an alternative, if it's not possible through a temp table, but rather through a permanent table, how can I drop the table later on as I didn't see any database command components for that either (only create table components)?

 

Roland

 

job design v2.pngcomponent list emptycomponent list emptyadding db connection as itemadding db connection as itemerror when running job and adding connection as itemerror when running job and adding connection as item


Accepted Solutions
Highlighted
Forteen Stars

Re: How to share a temporary table across jobs?

Hi,

 

to use a shared connection between subjobs, and parent and each child must contain tDBConnection (with the same Shared name)

 

btw, for delete table, you can always use tDBRow with a simple

DROP TABLE xxx;

command

-----------

View solution in original post


All Replies
Highlighted
Forteen Stars

Re: How to share a temporary table across jobs?

Hi,

 

to use a shared connection between subjobs, and parent and each child must contain tDBConnection (with the same Shared name)

 

btw, for delete table, you can always use tDBRow with a simple

DROP TABLE xxx;

command

-----------

View solution in original post

Highlighted
Seven Stars

Re: How to share a temporary table across jobs?

Hello,

In order to share the connection between jobs, you must have the tDBConnection component in each of the father and child jobs.

 

In the child jobs, no need to put the server and the idenfiants. You just add the same name of the connection.
When you trace the logs, you can see the message "existing connection detected with the key = connection name of the father job"

tDBRow to delete the created table (I advise you to put it in a tpostjob of the father job before closing the connection)

Highlighted
Six Stars

Re: How to share a temporary table across jobs?

Thanks. it worked.

Highlighted
Six Stars

Re: How to share a temporary table across jobs?

Thanks for the reply. Where can you exactly see in the log that it uses the same connection. When I run the parent job, the only log I see is in the display (see screenshot). Also why is it better to drop the table in a postjob instead of the main parent job as displayed here?

job log.png

Highlighted
Seven Stars

Re: How to share a temporary table across jobs?

In Run View ->Advanced Settings -> Log4j-->trace 

you will have first tDBconnection_1 (parent job) Create a shared connection "name"

then tDBConnection_2 (child job) fin a shared connection with key "name"

 

If you do not see the de log, it's disable by default, you can enable them in preferences log4j ou project settings (i dont remember lol)

the trace log is only for dev environnement and for prod, you can lunch warn ou error

Highlighted
Six Stars

Re: How to share a temporary table across jobs?

I did a quick search. It appears that the Log4j Logging Option is not available in Talend Open Studio which I'm using. 

Highlighted
Seven Stars

Re: How to share a temporary table across jobs?

I assure you it's an option of free and payed subscription 

You will fin it in the project management 

 

2020-06-30_22h49_01.png

 

Then you can use trace logs 

 

2020-06-30_22h49_38.png

Highlighted
Six Stars

Re: How to share a temporary table across jobs?

Thanks. I found it now.

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog