'Connect Google Cloud SQL Potsgres with Google DataStudio
I am wondering whats the best way to connect my google cloud sql postgres DB with data studio.
I do not see a google connector for cloud sql posgres, but only for cloud sql mysql in datastudio. There is a generic postgres connector, but I am not sure if this should be the way to go or if I should first transfer my data to google bigquery and then connect it to data studio.
Any idea's whats the best way?
Thanks and Regards
Solution 1:[1]
You can connect to Cloud SQL for PostgreSQL using Data Studio.
For that, follow the official Data Studio documentation for connecting to PostgreSQL:
- Sign in to Data Studio.
- In the top left, click Create, then select Data Source.
- Select the PostgreSQL connector.
- Configure access to your database using one of the connection options.
- Click AUTHENTICATE. You will see a list of tables in that database.
- Select a table.
- Click CONNECT.
NOTE: In order to authenticate after the 4th step, you will also need to open access to the following IP addresses so that Data Studio can access your database:
64.18.0.0/20
64.233.160.0/19
66.102.0.0/20
66.249.80.0/20
72.14.192.0/18
74.125.0.0/16
108.177.8.0/21
173.194.0.0/16
207.126.144.0/20
209.85.128.0/17
216.58.192.0/19
216.239.32.0/19
You can do that for your Cloud SQL instance either through the Cloud Console or using the gcloud command:
gcloud sql instances patch [INSTANCE_NAME] --authorized-networks=[IP_ADDR1],[IP_ADDR2]...
Solution 2:[2]
If you just want to pull simple dataset directly from your postgres database, you can connect to Data Studio by using generic postgres connector and allow public ip to authorize by following this google instruction.
On the other hand, if you want to query large dataset for reporting, BigQuery will be best suited as it's perfect for querying large dataset quickly and storing for the analytics.
Pricing wise for the long run, you can compare the pricing of Cloud Sql and Big Query as they are priced differently.
Querying data from cloud sql through BigQuery's federated query and explore it to data studio without storing dataset in BigQuery is not a recommended way for large dataset as it will increase the latency for your report.
Solution 3:[3]
I spent hours on this - I think there is something wrong with adding authorised networks IP addresses for your instance in google console. I named each address ds1, ds2 etc. and had to add them one by one. Adding all 12 then clicking 'save' didn't seem to save them.
Even when I had them all listed in console, it would not authenticate.
As a last gasp attempt I tried a v12 instance (was on v14) but made no difference.
Finally I ran the IP updates as a gcloud command:
gcloud sql instances patch [instance] --authorized-networks=64.18.0.0/20,64.233.160.0/19,66.102.0.0/20,66.249.80.0/20,72.14.192.0/18,74.125.0.0/16,108.177.8.0/21,173.194.0.0/16,207.126.144.0/20,209.85.128.0/17,216.58.192.0/19,216.239.32.0/19
This then let me in but only on the v12 instance.
In summary, stick to v12 and run a gcloud command above... Crazy!
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Deniss T. |
| Solution 2 | Lin Lae |
| Solution 3 | Alex Petrie |