Connecting Azure IoT Hub and CrateDB Cloud for the Ingestion of Sensor Data
Tags: timeseriesdatabase, data, database, azure
Published: 2019-06-26 08:22:27

---
title: Connecting Azure IoT Hub and CrateDB Cloud for the Ingestion of Sensor Data
published: true
tags: time-series-database,data,database,azure
canonical_url: https://crate.io/a/connecting-azure-iot-hub-and-cratedb-cloud-for-the-ingestion-of-sensor-data/
---

Thanks to the rise of the [_Internet of Things_](https://en.wikipedia.org/wiki/Internet_of_things) (IoT), vast amounts of machine data is being generated by things like smart factories and smart cities. Accordingly, there is a demand for linearly scalable [time-series](https://en.wikipedia.org/wiki/Time_series) databases that can handle data ingestion and processing at this scale.

[Microsoft Azure](https://azure.microsoft.com/en-us/?WT.mc_id=azuremedium-blog-brborges) and [CrateDB](https://crate.io/products/cratedb/) (a distributed SQL database) are an excellent fit for such applications. In fact, we recently published a blog post titled [Supercharge your Azure Internet-of-Things and Time-Series Data Applications](https://crate.io/a/supercharge-your-azure-internet-of-things-and-time-series-data-applications/).

Here’s a high-level view of what a [container ready](https://en.wikipedia.org/wiki/Container_%28virtualization%29), [horizontally scalable](https://stackoverflow.com/a/11715598), and [highly-available](https://en.wikipedia.org/wiki/High_availability) infrastructure using Azure and CrateDB might look like:

![](https://cdn-images-1.medium.com/max/1024/0*NsE7vLFIHyjkZtEY.png)

Let’s break that down:

1. The data being generated by your edge devices (e.g., sensors in a smart factory) is ingested by the Azure IoT Hub.
2. From there, you can use CrateDB (hosted on Microsoft Azure) to query and analyze the data in real-time.
3. Then, generate reports using Power BI or plug-in Grafana for data visualization tailored to time-series and machine data.

In this post, I will show you how to:

- Launch a CrateDB cluster on Azure,
- Connect it with an IoT Hub, and
- Simulate sensor data to be ingested with the help of an Azure IoT Solution Accelerator

### Simulate sensor data

First of all, we need to decide what the data looks like before we can start to generate and push it to the Azure IoT Hub.

Smart factories have a ton of sensors, taking measurements for a wide variety of parameters.

Data structures differ from sensor to sensor, but we can model these with a single table using [dynamic objects](https://crate.io/docs/crate/reference/en/latest/general/ddl/data-types.html#dynamic) that can be queried to an arbitrary depth. It’s not what we’d recommend for a production setup, but it makes things easier for demonstration purposes.

Azure IoT [Solution Accelerators](https://azure.microsoft.com/de-de/features/iot-accelerators/) provide a library of preconfigured templates to speed up your IoT projects.

The [Device Simulation](https://www.azureiotsolutions.com/Accelerators#/sdssolutions/new/device-simulation) accelerator allows you to simulate various types of IoT Devices. These devices will produce data that we can then ingest into CrateDB.

To create one, go to the [_New Device Simulation_](https://www.azureiotsolutions.com/Accelerators#/sdssolutions/new/device-simulation) page, provide a deployment name, and select the appropriate Azure subscription, deployment options, and Azure location.

Here’s what my setup looked like:

![](https://cdn-images-1.medium.com/max/1024/0*xNY5-P74EDEuuQ8u.png)

I gave my device simulation the name `CrateDBIngest`, selected my `crate-development` Azure subscription, selected _Provision new IoT Hub_, and then selected _West Europe_ as my Azure location.

When you’re done, select the _Create_ button to start the deployment process.

It should take about 15 to 20 minutes to deploy the new device simulation. And once complete, you will be notified via email.

The solution accelerator page should now look like this:

![](https://cdn-images-1.medium.com/max/1024/0*JVdHYr1UwJ3bFU-O.png)

From here, select the _My Solutions_ tab at the right-hand side of the top navigation bar and then select the appropriate _Launch_ button.

Once inside the solution accelerator, select _New Simulation_ and enter some information about your simulator:

![](https://cdn-images-1.medium.com/max/1024/0*mA3xnA47zfofr6vE.png)

I named the simulation `SensorDevice` and set the simulation to end 10 minutes after starting. Then, I selected the _Chiller_ device model, which basically sends simulated telemetry data on temperature, pressure, and humidity.

I set up 10 of these devices and configured them to send one message per second in aggregate.

Finally, I chose to use a pre-provisioned IoT Hub for this and set the throttling limit at _S2 Standard_.

When you’re ready, select the _Start simulation_ button. Azure will start running the simulation, and you should see a screen like this:

![](https://cdn-images-1.medium.com/max/1024/0*eBWLftc5_zGvix7X.png)

Here, we see that 520 messages were created during the 10 minutes the simulation ran. These messages (also known as [events](https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-about)) will be pushed to CrateDB once we are finished with the whole setup.

### Configure the Azure Portal

Now, let’s go to the [Azure Portal](https://portal.azure.com/) and configure a few things.

In the search bar on the top of the Azure Portal screen, search for _Resource Groups_. A new resource group should have been created for you using the same name as the deployment name of the solution accelerator. (This is not the case if you manually chose to deploy the solution accelerator to an existing resource group.)

My device simulation was named `CrateDBIngest`, so my resource group was also named `CrateDBIngest`:

![](https://cdn-images-1.medium.com/max/1024/0*yQAhc7mdgPkZe1uV.png)

There are 13 resources listed in this resource group. We need to alter the configuration for the _IoT Hub_ and the _Storage account_ resource types (see the _TYPE_ column).

### IoT Hub

In the screenshot above, you can see that my IoT Hub resource is named `iothub-oo6xk`.

Select your IoT Hub resource. Then, go to the _Built-in endpoints_ option on the left-hand side in the _Settings_ section and create a new consumer group under the _Consumer Groups_ section.

I chose the name `cratedbingest` for my [consumer group](https://docs.microsoft.com/en-us/azure/event-hubs/event-hubs-features#consumer-groups).

Make a note of the name you chose and copy down the listed _EventHub-compatible endpoint_ URL. You will need both in a later step (marked in red below).

![](https://cdn-images-1.medium.com/max/1024/0*ZaCBI4OYTJKKfHWU.png)

Once you’ve done that, select _Message routing_ from the menu list and add a new route. Name the route (I went with `cratedbingest` again). Then, select the endpoint _events_, set _Device Telemetry Message_ as the data source, and save your changes.

### Storage account

Now, go back to the resource group and create a new [storage account](https://docs.microsoft.com/en-us/azure/storage/common/storage-account-overview) with the following settings:

![](https://cdn-images-1.medium.com/max/1024/0*FD0cl-aePKA3sufa.png)

Note: You could have used the pre-created storage account created by the solution accelerator. However, it might make sense to have the data in separate storage accounts as deleting the solution accelerator in the future would require you to create a new one for other use cases.

We’re going to use [blob storage](https://azure.microsoft.com/en-us/services/storage/blobs/) to [checkpoint](https://en.wikipedia.org/wiki/Application_checkpointing) data from the events queue.

Once the storage account has been created, create a blob storage. Then, go to the _Access keys_ option in the _Settings_ section and copy the name of the storage account as well as the blob storage connection string (marked in red below).

### Set up CrateDB Cloud

Okay, at this point, your device simulator should be set up on Microsoft Azure, and you should have the following information:

- The name of your Azure consumer group
- The consumer group EventHub-compatible endpoint URL
- The name of the storage account you’re using for Azure blob storage
- The blob storage connection URL

With this in hand, you can proceed to set up CrateDB Cloud.

You can interact with CrateDB Cloud using [Croud](https://crate.io/docs/cloud/cli/en/latest/), a _command-line interface_ (CLI) tool.

Install Croud, like so:

```
$ pip install croud
```

Then, log in:

```
$ croud login
```

This will open up a browser window so that you can sign in to your CrateDB Cloud account:

![](https://cdn-images-1.medium.com/max/1024/0*_Ra2zwQIlo9eHgab.png)

If you do not currently have a CrateDB Cloud account, you can create one by selecting _Sign up_ on this screen.

A CrateDB Cloud account is organized like this:

![](https://cdn-images-1.medium.com/max/1024/0*l0WsfA7Gz_wQ0_xF.png)

Let’s break that down:

- At the top level, all accounts must belong to an organization
- Organizations can have multiple projects
- Projects can have multiple CrateDB clusters
- CrateDB clusters can have multiple event consumers

### Deploy a CrateDB cluster

First of all, create a new organization:

```
$ croud organizations create --name "CrateDBIngest" --plan-type 1 

+--------------------------------------+---------------+-----------+  
|id                                  | name          |   plan_type |                         
|--------------------------------------+---------------+-----------|  
|8a8e5841-ad1d-4519-8683-138a08b73130| CrateDBIngest | 1           | 
+--------------------------------------+---------------+-----------+
```

Now, create your first project:

```
$ croud projects create --name "CrateDBIngest" --org-id ORG_ID

+----------------------------------------------+
| id                                           |
|----------------------------------------------|
| d24b6665-9719-42e8-9876-9b7f300dd159         |
+----------------------------------------------+
```

Here, replace `ORG_ID` with your organization ID (given in the output from the command you ran above.

The output table returns the project ID (in my case, `d24b6665-9719-42e8-9876-9b7f300dd159`).

Now, deploy your first CrateDB cluster:

{% gist https://gist.github.com/tanay1337/57e03843970ed8b32af63e8569a0f81a %}

Let’s break that down:

- We’re using the `cratedb.az-gp1` product at the `xs` (extra small) tier. If you are interested in the available options, please contact us.
- Cluster sizes are measured in _units_, with one unit comprising three nodes. Here, we are deploying CrateDB version 3.3.3 in three node cluster (i.e., one unit) named `cratedbingest`.

Additionally:

- Replace `PROJECT_ID` with your project ID.
- Replace `USERNAME` and `PASSWORD` with the username and password you wish to use for the [CrateDB admin UI](https://crate.io/docs/clients/admin-ui/en/latest/).

If successful, the clusters deploy command will output a table with the following cluster information:

The command will return this information immediately, but it will take a few minutes for CrateDB Cloud to actually deploy the cluster.

You can check to see whether the cluster is up-and-running yet by visiting the cluster URL in your browser.

Once the cluster is running, the cluster URL will allow you to access your cluster’s admin UI. From here, you can authenticate with the username and password you specified during cluster creation.

After authenticating, you should see a screen like this:

![](https://cdn-images-1.medium.com/max/1024/0*l8vo6TqM7dKtBH_4.png)

### Create a sensor events table

Before you can consume the simulated events we previously generated, you must create a table to hold the sensor data.

In the CrateDB admin UI, navigate to the from the left-hand navigation menu. Then, copy and paste the following statement:

{% gist https://gist.github.com/tanay1337/6bdc0dee69af6a383dea05dfa1b03dff %}

Here, data from the sensor is modeled as a dynamic object (named `payload`) that can handle sensor readings with any arbitrary structure.

As well as recording the event timestamp, we also generate a timestamp for the beginning of the current week. This is used to [partition](https://crate.io/docs/crate/reference/en/latest/general/ddl/partitioned-tables.html) the table by week, which can be used to dramatically speed up date-ranged queries by limiting the total number of records that need to be processed.

Select _EXECUTE QUERY_ to create the table.

### Deploy a CrateDB event consumer

Now that you have a table to record sensor events, you can subscribe to your Device Simulation IoT Hub to receive events with a CrateDB event consumer.

You can deploy an event consumer with a command like this:

{% gist https://gist.github.com/tanay1337/5baae4182408a4a04d3a5695e3508559 %}

Let’s break that down:

- We’re using the `eventhub-consumer` product at the `xs` (extra small) tier. Like before, we'll give you information about the available products and product tiers during your account setup. (This information will be made public soon.)
- We are deploying a consumer named `cratedbingest` that will write to the previously created `raw` table in the `doc` [schema](https://crate.io/docs/crate/reference/en/latest/general/ddl/create-table.html#schemas).

Additionally:

- Replace `PROJECT_ID` and `CLUSTER_ID` with the appropriate project ID and cluster ID.
- Replace `EVENTHUB_DSN` with the [EventHub-compatible connection string](https://devblogs.microsoft.com/iotdev/understand-different-connection-strings-in-azure-iot-hub/) (copied down in a previous step) used by the Azure IoT Hub endpoint you configured earlier.
- Replace `CONSUMER_GROUP` with the name of the consumer group (copied down in a previous step) used by the Azure IoT Hub endpoint you configured earlier.
- Replace `STORAGE_DSN` with your blob storage connection URL (copied down in a previous step).
- Replace `STORAGE_CONTAINER` with the name of your Azure storage account (copied down in a previous step).

If this command is successful, the data generated by your Azure device simulator will start flowing into CrateDB.

You can verify this has worked by using the left-hand navigation menu in the CrateDB admin UI to access the [Tables](https://crate.io/docs/clients/admin-ui/en/latest/tables.html#tables-browser) screen.

You should see something like this:

![](https://cdn-images-1.medium.com/max/1024/0*UdE1fk7GE4yHe243.png)

Here, you can see the raw table holds 560 records.

If you select _QUERY TABLE_, you can query the table and see what your simulated data actually looks like:

![](https://cdn-images-1.medium.com/max/1024/0*eibMF-IDlmLQAwQZ.png)

Here, you can see that each sensor reading includes information about humidity, temperature, and pressure.

### Wrap-up

In this post, I showed you how to use Microsoft Azure to generate an event stream from a simulated IoT sensor. I then showed you how to deploy CrateDB cluster and subscribe it to that event stream using the Azure IoT hub.

Got a question? Give me a shout on [Twitter](https://twitter.com/tanay1337), and I’ll see how I can help.

_Originally published at_ [_https://crate.io/blog_](https://crate.io/a/connecting-azure-iot-hub-and-cratedb-cloud-for-the-ingestion-of-sensor-data/) _on June 17, 2019._

* * *