Connection Error while running federated query in BigQuery to Postgres Cloud SQL instance

Question

I am trying to connect to a Postgres SQL instance via a Big Query federated query. As you can see in the code below:

SELECT * 
FROM EXTERNAL_QUERY(
    'project-id.location-id.connection-id', 
    '''SELECT * FROM INFORMATION_SCHEMA.TABLES;''');

I am filling out everything as listed in the documentation but I get the following error:

Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: 
missing "=" after "[text that appears in the project-id portion of the connection id]" 
in connection info string at [1:15]

Not really sure why the connection string would want an "=" sign based on Google's documentation.

NOTE

The Cloud SQL and Big Query are located inside of the same project and within a location that permit federated queries.

Chris32 Chris32 · Accepted Answer · 2020-01-04T00:20:47

You could try this form:

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

Just substitute the first xxxx with your projectid, the region with your region and the last xxxx with the name you gave to the connection in The bigquery interface (not Cloud SQL info, that goes into the query)

Connection Error while running federated query in BigQuery to Postgres Cloud SQL instance

1 Answers