IoT Tank Monitoring Solution Part 3 — Visualizing data using BigQuery Federated Queries, CloudSQL and Data Studio

End to end solution to track tank level using cloud computing without having to worry too much with managing infrastructure.

Alvaro Viebrantz
Jan 6 · 4 min read

This is a 3 part tutorial on how to create a farm tank monitoring solution on Google Cloud.

In this latest part we are going to visualize our models data together with the telemetry data sent by the device. We are going to be using a feature on BigQuery called Federated Queries, which basically allows us to query external data inside of BigQuery, mixing different data sources and building our Data Lake more easily.

BigQuery Federated Queries is in beta right now and accepts connecting to Cloud SQL, CSV files in Google Cloud Storage and Google Cloud Big Table.

Set up Federated access to our Cloud SQL Database

Them we choose Cloud SQL datasource and add our database instance information, we used that same configuration ( instance connection, username, password and database name ) to deploy our Django Rest backend on Cloud Run. One additional configuration here is the connection name, to be used on BigQuery to query our external data. I called mine `tank-monitoring`.

And that’s basically it, now we can query Cloud SQL data from BigQuery using the `EXTERNAL_QUERY` command. Here is an example of getting the farm list from our Cloud SQL database using BigQuery ( you can test that in the BigQuery UI:


select farms.*
from EXTERNAL_QUERY(“[YOUR_PROJECT_NAME].us.tank-monitoring.farms”) farms

Now let’s build our dashboard using both data sources.

Building dashboard on Data Studio

SELECT 
device.deviceId,
tank.id as tankId,
tank.name as tankName,
tank.height,
farm.id as farmId,
farm.name as farmName,
JSON_EXTRACT(telemetry.data, ‘$.distance’) as distance,
if(tank.height > 0, 100*(tank.height — CAST(JSON_EXTRACT(telemetry.data, ‘$.distance’) as float64))/tank.height, 0) as level,
telemetry.time
FROM
EXTERNAL_QUERY(“[YOUR_PROJECT_NAME].us.tank-monitoring”, “SELECT * FROM tank_monitoring_farm;”) farm
left outer join EXTERNAL_QUERY(“[YOUR_PROJECT_NAME].us.tank-monitoring”, “SELECT * FROM tank_monitoring_tank;”) tank
on tank.farm_id = farm.id
left outer join EXTERNAL_QUERY(“[YOUR_PROJECT_NAME].us.tank-monitoring”, “SELECT * FROM tank_monitoring_device;”) device
on device.tank_id = tank.id
left outer join `[YOUR_PROJECT_NAME].tank_monitoring_dataset.device_telemetry` telemetry
on CAST(device.id as string) = telemetry.device_id
where telemetry.time between PARSE_TIMESTAMP(‘%Y%m%d’,@DS_START_DATE) and PARSE_TIMESTAMP(‘%Y%m%d’,@DS_END_DATE)
order by telemetry.time

Go to Data Studio to get started creating the dashboard and click on create Blank Report.

Them, create a new Data Source, search for BigQuery connector and select it.

On the next screen, choose Custom Query > [YOUR_PROJECT_NAME] > Enter Custom Query. Here copy our SQL query joining Cloud SQL DB and BigQuery. Enable date parameters to fill the `DS_START_DATE` and `DS_END_DATE` parameters. Them click connect.

The rest is mostly dragging and dropping some components to build your dashboard. I’ll not go thought each step, but after getting you data on Data Studio, it should be pretty straightforward to build the same thing.

Conclusion

Google Cloud - Community

A collection of technical articles published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Alvaro Viebrantz

Written by

Product Engineer @Leverege & Google Developer Expert for IoT. When I'm not cooking, I'm building fun stuff or helping my local dev community. GDG organizer.

Google Cloud - Community

A collection of technical articles published or curated by Google Cloud Developer Advocates. The views expressed are those of the authors and don't necessarily reflect those of Google.

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade