By using AWS re:Post, you agree to the AWS re:Post Terms of Use
AWS re:Postre:Post

Sending Reublished MQTT data to DynamoDB table

0

I have successfully republished data from my "lorawan/+/uplink" topic to my new topic "datashow". I am now trying send the data I extracted from the original topic to a DynamoDB. I created a rule named "SensorDB_rule" to select the data from the "datashow" topic and send it to a DynamoDB.

The data I receive is listed below.

{
  "output": {
    "DeviceID": "eui-70b3d57ed004c725",
    "PayloadData": {
      "Battery": 100,
      "Sensor": -27.730278573288206,
      "alt": 63.10,
      "lat": 38.789196,
      "lon": -77.073914,
      "time": "18:52:43"
    }
  }
}

The rule SQL Statement is listed below

SELECT output.DeviceID as Device_ID, output.PayloadData.Battery as Battery, output.PayloadData.Sensor as Sensor, output.PayloadData.alt as Altitude, output.PayloadData.lat as Latitude, output.PayloadData.lon as Longitude, output.PayloadData.time as Time FROM 'datashow'

I created a DynamoDB table named "SensorDB" and set "DeviceID" as my Partition Key and "time" as my Sort key. With both set to String

DeviceID

time

I'm using the Default settings for my DB table. and created the table

Back in the "SensorDB_rule" I selected my table I just created ("SensorDB") and entered the following

Partition key- DeviceID
Partition key type- STRING
Partition key value- $DeviceID
Sort Key- time
Sort key type- STRING
Sort key value- $time

and wrote the message to the column "MyMessageData"

finally I created a new IAM role names "SensorDB_role" and attached the following permissions

AWSIoTLogging AWSIoTRuleActions AWSIoTEventsFullAccess AWSIoTFullAccess AmazonDynamoDBFullAccess

I have double checked all my information but I am still not getting anything inside of my table. Any suggestions on how to fix this? (adding policies, correcting my SQL statement, changing my table settings)

Topics
AnalyticsDatabase
Tags
AnalyticsAmazon DynamoDB
Language
English
AWS-User-6259931
asked 2 years ago307 views
3 Answers
1

The only thing different in my CLI was this

   {
                "republish": {
                    "roleArn": "arn:aws:iam::795806302730:role/service-role/IoTRule_20220508_repost",
                    "topic": "datashow",

how do you create service roles?

AWS-User-6259931
answered 2 years ago
0

This method worked fine. It appears that the JSON structure needs to be converted when republishing.(I don't know why.)

  1. Create republish rule
EntryValue
ruleNameRepublish
sqlSELECT output.DeviceID as Device_ID, output.PayloadData.Battery as Battery, output.PayloadData.Sensor as Sensor, output.PayloadData.alt as Altitude, output.PayloadData.lat as Latitude, output.PayloadData.lon as Longitude, output.PayloadData.time as Time FROM 'lorawan/+/uplink'
actionrepublish
  1. Create insert to DynamoDB rule
EntryValue
ruleNameSensorDB_rule
sqlSELECT * FROM 'datashow'
actiondynamoDB
DynamoDB tableNameSensorDB
DynamoDB operationINSERT
DynamoDB hashKeyFieldDeviceID
DynamoDB hashKeyValue${Device_ID}
DynamoDB hashKeyTypeSTRING
DynamoDB rangeKeyFieldtime
DynamoDB rangeKeyValue${Time}
DynamoDB rangeKeyTypeSTRING
DynamoDB payloadFieldpayload

The results of the AWS CLI output are left as evidence.

[cloudshell-user@ip-10-0-77-70 ~]$ aws iot list-topic-rules
{
    "rules": [
        {
            "ruleArn": "arn:aws:iot:ap-northeast-1:795806302730:rule/SensorDB_rule",
            "ruleName": "SensorDB_rule",
            "topicPattern": "datashow",
            "createdAt": "2022-05-08T09:15:52+00:00",
            "ruleDisabled": false
        },
        {
            "ruleArn": "arn:aws:iot:ap-northeast-1:795806302730:rule/Republish",
            "ruleName": "Republish",
            "topicPattern": "lorawan/+/uplink",
            "createdAt": "2022-05-08T09:14:50+00:00",
            "ruleDisabled": false
        }
    ]
}
[cloudshell-user@ip-10-0-77-70 ~]$ aws iot get-topic-rule --rule-name SensorDB_rule
{
    "ruleArn": "arn:aws:iot:ap-northeast-1:795806302730:rule/SensorDB_rule",
    "rule": {
        "ruleName": "SensorDB_rule",
        "sql": "SELECT * FROM 'datashow'",
        "description": "",
        "createdAt": "2022-05-08T09:15:52+00:00",
        "actions": [
            {
                "dynamoDB": {
                    "tableName": "SensorDB",
                    "roleArn": "arn:aws:iam::795806302730:role/service-role/IoTRule_20220508_repost",
                    "operation": "INSERT",
                    "hashKeyField": "DeviceID",
                    "hashKeyValue": "${Device_ID}",
                    "hashKeyType": "STRING",
                    "rangeKeyField": "time",
                    "rangeKeyValue": "${Time}",
                    "rangeKeyType": "STRING",
                    "payloadField": "payload"
                }
            }
        ],
        "ruleDisabled": false,
        "awsIotSqlVersion": "2016-03-23"
    }
}
[cloudshell-user@ip-10-0-77-70 ~]$ aws iot get-topic-rule --rule-name Republish
{
    "ruleArn": "arn:aws:iot:ap-northeast-1:795806302730:rule/Republish",
    "rule": {
        "ruleName": "Republish",
        "sql": "SELECT output.DeviceID as Device_ID, output.PayloadData.Battery as Battery, output.PayloadData.Sensor as Sensor, output.PayloadData.alt as Altitude, output.PayloadData.lat as Latitude, output.PayloadData.lon as Longitude, output.PayloadData.time as Time FROM 'lorawan/+/uplink'",
        "description": "",
        "createdAt": "2022-05-08T09:14:50+00:00",
        "actions": [
            {
                "republish": {
                    "roleArn": "arn:aws:iam::795806302730:role/service-role/IoTRule_20220508_repost",
                    "topic": "datashow",
                    "qos": 0
                }
            }
        ],
        "ruleDisabled": false,
        "awsIotSqlVersion": "2016-03-23"
    }
}
[cloudshell-user@ip-10-0-77-70 ~]$

Thanks.

profile picture
moritalous
answered 2 years ago
  • AWS-User-6259931
    2 years ago

    I followed what you said and I am still not getting any data populating inside my table. I even checked my CLI to verify what you suggested. What do you mean by " JSON structure needs to be converted when republishing"

0

Sorry for the delay in confirmation. The service role was created in the management console.

[cloudshell-user@ip-10-1-1-158 ~]$ aws iam get-role --role-name IoTRule_20220508_repost
{
    "Role": {
        "Path": "/service-role/",
        "RoleName": "IoTRule_20220508_repost",
        "RoleId": "AROA3SSOAQIFJZM7UGIHM",
        "Arn": "arn:aws:iam::795806302730:role/service-role/IoTRule_20220508_repost",
        "CreateDate": "2022-05-08T08:55:10+00:00",
        "AssumeRolePolicyDocument": {
            "Version": "2012-10-17",
            "Statement": [
                {
                    "Effect": "Allow",
                    "Principal": {
                        "Service": "iot.amazonaws.com"
                    },
                    "Action": "sts:AssumeRole"
                }
            ]
        },
        "MaxSessionDuration": 3600,
        "RoleLastUsed": {
            "LastUsedDate": "2022-05-08T09:44:01+00:00",
            "Region": "ap-northeast-1"
        }
    }
}
[cloudshell-user@ip-10-1-1-158 ~]$ aws iam list-attached-role-policies --role-name IoTRule_20220508_repost
{
    "AttachedPolicies": [
        {
            "PolicyName": "AmazonDynamoDBFullAccess",
            "PolicyArn": "arn:aws:iam::aws:policy/AmazonDynamoDBFullAccess"
        },
        {
            "PolicyName": "AWSIoTRuleActions",
            "PolicyArn": "arn:aws:iam::aws:policy/service-role/AWSIoTRuleActions"
        },
        {
            "PolicyName": "AWSIoTFullAccess",
            "PolicyArn": "arn:aws:iam::aws:polic
profile picture
moritalous
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions

Relevant content