apache / apache/pinot

[Feature] support simple cursor operations

Open
#8,668 4 comments 2 reactions 1 assignee Claimed by @atris View on GitHub
Dominant language
Java
Stars
6.1k
Forks
1.5k
Avg merge
1d 21h
Merged PRs (30d)
189

Description

Background
===
One of the frequently asked features of Pinot is the ability to retrieve the query result in small batches. For example,
- users may perform resource-intense computations on the result on a row-by-row base; and materializing the entire result set in memory is wasteful.
- support for some other systems like sql-alchemy (in https://github.com/python-pinot-dbapi/pinot-dbapi/) requires an engine to implement fetch-one and fetch-many (which are considered [cursor operations](https://en.wikipedia.org/wiki/Cursor_(databases)))

Challenge
===
currently, Pinot executes these subset-of-result executions via the 2 argument limit clause; for example
```
SELECT * FROM myTbl limit 200,100
```
returns 100 rows of the SQL result, starting from the 200th row.

This poses a problem
1. it doesn't guarantee that the results are stable as the underlying data could've changed / or early termination of the limit kicks in and we have no idea which server returns the result set first.
2. it does the entire query execution again for each subset, e.g. `limit 0, 100` then `limit 100, 100` doesn't retain the previous execution result.

Proposal
===
Support a simple cursor operation by enabling data caching in Pinot broker before serving back to the users.
1. Return results with the cursor-support turned on should associate the result with the requestID being executed.
2. Subsequence cursor operation can reuse the same requestID to seek for the next subset.
3. Pinot broker caches the cursor result set until it times out.

Alternative Solution(s)?
===
Please comment below for any additional alternative solution

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.