Google Cloud IoT Core to Cloud SQL

Gus Class
Gus Class
Oct 30 · 5 min read

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:

OpenSSL commands for generating device keys

You will create your device with the public key. The following image shows the device being added in my registry:

Example device-id input, ES256 public key format, and EC public contents input into Cloud Console.

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.

Conversion from private key in pem format to text bytes.

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.

Serial output for Arduino showing connected!

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.

Using the Cloud SDK to pull your PubSub topic.

The output will look as follows if everything is working.

WiFi strength as seen in subscription pull output.

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:

Connect to MySQL using the Cloud Shell.

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.

Create a database for storing the WiFi strength.

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:

Cloud Functions example requirements.txt

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.

Selecting the WiFi strength from your Cloud SQL database.

If everything worked, you should now see the WiFi strength getting logged over time in the Cloud SQL database.

Results from select statement showing WiFi signal over time.

Closing thoughts

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!

Gus Class

Written by

Gus Class

All stack, led enthusiast, hecker.

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