AWS IoT Exploration – Part II – Device Data Interaction and Storage

Continuing with our AWS IoT explorations, in this post we see how to store incoming data from the IoT Device in a DynamoDB table using AWS IoT rules. Part 1 of this series details how to connect and authenticate a device with the AWS IoT platform.

Create a DynamoDB table

• For storing device data in DynamoDB, we must create a DynamoDB table with the following attributes,
• In the primary key section add sample_time. This is the time at which the sample was recorded.
• In sort key section add device_id which indicates the device that provided the sample
• device_data is the data received from the device and formatted by the rule query statement

Open the DynamoDB console, and select Create table.

Under Create DynamoDB table

• In the Table name section, enter the table name: Iotdevice_data.
• Under Primary key, in the Partition key, enter sample_time, and choose Number in the option list available adjacent to the field.
• Then check Add sort key.
• Below the Add sort key, enter device_id, and choose Number in the option list next to the field,
• Now choose Create at the bottom last.
• device_data can be defined while configuring the DynamoDB rule action.

Creating an AWS IoT rule for sending data to the AWS DynamoDB table

To store the IoT device data in the database table, use the rule query statement to format the device data. Here is sample data received from a weather sensor device in JSON format,

{
  "temperature": 22,
  "humidity": 84,
  "barometer": 1233,
  "wind": {
    "velocity": 32,
    "bearing": 245
  }
}

To store above data in the DynamoDB table, we use rule query statement to restructure the data as follows,

{
  "temperature": 22,
  "humidity": 84,
  "barometer": 1233,
  "wind_velocity": 32,
  "wind_bearing": 245
}

We can use Substitution templates while creating this rule. Substitution templates are expressions that allow us to insert dynamic values from functions and message payloads.

• Enter the Rules hub of the AWS IoT console.
• Choose Create in the Rules section
• Under Name, enter the rule’s name, Iotdevice_data_ddb. This name is unique within the AWS account and Region, and should not contain any spaces, however underscores are allowed.
• In the Description section, list the purpose of our rule. Enter a meaningful description that outlines the purpose of the rule.
• Under Create Rule’s, in Rule query statement section, In Using SQL version field, select 2016-03-23.
• Enter the following query in the Rule query statement edit box,

SELECT temperature, humidity, barometer,
wind.velocity as wind_velocity,
wind.bearing as wind_bearing,
FROM 'device/+/data'

• This statement listens for MQTT messages coming from the topic that matches the device/+/data topic filter.
• The wind attributes elements will be formatted as individual attributes. (e.g., wind_velocity and wind_bearing)
• The temperature, humidity, and barometer attributes will remain unchanged.

• Under Set one or more actions section,
  • To see the list of actions for this rule, choose Add
  • Choose Insert a message into a DynamoDB table
  • Then choose Configure action, at the bottom of the action list, to open the selected action’s configuration page
• In the Configure action section,
  • Under Table name, choose the DynamoDB table name from the list created in the above step: Iotdevice _data. Other fields such as Partition key, Partition key type, Sort key, and Sort key type are filled with values from the DynamoDB table.
  • Under Partition key value section, enter ${timestamp()}. In this rule, instead of using direct values from the message payload, it will use a Substitution template and return the value from the time stamp function.
  • Under Sort key value, enter ${cast(topic(2) AS DECIMAL)}. This is the second substitution template that will insert the second element value in topic name (i.e device_id) after casts to a decimal value to match the numeric format of the key.
  • Under Write message data to this column, enter device_data. This will create a new column with name device_data in the DynamoDB table.
  • Leave the Operation section empty.
  • Choose Create Role from the Choose or create a role to grant AWS IoT access to perform this action.
  • Under Create a new role section, enter Iotdevice _ddb_role, then choose Create role.
  • Choose Add action at the bottom of Configure action and choose Create rule at the bottom of Create a rule to create the rule.

Testing the AWS IoT Rule and DynamoDB table insertions

Using an MQTT client we can test the new rule to publish and subscribe to MQTT messages.

In the left navigation of AWS IoT console

• Choose MQTT client, then select Subscribe to a topic, add device/+/data and subscribe
• For Topic filter, enter the previously created topic, device/+/data in topic section
• Choose Subscribe.

With a specific device ID, device/22/data, publish a message to the input topic. You cannot publish to MQTT topics that contain wildcard characters.

• In the MQTT client section, choose Publish to a topic.
• Enter the input topic name, device/22/data for Topic name,
• enter the following sample data, for Message payload

{
 “temperature": 22,
 "humidity": 84,
 "barometer": 1233,
  "wind": {
    "velocity": 32,
    "bearing": 245
  }
}

• Choose Publish, to publish your MQTT message and Subscribe to a topic in the MQTT client section.
• Add device/+/data under the Subscribe column. Check if the sample data from the previous step appears there.

Check the row in the DynamoDB table created by your rule.

• Enter the DynamoDB Tables hub in the AWS IoT console, and select Iotdevice, then choose the Items tab in that window.
• Now we can see that the sample_time values in the table are linked. Send the first message, and we can see it will be the only one in that list that contains all the data
• Expand the device_data, if you want to see the data that resulted from the rule query statement. We can also edit this data.
• Choose Save, if you want to save the modifications or choose Cancel to exit without saving any changes

Sending Device Data in JSON format to DynamoDB table.

Data stored in DynamoDB successfully.

About the author

Rasmi Bhuyan

Rasmi has a Bachelor’s degree in Science and has been working with Excellarate (now Synerzip) for close to a year. He is an IoT enthusiast and has been tinkering with different platforms recently.