aws timestream查询以检测我什么时候无法获取数据

发布于 2025-02-06 09:13:50 字数 871 浏览 2 评论 0原文

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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(1

断念 2025-02-13 09:13:50

好的,我有一个答案(不一定是正确的答案)。我需要在子问题和主要查询中做什么。在子问题中,我会得到最大的时间。在主要查询中,最大时间比x秒少了。查询看起来像这样:

select count(Thing) from
(
select distinct Thing, max(time) as last
from "Uptime"."Uptime"
group by Thing) as t1
where t1.last < ago(5m)

这给出了我们在过去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:

select count(Thing) from
(
select distinct Thing, max(time) as last
from "Uptime"."Uptime"
group by Thing) as t1
where t1.last < ago(5m)

This gives a count of the number of things that we have not heard from within the last 5 minutes.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文