Timestamp Index

This feature is supported from Pinot 0.11+. 
Pinot introduces the TIMESTAMP data type from Pinot 0.8.0 release. This data type stores value as millisecond epoch long value internally.
Typically for analytics queries, users won't need this low level granularity, scanning the data and time value conversion can be costly for the big size of data.
A common query pattern for timestamp columns is filtering on a time range and then group by with different time granularities(days/month/etc).
The existing implementation requires the query executor to extract values, apply the transform functions then do filter/groupBy, no leverage on the dictionary or index.
Hence the inspiration of TIMESTAMP INDEX, which is used to improve the query performance for range query and group by queries on TIMESTAMP columns.
Supported data type
TIMESTAMP index can only be created on TIMESTAMP data type.
Timestamp Index
Users can configure the most useful granularities for a Timestamp data type column.
1.Pinot will pre-generate one column per time granularity with forward index and range index. The naming convention is ${ts_column_name}${ts_granularity}, e.g. Timestamp column ts with granularities DAY, MONTH will have two extra columns generated: $ts$DAY and $ts$MONTH.
2.Query overwrite for predicate and selection/group by:
2.1 GROUP BY: functions like dateTrunc('DAY', ts) will be translated to use the underly column $ts$DAY to fetch data.
2.2 PREDICATE: range index is auto-built for all granularity columns.
Example query usage:
1
select count(*), datetrunc('WEEK', ts) as tsWeek from airlineStats WHERE tsWeek > fromDateTime('2014-01-16', 'yyyy-MM-dd') group by tsWeek  limit 10
Copied!
Some preliminary benchmark shows the query perf over 2.7 billion records improved from 45 secs to 4.2 secs
1
select dateTrunc('YEAR', event_time) as y, dateTrunc('MONTH', event_time) as m,  sum(pull_request_commits) from githubEvents group by y, m limit 1000  Option(timeoutMs=3000000)
Copied!
​
Without Timestamp Index
vs.
With Timestamp Index
Usage
Timestamp index is configured per column basis inside the fieldConfigList section in table config.
Users need to specify TIMESTAMP as part of the indexTypes. Then in the field timestampConfig, specify the granularities that you want to index.
Sample config:
1
{
2
  "tableName": "airlineStats",
3
  "tableType": "OFFLINE",
4
  "segmentsConfig": {
5
    "timeColumnName": "DaysSinceEpoch",
6
    "timeType": "DAYS",
7
    "segmentPushType": "APPEND",
8
    "segmentAssignmentStrategy": "BalanceNumSegmentAssignmentStrategy",
9
    "replication": "1"
10
  },
11
  "tenants": {},
12
  "fieldConfigList": [
13
    {
14
      "name": "ts",
15
      "encodingType": "DICTIONARY",
16
      "indexTypes": ["TIMESTAMP"],
17
      "timestampConfig": {
18
        "granularities": [
19
          "DAY",
20
          "WEEK",
21
          "MONTH"
22
        ]
23
      }
24
    }
25
  ],
26
  "tableIndexConfig": {
27
    "loadMode": "MMAP"
28
  },
29
  "metadata": {
30
    "customConfigs": {}
31
  },
32
  "ingestionConfig": {}
33
}
34
​
Copied!
​

Geospatial

Next - Basics

Releases

Last modified 16d ago

Copy link

Contents

Supported data type

Timestamp Index

Usage