How to store multiple message in same row with AWS iot and dynamodbv2

Question

I am using AWS Iot core and dynamodbv2 to store my mqtt message.

My table Primary partition key deviceId and Rule query statement as

SELECT *, topic(2) AS deviceId FROM 'device/+' .

The first message publish{"deviceId": "Name1","temperature":25}.

The table store like:

deviceId    temperature 
Name1       25

When I publish the second message {"deviceId": "Name1","setpoint":23},

It will replace the previous message.

deviceId    setpoint 
Name1       23

I want to publish message separately. Is it possible to keep the previous message and store the both message like that? Thanks.

deviceId    temperature    setpoint 
Name1       25             23

You need to modify the second input and make it `{"deviceId": "Name1","temperature":25,"setpoint":23}` - how are you putting it in DDB? — Ermiya Eskandary, Nov 10 '21 at 10:17
@ErmiyaEskandary I created the rule in aws Iot [here](https://docs.aws.amazon.com/iot/latest/developerguide/iot-ddb-rule.html). If I would like to publish separately, can I still possible to store message together? Thanks — Nakamoto, Nov 10 '21 at 10:33
@Nakamoto if any of the provided answers helped you to solve your problem, please consider upvoting them. You can even pick the answer that solved your problem as the accepted answer — st.huber, Nov 19 '21 at 12:32

score 0 · Answer 1 · answered Nov 10 '21 at 11:50

Looks like you are using the dynamoDB PutItem method, that replaces a item if the same primary key is found.

According the Aws DynamoDB docs the PutItem method:

Creates a new item, or replaces an old item with a new item (including all the attributes). If an item already exists in the specified table with the same primary key, the new item completely replaces the existing item. You can perform a conditional put (insert a new item if one with the specified primary key doesn't exist), or replace an existing item if it has certain attribute values.

To ensure that a new item does not replace an existing item, use a conditional put operation with Exists set to false.

For understand how to do this using Node.js take a look here.

score 0 · Accepted Answer · answered Nov 11 '21 at 08:09

From the tutorial you mentioned and the way the system behaves, it looks like the PutItem method is used to insert elements into DynamoDB. Meaning new items will overwrite old items if an item with the same primary key already exists.

The problem here is, that your deviceId is a bad primary key as it is not unique. You expect to have more than one entry with primary key Name1 which is not possible. Instead, I suggest to adjust your SQL statement to get a unique key. This key could be generated with the timestamp() or traceid() functions of AWS IoT Core. Your SQL could the look ike this:

SELECT *, 
       topic(2) AS deviceId, 
       timestamp() as timestamp, 
       traceid() as traceId 
FROM 'device/+'

Then you use the timestamp or traceId or a compound key made up of timestamp+deviceId for instance as your primary key. The deviceId can be used as the sort key. This is also how it was described in the tutorial

sample_time is a primary key and describes the time the sample was recorded.

device_id is a sort key and describes the device that provided the sample

device_data is the data received from the device and formatted by the rule query statement

Be aware, that you cannot store the data like this

deviceId    temperature    setpoint 
Name1       25             23

unless your MQTT message containts temperature and setpoint. Otherwise they will always be stored separately.

The only "workaround" to store the data as you described is to write a small lambda that uses PutItem to store the data if none exists and UpdateItem to add "setpoint" or "temperature" to an already existing item. You could, most likely, even do without PutItem as UpdateItem:

Edits an existing item's attributes, or adds a new item to the table if it does not already exist. You can put, delete, or add attribute values. You can also perform a conditional update on an existing item (insert a new attribute name-value pair if it doesn't exist, or replace an existing name-value pair if it has certain expected attribute values).

If you are fine with only keeping the latest value of "temperature" and "setpoint" this set up is fine. If you need to keep a history of how the "temperature" changed over time then you should either add a timestamp to your message or use the SQL timestamp() function and use the timestamp as or as part of your primary key. In case you plan to have a lot, and I mean a lot, of devices sending their data to AWS IoT then the timestamp may not be good enough as a primary key and you need to have a compound made up by the timestamp and deviceId to keep it unique.

A great introduction on how DynamoDB works, partition keys, sort keys, indexes and more can be found in this video of Marcia Villalba.

Thanks mate, I tried lambda and found the solution in here [link](https://stackoverflow.com/questions/55825544/how-to-dynamically-update-an-attribute-in-a-dynamodb-item/59194533#59194533) — Nakamoto, Dec 09 '21 at 03:07

How to store multiple message in same row with AWS iot and dynamodbv2

2 Answers2