Storing ESP8266 data into Amazon DynamoDB using AWS IoT Core(MQTT) & Arduino

Overview:

In this tutorial, we shall discuss how to store ESP8266 data into the Amazon DynamoDB using AWS IoT Core(MQTT) &  ArduinoIDE In the previous article of this series I walked through how to connect a device like the ESP8266 to the AWS IoT Core service without the use of hard-coded credentials, and publish a message “Helloworld” to it over a secure connection like MQTT Protocol. Similarly, in this article, I will be generating JSON format data which contains mac_Id of ESP8266, a Random 4digit number, a random 4 char string, then publish this JSON data to the AWS IoT Core services using MQTT Protocol. After that, we will create an Amazon DynamoDB rule to fetch data from the MQTT Topic and pass to the Amazon DynamoDB, Where this data is segregated and saved in the different columns.

This tutorial is also available in the video format, you can either pause reading this article and watch the video or continue to read the below-written article.

Introduction to AWS IoT Core:

Internet of Things (IoT) is being integrated with almost every device nowadays. There is a number of hardware and software IoT platforms are available in the market for building IoT based application. In my previous article, I have explained how to interface DHT22 with NodeMCU and post the Temperature and Humidity to the Thingspeak webserver. Likewise, we can interface sensors to the hardware development kits like ESP32, ESP8266, Raspberry Pi, Particleboards( Aargon, Boron, Xenon) and post data to the clouds like Thingspeak, Ubidots, AWS IoT Core, Microsoft Azure.

Amazon is not only in e-commerce but also focusing on IoT and providing cloud-based service named as AWS IoT. Here, AWS IOT stands for Amazon Web Service Internet of Things. This service allows us to connect our devices to the internet for processing, operating and exchanging data securely. Along with AWS IoT, the Amazon Web Services also provides tons of other features like virtual machine deployment, web-hosting, etc.

Requirements for this Tutorial :

1. NodeMCU-ESP8266     Buy from Amazon
2. Data Cable                    Buy from Amazon
3. An active account on Amazon Web Services(AWS).
4. Arduino IDE installed on your computer with ESP8266 board configuration

Steps involved in this tutorial:

1. Setting up Amazon DynamoDB & Creating a rule to fetch data from the MQTT topic then pass to DynamoDB.
2. Implementation of code to post mac_Id, random_number, randon_string from ESP8266 to AWS
3. Demo: Data Storing into the Amazon DynamoDB

Before diving into this tutorial make sure you have completed the following two steps because this tutorial is a continuation of the following to tutorials.

1. Creating a Thing in AWS
2. Connecting NodeMCU (ESP8266) with AWS IoT Core.

1. Creating an Amazon DynamoDB rule and Setting up a DynamoDB data table.

What is AWS Rule? what it does???

An AWS IoT rule consists of an SQL SELECT statement, a topic filter, and a rule action. Devices send information to AWS IoT by publishing messages to MQTT topics. The SQL SELECT statement allows you to extract data from an incoming MQTT message. The topic filter of an AWS IoT rule specifies one or more MQTT topics. The rule is triggered when an MQTT message that matches the topic filter is received on a topic. Rule actions allow you to take the information extracted from an MQTT message and send it to another AWS service. Rule actions are defined for AWS services like Amazon DynamoDB, AWS Lambda, Amazon SNS, and Amazon S3. By using a Lambda rule, you can call other AWS or third-party web services. For a complete list of rule actions, see AWS IoT Rule Actions.

Creating an Amazon DynamoDB rule.

DynamoDB rules allow you to take information from an incoming MQTT message and write it to a DynamoDB table.

Creation of Amazon DynamoDB rule:

• In the AWS IoT console, in the navigation pane, choose Act.
• On the Rules page, choose to Create a rule.
• On the Create a rule page, enter a name and description for your rule, Description is optional.
  Note: AWS does not recommend the use of personally identifiable information in rule names or descriptions.
• Under Rule query statement, choose the latest version from the Using SQL version list. For Rule query statement, enter:

  select *, timestamp() AS ts from 'outTopic'

  ("select *" specifies that you want to send the entire MQTT message that triggered the rule & “timestamp() AS ts" specifies that  “timestamp()” will be copied to  “ts”   "from 'outTopic'" tells the rules engine to trigger this rule when an MQTT message whose topic matches this topic filter is received.

  Choose Add action.

  

• On Select an action, choose to Insert a message into a DynamoDB table and then choose Configure action.
  

• On Configure action, choose to create a new resource.

  

  Creation of Amazon DynamoDB

• On the Amazon DynamoDB page, choose Create table.
• On Create DynamoDB table, enter a name. In Partition key, enter mac_Id. Select Add sort key, and then enter ts in the Sort key field. Choose String for mac_Id (partition Key) and choose Number for ts (sort key) and then uncheck Use Default settings. again uncheck Read capacity and Write capacity; Edit Read capacity units and Write capacity units from 5 to 1. Now choose to Create as marked in the below screenshot.
• It takes a few seconds to create your DynamoDB table. Close the browser tab where the Amazon DynamoDB console is open. If you don’t close the tab, your DynamoDB table is not displayed in the Table name list on the Configure action page of the AWS IoT console.
• On Configure action, First of all, refresh the resources by clicking on the refreshsign between the Table name drop down and Create a new resource button. Then choose your new table from the Table name list.  Choose to Create a new role.
• In Create a new role, enter a unique name, and then choose to Create role.
• Choose Add action
• Choose Create rule.
• After the successful creation of the rule, you will see the following screenshot.

Testing an Amazon DynamoDB Rule:

• To test the rule, open the AWS IoT console and from the navigation pane, choose Test.
• Choose to Subscribe to a Topic, Type outTopic then tap on subscribe to a topic.
• After a successful subscription, you will see the following page on your screen.

2. Code Implementation to post mac_Id, random_number, randon_string from ESP8266 to AWS

Till now we have gone through Creating a DynamoDB rule and DynamoDB table, Subscription to a MQTT topic. So now we will see how to publish data to the subscribed MQTT Topic. So to this topic, we will post mac_Id, random_number, randon_string from ESP8266; In my previous article, I have already discussed how to post Helloworld messages from ESP8266. in this tutorial, I am not going to explain again. If you don’t know how to post data to AWS using ESP8266 you can check that here. We will use the same code which I have used in my previous article to communicate with AWS, but I will change the data format (i.e I will add extra code to post mac_Id, random_number, randon_string)

Open a new sketch file of Arduino IDE, Copy & Paste the below code into that and save with some file name. i.e “ESP8266_DynamoDB_data_mac”

You have to make sure that the WiFi username and password are provided which is available in the range.

const char* ssid = "Electronics_Innovation";
const char* password = "subscribe";

Also, change the AWS_endpoint which is the address of the MQTT broker for your AWS account in a specific region.

const char* AWS_endpoint = "xxxxxxxxxxxxxx-ats.iot.us-west-2.amazonaws.com"; //MQTT broker ip

This AWS_endpoint can be found in the Custom Endpoint section of the Interact tab of your registered things. as shown below.

We are using the below code to publish “hello world” to the topic “outTopic” every two seconds to the server.
If you want to change the message you can change here. or if you want some sensor data you accommodate the sensor code here.

 long now = millis();
if (now - lastMsg > 2000) {
lastMsg = now;
++value;
snprintf (msg, 75, "{\"message\": \"hello world #%ld\"}", value);
Serial.print("Publish message: ");
Serial.println(msg);
client.publish("outTopic", msg);
Serial.print("Heap: "); Serial.println(ESP.getFreeHeap()); //Low heap can cause problems
}

I will replace this code with the new code which can publish mac_Id, random_number, randon_string in stead of hello world.

The following code will publish the mac_Id, random_number, randon_string.

long now = millis();
if (now - lastMsg > 2000) {
lastMsg = now;
//========================================================================
String macIdStr = mac_Id;
uint8_t randomNumber = random(20, 50);
String randomString = String(random(0xffff), HEX);
snprintf (msg, BUFFER_LEN, "{\"mac_Id\" : \"%s\", \"random_number\" : %d, \"random_string\" : \"%s\"}", macIdStr.c_str(), randomNumber, randomString.c_str());

Serial.print("Publish message: ");
Serial.println(msg);
//mqttClient.publish("outTopic", msg);
client.publish("outTopic", msg);
//=========================================================================
Serial.print("Heap: "); Serial.println(ESP.getFreeHeap()); //Low heap can cause problems
}

As I have added new variables to the program,  I have to declare them, So the following will full fill the Varible declaration requirement.

//==========================================================================
#define BUFFER_LEN 256
long lastMsg = 0;
char msg[BUFFER_LEN];
int value = 0;
byte mac[6];
char mac_Id[18];
//===========================================================================

I have added one variable to store the mac_Id of the ESP8266 in the code, So we also need to implement code to get mac Id. So the following code will take care of that. The function “WiFi.macAddress(mac);” will return the mac address of the ESP8266 in a byte format. but we need that mac address in string format. So below code will convert the mac address from byte format to string and store into the mac_Id.

 //==========================================================================
WiFi.macAddress(mac);
snprintf(mac_Id, sizeof(mac_Id), "%02x:%02x:%02x:%02x:%02x:%02x",
mac[0], mac[1], mac[2], mac[3], mac[4], mac[5]);
Serial.print(mac_Id);
//============================================================================

Source Code & Program

Apart from that, I didn’t change anything about the code. Here I am connecting every block of the code and giving you the final updated code to post mac_Id, random number, and random String.

/*Developed by M V Subrahmanyam - https://www.linkedin.com/in/veera-subrahmanyam-mediboina-b63997145/
Project: AWS | NodeMCU ESP8266 Tutorials
Electronics Innovation - www.electronicsinnovation.com

GitHub - https://github.com/VeeruSubbuAmi
YouTube - http://bit.ly/Electronics_Innovation

Upload date: 07 October 2019

AWS Iot Core

This example needs https://github.com/esp8266/arduino-esp8266fs-plugin

It connects to AWS IoT server then:
- publishes "hello world" to the topic "outTopic" every two seconds
- subscribes to the topic "inTopic", printing out any messages
*/

#include "FS.h"
#include <ESP8266WiFi.h>
#include <PubSubClient.h> //https://www.arduinolibraries.info/libraries/pub-sub-client
#include <NTPClient.h> //https://www.arduinolibraries.info/libraries/ntp-client
#include <WiFiUdp.h>

// Update these with values suitable for your network.

const char* ssid = "Electronics_Innovation";
const char* password = "subscribe";

WiFiUDP ntpUDP;
NTPClient timeClient(ntpUDP, "pool.ntp.org");

const char* AWS_endpoint = "a249qqkbhtfj6o-ats.iot.us-west-2.amazonaws.com"; //MQTT broker ip

void callback(char* topic, byte* payload, unsigned int length) {
Serial.print("Message arrived [");
Serial.print(topic);
Serial.print("] ");
for (int i = 0; i < length; i++) {
Serial.print((char)payload[i]);
}
Serial.println();

}
WiFiClientSecure espClient;
PubSubClient client(AWS_endpoint, 8883, callback, espClient); //set MQTT port number to 8883 as per //standard
//============================================================================
#define BUFFER_LEN 256
long lastMsg = 0;
char msg[BUFFER_LEN];
int value = 0;
byte mac[6];
char mac_Id[18];
//============================================================================

void setup_wifi() {

delay(10);
// We start by connecting to a WiFi network
espClient.setBufferSizes(512, 512);
Serial.println();
Serial.print("Connecting to ");
Serial.println(ssid);

WiFi.begin(ssid, password);

while (WiFi.status() != WL_CONNECTED) {
delay(500);
Serial.print(".");
}

Serial.println("");
Serial.println("WiFi connected");
Serial.println("IP address: ");
Serial.println(WiFi.localIP());

timeClient.begin();
while(!timeClient.update()){
timeClient.forceUpdate();
}

espClient.setX509Time(timeClient.getEpochTime());

}

void reconnect() {
// Loop until we're reconnected
while (!client.connected()) {
Serial.print("Attempting MQTT connection...");
// Attempt to connect
if (client.connect("ESPthing")) {
Serial.println("connected");
// Once connected, publish an announcement...
client.publish("outTopic", "hello world");
// ... and resubscribe
client.subscribe("inTopic");
} else {
Serial.print("failed, rc=");
Serial.print(client.state());
Serial.println(" try again in 5 seconds");

char buf[256];
espClient.getLastSSLError(buf,256);
Serial.print("WiFiClientSecure SSL error: ");
Serial.println(buf);

// Wait 5 seconds before retrying
delay(5000);
}
}
}

void setup() {

Serial.begin(115200);
Serial.setDebugOutput(true);
// initialize digital pin LED_BUILTIN as an output.
pinMode(LED_BUILTIN, OUTPUT);
setup_wifi();
delay(1000);
if (!SPIFFS.begin()) {
Serial.println("Failed to mount file system");
return;
}

Serial.print("Heap: "); Serial.println(ESP.getFreeHeap());

// Load certificate file
File cert = SPIFFS.open("/cert.der", "r"); //replace cert.crt eith your uploaded file name
if (!cert) {
Serial.println("Failed to open cert file");
}
else
Serial.println("Success to open cert file");

delay(1000);

if (espClient.loadCertificate(cert))
Serial.println("cert loaded");
else
Serial.println("cert not loaded");

// Load private key file
File private_key = SPIFFS.open("/private.der", "r"); //replace private eith your uploaded file name
if (!private_key) {
Serial.println("Failed to open private cert file");
}
else
Serial.println("Success to open private cert file");

delay(1000);

if (espClient.loadPrivateKey(private_key))
Serial.println("private key loaded");
else
Serial.println("private key not loaded");

// Load CA file
File ca = SPIFFS.open("/ca.der", "r"); //replace ca eith your uploaded file name
if (!ca) {
Serial.println("Failed to open ca ");
}
else
Serial.println("Success to open ca");

delay(1000);

if(espClient.loadCACert(ca))
Serial.println("ca loaded");
else
Serial.println("ca failed");

Serial.print("Heap: "); Serial.println(ESP.getFreeHeap());

//===========================================================================
WiFi.macAddress(mac);
snprintf(mac_Id, sizeof(mac_Id), "%02x:%02x:%02x:%02x:%02x:%02x",
mac[0], mac[1], mac[2], mac[3], mac[4], mac[5]);
Serial.print(mac_Id);
//============================================================================

}

void loop() {

if (!client.connected()) {
reconnect();
}
client.loop();

long now = millis();
if (now - lastMsg > 2000) {
lastMsg = now;
//============================================================================
String macIdStr = mac_Id;
uint8_t randomNumber = random(20, 50);
String randomString = String(random(0xffff), HEX);
snprintf (msg, BUFFER_LEN, "{\"mac_Id\" : \"%s\", \"random_number\" : %d, \"random_string\" : \"%s\"}", macIdStr.c_str(), randomNumber, randomString.c_str());

Serial.print("Publish message: ");
Serial.println(msg);
//mqttClient.publish("outTopic", msg);
client.publish("outTopic", msg);
//=============================================================================
Serial.print("Heap: "); Serial.println(ESP.getFreeHeap()); //Low heap can cause problems
}
digitalWrite(LED_BUILTIN, HIGH); // turn the LED on (HIGH is the voltage level)
delay(100); // wait for a second
digitalWrite(LED_BUILTIN, LOW); // turn the LED off by making the voltage LOW
delay(100); // wait for a second
}

3.Demo: Data Storing into the Amazon DynamoDB

After successful Code Implementation, Connect your NodeMCU with your machine then check all the board configurations(Board, Upload Speed, Flash Size, Port) in your ArduinoIDE.

Please find the board configurations here.

tap on the upload button if all the board configuration are okay.

After successful Uploading, open your serial monitor and check whether your device is communicating with AWS or not. First off all your nodeMCU should connect with WiFi, after that, it will fetch the AWS certificates and Security keys from Flash memory. Then NodeMCU will attempt MQTT Connection with Amazon Webservices If the connection is successful then publishes the JSON message.

If your nodeMCU is able to communicate with AWS and publish JSON data. Now open the AWS IoT console and from the navigation pane, choose Test. Choose to Subscribe to a Topic, Type outTopic then tap on subscribe to a topic. You will see the data coming over here. We can see mac_Id, random_number, random string.

Now go to DynamoDB, You can open from Services, simply go to services and search for DynamoDB.

In DynamoDB go to tables and select the table which you have created recently. tap on the Items as shown below.

It will take two to three seconds to load based on your network connection, Once this page fully loaded you can see the data which we have published to the 'outTopic'
We have published JSON data to the outTopic, but if you see here all the data has been segregated and stored in the particular columns. This is happening because we have chosen an action: Split message into multiple columns of DynamoDB table(DynamoDBv2)

DynamoDBv2 Action:

The dynamoDBv2 the action allows you to write all or part of an MQTT message to a DynamoDB table. Each attribute in the payload is written to a separate column in the DynamoDB database.

This is all about Storing ESP8266 data into theDynamoDB using AWS IoT Core(MQTT) & Arduino, So far we have walked through Creating a DynamoDB rule, Creating a DynamoDB Table, Code Implementation for required JSON then Coomuncating with AWS using Certificates and Security keys.

Video Tutorial: Connecting NodeMCU with AWS IoT Core using Arduino IDE.