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
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 !