Google Cloud IoT Core makes it easy to get data from constrained devices to Google’s cloud by giving devices a conversion from HTTP or MQTT messages to Cloud PubSub messages.
Google Cloud SQL is a Google-managed Postgres or MySQL instance that is hosted on Google’s Cloud. Using a proxy, you can securely access the SQL instances using service credentials, just as you would authenticate the Cloud SDK or other services. What’s great about Cloud SQL is on Google-managed Cloud services such as Cloud Run or Cloud Functions, the services will have the connections ready to go with built-in service credentials.
Connecting between Cloud IoT Core and Cloud SQL is as easy as getting your data into Cloud PubSub and then migrating the data to your Cloud SQL instance.
Getting data from a device into Cloud PubSub
To get some data from a device into the Cloud, let’s start with a really basic example, reporting WiFi signal strength from an ESP32.
To follow along with a device, you will need an Espressif ESP32, there are many developer versions available through typical retail channels. Next, install the Arduino IDE, and add the ESP32 community boards to your IDE. If everything worked correctly, the Esp32 should show up when you connect it to your computer.
Using the library manager, install the Google Cloud IoT Core JWT library:
After the library is installed, select the Examples > Google Cloud IoT Core JWT > Esp32-lwmqtt demo. With the tooling in place on your machine you’re ready to setup a device registry and device.
Setup a device registry starting from the Google Cloud IoT Core console. Make sure to also add a subscription to the Cloud PubSub topic configured with your registry by navigating to the Cloud PubSub console, selecting your associated topic, and adding a subscription.
Create a device using the EC public / private keypair generated using the following command:
You will create your device with the public key. The following image shows the device being added in my registry:
Before you go to configure the sample, you will need to convert the private key one more time into a bytes format using another command.
Now you’re ready to setup the demo app with your device credentials. Open the file ciotc_config.h and replace the example values with those for your project and WiFi network. After you’re done, go ahead and save a copy of the example code to another location.
If your sample is configured correctly, you will see the following message in the Arduino serial console when the device connects.
Note that if you’re seeing a disconnect message or other error, your device either has not been registered as expected or one of the parameters is incorrect. It’s easiest to debug this by going through each parameter and ensuring they’re correct before trying to debug further.
By default, the demo will publish messages indicating WiFi strength. You can check that this is working by waiting a few minutes after the sample has started and then pulling the Cloud PubSub topic with the Cloud SDK.
The output will look as follows if everything is working.
Now you’re ready to migrate the data to Cloud SQL.
Migrating the data from Cloud PubSub to Cloud SQL
From the Cloud SQL console page, select the Create Instance button. Make note of your password and so forth. It will take a little time for the Cloud SQL instance to be created so while it’s being setup for you, it’s a great time to open up an instance of Cloud shell which we’ll be using in the next steps to create the database and table for storing data.
After your SQL instance has been initialized and the Cloud shell has been loaded, you can connect to your Cloud SQL instance with the following commands:
If you’re using MySQL, the default database user is the
root user; if you’re connecting to Postgres, the default database user is
postgres. I’ll just demonstrate using MySQL to simplify things. First you will need to create a database to store the WiFi strength, I’ll just call it wifistr.
If the commands succeeded, you should see a description for your new database table.
Now that you have the table created, you’re ready to respond to PubSub messages by turning them into SQL inserts. From your PubSub topic, select the + Trigger Cloud Function button to add a handler for when messages get published to the topic.
Change the Runtime to Python 3.7 and the default handler will just print the message as it comes in. You can try leaving the handler as the default to test that the triggers are working. Just click “View logs” from the Cloud Functions console to see the default messages with the payloads get logged.
Now that you’ve confirmed your messages are reaching Cloud Functions, update requirements.txt to be as follows:
And replace the main.py with the following example code, with your configuration in the variables at the top of the function.
Note that it’s a best practice to use the environment variables instead of using plaintext configuration in the code. After you have deployed the code, go back to the Cloud Shell window and select the rows in your database.
If everything worked, you should now see the WiFi strength getting logged over time in the Cloud SQL database.
Using Cloud Functions and Cloud SQL is a great way to delegate the management of data migration from IoT devices to Google Cloud. This approach requires no code runs on your own machines and still securely enables you to store data in a way that is familiar to you if you’re moving from MySQL or Postgres.
Depending on your data requirements this could get you up and running with analytics from device data.
For more information about Cloud SQL, check out Gabe’s blog post on getting started!