topic Re: Join 2 table with thousand columns in Talend Studio

Join 2 table with thousand columns

Anonymous — Fri, 11 Aug 2017 11:27:19 GMT

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.

Re: Join 2 table with thousand columns

Anonymous — Fri, 11 Aug 2017 11:30:27 GMT

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.

Re: Join 2 table with thousand columns

Anonymous — Fri, 11 Aug 2017 11:47:22 GMT

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

Re: Join 2 table with thousand columns

Anonymous — Fri, 11 Aug 2017 12:12:51 GMT

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

Re: Join 2 table with thousand columns

vapukov — Sat, 12 Aug 2017 08:11:49 GMT

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

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

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

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

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:

than in tMap use as usual INNER JOIN

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

and we use Advanced tab - use Filed option

result - as expected:

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

Re: Join 2 table with thousand columns

Anonymous — Mon, 14 Aug 2017 02:46:16 GMT

I got it, thanks very much, both of you