apache / apache/iceberg

Support version/timestamp range queries in Spark SQL syntax

Open
#15,699 4 comments 0 reactions 0 assignees View on GitHub
improvement
Dominant language
Java
Stars
9.2k
Forks
3.5k
Avg merge
2d 11h
Merged PRs (30d)
132

Description

### Feature Request / Improvement

Spark SQL supports point-in-time queries via `VERSION AS OF` and `TIMESTAMP AS OF`, but there is no SQL syntax for querying a range of versions or timestamps. This is useful for incremental consumption patterns.

The underlying scan infrastructure fully supports this. `IncrementalScan` provides `fromSnapshotInclusive`, `fromSnapshotExclusive`, and `toSnapshot`.

There is:
```
CALL spark_catalog.system.create_changelog_view(
table => 'db.tbl',
options => map('start-snapshot-id','1','end-snapshot-id', '2')
);
SELECT * FROM tbl_changes;
```
But this does not provide a direct SQL query syntax for version ranges.

## Proposal

Add SQL syntax for version/timestamp range queries. Possible forms (open for discussion):

```sql
-- by snapshot ID
SELECT * FROM db.table VERSION BETWEEN 1 AND 5

-- by timestamp
SELECT * FROM db.table TIMESTAMP BETWEEN '2024-01-01' AND '2024-06-01'

-- by tag/branch ref
SELECT * FROM db.table VERSION BETWEEN 'tag-a' AND 'tag-b'
```

### Query engine

Spark

### Willingness to contribute

- [ ] I can contribute this improvement/feature independently
- [x] I would be willing to contribute this improvement/feature with guidance from the Iceberg community
- [ ] I cannot contribute this improvement/feature at this time

Contributor guide

Open the contributing guide

Research direction

Start with the existing Spark SQL VERSION AS OF and TIMESTAMP AS OF support, then inspect IncrementalScan and its fromSnapshotInclusive, fromSnapshotExclusive, and toSnapshot fields. Review the create_changelog_view example for current range-query behavior. Done means the community agrees on range syntax and it uses the existing scan capabilities for snapshot IDs, timestamps, or refs.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, spark, sql
Domain
data-engineering, databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Quiet
Clarity
Needs clarification
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.