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

Questions tagged with Amazon Timestream

Sort by most recent

Browse through the questions and answers listed below or filter and sort to narrow down your results.

Calculating measure from multi part LoRaWAN uplink message before storing in Timestream database

Hi, I'm working on an application in which I store data from LoRaWAN IoT sensors into an Amazon Timestream table. So far I've been able to decode and load data from "simple" sensors (devices that send all their measurements in a single message) by using Lambda (for binary decoding) and IoT Core's rule engine (for storing into Timestream). The issue I'm encountering now is that I have a sensor that sends its data over two consecutive messages (Netvox R718N37, a current meter). Although the binary decoder can decode each individual message, I need to combine the data from both messages in order to build a record to load into the database. Just for clarity, the example below corresponds to two messages received from the sensor **Message 1:** ``` { "WirelessDeviceId": "aaabbbccc", "WirelessMetadata": { "LoRaWAN": { "ADR": true, "Bandwidth": 125, "ClassB": false, "CodeRate": "4/5", "DataRate": "3", "DevAddr": "123456789", "DevEui": "ggghhhiii", "FCnt": 203, "FOptLen": 0, "FPort": 6, "Frequency": "905300000", "Gateways": [{ "GatewayEui": "dddeeefff", "Rssi": -87, "Snr": 7.5 }], "MIC": "418bcec7", "MType": "UnconfirmedDataUp", "Major": "LoRaWANR1", "Modulation": "LORA", "PolarizationInversion": false, "SpreadingFactor": 7, "Timestamp": "2022-08-30T19:11:19Z" } }, "PayloadData": "AUoBJAAAAAAAAAE=", "DecodedPayloadData": { "devicetype": "R718N3-1", "battery": 3.6, "currentma1": 0, "currentma2": 0, "currentma3": 0, "multiplier1": 1 }, "ArrivalTimestamp": 1661886679775 } ``` **Message #2:** ``` { "WirelessDeviceId": "aaabbbccc", "WirelessMetadata": { "LoRaWAN": { "ADR": true, "Bandwidth": 125, "ClassB": false, "CodeRate": "4/5", "DataRate": "3", "DevAddr": "123456789", "DevEui": "ggghhhiii", "FCnt": 204, "FOptLen": 0, "FPort": 6, "Frequency": "904100000", "Gateways": [{ "GatewayEui": "dddeeefff", "Rssi": -87, "Snr": 9.5 }], "MIC": "5a21d54e", "MType": "UnconfirmedDataUp", "Major": "LoRaWANR1", "Modulation": "LORA", "PolarizationInversion": false, "SpreadingFactor": 7, "Timestamp": "2022-08-30T19:11:29Z" } }, "PayloadData": "AUoCJAEBAAAAAAA=", "DecodedPayloadData": { "devicetype": "R718N3-2", "battery": 3.6, "multiplier2": 1, "multiplier3": 1 }, "ArrivalTimestamp": 1661886689459 } ``` For instance, to calculate the current for phase 2 (a measurement I need in the Timestream table), I need to multiply `DecodedPayloadData.currentma2` and `DecodedPayloadData.multiplier2`, which are in different messages. My question is: what strategy would you recommend for me to combine the two consecutive MQTT messages? I need a way to get both messages into a Lambda function so I can run the calculations and store the data into the database. Thank you in advance,
1
answers
0
votes
33
views
asked 25 days ago

AWS IoT Timestream Rule Action write: Problem with float and int values

Hello, we have some sensors that send multiple variables (temp, humidity, pressure) through the IoT core writing them to timestream and dynamoDB at the same time using an IoT rule (SQL 2016-03-23). We are having some problems with rejected values when trying to write those values. Cloudwatch says: ``` "failedAction": "TimestreamAction", "errorMessage": "Failed to write records to Timestream. The error received was 'One or more records have been rejected. See RejectedRecords for details. (Service: AmazonTimestreamWrite; Status Code: 419; Error Code: RejectedRecordsException; Request ID: xxx; Proxy: null), Rejected records: [{RecordIndex: 9, Reason: Measure name already has an assigned measure value type. Each measure name can have only one measure value type and cannot be changed.,}]'. Message arrived on mit/decoded, Action: timestream, Database: sensordata-timestream-db, Table: mit-timestream-table" ``` The natural type of the values is `double` and the first write in timestream defined that measurement with the correct type. The problem relies when some of the measurements are with decimal part equal to zero (which can be interpreted as an `int`), for example `13.0`. In this case Timestream detects it as an `int` instead of `double` and rejects the measurement. Reading the documentation and different questions here in re:Post, I saw that a possible solution is to cast the variable as double, but seems not be working. This is how we are casting it: ``` SELECT cast(event.payload_sensordata.Hum_SHT as double) as Hum_SHT .... FROM .... ``` Another way we have tested without luck is casting as `DECIMAL` instead of `double`. This is what we are seeing in both tables: ![Enter image description here](/media/postImages/original/IMcxwgiNJ3RMaxmJoljYSYLw) Any thoughts on how to solve this within the IoT rule? Thanks!
2
answers
0
votes
45
views
asked a month ago