Skip to content

Latest commit

 

History

History
918 lines (700 loc) · 34.1 KB

sql-commands.md

File metadata and controls

918 lines (700 loc) · 34.1 KB

SQL Commands Reference

All SQL commands require the extensions to be registered:

// In SparkSession builder:
val spark = SparkSession.builder()
  .config("spark.sql.extensions", "io.indextables.extensions.IndexTablesSparkExtensions")
  .getOrCreate()

All commands support both INDEXTABLES and TANTIVY4SPARK keywords interchangeably.


Merge Splits

Consolidate small splits into larger ones for better read performance.

MERGE SPLITS 's3://bucket/path' TARGET SIZE 100M;
MERGE SPLITS 's3://bucket/path' MAX DEST SPLITS 10;
MERGE SPLITS 's3://bucket/path' MAX SOURCE SPLITS PER MERGE 500;
MERGE SPLITS 's3://bucket/path' WHERE date = '2024-01-01' TARGET SIZE 100M;
MERGE SPLITS 's3://bucket/path' TARGET SIZE 1G MAX DEST SPLITS 5 MAX SOURCE SPLITS PER MERGE 100;

Configuration:

  • TARGET SIZE: Maximum size of merged splits (default: 5GB)
  • MAX DEST SPLITS: Limits the number of destination (merged) splits to process (oldest first)
  • MAX SOURCE SPLITS PER MERGE: Maximum source splits per merge (default: 1000)
    • Config: spark.indextables.merge.maxSourceSplitsPerMerge: 1000
  • Skip Split Threshold: Splits at or above this size are excluded from merge
    • Config: spark.indextables.merge.skipSplitThreshold: 0.45 (default: 45% of target size)
    • Range: 0.0 to 1.0 (percentage of target size)
    • Example: With 5GB target and 0.45 threshold, splits >= 2.25GB are skipped

Purge IndexTable

Remove orphaned split files and old transaction logs.

PURGE INDEXTABLE 's3://bucket/path' DRY RUN;
PURGE INDEXTABLE 's3://bucket/path' OLDER THAN 7 DAYS;
PURGE INDEXTABLE 's3://bucket/path' OLDER THAN 168 HOURS;
PURGE INDEXTABLE 's3://bucket/path' OLDER THAN 14 DAYS DRY RUN;
PURGE INDEXTABLE 's3://bucket/path' OLDER THAN 7 DAYS TRANSACTION LOG RETENTION 30 DAYS;

Configuration:

spark.indextables.purge.defaultRetentionHours: 168 (7 days for splits)
spark.indextables.purge.minRetentionHours: 24 (safety check)
spark.indextables.purge.retentionCheckEnabled: true
spark.indextables.purge.parallelism: <auto>
spark.indextables.purge.maxFilesToDelete: 1000000
spark.indextables.purge.deleteRetries: 3

Common scenarios:

  • After failed writes that leave orphaned split files
  • After MERGE SPLITS operations
  • Regular maintenance (weekly/monthly cleanup)
  • Before archiving or migrating tables
  • Transaction log cleanup to reclaim storage

Safety features:

  • Minimum retention period enforced (default 24 hours)
  • DRY RUN mode shows preview before deletion
  • Distributed deletion across executors for scalability
  • Retry logic handles transient cloud storage errors
  • LEFT ANTI JOIN ensures only truly orphaned files deleted
  • Transaction log files referenced by checkpoints are never deleted

Drop Partitions

Logically remove partitions from a table.

DROP INDEXTABLES PARTITIONS FROM 's3://bucket/path' WHERE year = '2023';
DROP INDEXTABLES PARTITIONS FROM my_table WHERE date = '2024-01-01';

-- Range predicates
DROP INDEXTABLES PARTITIONS FROM 's3://bucket/path' WHERE year > '2020';
DROP INDEXTABLES PARTITIONS FROM 's3://bucket/path' WHERE month < 6;
DROP INDEXTABLES PARTITIONS FROM 's3://bucket/path' WHERE month BETWEEN 1 AND 6;

-- Compound predicates (AND, OR)
DROP INDEXTABLES PARTITIONS FROM 's3://bucket/path' WHERE region = 'us-east' AND year > '2020';
DROP INDEXTABLES PARTITIONS FROM 's3://bucket/path' WHERE year = '2022' OR year = '2023';

Key features:

  • WHERE clause required - Prevents accidental full table drops
  • Partition columns only - Validates that WHERE clause references only partition columns
  • Logical deletion - Adds RemoveAction entries to transaction log without physical deletion
  • Physical cleanup via PURGE - Use PURGE INDEXTABLE to delete files after retention period
  • Returns status - Reports partitions dropped, splits removed, and total size

Example workflow:

// 1. Drop old partitions
spark.sql("DROP INDEXTABLES PARTITIONS FROM 's3://bucket/table' WHERE year < '2022'").show()

// 2. Verify with DESCRIBE (optional)
spark.sql("DESCRIBE INDEXTABLES TRANSACTION LOG 's3://bucket/table' INCLUDE ALL").show()

// 3. After retention period, clean up physical files
spark.sql("PURGE INDEXTABLE 's3://bucket/table' OLDER THAN 7 DAYS").show()

Error handling:

  • Fails if WHERE clause references non-partition columns
  • Fails if table has no partition columns defined
  • Returns no_action if no partitions match the predicates

Checkpoint / Compact IndexTable

Force a checkpoint (creates V4 checkpoint with Avro state format).

CHECKPOINT INDEXTABLES 's3://bucket/path';
CHECKPOINT INDEXTABLES my_table;

-- COMPACT is an alias for CHECKPOINT
COMPACT INDEXTABLES 's3://bucket/path';

Key features:

  • Full compaction: Reads all current files and writes a fresh Avro state with no tombstones
  • Protocol upgrade: Creates V4 checkpoint with Avro state format (10x faster reads)
  • Schema deduplication: Uses hash-based schema references to reduce state size
  • Streaming write: Uses streaming to avoid OOM for large tables
  • Removes tombstones: Clears accumulated tombstones by writing only live file entries

When to use:

  • Force compaction: Use COMPACT when tombstone ratio is high (check with DESCRIBE INDEXTABLES STATE)
  • Force V4 upgrade: Upgrade existing V2/V3 tables to V4 protocol with Avro state
  • After bulk deletes: Compact after DROP PARTITIONS or large MERGE operations
  • Performance optimization: Compaction speeds up table reads by consolidating state
  • Before maintenance: Create checkpoint before PURGE operations

Output schema:

Column Type Description
table_path String Resolved table path
status String "SUCCESS" or "ERROR: "
checkpoint_version Long Transaction version at checkpoint
num_actions Long Total actions in checkpoint
num_files Long Number of split files
protocol_version Long Protocol version (4 for V4 with Avro state)
is_multi_part Boolean True if checkpoint split into parts

Truncate Time Travel

Remove all historical transaction log versions, keeping only current state.

TRUNCATE INDEXTABLES TIME TRAVEL 's3://bucket/path';
TRUNCATE INDEXTABLES TIME TRAVEL 's3://bucket/path' DRY RUN;

Key features:

  • Creates checkpoint first if none exists at current version
  • Deletes all transaction log version files older than the checkpoint
  • Deletes all checkpoint files except the latest
  • Preserves data files (only transaction log metadata affected)
  • DRY RUN mode for preview

When to use:

  • Reduce storage overhead after many small write operations
  • Clean up history when time travel is no longer needed
  • Prepare for archival
  • After MERGE operations

Output schema:

Column Type Description
table_path String Resolved table path
status String "SUCCESS", "DRY_RUN", or "ERROR"
checkpoint_version Long Version at which checkpoint exists
versions_deleted Long Number of version files deleted
checkpoints_deleted Long Number of checkpoint files deleted
files_preserved Long Number of data files (splits) in table
message String Descriptive status message

Safety notes:

  • After truncation, time travel to earlier versions is no longer possible
  • Always use DRY RUN first to preview what will be deleted
  • This operation is irreversible

Prewarm Cache

Pre-warm index caches across all executors for optimal query performance.

-- Basic prewarm (preloads default segments: TERM, POSTINGS)
PREWARM INDEXTABLES CACHE 's3://bucket/path';

-- Prewarm specific segments
PREWARM INDEXTABLES CACHE 's3://bucket/path' FOR SEGMENTS (TERM_DICT, FAST_FIELD);

-- Prewarm with field selection
PREWARM INDEXTABLES CACHE 's3://bucket/path' ON FIELDS (title, content, timestamp);

-- Prewarm with partition filter
PREWARM INDEXTABLES CACHE 's3://bucket/path' WHERE date >= '2024-01-01';

-- Prewarm with custom parallelism
PREWARM INDEXTABLES CACHE 's3://bucket/path' WITH PERWORKER PARALLELISM OF 5;

-- Full syntax with all options
PREWARM INDEXTABLES CACHE 's3://bucket/path'
  FOR SEGMENTS (TERM_DICT, FAST_FIELD, POSTINGS, FIELD_NORM)
  ON FIELDS (title, content)
  WITH PERWORKER PARALLELISM OF 4
  WHERE region = 'us-east';

Segment Aliases:

SQL Name Description
TERM_DICT, TERM Term dictionary (FST)
FAST_FIELD, FASTFIELD Fast fields for aggregations
POSTINGS, POSTING_LISTS Inverted index postings
POSITIONS, POSITION_LISTS Term positions within documents
FIELD_NORM, FIELDNORM Field norms for scoring
DOC_STORE, STORE Document storage

Default segments: TERM_DICT, POSTINGS (minimal set for query operations)

Read-time prewarm configuration:

spark.indextables.prewarm.enabled: false (default)
spark.indextables.prewarm.segments: "TERM_DICT,POSTINGS"
spark.indextables.prewarm.fields: "" (empty = all fields)
spark.indextables.prewarm.splitsPerTask: 2 (default)
spark.indextables.prewarm.partitionFilter: "" (default: empty = all partitions)
spark.indextables.prewarm.failOnMissingField: true (default)
spark.indextables.prewarm.catchUpNewHosts: false (default)

Output schema:

  • host: Executor hostname
  • splits_prewarmed: Count of splits prewarmed
  • segments: Comma-separated segment names
  • fields: Comma-separated field names (or "all")
  • duration_ms: Prewarm duration in milliseconds
  • status: "success", "partial", or "no_splits"
  • skipped_fields: Missing fields (if any)

Describe State

View checkpoint/state format information for a table.

DESCRIBE INDEXTABLES STATE 's3://bucket/path';

Output schema:

Column Type Description
format String "avro-state", "json", "json-multipart", or "none"
version Long Transaction version at checkpoint
num_files Long Number of split files
num_manifests Long Number of Avro manifest files (0 for JSON)
num_tombstones Long Number of removed file entries
tombstone_ratio Double Ratio of tombstones to total entries
needs_compaction Boolean True if tombstone ratio exceeds threshold

Migration from JSON to Avro:

DESCRIBE INDEXTABLES STATE 's3://bucket/path';    -- Check current format
CHECKPOINT INDEXTABLES 's3://bucket/path';         -- Upgrade to Avro
DESCRIBE INDEXTABLES STATE 's3://bucket/path';    -- Verify (should show "avro-state")

Describe Disk Cache

View disk cache statistics across all executors.

DESCRIBE INDEXTABLES DISK CACHE;

Output includes: executor_id, host, enabled, total_bytes, max_bytes, usage_percent, splits_cached, components_cached.

  • Auto-enabled on Databricks/EMR when /local_disk0 detected
  • Each executor maintains independent cache
  • Returns NULL when cache is disabled

Describe Storage Stats

View object storage (S3/Azure) access statistics across all executors.

DESCRIBE INDEXTABLES STORAGE STATS;

Output includes: executor_id, host, bytes_fetched, requests. Counters are cumulative since JVM startup.


Describe Data Skipping Stats

Note: These commands are not yet wired into the SQL grammar. The command implementations exist (DescribeDataSkippingStatsCommand, FlushDataSkippingStatsCommand, InvalidateDataSkippingCacheCommand) but require ANTLR grammar rules to be added before they can be invoked via SQL.

DESCRIBE INDEXTABLES DATA SKIPPING STATS;
FLUSH INDEXTABLES DATA SKIPPING STATS;        -- Reset stats, keep cache
INVALIDATE INDEXTABLES DATA SKIPPING CACHE;   -- Clear caches and stats

Metrics include: file pruning rates, filter type effectiveness, expression cache hit rates, partition filter cache performance.

Spark UI Integration: Data skipping metrics are also reported to the Spark UI SQL tab under the scan operator, showing total files considered, files pruned by partitions, files skipped by stats, files to scan, and total skip rate.


Describe Environment

View Spark and Hadoop configuration properties across all executors.

DESCRIBE INDEXTABLES ENVIRONMENT;

Output includes: host, role (driver/worker), property_type (spark/hadoop), property_name, property_value.

  • Sensitive values are automatically redacted (***REDACTED***)
  • Results can be filtered via temp view
spark.sql("DESCRIBE INDEXTABLES ENVIRONMENT").createOrReplaceTempView("env_props")
spark.sql("SELECT * FROM env_props WHERE property_name LIKE 'spark.indextables%'").show()

Describe Merge Jobs

View async merge-on-write job status on the driver.

DESCRIBE INDEXTABLES MERGE JOBS;

Output schema:

Column Type Description
job_id String Unique job identifier
table_path String Path of the table being merged
status String RUNNING, COMPLETED, FAILED, CANCELLED
total_groups Integer Total merge groups to process
completed_groups Integer Groups merged so far
total_batches Integer Total number of batches
completed_batches Integer Batches completed
progress_pct Double Percentage progress
duration_ms Long Duration in milliseconds
error_message String Error message if failed
// Wait for merges to complete
spark.sql("DESCRIBE INDEXTABLES MERGE JOBS").createOrReplaceTempView("merge_jobs")
while (spark.sql("SELECT COUNT(*) FROM merge_jobs WHERE status = 'RUNNING'").head().getLong(0) > 0) {
  Thread.sleep(5000)
}

Flush Disk Cache

Flush disk cache across all executors (clears cached data and locality state).

FLUSH INDEXTABLES DISK CACHE;
  • Flushes split cache managers (closes disk cache handles)
  • Clears split locality assignments and prewarm state tracking
  • Deletes all disk cache files at the configured path
  • Useful for testing, maintenance, and troubleshooting

Flush Searcher Cache

Flush in-memory searcher caches on the driver (split cache, locality manager, tantivy4java native caches).

FLUSH INDEXTABLES SEARCHER CACHE;

Output schema:

Column Type Description
cache_type String Cache component: "split_cache", "locality_manager", "tantivy_java_cache", or "error"
status String "success" or "failed"
cleared_entries Long Number of entries/managers flushed
message String Descriptive message
  • Flushes GlobalSplitCacheManager instances (closes split cache handles)
  • Clears driver-side split locality assignments
  • Flushes tantivy4java native caches
  • Returns one row per operation with success/failure status

Invalidate Transaction Log Cache

Invalidate transaction log caches to force fresh reads from storage.

-- Global invalidation (all caches)
INVALIDATE INDEXTABLES TRANSACTION LOG CACHE;

-- For a specific table
INVALIDATE INDEXTABLES TRANSACTION LOG CACHE FOR 's3://bucket/path';
INVALIDATE INDEXTABLES TRANSACTION LOG CACHE FOR my_table;

Output schema:

Column Type Description
table_path String Table path or "GLOBAL" for global invalidation
result String Descriptive result message
cache_hits_before Long Total cache hits before invalidation
cache_misses_before Long Total cache misses before invalidation
hit_rate_before String Cache hit rate percentage (e.g., "85.3%") or "N/A"
  • Global (no FOR clause): Clears all caches in EnhancedTransactionLogCache (actions, checkpoint, Avro manifest, filtered schema caches)
  • Table-specific (with FOR): Reads cache stats, then invalidates cache for that table
  • Returns hit/miss statistics collected before invalidation

Describe Transaction Log

View all transaction log actions for a table.

-- Current state (from latest checkpoint forward)
DESCRIBE INDEXTABLES TRANSACTION LOG 's3://bucket/path';
DESCRIBE INDEXTABLES TRANSACTION LOG my_catalog.my_database.my_table;

-- Complete history from version 0
DESCRIBE INDEXTABLES TRANSACTION LOG 's3://bucket/path' INCLUDE ALL;

Output schema (key columns -- 47 columns total):

Column Type Description
version Long Transaction version number
log_file_path String Path to the transaction log file
action_type String "add", "remove", "skip", "protocol", "metadata", "unknown"
path String File path (for add/remove/skip actions)
partition_values String JSON map of partition values
size Long File size in bytes
data_change Boolean Whether action represents a data change
num_records Long Number of records (add actions)
deletion_timestamp Timestamp When file was deleted (remove actions)
is_checkpoint Boolean True if action came from a checkpoint file

Additional columns include: tags, modification_time, stats, min_values, max_values, footer_start_offset, footer_end_offset, hotcache_start_offset, hotcache_length, has_footer_offsets, time_range_start, time_range_end, split_tags, delete_opstamp, num_merge_ops, doc_mapping_json, doc_mapping_ref, uncompressed_size_bytes, extended_file_metadata, skip_timestamp, skip_reason, skip_operation, skip_retry_after, skip_count, protocol_min_reader_version, protocol_min_writer_version, protocol_reader_features, protocol_writer_features, metadata_id, metadata_name, metadata_description, metadata_format_provider, metadata_format_options, metadata_schema_string, metadata_partition_columns, metadata_configuration, metadata_created_time.

  • Without INCLUDE ALL: Reads from latest checkpoint forward (more efficient for large tables)
  • With INCLUDE ALL: Reads complete history from version 0
  • Shows all action types: AddAction (files added), RemoveAction (files removed), SkipAction (files skipped), ProtocolAction (protocol versions), MetadataAction (schema/config)
  • Queryable via temp views for analysis
spark.sql("DESCRIBE INDEXTABLES TRANSACTION LOG '/path'").createOrReplaceTempView("txlog")
spark.sql("SELECT version, action_type, COUNT(*) FROM txlog GROUP BY version, action_type").show()

Repair IndexFiles Transaction Log

Repair corrupted or problematic transaction logs by validating split files exist and writing a clean log.

REPAIR INDEXFILES TRANSACTION LOG 's3://bucket/table/_transaction_log'
  AT LOCATION 's3://bucket/table/_transaction_log_repaired';

Output schema:

Column Type Description
source_path String Source transaction log path
target_path String Target (repaired) transaction log path
source_version Long Version of the source transaction log (-1 on error)
total_splits Integer Total splits in source
valid_splits Integer Splits validated as existing in storage
missing_splits Integer Splits not found in storage (excluded from repair)
status String "SUCCESS" or "ERROR: message"
  • Read-only repair: Never modifies or deletes source files
  • Validates all referenced split files actually exist in storage
  • Writes clean transaction log to a NEW location (target must not exist or be empty)
  • Applies statistics truncation automatically
  • Uses streaming write to avoid OOM for large tables
  • Source path must end with _transaction_log

Common scenarios:

  • Corrupted checkpoint recovery
  • Orphaned file cleanup (create log excluding missing splits)
  • Transaction log optimization (consolidate fragmented history)
  • Migration preparation

Post-repair workflow:

// 1. Run repair
spark.sql("""REPAIR INDEXFILES TRANSACTION LOG '/path/table/_transaction_log'
  AT LOCATION '/path/repaired/_transaction_log'""").show()

// 2. Backup original and swap
val fs = new Path(tablePath).getFileSystem(hadoopConf)
fs.rename(originalLogPath, backupPath)
fs.rename(repairedLogPath, originalLogPath)

// 3. Table is now readable with the repaired log
spark.read.format("io.indextables.provider.IndexTablesProvider").load(tablePath)

Describe Component Sizes

Inspect per-field sub-component sizes across all splits for storage analysis.

DESCRIBE INDEXTABLES COMPONENT SIZES 's3://bucket/path';
DESCRIBE INDEXTABLES COMPONENT SIZES my_table;

-- With partition filter
DESCRIBE INDEXTABLES COMPONENT SIZES '/path' WHERE year = '2024';

Output schema:

Column Type Description
split_path String Path to the split file
partition_values String JSON map of partition key/values (null if unpartitioned)
component_key String Component identifier (e.g., score.fastfield, _term_total)
size_bytes Long Size in bytes
component_type String Category: fastfield, fieldnorm, term, postings, positions, store
field_name String Field name (null for segment-level components like _term_total)
  • Component key formats: per-field ({field}.fastfield, {field}.fieldnorm) and segment-level (_term_total, _postings_total, _positions_total, _store)
  • Supports WHERE clause for partition filtering
  • Executes in parallel across executors
  • Queryable via temp views:
spark.sql("DESCRIBE INDEXTABLES COMPONENT SIZES '/path'").createOrReplaceTempView("components")
spark.sql("SELECT component_type, SUM(size_bytes) FROM components GROUP BY component_type").show()

Describe Prewarm Jobs

View async prewarm job status across all executors.

DESCRIBE INDEXTABLES PREWARM JOBS;

Output schema:

Column Type Description
executor_id String Executor identifier (e.g., "executor-0", "driver")
host String Host address of the executor
job_id String Unique job identifier
table_path String Path of the table being prewarmed
status String RUNNING, COMPLETED, FAILED, CANCELLED
total_splits Integer Total splits to prewarm
completed_splits Integer Splits prewarmed so far
progress_pct Double Percentage progress
duration_ms Long Duration in milliseconds
error_message String Error message if failed (null otherwise)
  • Collects job status from every executor and the driver
  • Each executor maintains its own async prewarm job state

Wait For Prewarm Jobs

Block until async prewarm jobs complete or timeout.

-- Wait for all jobs (1 hour default timeout)
WAIT FOR INDEXTABLES PREWARM JOBS;

-- Wait for jobs on a specific table
WAIT FOR INDEXTABLES PREWARM JOBS 's3://bucket/table';
WAIT FOR INDEXTABLES PREWARM JOBS my_table;

-- Wait for a specific job
WAIT FOR INDEXTABLES PREWARM JOBS JOB 'abc-123-def-456';

-- Wait with custom timeout (seconds)
WAIT FOR INDEXTABLES PREWARM JOBS TIMEOUT 300;

-- Full syntax
WAIT FOR INDEXTABLES PREWARM JOBS 's3://bucket/path' JOB 'job-123' TIMEOUT 1800;

Output schema:

Column Type Description
executor_id String Executor identifier
host String Host address of the executor
job_id String Unique job identifier
table_path String Path of the table prewarmed
status String COMPLETED, FAILED, CANCELLED, TIMEOUT, not_found
total_splits Integer Total number of splits
splits_prewarmed Integer Splits successfully prewarmed
duration_ms Long Total duration in milliseconds
error_message String Error message if failed (null otherwise)
  • Polls all executors every 5 seconds
  • Default timeout: 3600 seconds (1 hour)
  • Can filter by table path, specific job ID, or both
  • Returns immediately with not_found if no matching jobs
  • On timeout, returns current status as TIMEOUT (was: RUNNING)

Build Companion

Build a companion search index for an external table (Delta, Parquet, or Iceberg).

-- Basic Delta companion
BUILD INDEXTABLES COMPANION FOR DELTA 's3://bucket/delta_table'
  AT LOCATION 's3://bucket/index';

-- Delta with options
BUILD INDEXTABLES COMPANION FOR DELTA 's3://bucket/delta'
  INDEXING MODES ('content':'text', 'status':'string')
  FASTFIELDS MODE PARQUET_ONLY
  TARGET INPUT SIZE 1G
  WRITER HEAP SIZE 512M
  FROM VERSION 42
  WHERE year = '2024'
  AT LOCATION 's3://bucket/index'
  DRY RUN;

-- Delta with Unity Catalog
BUILD INDEXTABLES COMPANION FOR DELTA 'my_schema.events'
  CATALOG 'unity' TYPE 'rest'
  AT LOCATION 's3://bucket/companion';

-- Parquet with schema source
BUILD INDEXTABLES COMPANION FOR PARQUET 's3://bucket/data'
  SCHEMA SOURCE 's3://bucket/data/part-00000.parquet'
  AT LOCATION 's3://bucket/index';

-- Iceberg with catalog and warehouse
BUILD INDEXTABLES COMPANION FOR ICEBERG 'analytics.web_events'
  CATALOG 'uc_catalog' TYPE 'rest'
  WAREHOUSE 's3://unity-warehouse/iceberg'
  AT LOCATION 's3://bucket/index';

-- Selective indexing: only index specific columns
BUILD INDEXTABLES COMPANION FOR ICEBERG 'analytics.web_events'
  CATALOG 'uc_catalog' TYPE 'rest'
  WAREHOUSE 's3://unity-warehouse/iceberg'
  INCLUDE COLUMNS ('message', 'level', 'trace_id')
  AT LOCATION 's3://bucket/index';

-- Exclude large/unneeded columns from the index
BUILD INDEXTABLES COMPANION FOR ICEBERG 'analytics.web_events'
  CATALOG 'uc_catalog' TYPE 'rest'
  WAREHOUSE 's3://unity-warehouse/iceberg'
  EXCLUDE COLUMNS ('raw_payload', 'binary_data', 'debug_state')
  AT LOCATION 's3://bucket/index';

-- Combine selective indexing with indexing modes
BUILD INDEXTABLES COMPANION FOR ICEBERG 'analytics.web_events'
  CATALOG 'uc_catalog' TYPE 'rest'
  WAREHOUSE 's3://unity-warehouse/iceberg'
  INCLUDE COLUMNS ('message', 'trace_id', 'status')
  INDEXING MODES ('message':'text', 'trace_id':'exact_only')
  AT LOCATION 's3://bucket/index';

Output schema:

Column Type Description
table_path String Destination IndexTables path
source_path String Source table path
status String "success", "no_action", "dry_run", or "error"
source_version Long Delta version, Iceberg snapshot ID, or null
splits_created Integer Companion splits created
splits_invalidated Integer Companion splits invalidated
parquet_files_indexed Integer Parquet files processed
parquet_bytes_downloaded Long Bytes downloaded from source
split_bytes_uploaded Long Bytes uploaded for splits
duration_ms Long Duration in milliseconds
message String Descriptive status message

Options:

Option Applies To Description
SCHEMA SOURCE path Parquet only Path to a parquet file for schema inference
CATALOG name Delta, Iceberg Catalog name for table resolution
TYPE type Delta, Iceberg Catalog type (e.g., "rest" for Unity Catalog)
WAREHOUSE path Iceberg only Warehouse path
INDEXING MODES (...) All Per-field indexing modes (see table below)
FASTFIELDS MODE All HYBRID (default), DISABLED, or PARQUET_ONLY
TARGET INPUT SIZE size All Max input size per indexing group (default: 2GB)
WRITER HEAP SIZE size All Writer heap memory per executor
FROM VERSION n Delta only Start from a specific Delta version
FROM SNAPSHOT n Iceberg only Start from a specific snapshot ID
INCLUDE COLUMNS (...) All Index only the listed columns. Mutually exclusive with EXCLUDE COLUMNS.
EXCLUDE COLUMNS (...) All Index all columns except the listed ones. Mutually exclusive with INCLUDE COLUMNS.
WHERE predicate All Partition filter predicate
DRY RUN All Preview without creating splits

Configuration:

spark.indextables.companion.sync.batchSize: <defaultParallelism> (indexing tasks per batch)
spark.indextables.companion.sync.maxConcurrentBatches: 6
spark.indextables.companion.writerHeapSize: 1G
spark.indextables.companion.readerBatchSize: 8192
spark.indextables.companion.schedulerPool: "indextables-companion" (FAIR scheduler pool)

Indexing Modes:

Mode Description
string Default. Exact matching with full filter pushdown (===, >, <, IN, etc.)
text Full-text search via IndexQuery. No exact-match pushdown.
ip / ipaddress IP address field type
json JSON field (automatic for Struct/Array/Map)
exact_only Replace string with U64 xxHash64. ~80% size reduction for ID/UUID fields. Supports EqualTo pushdown (hash-based).
text_uuid_exactonly Strip UUIDs from text for "default" tokenizer; UUIDs indexed in companion U64 hash field. Use IndexQuery for queries.
text_uuid_strip Strip UUIDs from text for "default" tokenizer; UUIDs discarded. Use IndexQuery for queries.
text_custom_exactonly:<regex> Same as text_uuid_exactonly but with custom regex pattern.
text_custom_strip:<regex> Same as text_uuid_strip but with custom regex pattern.

Compact String Indexing Examples:

-- Hash UUID/ID columns for ~80% size reduction
BUILD INDEXTABLES COMPANION FOR PARQUET 's3://bucket/data'
  INDEXING MODES (
    'trace_id':'exact_only',
    'request_id':'exact_only',
    'message':'text_uuid_exactonly',
    'raw_log':'text_uuid_strip',
    'audit_log':'text_custom_exactonly:\d{3}-\d{2}-\d{4}',
    'notes':'text_custom_strip:ORD-\d{8}'
  )
  AT LOCATION 's3://bucket/index';
  • Creates minimal Quickwit splits that reference external parquet files (45-70% split size reduction)
  • Supports incremental sync via anti-join reconciliation (detects new/removed parquet files)
  • Batched concurrent dispatch using Spark's FAIR scheduler
  • Companion index is read via standard IndexTables DataSource API

Selective Column Indexing

INCLUDE COLUMNS and EXCLUDE COLUMNS control which columns are indexed in the companion. This is useful for wide tables where only a subset of columns need to be searchable, reducing index size, storage costs, and sync time.

Input Validation:

  • INCLUDE and EXCLUDE are mutually exclusive (specifying both is an error)
  • Column names are case-insensitive (matched against the source schema)
  • Duplicate column names are detected and rejected
  • Non-existent columns produce an error listing available columns (showing 20 of N for wide tables)
  • Partition columns are silently filtered out with a warning (they are always indexed regardless)
  • At least one non-partition column must remain indexed after filtering

Cross-Feature Validation:

  • All INDEXING MODES fields must be within INCLUDE COLUMNS (if specified) and must not be in EXCLUDE COLUMNS
  • All HASHED FASTFIELDS fields must be within INCLUDE COLUMNS (if specified) and must not be in EXCLUDE COLUMNS
  • Binary columns produce a warning (stored in the index but not searchable)

Incremental Sync:

  • The validated include/exclude column lists are persisted in companion metadata (transaction log configuration)
  • On subsequent syncs without explicit INCLUDE/EXCLUDE, the stored settings are automatically reused
  • Explicit parameters override stored settings; a warning is logged if the column selection changed

Schema Evolution:

  • Type widening (e.g., int to bigint): logged as a warning, sync continues
  • Breaking type changes (e.g., string to int): error, requires INVALIDATE ALL PARTITIONS
  • Dropped columns in stored metadata: silently skipped on incremental sync
  • New columns added to the source: warning if EXCLUDE COLUMNS is active (new columns will be auto-indexed)

FFI Profiler

Control the tantivy4java FFI read-path profiler. The profiler tracks invocation count, total time, min time, and max time for 54 code sections across all FFI read operations, plus hit/miss counters for 5 cache layers. Overhead is ~1ns per section when disabled, ~64ns when enabled.

Enable / Disable

ENABLE INDEXTABLES PROFILER;
DISABLE INDEXTABLES PROFILER;

ENABLE auto-resets all counters for a clean start. DISABLE preserves counters (still readable after disable). Both commands broadcast to all executor hosts.

Output schema:

Column Type Description
status String "enabled" or "disabled"
host_count Integer Number of executor hosts reached
message String Descriptive status message

Describe Profiler (Section Timings)

DESCRIBE INDEXTABLES PROFILER;

Returns per-section timing counters aggregated across all hosts. Sections with zero invocations are omitted.

Output schema:

Column Type Description
section String Section name (e.g., "leaf_search", "doc_batch_prefetch")
category String Logical group ("search", "aggregation", "doc_retrieval", "streaming", etc.)
calls Long Number of invocations
total_ms Double Cumulative wall-clock milliseconds
avg_us Double Average microseconds per call
min_us Double Fastest invocation in microseconds
max_us Double Slowest invocation in microseconds

Describe Profiler Cache

DESCRIBE INDEXTABLES PROFILER CACHE;

Returns cache hit/miss counters aggregated across all hosts. Caches with zero hits and misses are omitted.

Output schema:

Column Type Description
cache String Cache name ("byte_range", "l2_disk", "searcher", "parquet_metadata", "pq_column")
hits Long Hit count
misses Long Miss count
hit_rate Double hits / (hits + misses), or null if both are 0

Reset Profiler

RESET INDEXTABLES PROFILER;
RESET INDEXTABLES PROFILER CACHE;

Atomically reads and zeros counters on all hosts. Returns the pre-reset values using the same schema as the corresponding DESCRIBE command.

Example Workflow

-- Enable profiling
ENABLE INDEXTABLES PROFILER;

-- Run workload
SELECT * FROM my_index WHERE title = 'example' LIMIT 100;
SELECT COUNT(*), AVG(score) FROM my_index WHERE category = 'tech';

-- Check timing breakdown
DESCRIBE INDEXTABLES PROFILER;

-- Check cache effectiveness
DESCRIBE INDEXTABLES PROFILER CACHE;

-- Reset and run another workload to compare
RESET INDEXTABLES PROFILER;
SELECT * FROM my_index WHERE content indexquery 'machine learning';
DESCRIBE INDEXTABLES PROFILER;

-- Disable when done
DISABLE INDEXTABLES PROFILER;