Six Stars

Join 2 table with thousand columns

Hi everyone,

I'm new to Talend and I have a question:
I have table A with 1000 column, table B have a column name utcdate

I want to join 2 table on 2 key column and with condition that utcdate column in table B is in a range.

How to join it with tMap ?
Note that table A have 1000 column so that I have to use 3 tPostgresqlInput components to get data, and one other tPostgesqlInput for table B.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Ten Stars

Re: Join 2 table with thousand columns

as DBA, I always sure - 1000 columns it always wrong design

plus I absolutely agree with @rhall_2_0 - this better to do inside database, with plain SQL

in this case - You do not need Talend at all Smiley Happy or just could use single component tPostgreSQLRow with SQL code

 

but if accept this situation as fact, You also could use a trick:

Screen Shot 2017-08-12 at 7.10.19 PM.png

 

Screen Shot 2017-08-12 at 6.58.30 PM.png

 

as You can see - we have 2 tables with 10 columns, and we want UPDATE column c20 in table 1 for all rows matched by column c9 with value from Table 2 (matched column c9, values from column c20)

 

we do not need for this define all columns in Talend, we define in tPostgreSQLInput only necessary columns:

Screen Shot 2017-08-12 at 6.58.46 PM.pngScreen Shot 2017-08-12 at 7.04.44 PM.png

 

than in tMap use as usual INNER JOIN

Screen Shot 2017-08-12 at 6.58.56 PM.png

 

same for output flow we define only key column + target column

and we use Advanced tab - use Filed option

Screen Shot 2017-08-12 at 6.59.13 PM.pngScreen Shot 2017-08-12 at 6.59.21 PM.png

 

result - as expected:

Screen Shot 2017-08-12 at 7.10.32 PM.png

 

 

in case of "Use Filed option" Talend generate SQL code similar for what You can run manually

UPDATE Table1 SET c20 = value WHERE id = value

 

 

-----------
5 REPLIES
Twelve Stars

Re: Join 2 table with thousand columns

Wow! 1000 columns! Are table A and B in the same database? If so, I would recommend joining your t{db}Input component and only bring in the data you need. 

Rilhia Solutions
Six Stars

Re: Join 2 table with thousand columns

Thank you but i don't get any solution with your comment, can you explain your idea ?
Twelve Stars

Re: Join 2 table with thousand columns

Sorry, my suggestion was that you write a SQL query joining the 2 tables (assuming they are both in the same database) and handle the date range logic there. The point of my suggestion was that ....

1) You do not want to be dealing with 1000 columns unless you need every single one, in your job

2) The memory overhead of dealing with that much data will lead to issues further down the line

3) Since your database is arguably going to be one of your more powerful systems (and it is meant for querying) it makes sense to filter/join your data there and send it to Talend for further processing.

 

I was kind of preempting future issues you will have with so much data AND assuming you could probably perform the join logic easier in SQL

Rilhia Solutions
Highlighted
Ten Stars

Re: Join 2 table with thousand columns

as DBA, I always sure - 1000 columns it always wrong design

plus I absolutely agree with @rhall_2_0 - this better to do inside database, with plain SQL

in this case - You do not need Talend at all Smiley Happy or just could use single component tPostgreSQLRow with SQL code

 

but if accept this situation as fact, You also could use a trick:

Screen Shot 2017-08-12 at 7.10.19 PM.png

 

Screen Shot 2017-08-12 at 6.58.30 PM.png

 

as You can see - we have 2 tables with 10 columns, and we want UPDATE column c20 in table 1 for all rows matched by column c9 with value from Table 2 (matched column c9, values from column c20)

 

we do not need for this define all columns in Talend, we define in tPostgreSQLInput only necessary columns:

Screen Shot 2017-08-12 at 6.58.46 PM.pngScreen Shot 2017-08-12 at 7.04.44 PM.png

 

than in tMap use as usual INNER JOIN

Screen Shot 2017-08-12 at 6.58.56 PM.png

 

same for output flow we define only key column + target column

and we use Advanced tab - use Filed option

Screen Shot 2017-08-12 at 6.59.13 PM.pngScreen Shot 2017-08-12 at 6.59.21 PM.png

 

result - as expected:

Screen Shot 2017-08-12 at 7.10.32 PM.png

 

 

in case of "Use Filed option" Talend generate SQL code similar for what You can run manually

UPDATE Table1 SET c20 = value WHERE id = value

 

 

-----------
Six Stars

Re: Join 2 table with thousand columns

I got it, thanks very much, both of you Smiley Very Happy