vinishjail97 commented on code in PR #992:
URL: https://github.com/apache/incubator-xtable/pull/992#discussion_r4394929146
##########
website/docs/how-to-index-sql.md:
##########
@@ -0,0 +1,125 @@
+---
+sidebar_position: 4
+title: "Index Iceberg tables with Spark SQL"
+---
+
+# Index Iceberg tables with Spark SQL
+
+Apache XTable™ (Incubating) can keep a secondary index on a column of an
Apache Iceberg table. The index maps each value
+of the column to the data files that hold it, and is stored as an Apache Hudi
metadata table next to
+the table's data. The XTable Spark SQL extensions let you manage the index
with SQL, and they use it
+automatically for point lookups:
+
+```sql
+CREATE INDEX email_idx ON prod.db.customer USING xtable (email);
+
+-- reads only the files that hold these emails
+SELECT * FROM prod.db.customer WHERE email IN ('[email protected]',
'[email protected]');
+```
+
+## Setting up Spark
+
+The extensions need Spark 3.4 with Scala 2.12, the Iceberg Spark runtime and
the Hudi Spark bundle.
+They ship in the [XTable Spark runtime jar](how-to-spark-runtime), so no other
XTable jar is needed.
+Until a release includes them, build the jar from source:
+
+```shell
+./mvnw -pl xtable-spark-runtime -am package -DskipTests
+```
+
+Put these jars on the classpath of the driver and the executors. Use
+`spark.driver.extraClassPath` and `spark.executor.extraClassPath` rather than
`--jars`, so that
+XTable and Hudi are loaded by the same class loader:
+
+- `xtable-spark-runtime/target/xtable-spark-runtime_2.12-<version>.jar`
+- `org.apache.hudi:hudi-spark3.4-bundle_2.12:1.2.1`
+- `org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:1.9.2`
+
+Then add the extensions after Iceberg's, and use Kryo, which Hudi needs:
+
+```properties
+spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions,org.apache.xtable.spark.extensions.XTableSparkSessionExtensions
+spark.serializer=org.apache.spark.serializer.KryoSerializer
+```
+
+The extensions work with any Iceberg catalog that Spark is configured with.
+
+## Managing indexes
+
+```sql
+-- builds the index from the current snapshot of the table
+CREATE INDEX [IF NOT EXISTS] email_idx ON prod.db.customer USING xtable (email)
+ [OPTIONS ('xtable.hudi.target.metadata.record.index.min.filegroup.count' =
'64')];
+
+-- syncs the index with the current snapshot of the table
+REFRESH INDEX email_idx ON prod.db.customer;
+
+DROP INDEX [IF EXISTS] email_idx ON prod.db.customer;
+```
+
+- An index covers one top level column of type `string`, `int` or `bigint`. A
table can have one
+ index, and its column does not change. To index another column, drop the
index and create a new
+ one.
+- The index definition is stored in the table properties as
`xtable.index.<name>.column`, and
+ options as `xtable.index.<name>.option.<key>`. `SHOW TBLPROPERTIES` lists
them.
+- Options are passed to the Hudi target that stores the index, for example the
+ `xtable.hudi.target.metadata.record.index.*` file group counts.
+- The index files are stored under `.hoodie/` in the data folder of the table.
Dropping the index
+ deletes that folder and keeps the data files.
+
+## Keeping the index current
Review Comment:
Restructured in b57e1b83 into two parts: building the index with a sync job,
and querying it with SQL and DataFrames.
##########
website/docs/how-to-index-sql.md:
##########
@@ -0,0 +1,125 @@
+---
+sidebar_position: 4
+title: "Index Iceberg tables with Spark SQL"
+---
+
+# Index Iceberg tables with Spark SQL
+
+Apache XTable™ (Incubating) can keep a secondary index on a column of an
Apache Iceberg table. The index maps each value
+of the column to the data files that hold it, and is stored as an Apache Hudi
metadata table next to
+the table's data. The XTable Spark SQL extensions let you manage the index
with SQL, and they use it
+automatically for point lookups:
+
+```sql
+CREATE INDEX email_idx ON prod.db.customer USING xtable (email);
+
+-- reads only the files that hold these emails
+SELECT * FROM prod.db.customer WHERE email IN ('[email protected]',
'[email protected]');
+```
+
+## Setting up Spark
+
+The extensions need Spark 3.4 with Scala 2.12, the Iceberg Spark runtime and
the Hudi Spark bundle.
+They ship in the [XTable Spark runtime jar](how-to-spark-runtime), so no other
XTable jar is needed.
+Until a release includes them, build the jar from source:
+
+```shell
+./mvnw -pl xtable-spark-runtime -am package -DskipTests
+```
+
+Put these jars on the classpath of the driver and the executors. Use
+`spark.driver.extraClassPath` and `spark.executor.extraClassPath` rather than
`--jars`, so that
+XTable and Hudi are loaded by the same class loader:
+
+- `xtable-spark-runtime/target/xtable-spark-runtime_2.12-<version>.jar`
+- `org.apache.hudi:hudi-spark3.4-bundle_2.12:1.2.1`
+- `org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:1.9.2`
+
+Then add the extensions after Iceberg's, and use Kryo, which Hudi needs:
+
+```properties
+spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions,org.apache.xtable.spark.extensions.XTableSparkSessionExtensions
+spark.serializer=org.apache.spark.serializer.KryoSerializer
+```
+
+The extensions work with any Iceberg catalog that Spark is configured with.
+
+## Managing indexes
+
+```sql
+-- builds the index from the current snapshot of the table
+CREATE INDEX [IF NOT EXISTS] email_idx ON prod.db.customer USING xtable (email)
+ [OPTIONS ('xtable.hudi.target.metadata.record.index.min.filegroup.count' =
'64')];
+
+-- syncs the index with the current snapshot of the table
+REFRESH INDEX email_idx ON prod.db.customer;
+
+DROP INDEX [IF EXISTS] email_idx ON prod.db.customer;
+```
+
+- An index covers one top level column of type `string`, `int` or `bigint`. A
table can have one
+ index, and its column does not change. To index another column, drop the
index and create a new
+ one.
+- The index definition is stored in the table properties as
`xtable.index.<name>.column`, and
+ options as `xtable.index.<name>.option.<key>`. `SHOW TBLPROPERTIES` lists
them.
+- Options are passed to the Hudi target that stores the index, for example the
+ `xtable.hudi.target.metadata.record.index.*` file group counts.
+- The index files are stored under `.hoodie/` in the data folder of the table.
Dropping the index
+ deletes that folder and keeps the data files.
+
+## Keeping the index current
+
+A sync job that already runs XTable keeps the index current. Add a Hudi target
with the index
+settings to the `--datasetconfig` of `XTableSparkSync` in the runtime jar, or
of `RunSync`:
+
+```yaml
+sourceFormat: ICEBERG
+targetFormats:
+ - HUDI
+datasets:
+ - tableBasePath: s3://bucket/warehouse/db.db/customer
+ tableDataPath: s3://bucket/warehouse/db.db/customer/data
+ tableName: customer
+ namespace: db
+ targetProperties:
+ HUDI:
+ xtable.hudi.target.table_version: '9'
+ xtable.hudi.target.execution.engine: spark
+ xtable.hudi.target.secondary.index.column: c_email_address
+```
+
+The column must match the column of `CREATE INDEX`, otherwise the sync fails.
Without a sync job,
+run `REFRESH INDEX` after writes.
+
+## Querying with an index
+
+Queries do not change. When a filter compares an indexed column with literals,
for example
+`email = 'x'` or `email IN ('x', 'y')`, possibly combined with other filters
by `AND`, the extensions
+look the values up in the index and read only the files that hold them. The
query returns the same
+rows as without the index.
+
+The index is not used when:
+
+- the table has new snapshots that the index was not refreshed with. The query
then reads the table
Review Comment:
Yes, in b57e1b83 the stale case logs a WARN that names both snapshots and
says to run the XTable sync.
##########
website/docs/how-to-index-sql.md:
##########
@@ -0,0 +1,125 @@
+---
+sidebar_position: 4
+title: "Index Iceberg tables with Spark SQL"
+---
+
+# Index Iceberg tables with Spark SQL
+
+Apache XTable™ (Incubating) can keep a secondary index on a column of an
Apache Iceberg table. The index maps each value
+of the column to the data files that hold it, and is stored as an Apache Hudi
metadata table next to
+the table's data. The XTable Spark SQL extensions let you manage the index
with SQL, and they use it
+automatically for point lookups:
+
+```sql
+CREATE INDEX email_idx ON prod.db.customer USING xtable (email);
+
+-- reads only the files that hold these emails
+SELECT * FROM prod.db.customer WHERE email IN ('[email protected]',
'[email protected]');
+```
+
+## Setting up Spark
+
+The extensions need Spark 3.4 with Scala 2.12, the Iceberg Spark runtime and
the Hudi Spark bundle.
+They ship in the [XTable Spark runtime jar](how-to-spark-runtime), so no other
XTable jar is needed.
+Until a release includes them, build the jar from source:
+
+```shell
+./mvnw -pl xtable-spark-runtime -am package -DskipTests
+```
+
+Put these jars on the classpath of the driver and the executors. Use
+`spark.driver.extraClassPath` and `spark.executor.extraClassPath` rather than
`--jars`, so that
+XTable and Hudi are loaded by the same class loader:
+
+- `xtable-spark-runtime/target/xtable-spark-runtime_2.12-<version>.jar`
+- `org.apache.hudi:hudi-spark3.4-bundle_2.12:1.2.1`
+- `org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:1.9.2`
+
+Then add the extensions after Iceberg's, and use Kryo, which Hudi needs:
+
+```properties
+spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions,org.apache.xtable.spark.extensions.XTableSparkSessionExtensions
+spark.serializer=org.apache.spark.serializer.KryoSerializer
+```
+
+The extensions work with any Iceberg catalog that Spark is configured with.
+
+## Managing indexes
+
+```sql
+-- builds the index from the current snapshot of the table
+CREATE INDEX [IF NOT EXISTS] email_idx ON prod.db.customer USING xtable (email)
+ [OPTIONS ('xtable.hudi.target.metadata.record.index.min.filegroup.count' =
'64')];
+
+-- syncs the index with the current snapshot of the table
+REFRESH INDEX email_idx ON prod.db.customer;
+
+DROP INDEX [IF EXISTS] email_idx ON prod.db.customer;
+```
+
+- An index covers one top level column of type `string`, `int` or `bigint`. A
table can have one
+ index, and its column does not change. To index another column, drop the
index and create a new
+ one.
+- The index definition is stored in the table properties as
`xtable.index.<name>.column`, and
+ options as `xtable.index.<name>.option.<key>`. `SHOW TBLPROPERTIES` lists
them.
+- Options are passed to the Hudi target that stores the index, for example the
+ `xtable.hudi.target.metadata.record.index.*` file group counts.
+- The index files are stored under `.hoodie/` in the data folder of the table.
Dropping the index
+ deletes that folder and keeps the data files.
+
+## Keeping the index current
+
+A sync job that already runs XTable keeps the index current. Add a Hudi target
with the index
+settings to the `--datasetconfig` of `XTableSparkSync` in the runtime jar, or
of `RunSync`:
+
+```yaml
+sourceFormat: ICEBERG
+targetFormats:
+ - HUDI
+datasets:
+ - tableBasePath: s3://bucket/warehouse/db.db/customer
+ tableDataPath: s3://bucket/warehouse/db.db/customer/data
+ tableName: customer
+ namespace: db
+ targetProperties:
+ HUDI:
+ xtable.hudi.target.table_version: '9'
+ xtable.hudi.target.execution.engine: spark
+ xtable.hudi.target.secondary.index.column: c_email_address
+```
+
+The column must match the column of `CREATE INDEX`, otherwise the sync fails.
Without a sync job,
+run `REFRESH INDEX` after writes.
+
+## Querying with an index
Review Comment:
`SET spark.xtable.index.pruning.enabled=false` turns index reads off without
removing the jar; the doc now has a section for it.
##########
website/docs/how-to-index-sql.md:
##########
@@ -0,0 +1,125 @@
+---
+sidebar_position: 4
+title: "Index Iceberg tables with Spark SQL"
+---
+
+# Index Iceberg tables with Spark SQL
+
+Apache XTable™ (Incubating) can keep a secondary index on a column of an
Apache Iceberg table. The index maps each value
+of the column to the data files that hold it, and is stored as an Apache Hudi
metadata table next to
+the table's data. The XTable Spark SQL extensions let you manage the index
with SQL, and they use it
+automatically for point lookups:
+
+```sql
+CREATE INDEX email_idx ON prod.db.customer USING xtable (email);
+
+-- reads only the files that hold these emails
+SELECT * FROM prod.db.customer WHERE email IN ('[email protected]',
'[email protected]');
+```
+
+## Setting up Spark
+
+The extensions need Spark 3.4 with Scala 2.12, the Iceberg Spark runtime and
the Hudi Spark bundle.
+They ship in the [XTable Spark runtime jar](how-to-spark-runtime), so no other
XTable jar is needed.
+Until a release includes them, build the jar from source:
+
+```shell
+./mvnw -pl xtable-spark-runtime -am package -DskipTests
+```
+
+Put these jars on the classpath of the driver and the executors. Use
+`spark.driver.extraClassPath` and `spark.executor.extraClassPath` rather than
`--jars`, so that
+XTable and Hudi are loaded by the same class loader:
+
+- `xtable-spark-runtime/target/xtable-spark-runtime_2.12-<version>.jar`
+- `org.apache.hudi:hudi-spark3.4-bundle_2.12:1.2.1`
+- `org.apache.iceberg:iceberg-spark-runtime-3.4_2.12:1.9.2`
+
+Then add the extensions after Iceberg's, and use Kryo, which Hudi needs:
+
+```properties
+spark.sql.extensions=org.apache.iceberg.spark.extensions.IcebergSparkSessionExtensions,org.apache.xtable.spark.extensions.XTableSparkSessionExtensions
+spark.serializer=org.apache.spark.serializer.KryoSerializer
+```
+
+The extensions work with any Iceberg catalog that Spark is configured with.
+
+## Managing indexes
+
+```sql
+-- builds the index from the current snapshot of the table
+CREATE INDEX [IF NOT EXISTS] email_idx ON prod.db.customer USING xtable (email)
+ [OPTIONS ('xtable.hudi.target.metadata.record.index.min.filegroup.count' =
'64')];
+
+-- syncs the index with the current snapshot of the table
+REFRESH INDEX email_idx ON prod.db.customer;
+
+DROP INDEX [IF EXISTS] email_idx ON prod.db.customer;
+```
+
+- An index covers one top level column of type `string`, `int` or `bigint`. A
table can have one
+ index, and its column does not change. To index another column, drop the
index and create a new
+ one.
+- The index definition is stored in the table properties as
`xtable.index.<name>.column`, and
+ options as `xtable.index.<name>.option.<key>`. `SHOW TBLPROPERTIES` lists
them.
+- Options are passed to the Hudi target that stores the index, for example the
+ `xtable.hudi.target.metadata.record.index.*` file group counts.
+- The index files are stored under `.hoodie/` in the data folder of the table.
Dropping the index
+ deletes that folder and keeps the data files.
+
+## Keeping the index current
+
+A sync job that already runs XTable keeps the index current. Add a Hudi target
with the index
+settings to the `--datasetconfig` of `XTableSparkSync` in the runtime jar, or
of `RunSync`:
+
+```yaml
+sourceFormat: ICEBERG
+targetFormats:
+ - HUDI
+datasets:
+ - tableBasePath: s3://bucket/warehouse/db.db/customer
+ tableDataPath: s3://bucket/warehouse/db.db/customer/data
+ tableName: customer
+ namespace: db
+ targetProperties:
+ HUDI:
+ xtable.hudi.target.table_version: '9'
+ xtable.hudi.target.execution.engine: spark
+ xtable.hudi.target.secondary.index.column: c_email_address
+```
+
+The column must match the column of `CREATE INDEX`, otherwise the sync fails.
Without a sync job,
+run `REFRESH INDEX` after writes.
+
+## Querying with an index
+
+Queries do not change. When a filter compares an indexed column with literals,
for example
+`email = 'x'` or `email IN ('x', 'y')`, possibly combined with other filters
by `AND`, the extensions
+look the values up in the index and read only the files that hold them. The
query returns the same
+rows as without the index.
+
+The index is not used when:
+
+- the table has new snapshots that the index was not refreshed with. The query
then reads the table
+ as usual, so results stay correct; run `REFRESH INDEX` to use the index
again.
+- the query reads an older snapshot (`VERSION AS OF`, `TIMESTAMP AS OF`) or a
branch.
+- the scan is small. A lookup runs a Spark job, which costs more than reading
a few files. The
+ index is used only when Iceberg's own partition and min/max pruning leave at
least
+ `spark.xtable.index.pruning.minCandidateFiles` files or
`spark.xtable.index.pruning.minCandidateBytes`
+ bytes to read.
+- the filter has more than `spark.xtable.index.pruning.maxKeys` values.
+
+The driver log shows for each query whether the index was used and how many
files it selected.
+
+| Setting | Default | Meaning |
+|---|---|---|
+| `spark.xtable.index.pruning.enabled` | `true` | Use indexes for queries |
Review Comment:
It defaults to `true`, but the rule only runs when `spark.sql.extensions`
lists the XTable extensions, so it is still opt-in.
##########
website/sidebars.js:
##########
@@ -20,7 +20,8 @@ module.exports = {
items: [
'how-to',
'how-to-catalog-sync',
- 'how-to-spark-runtime'
+ 'how-to-spark-runtime',
+ 'how-to-index-sql'
Review Comment:
Renamed to `how-to-index` in b57e1b83.
##########
xtable-spark-extensions/src/test/resources/log4j2.xml:
##########
@@ -0,0 +1,35 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ~ Licensed to the Apache Software Foundation (ASF) under one or more
+ ~ contributor license agreements. See the NOTICE file distributed with
+ ~ this work for additional information regarding copyright ownership.
+ ~ The ASF licenses this file to You under the Apache License, Version 2.0
+ ~ (the "License"); you may not use this file except in compliance with
+ ~ the License. You may obtain a copy of the License at
+ ~
+ ~ http://www.apache.org/licenses/LICENSE-2.0
+ ~
+ ~ Unless required by applicable law or agreed to in writing, software
+ ~ distributed under the License is distributed on an "AS IS" BASIS,
+ ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ ~ See the License for the specific language governing permissions and
+ ~ limitations under the License.
+-->
+<Configuration status="warn">
Review Comment:
Not required, removed in b57e1b83.
--
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]
