I am trying to use the Federated Query functionality in BigQuery to query some data from my Cloud SQL Postgres DB.
SELECT * FROM
EXTERNAL_QUERY('{project_name}.US.app_postgres_db', 'select id from posts limit 10')
Unfortunately I get the following error:
Invalid table-valued function EXTERNAL_QUERY Connect to PostgreSQL server failed: missing "=" after "xxxx" in connection info string at [3:1]
It is quite similar to the error discussed here, but it was not solved (the word that appears in the quotes is different though): Connection Error while running federated query in BigQuery to Postgres Cloud SQL instance
For reference, I set up the connection in BQ with the following command:
bq mk --connection --display_name='App DB' --connection_type='CLOUD_SQL'
--properties='{"instanceId":"{project-name}:us-central1:postgres-main","database":"main","type":"POSTGRES"}'
--connection_credential='{"username":"{username}", "password":"{password}"}'
--project_id={project-name} --location=us app_postgres_db
Advertisement
Answer
I confirm, it’s the password with space which cause the issue. I tried several things and I found one which works!
Here my original password: root is root
Here the password definition in the command line: "password":"root\ is\ root"
With MySQL no problem with the spaces!!! I will open an issue for that, but here the workaround!