Connect Google Cloud SQL Potsgres with Google DataStudio

Question

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

Answer 1

You can connect to Cloud SQL for PostgreSQL using Data Studio.

For that, follow the official Data Studio documentation for connecting to PostgreSQL:

1. Sign in to Data Studio.
2. In the top left, click Create, then select Data Source.
3. Select the PostgreSQL connector.
4. Configure access to your database using one of the connection options.
5. Click AUTHENTICATE. You will see a list of tables in that database.
6. Select a table.
7. 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]...

Answer 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.