Issue with IoT Rule and Timestream Integration - Measure Value Type Conflict

Question

Hello,
I’m experiencing an issue with the integration between AWS IoT and Timestream, specifically with how data is handled in the Timestream table when using an IoT Rule.Here’s an example of the message I am sending to the IoT topic:
{
  "x": 33.0,
  "y": 35.5,
  "z": true,
  "ts": "2024-10-15T08:20:02.043314+00:00"
}

The IoT Rule SQL statement is:
SELECT * FROM "topic/#"

In this scenario, even if I send values like x as an int instead of a float, they are successfully stored in the Timestream table as float. This behavior is acceptable and works as expected

However, when I modify the IoT Rule to the following:
SELECT 
  x,
  y,
  z,
FROM "topic/#"

And send the same values, I encounter the following error:

{
  "failures": [
    {
      "failedAction": "TimestreamAction",
      "failedResource": "DB",
      "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: LWER12XEL698JMM397KOQ5GDPZF4; Proxy: null), Rejected records: [{RecordIndex: 0,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 topic/1, Action: timestream, Database: DB, Table: data"
    }
  ]
}

It seems that when I send an int for values that should be float, the Timestream table rejects the records, returning the error that the measure name already has an assigned measure value type. This behavior differs from the previous query where the values were stored as float without any issue.Could you please clarify if this is the expected behavior, or if there is a bug causing this issue? I would like to know how to proceed with handling this scenario, as it impacts the data ingestion pipeline.Thank you for your assistance.Best regards,
Any thoughts on how to solve this ?.

re:Post Agent · Answer

The issue you're experiencing is related to how AWS IoT Core rule actions interact with Amazon Timestream, particularly regarding data type handling. This behavior is expected, but there are ways to address it.

When you use SELECT * in your IoT rule, AWS IoT Core automatically infers the data types from the message payload. In this case, it's able to convert integers to floats seamlessly, which is why you don't see any errors with your first query.

However, when you explicitly select specific fields (x, y, z) in your rule, AWS IoT Core treats the data types more strictly. Timestream has a strict policy about measure value types - once a measure name is associated with a specific data type, it cannot be changed. This is why you're seeing the error about the measure name already having an assigned measure value type.

To solve this issue, you have a few options:

1. Use type casting in your IoT rule SQL statement to ensure consistent data types. For example:

SELECT 
CAST(x AS DOUBLE) AS x,
CAST(y AS DOUBLE) AS y,
z
FROM "topic/#"

This will ensure that 'x' and 'y' are always sent as doubles (floats) to Timestream, regardless of whether they're integers or floats in the original message.

2. If you need to maintain the original data types, you could use separate measure names for different types. For example:

SELECT 
CASE WHEN typeof(x) = 'Int' THEN x END AS x_int,
CASE WHEN typeof(x) = 'Double' THEN x END AS x_double,
y,
z
FROM "topic/#"

This approach will create separate measures for integer and double values.

3. Standardize your data at the source to always send consistent data types.

Remember, when working with Timestream via IoT Core rules, it's crucial to maintain consistency in the data types you're sending for each measure name. Once a measure name is associated with a specific data type in Timestream, you can't change it without recreating the table.

**Sources**  
[AWS IoT Core - Amazon Timestream](https://docs.aws.amazon.com/timestream/latest/developerguide/IOT-Core.html)  
[AWS IoT rule actions - AWS IoT Core](https://docs.aws.amazon.com/iot/latest/developerguide/iot-rule-actions.html)  
[Troubleshoot a AWS IoT SiteWise rule action - AWS IoT SiteWise](https://docs.aws.amazon.com/iot-sitewise/latest/userguide/troubleshoot-rule.html)

Issue with IoT Rule and Timestream Integration - Measure Value Type Conflict

Relevant content