topic Re: ELT UPSERT into GCP in Talend Studio

ELT UPSERT into GCP

rmartin2 — Sat, 16 Nov 2024 04:53:21 GMT

Hello everyone !

Here is my problematic :

I have an ELT solution, going from MSSQL to MSSQL, which is using hundreds of millions of lines in a single job, even billions, and there's a lot of them.

The server is now reaching it's maximum capacity and we are now trying to move to a different kind of solution : Google Cloud Platform with Postgre as DBMS.

It's where I'm hitting a wall : for Postgre on GCP, unlike MySQL, you need to use JDBC drivers while you can use MySQL native component to access MySQL DBMS on GCP. This leads to many restriction, some removed with the 7.2 recently released.

Still, for an insert, JDBCELT components are enough. The problem comes when you try to do an update. It's a pure massacre : you have to use nested request to do your update.

Example here : Bekwam Blog: Updating a Table with the Talend Open Studio ELT Components

This is a nasty solution.

My solution is not a good solution either : flag whether it's going to be an update or an insert with joining with target table, and :

- do your insert in ELT mode ==>Fast and cool

- do you update using a temporary table, and using ETL mechanic in another Subjob to do your biding (non-ELT components)

MSSQL and Oracle have a Merge option, but not Postgre or MySQL, so this wall will come up sooner or later.

Table have like a good hundred of columns something, so I don't want to map it myself.

Do you have a better idea/practice than my solution to avoid nested updates ?

Thank you in advance for your feedback.

Sincerely,

Re: ELT UPSERT into GCP

Anonymous — Mon, 09 Sep 2019 04:41:18 GMT

Hello,

Could you please let us know if this new feature jira issue is what you are looking for?

https://jira.talendforge.org/browse/TDI-21605

Best regards

Sabrina

Re: ELT UPSERT into GCP

rmartin2 — Mon, 09 Sep 2019 08:19:16 GMT

Hello !

I've been told to use the SQLTemplateMerge component.

But in the end it doesn't solve the underlying problem : how to integrate a "Merge" operation combined with tELTMap component.

Actually you just can't. And when you have millions of rows to process with dozens of joining and filter operations, you'd better have a good request in the select and not a hand-made one, which is hard to maintain.

I've discussed with two of your employees and actually there's no plan for making possible the combination of these two components.

I hope that in the near future, it's going to be implemented.