vinishjail97 commented on issue #887: URL: https://github.com/apache/incubator-xtable/issues/887#issuecomment-5962470527
Update on the secondary index for Iceberg tables, and a SQL interface for it. **#935 (Hudi backed secondary index for Iceberg)** now runs on the released Hudi 1.2.1, which includes apache/hudi#19869. It was validated on S3 against TPC-DS tables: `customer` (12M rows) and the partitioned `web_returns` (68.8M rows), with every lookup checked against Iceberg's own `_file` and `_pos`, including repeated values. **Spark SQL extensions (`xtable-spark-extensions`)**, stacked on #935 for now: https://github.com/vinishjail97/onetable/pull/3. It would be good to open it here once #935 lands. ```sql CREATE INDEX email_idx ON cat.db.customer USING xtable (email); REFRESH INDEX email_idx ON cat.db.customer; DROP INDEX email_idx ON cat.db.customer; -- reads only the files that hold these keys SELECT * FROM cat.db.customer WHERE email IN ('[email protected]', '[email protected]'); ``` - Setup is one entry after Iceberg's extensions: `org.apache.xtable.spark.extensions.XTableSparkSessionExtensions`. - `CREATE INDEX` and `DROP INDEX` are Spark's own syntax; a planner strategy runs them for Iceberg tables, since Iceberg's `SparkTable` does not implement `SupportsIndex`. The index definition is kept in the table properties. - For `col = literal` and `col IN (...)`, an optimizer rule looks the keys up and stages only the matching files as the scan's task set (`scan-task-set-id`). Iceberg does not prune files on a `_file` filter, so this is the route that works. Results are unchanged, because the filter stays in the plan. - The rule falls back to a normal scan when the index is behind the table's snapshot, for time travel and branch reads, and when Iceberg's own min/max pruning already leaves a small scan. - It follows Iceberg's layout: `xtable-spark-extensions` holds the code, and a runtime jar (#836) can bundle it. The work also found two gaps in #935's index API, fixed in the same stack: a second indexed column, or a column indexed again after a drop, was never built, because an incremental sync makes no Hudi commit without a new snapshot (now built with Hudi's indexer); and a failed sync was reported as success. It also adds `dropIndex` and `getLastSyncedSourceIdentifier()`. **RFC-4 (#933)** is updated to match the code: the actual Hudi target properties, the `Index` API and lookup schema, a new Spark SQL section, and the consistency check the SQL rule uses. Not covered yet: joins, `MERGE`, `UPDATE` and `DELETE` (they need the lookup on the other side's keys before the scan), refresh from `RunSync`, and Spark 3.5 / 4.x builds. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected]
