Using Data Studio to Generate Reports from Cloud SQL PostgreSQL

March 21, 2021
1:18 am

Creating a dashboard with numbers and fancy charts is always a pain and involves whole lot of work.

If the dashboard is for your clients, your end users, then it’s inevitable and it will be worth the time doing yourself given future changes are always going to happend.

For internal monitoring, data reports, it’s a different story. We can always make our own dashboard to make the team happy but it has its costs, while at the same time there are already a lot of BI tools that is easy to use and flexible enough for this purpose. So this article will cover a very specific scenario that an application’s database is served on Cloud SQL and wants to use Data Studio to generate reports.

Before I get into the details, I want to list my arguments about using an BI tool over building a dashboard myself:

way less work involved
flexible. The requirment for the metrics and reports will change over the future, comparing with updating a manually built dashboard (ux + api), it will be way much easier to update the settings in the bi tool
engineering team does not have to get involved once other roles get involved (BI, or PM that knows about SQL)

Create Connection from BigQuery to Cloud SQL

Though Data Studio has an option “PostgreSQL” when adding a new Data Source, but unfortunatelly, it’s very difficult for the PostgreSQL on Cloud SQL (I’ve tried), but if you are using MySQL, you shall just choose “Cloud SQL for MySQL”, which will not be covered by this article.

So that’s why we will be using the “BigQuery” option. But before we start configuring it on Data Studio, we need to create a connection from BigQuery to Cloud SQL.

Create Connection

Go to BigQuery on your GCP Console and get into the “SQL Workspace” tab.
From the “Add Data” dropdown, select “External data source”:

The Big form will appear from the right drawer, fill in all the details about your database, then click “Create connection”

Once you finished, you shall be able to see the new connection under the pinned projects on the left column(if not, try refreshing the page):

Create Queries As Data Sources

Data Sources will not be able to query your whole database, what it can do is it get a query result and use that result as a individual data source. You can then in the Data Studio further change the result based on your report requirements.

So In the BigQuery side, what we need to do is just create queries that can be used for Data Studio. For example:

A Query to get users
A query to get orders

The easiest way to create a new exmaple query is select your connection and on the right hand side click the ampifier icon:

An example of a query (notice you need to use the “EXTERNAL_QUERY” function to wrap your actual SQL):

SELECT
  *
FROM
    EXTERNAL_QUERY(
    "product-id.region-id.connection-name",
    '''
        SELECT * FROM "user";
    '''
    );

You can click the “Run” button to preview the result. Once you are happy with the result, you can save the query so that you can further change or reuse it (but not necessary in our case). So copy the query and we will use that in Data Studio.

Add Data Source to Data Studio

Now we have setup Big Query connection to our Cloud SQL and we have copyed our query, we can add the query to Data Studio as our Data Source.

In the Data Studio main page, on the left hand side big “Create” button, select “Data source”:

Choose “BigQuery”:

In the next step from the left column, pick “Custom Query” then choose your GCP project that your Big Query connect is in the column next. After that simply just paste your query into the editor:

If it all goes well, the next page will be the newly created data source page, you can further confiure all the columns, give it a better name etc.

Use Data Source to your Report

Get back to the main page of Data Studio, now we can create a new Report. After you jump into the new report page, Simply create a new chat to the canvas: