OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

How to deploy a scheduled query on an empty table using CloudFormation?

  • Thread starter Thread starter Ben Flock
  • Start date Start date
B

Ben Flock

Guest
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:

ColumnDatatypeValues
timeTIMESTAMPYYYY-MM-DD hh:mm:ss.xxxx
dev_euiVARCHAR'6081F9xxxxxxxxxx'
channelVARCHAR'1', '2', '3', '4'
measure_nameVARCHAR'reading'
amperageDOUBLE0.0 - 50.0 (Amps)
stateBIGINT0, 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:

Code:
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:

Code:
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.
<p>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.</p>
<p>The Scheduled Query is supposed to read from a table called "Readings", which has the following schema:</p>
<div class="s-table-container">
<table class="s-table">
<thead>
<tr>
<th>Column</th>
<th>Datatype</th>
<th>Values</th>
</tr>
</thead>
<tbody>
<tr>
<td>time</td>
<td>TIMESTAMP</td>
<td>YYYY-MM-DD hh:mm:ss.xxxx</td>
</tr>
<tr>
<td>dev_eui</td>
<td>VARCHAR</td>
<td>'6081F9xxxxxxxxxx'</td>
</tr>
<tr>
<td>channel</td>
<td>VARCHAR</td>
<td>'1', '2', '3', '4'</td>
</tr>
<tr>
<td>measure_name</td>
<td>VARCHAR</td>
<td>'reading'</td>
</tr>
<tr>
<td>amperage</td>
<td>DOUBLE</td>
<td>0.0 - 50.0 (Amps)</td>
</tr>
<tr>
<td>state</td>
<td>BIGINT</td>
<td>0, 1, or 2</td>
</tr>
</tbody>
</table>
</div>
<p>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:</p>
<pre class="lang-sql prettyprint-override"><code>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
), ...
</code></pre>
<p>When we do <code>sam deploy</code>, we get the following error:</p>
<pre><code>Resource handler returned message: "line 3:8: Column 'dev_eui' does not exist (Service: AmazonTimestreamQuery; Status Code: 400; Error Code: ValidationException;
</code></pre>
<p>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.</p>
<p>Thanks.</p>
Continue reading...
 

Latest posts

S
Replies
0
Views
1
Safwan Aipuram
S
Top