Unable to connect from BigQuery job to Cloud SQL Postgres

3
votes

I am not able to use the federated query capability from Google BigQuery to Google Cloud SQL Postgres. Google announced this federated query capability for BigQuery recently in beta state.

I use EXTERNAL_QUERY statement like described in documentation but am not able to connect to my Cloud SQL instance. For example with query

SELECT * FROM EXTERNAL_QUERY('my-project.europe-north1.my-connection', 'SELECT * FROM mytable;');

or 

SELECT * FROM EXTERNAL_QUERY("my-project.europe-north1.pg1", "SELECT * FROM INFORMATION_SCHEMA.TABLES;");

I receive this error :

Invalid table-valued function EXTERNAL_QUERY Connection to PostgreSQL server failed: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

Sometimes the error is this:

Error encountered during execution. Retrying may solve the problem.

I have followed the instructions on page https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries and enabled BigQuery Connection API. Some documents use different quotations for EXTERNAL_QUERY (“ or ‘ or ‘’’) but all the variants end with same result.

I cannot see any errors in stackdriver postgres logs. How could I correct this connectivity error? Any suggestions how to debug it further?

4
postgresqlgoogle-bigquerygoogle-cloud-sql
Some additional information: Cloud SQL has public ip, tested with pg9.6 and pg11, tested with totally separate Google account. All result in same error! – Veikko
Now tested also with Cloud SQL MySQL instance. Same problem with error message "Invalid table-valued function EXTERNAL_QUERY Failed to connect to MySQL database. Error: MysqlErrorCode(2013): Lost connection to MySQL server during query at [1:15]". I also tested with totally separate account and project, same problem! What am I missing in the setup? Have any of you been able to use external_query? – Veikko

4 Answers

2
votes

I just tried and it works, as far as the bigquery query runs in EU (as of today 6 October it works). My example:

SELECT * FROM EXTERNAL_QUERY("projects/xxxxx-xxxxxx/locations/europe-west1/connections/xxxxxx", "SELECT * FROM data.datos_ingresos_netos")

Just substitute the first xxxxs with your projectid and the last ones with the name you gave to the connection in The bigquery interface (not cloudsql info, that goes into the query)

1
votes

Unfortunately BigQuery federated queries to Cloud SQL work currently only in US regions (2019 September). The documents (https://cloud.google.com/bigquery/docs/cloud-sql-federated-queries) say it should work also in other regions but this is not the case.

I tested the setup from original question multiple times in EU and europe-north1 but was not able to get it working. When I changed the setup to US or us-central1 it works!

Federated queries to Cloud SQL are in preview so the feature is evolving. Let's hope Google gets this working in other regions soon.

0
votes

The BigQuery dataset and the Cloud SQL instance must be in the same region, or same location if the dataset is in a multi-region location such as US and EU.

Double check this according to Known issues listed.

0
votes

server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

This message usually means that you will find more information in the logs of the remote database server. No useful information could be sent to the "client" server because the connection disappeared so there was no way to send it. So you have look in the remote server.