Note: GCP CloudSQL MySQL lacks support for MySQL 8, and will unfortunately work with Aiven MySQL, 2020-05-01
Let's say you had a need to create reports or perform some analysis on data you have in Aiven PostgreSQL and you were looking for tools to do it. Google data studio is exactly one such tool that allows you to accomplish that task. Data studio goes beyond PostgreSQL, and allows integration of other data sources such as, YouTube Analytics, MySQL, BigQuery, AdWords, etc.
Integrating Google data studio with Aiven PostgreSQL is very straightforward. This help article will show you the necessary steps to integrate the two technologies.
- Make sure you have openssl installed on your operating system. Openssl will be used to generate client side public/private key pairs. Let's call these client-cert.pem and client-key.pem respectively.
- Make sure you have downloaded the CA certificate for your Aiven PostgreSQL instance from the Aiven console. Let's call this ca.pem.
- You should also have a google account.
Generating client public/private keys pairs
Use openssl to generate the client public/private key pairs as follows.
openssl req -x509 -newkey rsa:2048 -keyout client-key.pem -out client-cert.pem -days 3650 -nodes -subj '/CN=localhost'
Connecting to Aiven PostgreSQL datasource from Data Studio
Once you navigate to Google data studio, we will show you how to add Aiven PostgreSQL as a datasource there.
Select "Data Sources" on the left followed by the blue (+) on the bottom right corner to add a new data source. Please follow the next set of prompts to agree to google terms and conditions.
Select "PostgreSQL" as the data source type on the left and proceed to "Authenticate" Aiven PostgreSQL. Follow the next set of steps to authenticate with your google account.
Under the "Basic" settings, input the values for host, port, database, username, and password. (Note: these values can be obtained from the Aiven console). Remember to enable SSL for over-the-wire encryption of data and enter the cert and key files as shown above*. Finally, click "Authenticate" to authenticate with the Aiven PostgreSQL database server.
Once you have authenticated with the database, you can proceed to query data from it, and creating elegant analytical reports.
*) Please note that, unlike mentioned in the SSL settings, the form requires the client private key to be uploaded twice: in "Client certificate" and "Client private key". This it is believed to be a known issue in Google Studio. For more details please do refer to this discussion in Data Studio Help.