I'm trying to connect BigQuery to a CloudSQL Postgres DB using an "External Data Source". After setting up the connection, I see this error in BigQuery:
Invalid table-valued function EXTERNAL_QUERY
Connect to PostgreSQL server failed: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
at [1:15]
I tried setting up a sandbox project, and ran into the same issue there. I then tried enabling a "Public IP" connection in the CloudSQL configuration, which appeared to fix the issue in the sandbox.
I would like to be able to do this without turning on the public IP, since I want to keep my data in my private network only. Is it possible to use a private IP CloudSQL instance with BigQuery the way that I am describing?
As per documentation, connections between Cloud SQL and Big Query using private IPs are not supported, and public IPs should be used to do so.
As an alternative, you can add public IP connectivity with NO authorized address.
While this is not as secure as private IP-only, your instance will remain inaccessible from the public internet, and can be queried from BigQuery.
You can see further information regarding public IPs for Cloud SQL here.