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.
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
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 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