- Newest
- Most votes
- Most comments
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:
-
Use SQL V1 (2015-10-08) for your rule query statement. This version will process whole number numeric values as Decimal values.
-
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
Relevant content
- asked 2 years ago
- AWS OFFICIALUpdated 3 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated 2 years ago
- AWS OFFICIALUpdated a year ago