Search code examples
google-cloud-platformdataformfederated-queriescloudsql

Is it possible to use federated queries from GCP Dataform?

I'd like to query tables in CloudSQL from Dataform. I set up an external connection to the CloudSQL database in bigquery, and am able to get query results when I run the federated query from BigQuery.

However, when I try to run the same query from a GCP Dataform job, it does not work, and I get an error that it cannot find the connection.

Is it possble to run federated queries from GCP Datafrom, and if so which setting and/or permissions are needed to get this working?

In BigQuery, i have setup a external connection named external-connection-test

In Dataform I have:

federated.sqlx:

config {
type: "table",
} 

SELECT
*
FROM
  EXTERNAL_QUERY("projects/my-project/locations/my-location/connections/external-connection-test",
"SELECT * from example_table'")

When I run the query in BigQuery it returns the expected results, but when I run it in BigQuery it get this warning:

enter image description here

Solution

  • I was facing the same issue and managed to fix it by matching the location of the defaultLocation setting in the workflow_settings.yaml to the Big Query External Connection's.

    My CloudSQL Postgres instance and Big Query External Connection are set in southamerica-east1 and my initial defaultLocation was pointing to Dataform's default US. Changing it to southamerica-east1 and deleting the initial dataset, so it was recreated in the correct region, got it working.