Ingesting IoT Data to Timestream with Timestamp

0

I wish to ingest data to Timestream with a custom timestamp from IoT. These timestamps will often be in the past -- but well within the retention period. Here are a couple sample messages:

{
  "time": "2020-09-03T17:50:07.790000",
  "Battery Cranking Voltage": "12.035839999999999"
}

{
  "time": "2020-09-03T17:42:28.770000",
  "Electric Energy Out": "6798.6"
}

Note the time property is important and related to the measurement. Also note that the measurement changes with each message. AND there are a large and flexible number of measurements.

Setting an IoT Rule/Action to send this to Timestream will succeed in creating measure_names and _values, but with the WRONG timestamp. The records are stamped with the INGEST time not the value from the time field.

I can "fix" that by changing the NAME of the 'time' property to, say 'timestamp,' and parsing that field with time_to_epoch. BUT, that will create extraneous records in Timestream where the measure_name is 'timestamp' and the value is the same as the time.

So I'm left with a choice between doubling the size of my database or not having useful timestamps.

Is there another way?

---- EDIT ----

To be more detailed, here is another run:

example messages published on topic vt/cvra/teleTester ` { "timestamp": "2020-10-01 19:50:36.050", "Road Speed": "2.0" }

{ "timestamp": "2020-10-01 19:50:34.147", "Gear Position": "3.0" } `

IoT Rule SQL: SELECT * FROM 'vt/cvra/+/cardata'

TImestamp value field: ${time_to_epoch(timestamp, "yyyy-MM-dd HH:mm:ss.SSS")} Units : MILLISECONDS

Results in tImestream:

device_id measure_value::varchar measure_name time

teleTester	2020-10-01 19:50:36.050	timestamp	2020-10-01 19:50:36.050000000
teleTester	2.0	Road Speed	2020-10-01 19:50:36.050000000
teleTester	2020-10-01 19:50:35.050	timestamp	2020-10-01 19:50:35.050000000
teleTester	0.0	Road Speed	2020-10-01 19:50:35.050000000
teleTester	3.0	Gear Position	2020-10-01 19:50:34.147000000
teleTester	2020-10-01 19:50:34.147	timestamp	2020-10-01 19:50:34.147000000

See how there are extra rows with the measure of timestamp?

I'm looking to suppress that.

asked 2 years ago312 views
1 Answer
0
Accepted Answer

I did manage to work around this issue. You simply cannot pass time in the payload. You can use a timestamp from the payload, but it will creat unnecessary storage by also adding the property as a measure. I worked around it by passing epoch millis on the topic and extracting the timestamp from there. See http://iot.awsworkshops.com/amazon-timestream/lab131a-iotandtimestream/

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