GCP BigQuery message "Not found: Connection" when I use a connection in scripting

Question

I am using GCP bigquery. I have an external connection "myconnectionid" defined. This connection is of connection type "Cloud SQL - MySQL" and it works fine for a running fedarated query from bigquery to the cloudsql mysql instance such as below.

SELECT * FROM 
EXTERNAL_QUERY("myconnectionid","SELECT CURDATE() from Dual;")

However when I attempt to declare a SQL variable and assign the return value from federated query to that variable ( I suppose that amounts to bigquery scripting) I get a message "Not found: Connection myconnectionid". Please see example of error causing snippet of code below

DECLARE MYDATE DATE;
SET MYDATE = (SELECT * FROM 
EXTERNAL_QUERY("myconnectionid","SELECT CURDATE() from Dual;"))

This snippet gives me an error "Not found: Connection myconnectionid at [2:14]"

Do gcp bigquery external connections need a special handling in scripting ? Or more simply ; do you know how I could overcome this error ?

I am reading through google documentation on connections https://cloud.google.com/bigquery/docs/working-with-connections#federated_query_syntax and also the google documentation on big-query scripting https://cloud.google.com/bigquery/docs/reference/standard-sql/scripting No luck yet.

Your help much appreciated

Thanks !!

Answer 1

I feel you may be hitting a bug that your query was not routed to correct region (where you have the connection defined).

The workaround could be:

• In UI: Query Settings -> Additional settings -> Processing location, pick the location/region where you have the connection defined
• Or, in the query, reference an entity from a dataset in the same location as the connection, for instance, add a query like SELECT dataset_in_that_location.f();