https://pinot.apache.org/ logo
Join Slack
Powered by
Hi all, I want to know about timestamp index. I ha...
# general
e
Hi all, I want to know about timestamp index. I have a dataset containing 1 billion records representing user behavior logs. It contains details such as request_timestamp, url, action, and section. I want to do 5-minute aggregations on this dataset. What I'm curious about is if a timestamp index with "MINUTE" granularities is applied to a timestamp column, can this index be used for 5-minute aggregation? For example, if I use the round function for the 
$ts$MINUTE
column as a group by condition as shown below, will a timestamp index be used?
Copy code
select 
	toDateTime(round($request_timestamp$MINUTE, 300000), 'yyyy-MM-dd HH:mm', 'Asia/Seoul') AS time_bucket
	, url
	, action
	, count(1)
from user_behavior_log
group by time_bucket, url, action
How should I query for the 5 minute aggregation?
m
I don’t think it will, but….you can always create your own derived column that stores each row’s timestamp at a 5 minute interval. You could then create a range index on that derived column. Would that work?
e
I agree. Perhaps, if I create a derived column at 5-minute intervals, it will work. 😭 Actually, what I really want to do is use a timestamp index to aggregate at variable intervals such as 5 minutes, 10 minutes, and 30 minutes at query time. Maybe it was impossible?
m
I mean the timestamp index is effectively a wrapper for a very specific type of timestamp filtering
i.e. when you’re using the dataTrunc function to truncate the date and search on that value
it then abstracts adding a range index to each of the dateTrunc granularities
it then rewrites queries to use those derived columns (+ range index indirectly)
Copy code
select datetrunc('WEEK', DateEpoch) as tsWeek, count(*) 
from crimes_indexed
GROUP BY tsWeek
limit 10
becomes:
Copy code
select  $DateEpoch$WEEK as tsWeek, count(*) 
from crimes_indexed
GROUP BY tsWeek
limit 10
the timestamp index is doing all its work at segment creation time
so you have to specify your granularities up front
could definitely do what you’re saying with derived columns
if that solves your problem I can write up how to do it
j
what was the benefit of adding the timestamp index over just having folks define derived columns?
Open in Slack
PreviousNext
Powered by