Questions tagged with Amazon Timestream

I have a python lambda streaming data to timestream that has been running for the last few months. The lambda has always run well under a second, with an average of 15-30ms - regardless if the data itself is current (memory store) or historical (magnetic store). This week the lambda is timing out, even when the timeout duration is set to a max of 15 mins. The place its getting stuck is where we make the call to timestream to write the actual records. It just sits here, for a duration of between 30ms to 15mins. The data that gets stuck is always processed on a retry and appears in the DB, so its not a data issue. This is causing our SQS queues to build up, as our lambda failures and timeouts cause throttling, and a reduced concurrency. The line in question : result = write_client.write_records(DatabaseName=DATABASE_NAME, TableName=table_name, CommonAttributes=common_attributes, Records=records)lg...

I have a Timestream table with about 500,000 records. There are some of those that I want to delete, clean up and rearrange. What is the best method for this task? I understand that Timestream does not allow for deletions (currently anyway). I am thinking about exporting, cleaning the data, deleting the table, creating a new table and then inserting the cleaned up data into the newly created table. I don't see anything out there that might fit the bill except writing some pathon or something. Anybody have input for this?lg...

Hi, I realised that my timstream data time does not reflect my local time. Is there a way to convert to a local timestamp? Thank you. My local time is UTC +8. lg...

Hi, I'm working on an application in which I store data from LoRaWAN IoT sensors into an Amazon Timestream table. So far I've been able to decode and load data from "simple" sensors (devices that send all their measurements in a single message) by using Lambda (for binary decoding) and IoT Core's rule engine (for storing into Timestream). The issue I'm encountering now is that I have a sensor that sends its data over two consecutive messages (Netvox R718N37, a current meter). Although the binary decoder can decode each individual message, I need to combine the data from both messages in order to build a record to load into the database. Just for clarity, the example below corresponds to two messages received from the sensor **Message 1:** ``` { "WirelessDeviceId": "aaabbbccc", "WirelessMetadata": { "LoRaWAN": { "ADR": true, "Bandwidth": 125, "ClassB": false, "CodeRate": "4/5", "DataRate": "3", "DevAddr": "123456789", "DevEui": "ggghhhiii", "FCnt": 203, "FOptLen": 0, "FPort": 6, "Frequency": "905300000", "Gateways": [{ "GatewayEui": "dddeeefff", "Rssi": -87, "Snr": 7.5 }], "MIC": "418bcec7", "MType": "UnconfirmedDataUp", "Major": "LoRaWANR1", "Modulation": "LORA", "PolarizationInversion": false, "SpreadingFactor": 7, "Timestamp": "2022-08-30T19:11:19Z" } }, "PayloadData": "AUoBJAAAAAAAAAE=", "DecodedPayloadData": { "devicetype": "R718N3-1", "battery": 3.6, "currentma1": 0, "currentma2": 0, "currentma3": 0, "multiplier1": 1 }, "ArrivalTimestamp": 1661886679775 } ``` **Message #2:** ``` { "WirelessDeviceId": "aaabbbccc", "WirelessMetadata": { "LoRaWAN": { "ADR": true, "Bandwidth": 125, "ClassB": false, "CodeRate": "4/5", "DataRate": "3", "DevAddr": "123456789", "DevEui": "ggghhhiii", "FCnt": 204, "FOptLen": 0, "FPort": 6, "Frequency": "904100000", "Gateways": [{ "GatewayEui": "dddeeefff", "Rssi": -87, "Snr": 9.5 }], "MIC": "5a21d54e", "MType": "UnconfirmedDataUp", "Major": "LoRaWANR1", "Modulation": "LORA", "PolarizationInversion": false, "SpreadingFactor": 7, "Timestamp": "2022-08-30T19:11:29Z" } }, "PayloadData": "AUoCJAEBAAAAAAA=", "DecodedPayloadData": { "devicetype": "R718N3-2", "battery": 3.6, "multiplier2": 1, "multiplier3": 1 }, "ArrivalTimestamp": 1661886689459 } ``` For instance, to calculate the current for phase 2 (a measurement I need in the Timestream table), I need to multiply `DecodedPayloadData.currentma2` and `DecodedPayloadData.multiplier2`, which are in different messages. My question is: what strategy would you recommend for me to combine the two consecutive MQTT messages? I need a way to get both messages into a Lambda function so I can run the calculations and store the data into the database. Thank you in advance,lg...

Timestream DB was [released in the Sydney region on August 5](https://aws.amazon.com/about-aws/whats-new/2022/08/amazon-timestream-available-asia-pacific-sydney-tokyo-regions/) but Timestream is not available in the list of Rule Actions in AWS IoT Core from the Sydney region. Is there an extra configuration step needed for it to show, or an ETA for when this option will become available?lg...

Hello: I have a MULTI measure table in AWS Timestream, and when I run a SELECT to bring values from a measurement, it brings rows including those where the measurement does not exist. For example, having measures like 'temp' and 'humidity', the following query: ``` SELECT temp, time FROM "test"."reports" WHERE time between ago(1d) and now() ORDER BY time DESC LIMIT 100 ``` brings these records: ``` temp time 21.55 2022-08-27 00:02:43.478000000 - 2022-08-26 23:35:11.490000000 23.2 2022-08-26 23:34:36.090000000 24.1 2022-08-26 23:34:17.332000000 24.1 2022-08-26 23:31:09.975000000 24.1 2022-08-26 23:29:56.867000000 ``` So my question is why the record without 'temp' data is returned by the SELECT statement? How should I filter out this value? Thankslg...

Hi All, Not sure whom to post this. I have found typo in the document and wanted to check from Amazon team. Page: 135 Sentence: *The query run triggered at 2021-12-01 00:00:00 will cover the time range 2021-11-30 23:50:00 to **2021-12-30 00:01:00** and the query* I believe **2021-12-30 00:01:00** should be **2021-12-01 00:01:00**lg...

Hi, I am new to timestream. I am currently trying to send payload through MQTT. In my payload, there is only the following: "device_id": "Device 1", "a_x": 101.68888 When I run my query in my database, only Device 1 is shown. The a_x and 101.68888 are not reflected. Is it possible for timstream to show decimal values? Here is my SQL in my Iot Core rule SQL version: 2015-10-08 SELECT*, a_x FROM 'outTopic' Thank you.lg...

I have a payload of: `{"telemetry": [{"feed": "Channel 1", "reading": 52.7,"unit": "PSI"},{"feed": "Channel 2", "reading": 36.29,"unit": "PSI"},{"feed": "Channel 3", "reading": 34.7,"unit": "PSI"}]}` I have tried a couple of rules: 1st rule >`SELECT get(telemetry,0).feed as feed, get(telemetry,0).reading as reading, get(telemetry,0).unit as unit FROM 'my_topic/#'` but with this rule it only gives me the 1st in the array, how would i iterate through the whole array? The length of the array can vary so i cannot hard code it. 2nd rule >`SELECT (SELECT VALUE feed FROM telemetry) as feed, (SELECT VALUE reading FROM telemetry) as reading, (SELECT VALUE unit FROM telemetry) as unit FROM 'my_topic/#'` Rule has action to TimeStream with dimensions: TenantId = TenantId *(Hard coded tenant id)* Feed = ${feed} and have also tried ${telemetry.feed} 1st problem: I get Errors at dimensions.1: [Dimension value can not be empty.] 2nd problem: If i keep only 1 dimension which is TenantId, it will insert but i really want "Feed" as a dimension. Moreover, I do not like the value of ["Channel 1","Channel 2","Channel 3"] as a value stored in TimeStream as well as the reading is not inserted when I use the 2nd rule. I very much prefer the 1st rule where each reading has a record. Any help is much appreciated.lg...

Hello, we have some sensors that send multiple variables (temp, humidity, pressure) through the IoT core writing them to timestream and dynamoDB at the same time using an IoT rule (SQL 2016-03-23). We are having some problems with rejected values when trying to write those values. Cloudwatch says: ``` "failedAction": "TimestreamAction", "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: xxx; Proxy: null), Rejected records: [{RecordIndex: 9, 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 mit/decoded, Action: timestream, Database: sensordata-timestream-db, Table: mit-timestream-table" ``` The natural type of the values is `double` and the first write in timestream defined that measurement with the correct type. The problem relies when some of the measurements are with decimal part equal to zero (which can be interpreted as an `int`), for example `13.0`. In this case Timestream detects it as an `int` instead of `double` and rejects the measurement. Reading the documentation and different questions here in re:Post, I saw that a possible solution is to cast the variable as double, but seems not be working. This is how we are casting it: ``` SELECT cast(event.payload_sensordata.Hum_SHT as double) as Hum_SHT .... FROM .... ``` Another way we have tested without luck is casting as `DECIMAL` instead of `double`. This is what we are seeing in both tables:  Any thoughts on how to solve this within the IoT rule? Thanks!lg...

I am getting the following error when I try to query my table in AWS TSDB using Python: botocore.errorfactory.ValidationException: An error occurred (ValidationException) when calling the Query operation: The query syntax is invalid at line 3:18 This is the piece of code I run based on https://github.com/awslabs/amazon-timestream-tools/blob/mainline/sample_apps/python/QueryExample.py import boto3 from botocore.config import Config session = boto3.Session() query_client = session.client('timestream-query') DATABASE_NAME = "test" TABLE_NAME = "test_table" class Query(object): def __init__(self, client): self.client = client self.paginator = client.get_paginator('query') def run_query(self, query_string): try: page_iterator = self.paginator.paginate(QueryString=query_string) for page in page_iterator: self._parse_query_result(page) except Exception as err: print("Exception while running query:", err) query = Query(query_client) QUERY_1 = f""" SELECT plant, edgedevice, measure_value::double FROM {DATABASE_NAME}.{TABLE_NAME} LIMIT 10 """ query_output = query.run_query(QUERY_1)lg...

I'm writing to the magnetic storage of Timestream, and previously my writes were almost instantaneous. In the last two days, I've started noticing a very significant delay between writing and seeing the entries show up in the database - for example I've been waiting almost an hour now and still don't see the entries in the database. I've confirmed there are no errors on our end when writing to Timestream, and also saw a similar delay yesterday where the DB was populated after a few hours. Are there any possible causes of this on my end?lg...