@Rocky How to SELECT from an external Iceberg catalog using a schema_id other than the latest one ?

In StarRocks, there is no direct SQL syntax to

SELECT

from an Iceberg table using a specific

schema_id

(e.g.,

FOR SCHEMA_ID AS OF

). However, since Apache Iceberg links every snapshot to a specific schema, you can achieve this by using the Time Travel feature to query a snapshot that was created using the desired schema. Recommended Workflow 1. Identify the Snapshot ID for your Schema You can query the Iceberg metadata tables in StarRocks to find which

snapshot_id

corresponds to the

schema_id

you want to use. The

metadata_log_entries

table is particularly useful here:

sql
SELECT timestamp, latest_snapshot_id, latest_schema_id 
FROM <catalog_name>.<database_name>.<table_name>$metadata_log_entries;

2. Query using Time Travel Once you have the

snapshot_id

that uses your target schema, use the

FOR VERSION AS OF

syntax:

sql
SELECT * 
FROM <catalog_name>.<database_name>.<table_name> 
FOR VERSION AS OF <snapshot_id>;

Key Considerations * Feature Support: Time Travel for Iceberg catalogs is supported in StarRocks v3.4.0 and later. * Alternative (Timestamp): If you know when the specific schema was active, you can also use `FOR TIMESTAMP AS OF`:

sql
    SELECT *
    FROM <table_name>
    FOR TIMESTAMP AS OF '2023-10-01 12:00:00';

* Automatic Schema Handling: When you perform a Time Travel query, StarRocks automatically uses the schema that was associated with the table at that specific snapshot or point in time. If you are using a version of StarRocks older than v3.4, you may need to use an external engine (like Spark or Trino) to query historical schemas or upgrade to a supported StarRocks version. References • data_source/catalog/iceberg/iceberg_meta_table.md • quick_start/iceberg.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/procedures.md • data_source/catalog/iceberg/iceberg_timetravel.md • data_source/External_table.md

@Rocky looks like even targeting the original snapshot ID manually, still displays the updated schema (which didn't exist at the time of creation of the original snapshot).

The behavior you are seeing is a known characteristic of how Time Travel is currently implemented in StarRocks. Even when you specify a historical

snapshot_id

using

FOR VERSION AS OF

, StarRocks (as of v3.4 and v3.5) typically uses the latest schema cached in its metadata to project the query results. This means that if you added a column after the snapshot was taken, it may appear as

NULL

in your results, and if you dropped a column, it may not appear at all, even though the data files for that old snapshot contain it. Here are the details and how you can work around this: 1. Why this happens Time Travel in StarRocks is currently a Beta feature. In its current implementation, the Frontend (FE) planner uses the table's current metadata to define the result set (the columns and types), while the Backend (BE) filters the data files to match the requested

snapshot_id

. Since the Iceberg metadata file used by the FE is the "latest" one, it defaults to the latest schema version. 2. How to find the Schema at that Snapshot If you need to verify exactly what the schema was at a specific point in time, you can query the

$metadata_log_entries

table to find the path of the specific metadata JSON file that was active:

sql
SELECT timestamp, file 
FROM <catalog>.<db>.<table>$metadata_log_entries 
ORDER BY timestamp DESC;

The

file

column points to a JSON file on your storage (S3/HDFS). This file contains the complete

schemas

and the

current-schema-id

that was active at that time. 3. Workarounds * Permanent Rollback (Use with Caution): If you need to revert the table to that specific schema and snapshot state globally, you can use the

rollback_to_snapshot

procedure. This will update the table's metadata to point to the historical state:

sql
    ALTER TABLE <table_name> EXECUTE rollback_to_snapshot(<snapshot_id>);

Warning: This affects all users and queries on this table. * Manual Projection: If you know the names of the columns in the old schema, you can explicitly name them in your

SELECT

statement instead of using

SELECT *

. * External Table (Manual): You can create a new StarRocks External Table definition that manually specifies the old column list, pointing it to the same underlying Iceberg path. 4. Recommendation Since this feature is in Beta, if snapshot-aware schema selection is critical for your workflow, I recommend: 1. Checking the latest StarRocks release notes (v4.0+) as improvements to Iceberg metadata handling are frequent. 2. Opening a feature request or checking the StarRocks GitHub Issues to see if a fix for "Time Travel schema projection" is scheduled for a future patch. References • data_source/catalog/iceberg/iceberg_timetravel.md • data_source/catalog/iceberg/iceberg_meta_table.md • data_source/catalog/iceberg/procedures.md • data_source/External_table.md • release_notes/release-4.0.md References • data_source/catalog/iceberg/iceberg_timetravel.md • data_source/catalog/iceberg/iceberg_catalog.md • data_source/catalog/iceberg/iceberg_meta_table.md • data_source/feature-support-data-lake-analytics.md