Federated queries from Dataform

[ChrisOrbisk]

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?

[NorieRam]

Hi @ChrisOrbisk,

Welcome to Google Cloud Community!

Dataform is excellent at transforming data already within BigQuery, but it currently doesn't directly support pulling data from external sources like Cloud SQL using federated queries, even though you have a working federated query in BigQuery.

Here are some workaround for you to try:

• Bring the CloudSQL data into BigQuery. You could use Dataform to schedule this or look into Cloud Functions/Dataflow. 
• If you need to do complex joins across BigQuery and CloudSQL in one go, tools like dbt or custom scripts might be worth exploring.

Here are the documentation that might be helpful to you: 

• Loading and transforming data into BigQuery using dbt
• Connect to Cloud SQL

I hope it helps.

[ChrisOrbisk]

Is there somewhere i could add a feature request?

[NorieRam]

@ChrisOrbisk Here’s where you can file a feature request. Please note that I cannot specify when this enhancement will be implemented. For future updates, I recommend monitoring the issue tracker. Also, don’t forget to check the Dataform release notes for the most recent updates and developments.

[m-hoss]

Hi. This is possible. I have done this. Make sure the location for your Connection in BigQuery and the defaultLocation for you DataForm repository are the same, as well as any relevant IAM like BigQuery Connection User.

[ChrisOrbisk]

Thanks, i think i have all the right IAM setting, but not sure about defaultLocation, will give it a try.