0

We are using AWS SAM to deploy our stack to AWS Cloudformation. In our deployment, we create a database and create 4 tables for the database. We also create a scheduled query to periodically perform aggregations on one of the tables. However, CloudFormation seems to be attempting to validate the query before pushing it to timestream. Since Timestream has a dynamic schema, the columns we are attempting to reference are not found until data has been pushed to the table, but because this is an initial deployment, there is no data in the table, so the schema does not yet contain the columns we are looking for. We are looking for a way to deploy the scheduled query without needing the table it is reading from to have data at the time of deployment.

The Scheduled Query is supposed to read from a table called "Readings", which has the following schema:

Column Datatype Values
time TIMESTAMP YYYY-MM-DD hh:mm:ss.xxxx
dev_eui VARCHAR '6081F9xxxxxxxxxx'
channel VARCHAR '1', '2', '3', '4'
measure_name VARCHAR 'reading'
amperage DOUBLE 0.0 - 50.0 (Amps)
state BIGINT 0, 1, or 2

and perform an aggregation on this data every hour. New data are added to this table every minute. The scheduled query runs every hour. It begins as follows:

WITH raw_data as (
    SELECT time
        ,dev_eui
        ,channel
        ,min(state) as state
    FROM MachineMonitoring.Readings
    WHERE time BETWEEN @scheduled_runtime - 1h AND @scheduled_runtime
    GROUP BY time, dev_eui, channel
), ...

When we do sam deploy, we get the following error:

Resource handler returned message: "line 3:8: Column 'dev_eui' does not exist (Service: AmazonTimestreamQuery; Status Code: 400; Error Code: ValidationException;

Our current solution is to have two separate SAM templates, one for an initial deployment and one for after data has been pushed to timestream. We are looking for a more intuitive solution.

Thanks.

Ben Flock
  • 1

0 Answers0