Connect Google Cloud SQL Potsgres with Google DataStudio

1
votes

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

2
google-cloud-sqlgoogle-data-studio
Sadly, a Cloud SQL connector doesn't exist and you need to use the public IP to reach it and to grant network on your instance. I didn't try, but you can try to use BigQuery federated queries, but it will increase the latency in data recovery. - guillaume blaquiere

2 Answers

3
votes

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]...
1
votes

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.