CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerygoogle-cloud-sql
Yogesh Devi
Yogesh Devi

Reputation: 689

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

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

Upvotes: 4

Views: 3215

Answers (2)

Yogesh Devi
Yogesh Devi

Reputation: 689

I created a new database and connection almost two years after I originally encountered this and ran into exact same problem

The only definitive way I could get this to work is to ensure that the BQ database , the external connection and the cloud SQL database are in exact same region.

So take away is => if you plan to use BQ "federated Queries" then ensure that the BQ database, the external connection and the cloud SQL database are in exact same region!

Upvotes: 0

Yun Zhang
Yun Zhang

Reputation: 5503

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

Upvotes: 4

Related Questions