Google Data Studio is a tool that you can integrate with Aiven for PostgreSQL for creating reports or performing analysis on data that you have in your PostgreSQL database. Google Data Studio goes beyond PostgreSQL, and allows the integration of other data sources such as YouTube Analytics, MySQL, BigQuery, AdWords, etc.

Prerequisites

  • Install OpenSSL on your operating system. OpenSSL is used to generate client-side public and private key pairs. In the examples in this article, these are called client-cert.pem and client-key.pem respectively.

  • Download the CA certificate for your Aiven for PostgreSQL instance from the Aiven web console. The examples here use ca.pem to refer to this.

  • A Google account.

To integrate Google Data Studio and your Aiven for PostgreSQL instance:

  1. Run the following OpenSSL command to generate the client-side public and private key pairs:

    openssl req -x509 -newkey rsa:2048 -keyout client-key.pem -out client-cert.pem -days 3650 -nodes -subj '/CN=localhost'

  2. Connect to your Aiven for PostgreSQL data source from Data Studio:

    1. Open Google Data Studio.

    2. Select Data Sources in the left menu and then click the blue + button in the bottom-right corner to add a new data source.

    3. Agree to the Google terms and conditions when prompted.

    4. Select PostgreSQL as the data source type and then click Authorize to authenticate your Aiven for PostgreSQL instance with your Google account.


    5. On the Basic tab, enter the Host Name, Port, Database, Username, and Password values.
      You can get these values from the Overview page for your service in the Aiven web console.

    6. Select Enable SSL and upload your certificate and key files.
      Note: It is not a mistake in the screenshot above. You have to upload the client private key twice in the SSL settings section of the form: in both the Client certificate and Client private key fields. This is believed to be a known issue in Google Data Studio. For more details, please refer to this discussion in Data Studio Help.

    7. Click Authenticate.

Once the database authentication is complete, you can proceed to query data from it and create elegant analytics reports.


Did this answer your question?