You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hudi.apache.org by "hudi-bot (via GitHub)" <gi...@apache.org> on 2025/11/30 03:47:40 UTC
[I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
hudi-bot opened a new issue, #14869:
URL: https://github.com/apache/hudi/issues/14869
In the module of DeltaStreamer, Option of QUERY_TYPE_OPT_KEY and BEGIN_INSTANTTIME_OPT_KEY is used to tell the DeltaStreamer to query data after the specific time.
Such as method is not very convenient for user. So if we can implement the function that User can set BEGIN_INSTANTTIME_OPT_KEY and BEGIN_INSTANTTIME_OPT_KEY at the sql, which is not only very convinient for user, also very a elegant implementation.
## JIRA info
- Link: https://issues.apache.org/jira/browse/HUDI-2470
- Type: Improvement
---
## Comments
21/Sep/21 12:23;David_Liang;now we have preliminary implement this function, testing now;;;
---
22/Sep/21 02:05;David_Liang;please assign to me? ;;;
---
08/Oct/21 00:54;vinoth;Assigned it you! Look forward to the PR ;;;
---
21/Oct/21 07:03;xushiyan;[~David_Liang] any chance you could put up an early version of the PR ? ;;;
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "nsivabalan (via GitHub)" <gi...@apache.org>.
nsivabalan closed issue #14869: use commit_time in the WHERE STATEMENT to optimize the incremental query
URL: https://github.com/apache/hudi/issues/14869
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "praneethkaturi (via GitHub)" <gi...@apache.org>.
praneethkaturi commented on issue #14869:
URL: https://github.com/apache/hudi/issues/14869#issuecomment-4385084756
@xushiyan thanks!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "nsivabalan (via GitHub)" <gi...@apache.org>.
nsivabalan commented on issue #14869:
URL: https://github.com/apache/hudi/issues/14869#issuecomment-4795273573
This appears to be addressed on master. HUDI-6223 (commit `243098c3`, PR #8729, merged June 2023) added the `hudi_table_changes` Spark SQL table-valued function, which lets you run incremental queries directly from SQL with start/end instant times — no need to set `BEGIN_INSTANTTIME_OPT_KEY` as a DataFrame option.
Example:
```sql
SELECT * FROM hudi_table_changes('my_table', 'latest_state', '20231001000000', '20231002000000');
-- or use 'earliest' for the start instant
SELECT * FROM hudi_table_changes('my_table', 'latest_state', 'earliest');
```
See `HoodieTableChanges.scala` and tests in `TestHoodieTableValuedFunction.scala` / `TestSparkSqlCoreFlow.scala`. Closing out as resolved seems appropriate — feel free to reopen if the original ask was something different (e.g. literal `WHERE _hoodie_commit_time > ...` predicate pushdown into incremental scan).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "codope (via GitHub)" <gi...@apache.org>.
codope commented on issue #14869:
URL: https://github.com/apache/hudi/issues/14869#issuecomment-4524807700
@praneethkaturi thanks for digging into this issue. I think Option 2 makes most sense to me and also a very useful contribution:
> 2. Just make the existing query faster. Same SQL stays a snapshot query, but Hudi uses the _hoodie_commit_time predicate to skip files that can't possibly match.
The JIRA issue is old and at the time i don't think we had data skipping based on meta columns. Option 2 is a natural extension of what Hudi already does. `_hoodie_commit_time` is already in [META_COLS_TO_ALWAYS_INDEX](https://github.com/apache/hudi/blob/e299b84b10b5ccd4ee5c75e541f0109a85549d7a/hudi-common/src/main/java/org/apache/hudi/metadata/HoodieTableMetadataUtil.java#L1613), so the column stats partition of the metadata table already tracks min/max for it per file. The work is wiring HoodieFileIndex / ColumnStatsIndexSupport (and PartitionStatsIndexSupport) to honor a `_hoodie_commit_time` predicate the way they honor any other range predicate, and then verifying the same applies to log files for MOR. This helps Hudi's two core workloads simultaneously: snapshot reads with a "since" filter (very common in batch ETL backfills and audit queries) become file-pruned, and incremental reads with additional commit-time filters get tighter pruning on top of the timeline-driven file
selection.
Regarding engine scope, let's start with Spark. The metadata table column_stats path and `HoodieFileIndex` are already very mature there, and that's where the ticket originated (DeltaStreamer). Flink should follow as a separate PR; the underlying metadata is engine-independent.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "xushiyan (via GitHub)" <gi...@apache.org>.
xushiyan commented on issue #14869:
URL: https://github.com/apache/hudi/issues/14869#issuecomment-4465041417
For @yihua @bhasudha to share directions on this.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "praneethkaturi (via GitHub)" <gi...@apache.org>.
praneethkaturi commented on issue #14869:
URL: https://github.com/apache/hudi/issues/14869#issuecomment-4366079684
@xushiyan is this still open? I'd love to pick this up!
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "xushiyan (via GitHub)" <gi...@apache.org>.
xushiyan commented on issue #14869:
URL: https://github.com/apache/hudi/issues/14869#issuecomment-4374093265
@praneethkaturi sure go ahead
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] use commit_time in the WHERE STATEMENT to optimize the incremental query [hudi]
Posted by "praneethkaturi (via GitHub)" <gi...@apache.org>.
praneethkaturi commented on issue #14869:
URL: https://github.com/apache/hudi/issues/14869#issuecomment-4453786892
Hi @xushiyan, thanks for letting me take this on. This is my first contribution to Hudi and I'm still finding my way around the codebase, so I'd really appreciate some guidance before I commit to a direction.
I've spent the last few days reading the Spark read path - DefaultSource, IncrementalRelationV2, HoodieFileIndex and I think I have a rough mental model of how snapshot and incremental queries are dispatched today. But when I re-read the ticket, I realized I'm not sure what behavior we actually want to ship. A few different things would all be consistent with the description:
1. Auto-detect and rewrite. A normal SELECT * FROM trips WHERE _hoodie_commit_time > 'X' gets transparently turned into an incremental query under the hood.
2. Just make the existing query faster. Same SQL stays a snapshot query, but Hudi uses the _hoodie_commit_time predicate to skip files that can't possibly match.
3. SQL-native syntax for the existing options. Instead of WHERE-clause magic, introduce a hint or new syntax so users can say "this is an incremental query starting at X" directly in SQL.
These feel quite different to me both in scope and risk, and I don't have enough context to pick the right one on my own. A few questions to help me get unstuck:
1. Engine scope. Should I focus on Spark for now, or is this expected to work for Flink too? The ticket mentions DeltaStreamer (Spark-only) but the title is general, so I wasn't sure.
2. Which of the three interpretations matches what you had in mind? Or is the scope still open and you'd like me to propose one?
3. Are there known traps — things like "don't change the meaning of a regular SELECT" or "must work with archived commits" that I should design around?
**Any additional info regarding the scope will be helpful too**! Apologies for the long-winded comment, I'd rather
over-communicate at the start than head off in the wrong direction and waste your time on review.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: commits-unsubscribe@hudi.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org