apache / apache/pinot

Create DATETIMECONVERTWINDOWHOP function

Open
#11,775 11 comments 7 reactions 0 assignees View on GitHub
feature stale
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

# Create a Window HOP function similar to DATETIMECONVERT.

The `DATETIMECONVERTWINDOWHOP` converts the value from a column that contains an epoch timestamp into another time unit and buckets based on the given time granularity and window hop size.

A hopping time window has a fixed duration (hopWindow paramenter) and hops by a specified hop interval (outputGranularity parameter). If the hop interval is smaller than the window size, hopping windows are overlapping. Thus, rows can be assigned to multiple windows. For example, a hopping window of 15 minutes size and 5 minute hop interval assigns each row to 3 different windows of 15 minute size, which are evaluated in an interval of 5 minutes. This is why the function returns an array of timestamps, in this example the array would contain 3 elements.

# Signature
```
DATETIMECONVERTWINDOWHOP(columnName, inputFormat, outputFormat, outputGranularity, hopWindow)
inputFormat and outputFormat are defined using the following structure:

# Usage Examples
These examples are based on the [Batch JSON Quick Start](https://docs.pinot.apache.org/basics/getting-started/quick-start#batch-json).
created_at_timestamp from milliseconds since epoch to seconds since epoch, bucketed to 1 hour window with 15 min granularity:

```
select id,
created_at_timestamp,
cast(created_at_timestamp AS long) AS timeInMs,
DATETIMECONVERTWINDOWHOP(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:SECONDS:EPOCH',
'15:MINUTES',
'1:HOURS'
) AS windowHops
from githubEvents
WHERE id = 7044874134
```
id | created_at_timestamp | timeInMs | windowHops
-- | -- | -- | --
7044874134 | 2018-01-01 11:00:00.0 | 1514804402000 | [1514804402,   1514803502, 1514802602, 1514801702]

Moving window of unique user counts per hour with 15 min granularity:

```
select
DATETIMECONVERTWINDOWHOP(
created_at_timestamp,
'1:MILLISECONDS:EPOCH',
'1:SECONDS:EPOCH',
'15:MINUTES',
'1:HOURS'
) AS windowHops,
DISTINCTCOUNT(id) AS unique_prs
from githubEvents
group by 1
ORDER BY 1
```

windowHops | unique_prs
-- | --
1514801700 | 6680
1514802600 | 10000
1514803500 | 10000
1514804400 | 10000
1514805300 | 3320

Contributor guide

Open the contributing guide

Research direction

Start by locating the existing DATETIMECONVERT function implementation and its tests, then trace how SQL functions are registered and validated. Add the analogous hopping-window behavior described by the signature and examples, and verify the documented query results and returned timestamp arrays with focused tests.

Written by the indexing model from the issue text.

Assessment

Tech stack
java
Domain
databases
Issue type
Feature
Difficulty
4/5
Estimated time
3-5 days
Activity status
Active
Clarity
Mostly clear
Newbie friendliness
68/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.