clickhouse - how get count datetime per 1minute or 1day ,

Question

I have a table in Clickhouse. for keep statistics and metrics.
and structure is:

datetime|metric_name|metric_value

I want to keep statistics and limit number of accesses in 1 minute, 1 hour, 1 day and so on. So I need event counts in last minute, hour or day for every metric_name and I want to prepare statistics in a chart.
I do not know how to make a query. I get the count of metrics statistics based on the exact for example 1 minute, 1 hour, 1 day and so on.

I used to work on inflxdb:

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND time >= now() - 1h GROUP BY time(5m) fill(0)

In fact, I want to get the number of each metric per 5 minutes in the previous 1 hour.

I do not know how to use aggregations for this problem

score 7 · Accepted Answer · edited Jun 20 '20 at 09:12

7

ClickHouse has functions for generating Date/DateTime group buckets such as toStartOfWeek, toStartOfHour, toStartOfFiveMinute. You can also use intDiv function to manually divide value ranges. However the fill feature is still in the roadmap.

For example, you can rewrite the influx sql without the fill in ClickHouse like this,

SELECT SUM(value) FROM `TABLE` WHERE `metric_name`=`metric_value` AND 
time >= now() - 1h GROUP BY toStartOfFiveMinute(time)

You can also refer to this discussion https://github.com/yandex/ClickHouse/issues/379

update

There is a timeSlots function that can help generating empty buckets. Here is a working example

SELECT
    slot,
    metric_value_sum
FROM
(
    SELECT
        toStartOfFiveMinute(datetime) AS slot,
        SUM(metric_value) AS metric_value_sum
    FROM metrics
    WHERE (metric_name = 'k1') AND (datetime >= (now() - toIntervalHour(1)))
    GROUP BY slot
)
ANY RIGHT JOIN
(
    SELECT arrayJoin(timeSlots(now() - toIntervalHour(1), toUInt32(3600), 300)) AS slot
) USING (slot)

edited Jun 20 '20 at 09:12

Community

1
1

answered Jan 02 '19 at 15:17

Amos

3,238
4
19
41

Thank you.In your opinion, to fill in empty values. Is the following method suitable and optimal? https://stackoverflow.com/a/50325639/1348284 – aziminia Jan 03 '19 at 08:25
Thank you for update! how can i use intDiv instead toStartOfFiveMinute for any ranges? For example, for the past 2 minutes every 10 seconds. – aziminia Jan 03 '19 at 10:40
2

Here is the expression that rounds to every 10 seconds `toDateTime(intDiv(toUInt32(now()), 10) * 10);` hope you get the idea – Amos Jan 03 '19 at 13:14
1

This query: `SELECT SUM(value) FROM TABLE WHERE metric_name = metric_value AND time >= now() - 1h GROUP BY toStartOfFiveMinute(time)` returns an exception: `DB::Exception: Column created_at is not under aggregate function and not in GROUP BY..` – quickinsights Jul 17 '20 at 19:05

clickhouse - how get count datetime per 1minute or 1day ,

1 Answers1

update