BigQuery federated queries against WordPress Cloud SQL Database

BigQuery has added a new feature, in which you can connect to a Cloud SQL instance and make federated queries. In this how-to, I will explain how I made an external connection from BigQuery, then using federated queries to query my cloud SQL WordPress database and made a Top Story report in Google Data Studio.

I recently wrote an article on how to migrate your WordPress database to Cloud SQL – lets now look at how we can use the Cloud SQL data to make a Top Story report with Data Studio.

Create a connection

To get started you need to open up BigQuery if you do not have a dataset yet please create one. Click on ADD DATA from the BigQuery navigation menu.

This action will open up a Create connection form, fill in all the details – you may want to check the connection details that you have set up for your Cloud SQL database.

When your connection has been created, you will now see the Eternal connections details in your dataset.

Federated Query

Now you have an External connection created, next we will be making an External Queries to your WordPress Cloud SQL database. I will show you how to make a query, I will do it on a full database. You will notice that I will use a Google SQL statement that is not available in SQL. The SQL I will use is EXTERNAL_QUERY and the parameters are dataset and connections as shown below on my dataset.

SELECT
  *
FROM
  EXTERNAL_QUERY("thunderbeardesign.eu.torbjorn_wordpress",
    "SELECT p.*, ( SELECT guid FROM wordpress.wp_posts WHERE id = m.meta_value ) AS imgurl, (SELECT meta_value FROM wordpress.wp_postmeta pm WHERE meta_key='_wp_attachment_metadata' AND pm.post_id=m.meta_value ) AS imgdetails FROM wordpress.wp_posts p LEFT JOIN  wordpress.wp_postmeta m ON(p.id = m.post_id AND m.meta_key =  '_thumbnail_id' ) WHERE p.post_type =  'post' AND p.post_status =  'publish'");

When I get the result of the query I saw it as a view, that way any new content in my WordPress site will be automatically added to the result.

Data Studio Report

In the data studio I first create a connector to my data source, in my cases I use BigQuery and the data source is my view that I saved.

I then make the layout of my report, I went with table design, with an image and a clickable link. If you want the report to be sent to you in Data Studio you can schedule the report to be sent out.

Conclusion

This is just an example of how you could connect BigQuery to your Cloud SQL database to get access to your data easily and use it for analysis, machine learning or reporting with Data Studio.

All the complexity has been hidden away from the users on these serverless data solutions, and with a few clicks you have set up your environment and you can focus on making something from your dataset.