Manage, Maintain, and Interoperate with External Engines

Singdata Lakehouse provides automated storage maintenance and operational inspection for Managed Iceberg Tables, and exposes a built-in Apache Iceberg REST Catalog so external compute engines can securely read and write the same tables.

Table Maintenance and Optimization

Frequent inserts, micro-batch writes, and Merge-on-Read (MoR) row-level updates accumulate small data files and position delete files over time, which can degrade query performance and increase storage overhead. Singdata Lakehouse maintains Managed Iceberg Tables through the following mechanisms:

1. File Compaction (Automatic Compaction & OPTIMIZE)

  • Background automatic compaction: Background services monitor small data files and delete files on Managed Iceberg Tables and rewrite them via standard Iceberg RewriteFiles commits without disrupting concurrent reads or writes.
  • Manual OPTIMIZE: Trigger compaction on demand for an entire table or a specific partition to compact small files and fold position deletes back into clean base data files, and inspect compaction tasks via SHOW COMPACTION JOBS FOR TABLE:

-- Compact small data files and associated position delete files across the table OPTIMIZE orders_iceberg; -- Compact only a specific partition OPTIMIZE daily_orders_iceberg WHERE ds = '2025-02-01'; -- Inspect compaction job history for the table SHOW COMPACTION JOBS FOR TABLE orders_iceberg;

2. Snapshot Expiration

  • A background recycling task periodically expires old snapshots, atomically updates the table metadata pointer, and physically removes unreferenced historical data, delete, and metadata files.
  • Retention behavior follows standard Iceberg table properties such as history.expire.max-snapshot-age-ms and gc.enabled.

3. Orphan File Cleanup

  • Failed or interrupted write jobs can leave unreferenced files in object storage.
  • A background recycling task periodically scans for and deletes unreferenced files older than the retention threshold. Setting 'gc.enabled' = 'false' skips orphan file cleanup.

4. Metadata Inspection and Time Travel Queries

Spark-style Iceberg Metadata Tables (such as SELECT * FROM <table>.snapshots, <table>.files, or <table>.manifests) are not yet available at this stage. Use native Lakehouse SQL inspection statements to inspect table metadata and run Time Travel queries over retained snapshots:

-- View table schema, properties, and DDL DESC TABLE orders_iceberg; DESC EXTENDED orders_iceberg; SHOW CREATE TABLE orders_iceberg; SHOW PROPERTIES IN TABLE orders_iceberg; -- View commit history (version is the Iceberg snapshot ID; sequence is the Iceberg sequence number) DESC HISTORY orders_iceberg; DESC HISTORY EXTENDED orders_iceberg; -- Query historical snapshots via Time Travel (get snapshot_id from the version column of DESC HISTORY) SELECT * FROM orders_iceberg VERSION AS OF '<snapshot_id>'; SELECT * FROM orders_iceberg TIMESTAMP AS OF '2025-02-01 12:00:00'; -- View partitions and extended partition metrics (row count, size, data/delete file counts) SHOW PARTITIONS orders_iceberg; SHOW PARTITIONS EXTENDED orders_iceberg; -- View current table files SHOW FILES FOR orders_iceberg;

External Engine Interoperability via Iceberg REST Catalog

Singdata Lakehouse exposes an Apache Iceberg REST Catalog endpoint so external compute engines such as Apache Spark and DuckDB can discover, query, and write to Managed Iceberg Tables directly without duplicating data.

Architecture and Security

  • Unified catalog coordination: External engines load table metadata and commit new snapshots through the REST Catalog. All cross-engine commits go through atomic pointer publication and conflict checking in the Lakehouse catalog.
  • Unified permissions and Vended Credentials: Access control reuses Lakehouse native privileges (SELECT / READ_METADATA for reads, INSERT for writes). The server supports Vended Credentials to issue short-lived object storage credentials (default STS TTL: 1 hour), avoiding long-lived static storage keys on external clients.

External Engine Capability Scope

OperationCurrent StatusDetails
Catalog & table discovery✅ SupportedDiscover namespaces and tables, load table metadata (SHOW NAMESPACES, SHOW TABLES, DESCRIBE TABLE)
Data reads & historical snapshot reads✅ SupportedFull reads of Managed Iceberg Tables (including MoR position deletes and CoW data files); compatible clients can read retained historical snapshots by snapshot ID
Data writes & row-level DML✅ SupportedINSERT INTO, INSERT OVERWRITE, UPDATE, DELETE, MERGE INTO
Table creation & schema DDLNot yet availableRun table creation (CREATE TABLE) and schema/partition DDL in Singdata Lakehouse
Advanced REST extensionsNot yet availableView / UDF catalog endpoints and server-side scan planning (/plan) are planned for future iterations

Multi-Engine Governance Best Practices

  1. Always access through the REST Catalog — avoid direct object storage writes: External engines should read and write Managed Iceberg Tables exclusively through the Lakehouse Iceberg REST Catalog. Bypassing the catalog to modify files directly in object storage can cause metadata inconsistency, orphan files, or data loss.
  2. Understand per-engine MoR / CoW behavior: Standard write.delete.mode, write.update.mode, and write.merge.mode properties define table-level defaults, though individual engines may override them via session settings. Mixing CoW and MoR snapshots is valid under the Iceberg specification as long as every commit is coordinated through the same catalog.
  3. Centralize DDL in Singdata Lakehouse: Perform table creation, schema evolution, and table drops in Singdata Lakehouse while using external engines for analytics and data processing workloads.

For complete PySpark setup instructions, dependency versions, and regional REST endpoints, see Spark Lakehouse Iceberg REST.