GCP BigQuery fedarated query to cloudsql gives connection not found error

Question

I need to populate a BigQuery table by executing a cloud sql "federated query" ( see https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries#overview to read about federated queries) In simpler words I create a "connection" to cloudsql MySql database and run an "External" mysql query from BIGQuery console to insert data into a BIGQuery table.

I do have a valid "connection" (called myconnectionid) set-up from my BigQuery console. If I run query as follows BigQuery console, it runs fine and gives me rows from my cloudsql mysql table

SELECT * FROM EXTERNAL_QUERY("myproject.us.myconnectionid", "SELECT * from my-cloudsql-table;") 

However if I run the same query as part of an insert into as follows

insert into my-bq-dataset.my-bq-table SELECT * FROM EXTERNAL_QUERY("myproject.us.myconnectionid", "SELECT * from my-cloudsql-table;") 

Then I get an error "Not found: Connection myconnectionid

Since the a plain select using same External connection with id "myconnectionid" works fine ; I wonder if I am making a mistake in writing my "insert into" sql , or is it something else thats needed for a connection so it may be used as part of insert into statement.

Please do share your thoughts

Huge thanks for your help

And wishing you a happy and safe holiday season

Answer 1

I found that this error occurs when your connection is created in a region / location that is different from the region where the cloudsql instance resides. As far as you create a connection in the SAME region as your cloudsql mysql instance's region this works fine . Thanks !