CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-cloud-sql
user2491463
user2491463

Reputation: 512

Merge bigquery table with google cloud postgres table with federated query

I am trying to merge a bigquery table (target) with a google cloud postgres table (source) with a federated query. However it looks like bigquery will not accept a federated query in the "using" clause.

Syntax error: Expected "(" or keyword SELECT or keyword WITH but got identifier "EXTERNAL_QUERY" at [3:9]

My query looks something like the below.

MERGE bigquery_dataset.bigquery_table TARGET
USING (
    EXTERNAL_QUERY("projects/company-co/locations/us/connections/company","SELECT * FROM postgres_schema.postgres_table")
        ) SOURCE

ON target.id = source.id

WHEN MATCHED THEN ...
WHEN NOT MATCHED BY TARGET THEN ...
WHEN NOT MATCHED BY SOURCE THEN ...

Are there any known workarounds for this type of functionality? Or is there any other way to perform this type of merge?

Upvotes: 0

Views: 477

Answers (1)

Sandeep Mohanty
Sandeep Mohanty

Reputation: 1552

As per your requirement if you want to run federated queries in BigQuery where your external data source is located in Cloud PostgreSQL instance, you need to define the source dataset using the SQL function i.e EXTERNAL_QUERY

The error you are getting : “Syntax error: Expected "(" or keyword SELECT or keyword WITH but got identifier "EXTERNAL_QUERY" at [3:9]” is because you are missing out the SELECT statement before your EXTERNAL_QUERY.

As per this doc, the syntax should be :

SELECT * FROM EXTERNAL_QUERY(connection_id, external_database_query[, options]);

I tried running the federated query in BigQuery where the source is in Cloud PostgreSQL and it is working as expected.

SQL QUERY :

MERGE myproject.demo.tab1 TARGET
USING (
  select * from EXTERNAL_QUERY("projects/myproject/locations/us-central1/connections/sqltobig", "SELECT * FROM entries;")
       ) SOURCE

ON target.entryID = source.entryID

WHEN MATCHED THEN
DELETE

WHEN NOT MATCHED THEN
INSERT(guestName, content, entryID)
VALUES(guestName, content, entryID)

Upvotes: 2

Related Questions