Converting Integer Values to Doubles and Deleting Specific Rows in AWS IoT Core for Timestream Integration

0

I wrote the following code in my IoT Core rule to save integer value measure_value::double like 0 or 10.0 in a table of Timestream. Because first value was Double and the integer value couldn't be saved. And also to delete the rows where the measure_name column has the value of timestamp to reduce memory usage:

SELECT *, cast(measure_value AS double) AS double_value
FROM 'AWS Iot Core 2'
WHERE measure_name <> 'timestamp';

base on Note written in : https://docs.aws.amazon.com/iot/latest/developerguide/timestream-rule-action.html

However, it was not successful. How can I achieve this?

1 Answer
-1

The approach you've taken is on the right track, but there are a few adjustments needed to achieve your goals.

First, regarding the conversion of integer values to doubles, it's important to note that in SQL V2 (2016-03-23), which is likely what you're using, whole numbers are automatically converted to their Integer representation. This happens even when you explicitly cast them to a Decimal value.

To address this issue, you have two options:

  1. Use SQL V1 (2015-10-08) for your rule query statement. This version will process whole number numeric values as Decimal values.

  2. If you need to use SQL V2, you might need to add a small decimal value to your integers to force them to be treated as doubles. For example:

SELECT *, (measure_value + 0.0) AS double_value
FROM 'AWS IoT Core 2'
WHERE measure_name <> 'timestamp';

This will add 0.0 to your measure_value, forcing it to be treated as a double.

Regarding your attempt to delete rows where the measure_name is 'timestamp', your WHERE clause is correct. However, it's important to understand that this doesn't delete existing rows from Timestream. Instead, it filters out these rows before they are inserted into Timestream.

If you want to reduce memory usage in Timestream, you should consider setting up a retention policy on your Timestream table. This will automatically delete old data based on the rules you set.

Lastly, make sure that your IAM role associated with this rule has the necessary permissions to write to your Timestream table. Also, if you're using encryption at rest, ensure that your AWS KMS key has the required permissions.

Remember, when setting up your Timestream action in the IoT rule, you'll need to specify the database name, table name, dimensions, and other required parameters. The measure names and values will be derived from your SELECT statement results.
Sources
Timestream - AWS IoT Core
Troubleshoot a AWS IoT SiteWise rule action - AWS IoT SiteWise
AWS IoT Core - Amazon Timestream
TimestreamAction - AWS IoT

profile picture
answered a month 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