CODEWITHSUNDEEP
pythonamazon-web-servicesamazon-dynamodbmqttaws-iot
Federico
Federico

Reputation: 1157

AWS store MQTT message to DynamoDB

I built a python script that sends MQTT message periodically.

This is the JSON string sent to the backend:

{"Id": "1234", "Ut": 1488395951, "Temp": 22.86, "Rh": 48.24}

On backend side, I want to store the MQTT message into DynamoDB table.

I followed an Amazon tutorial and the data contained into the MQTT messages are stored into the table.

My problem regards the table columns.

The table has only 3 colums:

It is possible to have columns for each key contained into MQTT message?

I would have this columns: - Id - Ut - Temp - Rh

Thanks for the help!

Upvotes: 4

Views: 3759

Answers (4)

Ben T
Ben T

Reputation: 4946

It is possible to have columns for each key contained into MQTT message?

I would have this columns: - Id - Ut - Temp - Rh

The simplest and easiest way to implement this is to use the AWS IoT DynamoDBv2 action without the need to implement a Lambda.

From https://docs.aws.amazon.com/iot/latest/developerguide/iot-rule-actions.html#dynamodb-v2-rule:

The dynamoDBv2 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.

The MQTT message payload must contain a root-level key that matches the table's primary partition key and a root-level key that matches the table's primary sort key, if one is defined.

The table can continue to have Id as the Partition Key and Ut as the Sort Key. The MQTT message already contains the partition and sort key so the AWS IoT SQL statement can simply be:

SELECT * FROM 'mytopic'

Upvotes: 1

ChrisWue
ChrisWue

Reputation: 19020

I assume you're trying to come up with an IoT Gateway rule storing your message in a DynamoDB table. Unfortunately the IoT rules do not allow to store data in multiple columns - you can only store the payload (or a subset thereof) in a single column. There is currently no provision to extract properties from the payload into several different columns.

The only way around this (which I've found so far) is to call a lambda function through the IoT rules which runs code similar to what the other answers have shown.

Upvotes: 2

readyornot
readyornot

Reputation: 2863

Yes, definitely. You can create columns for whatever you want to store in a DynamoDB table, instead of or in addition to storing the full payload.

Following Step 3.1: Create a New Item from this tutorial, you would update your put_item code to include all your item details...

#Parse your JSON message and get out all your attributes
id = message["Id"]
ut = message["Ut"]
temp = message["Temp"]
rh = message["Rh"]

response = table.put_item(
   Item={
        'id': id,
        'ut': ut,
        'temp': temp,
        'rh': rh
    }
)

Now if you look at your table in the DynamoDB console, you'll see new columns have been created for your additional attributes.

Upvotes: 2

Vor
Vor

Reputation: 35099

Yes you can.

In DynamoDB you don't need to create what you call "columns". The only requirements for successful put operation is that you provide your primary attributes (in your case Id and Ut).

From the docs:

A map of attribute name/value pairs, one for each attribute. Only the primary key attributes are required; you can optionally provide other attribute name-value pairs for the item.

In python you can do something similar to this (may need to check the syntax and adjust column types):

import boto3
client = boto3.client('dynamodb')
response = client.put_item(
    TableName = 'Messages',
    Item={
        'Id': {
            'S': '1234'
        },
        'Ut': {
            'S': '1488395951'
        },
        'Temp': {
            'S': '22.86'
        },
        'Rh': {
            'S': '48.24'
        }
    }
)

Upvotes: 1

Related Questions