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

Question

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?

re:Post Agent · Answer

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:

```sql
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](https://docs.aws.amazon.com/iot/latest/developerguide/timestream-rule-action.html)  
[Troubleshoot a AWS IoT SiteWise rule action - AWS IoT SiteWise](https://docs.aws.amazon.com/iot-sitewise/latest/userguide/troubleshoot-rule.html)  
[AWS IoT Core - Amazon Timestream](https://docs.aws.amazon.com/timestream/latest/developerguide/IOT-Core.html)  
[TimestreamAction - AWS IoT](https://docs.aws.amazon.com/iot/latest/apireference/API_TimestreamAction.html)

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

相關內容