aws timestream查询以检测我什么时候无法获取数据
AWS Timestream使用基于SQL的查询语言。我有一个时间流数据库表,每4分钟都会从发件人那里收到心跳。想法是要检测到有所下降。数据看起来像这样:
Thing measure_name time measure_value::bigint
Machine1 Time 2022-06-10 20:27:51.095000000 1654892871095
Machine1 Time 2022-06-10 20:27:29.676000000 1654892849676
Machine2 Time 2022-06-10 20:25:31.654000000 1654892731654
Machine2 Time 2022-06-10 20:25:30.689000000 1654892730689
一周后,数据将退出桌子。
我需要以某种方式编写一个查询,该查询确定表中的所有内容,如果表格在表中没有任何心跳。我觉得这是可能的,但我只是不知道如何。我已经为某种特定的东西写了一个静态查询:
SELECT
CASE
WHEN count(Thing) > 0 THEN 'Up'
ELSE 'Down'
END AS Status
FROM "Uptime"."Uptime" WHERE time between ago(15m) and now() and Thing = 'Machine1'
但是它有一些弱点。我不想为每件事创建时编写一个新的查询,并在被破坏时删除特定的查询。
我想我想把所有东西都放在桌子上,然后对每件事进行查询,然后返回掉落的东西列表。我只需要在一个查询中执行此操作。
如何?
AWS Timestream uses a query language based on SQL. I have a Timestream database table that is receiving a heartbeat from senders every 4 minutes. The thought is to detect is something is down. The data looks like this:
Thing measure_name time measure_value::bigint
Machine1 Time 2022-06-10 20:27:51.095000000 1654892871095
Machine1 Time 2022-06-10 20:27:29.676000000 1654892849676
Machine2 Time 2022-06-10 20:25:31.654000000 1654892731654
Machine2 Time 2022-06-10 20:25:30.689000000 1654892730689
The data drops out of the table after a week.
I need to somehow write a query that determines for all things in the table if each thing is in the table does not have any heartbeats in the last x minutes. I feel like this is possible but I just don't know how. I have written a static query for a particular thing like so:
SELECT
CASE
WHEN count(Thing) > 0 THEN 'Up'
ELSE 'Down'
END AS Status
FROM "Uptime"."Uptime" WHERE time between ago(15m) and now() and Thing = 'Machine1'
It has some weaknesses though. I don't want to write a new query for each thing as they are created and remove a thing specific query when the thing is destroyed.
I guess I want to get all the things in the table and then do a query for each thing and return a list of things that are down. I just need to do this in a single query.
How?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
好的,我有一个答案(不一定是正确的答案)。我需要在子问题和主要查询中做什么。在子问题中,我会得到最大的时间。在主要查询中,最大时间比x秒少了。查询看起来像这样:
这给出了我们在过去5分钟内从未听说过的事情的数量。
OK, I have an answer (not necessarily the right answer). What I needed to do in a sub-query and then a main query. In the sub-query I would get the max time grouped by thing. IN the main query get the count of the things that have a max time of less then x seconds. The query looks like this:
This gives a count of the number of things that we have not heard from within the last 5 minutes.