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

Have you tried to enter the full id of the connection (in the format project-id:location-id:instance-id) as in the example in the documentation? — Kyrylo Bulat
perfectly works for me in both cases - with short connection id and with full id. meantime (this does not related to your current issue but will most likely be the next one) add limit 1 to the end of query - otherwise you will get scalar subquery producing more than one element error — Mikhail Berlyant

Yun Zhang Yun Zhang · Accepted Answer · 2021-01-10T07:34:57

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();

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

1 Answers