How to generate and ingest sensor data using Azure IoT Hub and CrateDB Cloud

2020-06-24, by Carlota Soto

To be able to operate industrial IoT in the cloud solves many problems for manufacturers. The power of the cloud gives the industry the keys to the kingdom: immediate scalability, inter-connectivity, and simple integration.

In industrial IoT, thousands of sensors are ingesting data in real-time; to establish efficient connections between all these devices and the cloud might seem challenging, but the good news is that technology is advancing fast. To run industrial IoT in the cloud is now more accessible than ever, and there are many integrated solutions already existing in the market.

The partnership between Crate.io and Microsoft Azure is a good example of this. CrateDB Cloud, a database purpose-built to handle industrial IoT datasets, is fully integrated into the Azure ecosystem. At the same time, Azure IoT Hub takes care of the communication between the sensors and the IoT application, making it easy to build an efficient integration between the edge and the cloud.

In this tutorial, we will present how to integrate CrateDB Cloud with Azure IoT Hub. To show how they would operate together in a real industrial IoT application, we will also simulate a virtual stream of sensor data using the Device Simulation Solution Accelerator. The IoT Solution Accelerators are very useful tools to mimic IoT applications in a realistic way, producing sensor data and events that can then be ingested by CrateDB. Let's get started.

Generating sensor data 

As the first step, go to the New Device Simulation page and enter the required information:

Click Create. To deploy the simulation will take 15 or 20 minutes. When it’s ready, you’ll receive a notification in your mailbox.

Go to the Device Simulation interface, by clicking My solutions -> Launch. Then open a new simulation and configure it as you please.

In our example, the simulation will last 10 minutes. For the device model, we chose Chiller—a model able to simulate data from temperature, pressure, and humidity sensors. The number of virtual sensors was set to 10, with each one of them sending one message per second.

When you finish setting up your simulation, click Start simulation. Azure will then start working on it, displaying a screen like this when it’s done:

The simulation produced 520 total messages (events). Once Azure IoT Hub is configured, we’ll push these events to CrateDB.

Configuring Azure IoT Hub

Go to the Azure Portal. In Resource Groups (use the search bar at the top) you’ll find a new resource group available with the same name you used in your device simulation:

All your resources will be displayed. The next step is to adjust the configurations of the IoT Hub and Storage account resource types (to identify them, look at the TYPE column).

As you can see on the screen above, our IoT Hub resource has the name iothub-oo6xk. Select your IoT Hub resource and go to Settings -> Built-in endpoints. Go to Consumer Groups, and create a new one. Then, use the name of your IoT Hub resource to build the EventHub-compatible endpoint URL. (You will need this URL in a later step)

Now, choose Message routing from the menu to add a new route, and give the route a name. Choose the endpoint events, and set the data source as Device Telemetry Message.

To set up the right configuration for the storage account, return to the resource group and create a new storage account using the settings in the screenshot below:

To checkpoint data from the events queue, we’ll use blob storage. With your storage account in place, create a blob storage. Next, go to Settings -> Access keys, copy both the storage account name and the blob storage connection string, and you’re done.

Deploying a cluster in CrateDB Cloud

You can interact with CrateDB Cloud using Croud. Install Croud by running the following command:

And log in:

A browser window will open; use it to log into your CrateDB account. If you don’t have one, just sign up by clicking the link.

Structurally, all CrateDB Cloud accounts belong to an organization. Each organization can have multiple projects, each project can have multiple CrateDB clusters, and each of those clusters can have multiple event customers:

Organization → project →  cluster → consumers

So, as a first step, we need to create an organization. Do it with this command:

The table above displays your organization id. Now, you’re ready to create a project. Do it by running the next command, making sure to replace ORG_ID with your organization id:



Now, the output table gives you the project id.  So let’s deploy a CrateDB cluster: make sure to replace PROJECT_ID with your project id, and USERNAME and PASSWORD with your CrateDB admin UI username and password.



The command will return the output table right away, but CrateDB Cloud will need a few minutes to complete the deployment of the cluster. You can type the cluster URL (you’ll find it in the table) in a browser to check if the cluster is available; if it is, it will take you to the cluster’s admin UI.

To login into the admin UI, use the same username and password you used when setting up the cluster. Your interface will be similar to this:

Ingesting the data: connecting CrateDB Cloud to Azure IoT Hub

Now we’re ready to move the virtual sensor data we generated earlier.

The first step is to create a table in CrateDB to hold the data. In order to do so, select Console in the CrateDB UI and enter the following command:

This command instructs that sensor data should be modeled as a dynamic object, named payload, which is able to handle arbitrarily structured sensor readings. It also makes it possible to partition the table by week using an event timestamp. This partitioning is key in increasing CrateDB’s query speed since it reduces the total number of records to process. To create the table, select EXECUTE QUERY.

Once the table is done, we’re ready to receive data from Azure IoT Hub. In order to do that, deploy an event consumer in CrateDB Cloud using the command below, making sure you:

  • Replace PROJECT_IDand CLUSTER_ID with your information
  • Replace EVENTHUB_DSN with the EventHub-compatible connection string
  • Replace CONSUMER_GROUP with the name of the consumer group you used in the Azure IoT Hub endpoint
  • Replace STORAGE_DSN with the correct blob storage connection URL
  • Replace STORAGE_CONTAINER with your Azure storage account name

If the command is successful, the data will flow from the Device Simulator into CrateDB Cloud.

Verify that everything is working by going to the Tables screen in the CrateDB UI:

To view the simulated data, select QUERY TABLE. You can now view the data on humidity, temperature, and pressure produced by the simulated IoT sensors.

Now it's time to play with it!

 

 


CrateDB Cloud is a fully-managed database-as-a-service, operated by the Crate.io engineers. It is a distributed SQL database purpose-built for complex machine data operations, perfect for running industrial IoT in the cloud.

Want to explore more? Check out our Resource Library!

 
Newsletter

Stay up to date

Sign up here to keep informed about CrateDB product news,
events, how-to articles, and community update.