Update and Insert in BigQuery (UPSERT)

KShah1665472180 — Fri, 15 Nov 2024 22:28:32 GMT

Hello,

I have a process that reads the data from PostgreSQL and uploads the data in BigQuery.

In order to execute this, I'm using tDBInput(PostgreSQL) --> tmap --> tBigQuerySQLrow --> tBigQueryOutput

In certain conditions, I have to Update or Insert data in BigQuery.

Does someone know what it the best way to Update/Insert data from BigQuery?

I have used this query in tBigQuerySQLRow :-

"DECLARE fields STRING;

DECLARE updates STRING;

EXECUTE IMMEDIATE (

\"SELECT STRING_AGG(column_name) FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Table1'\"

) INTO fields;

EXECUTE IMMEDIATE (

\"\"\"WITH t AS (SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'Table1')

SELECT STRING_AGG(\"t.\"||column_name ||\" = \"|| \"s.\"||column_name) from t join t as s using(column_name)\"\"\"

) INTO updates;

EXECUTE IMMEDIATE \"\"\"

MERGE `Table1` T

USING `Table2` S

ON T.id = S.id

WHEN NOT MATCHED THEN

INSERT (\"\"\"||fields||\"\"\") VALUES (\"\"\"||fields||\"\"\")\"\"\""

PFA screenshot of my Job.

Thank You.

topic Update and Insert in BigQuery (UPSERT) in Talend Studio