This submission comes from one of our community contributors Yitaek Hwang who has put together a nice guide for streaming fitness data into QuestDB with Google Cloud Platform.
Thanks for your contribution, Yitaek!
Thanks to the growing popularity of fitness trackers and smartwatches, more people are tracking their biometrics data closely and integrating IoT into their everyday lives. In my search for a DIY heart rate tracker, I found an excellent walkthrough from Brandon Freitag and Gabe Weiss, using Google Cloud services to stream data from a Raspberry Pi with a heart rate sensor to BigQuery via IoT Core and Cloud Dataflow.
Although Cloud Dataflow supports streaming inserts to BigQuery, I wanted to take this opportunity to try out a new time-series database I came across called QuestDB. QuestDB is a fast open-source time-series database with Postgres and Influx line protocol compatibility. The live demo on the website queries the NYC taxi rides dataset with over 1.6 billion rows in milliseconds, so I was excited to give this database a try. To round out the end-to-end demo, I used Grafana to pull and visualize data from QuestDB.
In this tutorial, we will use a Debian image and a Python script to send simulated sensor data through IoT Core. If you wish to send real sensor data from a Raspberry Pi, the kit listed above contains everything you need along with install instructions. If you have a Raspberry Pi without the kit, you can directly swap it out for the VM instance below and run the Python script provided or add your own inputs.
#Google Cloud Setup
In order to use Cloud IoT Core and Cloud Pub/Sub, you need to first create a
Google Cloud Platform account and a new project (mine is called
questdb-iot-demo ). Navigate to
APIs & Services -> Enable APIs and Services -> Search for APIs & Services
and enable the following APIs:
- IoT Core
- Compute Engine
IoT Core is Google's fully-managed IoT service to help securely connect and
manage IoT devices. In this demo, we will create a registry called
and send MQTT data. Click on Create Registry and set the Registry ID and
Region based on the geographic region closest to you (for me it was
Next, we need to configure a Pub/Sub topic to publish device data to. Under
"Select a Cloud Pub/Sub topic", click on Create a Topic and give it the
Once the registry properties and Cloud Pub/Sub topics are configured, click on Create
Now it's time to add our simulated device. In order for our device to communicate with IoT Core, we need to add a public key. Head over to Compute Engine -> Create.
The default options (
e2-medium, Debian 10 image,
us-central1) will work for
our simulator. Make sure to match the region with the IoT Core registry region
if you chose something other than
us-central1. Once the VM is ready, click on
the SSH button under "Connect" and install the project code with the
Finally, we need to create a subscription to our Pub/Sub topic to pull messages
and insert into QuestDB. Head over to "Pub/Sub" and click on our
topic. Give the subscription the name
questdb and click Create.
#IoT Device Setup
Once you have the
ec_public.pem key, head back to the IoT Core Registry. Under
"Devices", click on Create a Device. For Device ID, enter
raspberryHeartRate and expand the Communication, Cloud Logging,
Under Authentication, change the Public key format to
ES256, paste in the
key from our VM or Raspberry Pi and click "Create":
At this point, we have everything on Google Cloud to send data to our Pub/Sub topic. Now we need to write some code to take those messages and insert them into QuestDB. Let's start by starting up QuestDB via Docker.
The above command pulls the latest QuestDB image (v5.0.6) and maps port
for the console UI and port
8812 for Postgres connections. By giving the
container the name
questdb-heartrate we can refer to the container later on
after it has been stopped and the heart rate data is persisted:
Open up the QuestDB console at
http://127.0.0.1:9000/ and create our
Note: If you don't see the
heart_rate table populated on the tables and
schema explorer panel, click on the refresh icon above the tables.)
#Pub/Sub to QuestDB
Since there's no native integration for Pub/Sub, we will need to write a simple program to listen to new Pub/Sub messages and insert the data into QuestDB. I'm using NodeJS v14.15.4, but you can use similar client libraries for Pub/Sub and Postgres to achieve the same.
First, configure the
gcloud SDK to authenticate with your GCP project without
having to download a service account (see
Simple GCP Authentication with Service Accounts
for more details).
Next, create a new NodeJS workspace and install @google-cloud/pubsub and pg packages. You can use the code below to listen to Pub/Sub and stream to QuestDB:
Finally, we are ready to send the simulated data. Switch back to the Compute Engine and ssh into the VM again. Issue the command below to send the data to our IoT Core device:
If successful, you should see some logs like:
Now run our NodeJS code and we should see data populated in QuestDB:
#Visualizing Data with Grafana
Although QuestDB console provides some default visualizations out of the box, to simulate a scenario of combining all the metrics, we'll set up a Postgres data source and visualize our heart rate data. To start Grafana from Docker, run the following:
http://localhost:3000/login using the
default credentials (
admin). Under Configuration -> Data Sources,
search for Postgres:
Provide the following login credentials (password:
quest), ensure SSL is
disabled and click Save and Test:
Finally, let's create a dashboard. Add a panel to the Grafana dashboard and add the following SQL query:
If all is working as expected, we can now see the sample heart rate data:
If we run the same query on QuestDB, we have the option to visualize the same data points there for comparison using the Chart feature:
At this point, we have an end-to-end system of a device securely sending data
via IoT Core and streaming data into QuestDB. We can extend this example to
multiple devices by adding them under IoT Core and scaling our server to using
pooled connections to more efficiently add data to QuestDB. At scale, we can
also look at aggregates instead of raw data points, (e.g.
avg(heartrate) as avg_hr from heart_rate SAMPLE BY 1d)
If you like this content, we'd love to know your thoughts! Feel free to share your feedback or just come and say hello in the QuestDB Community Slack.