Iceberg Procedures
StarRocks Iceberg Catalog supports a variety of procedures for managing Iceberg tables, including snapshot management, branch management, data maintenance, metadata management, and table management.
You must have the appropriate privileges to execute procedures. For more information about privileges, see Privileges.
Snapshot management
Rollback to snapshot
Rolls back the table to a specific snapshot. This operation sets the table's current snapshot to the specified snapshot ID.
rollback_to_snapshot Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE rollback_to_snapshot(<snapshot_id>)
Parameters
snapshot_id: ID of the snapshot to which you want to roll back the table.
Example
Roll back the table to snapshot with ID 98765:
ALTER TABLE iceberg.sales.order
EXECUTE rollback_to_snapshot(98765);
Rollback to timestamp
Rolls back the table to the latest snapshot that was committed before the specified timestamp. The timestamp is interpreted in the session time zone.
rollback_to_timestamp Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE rollback_to_timestamp(<timestamp>)
Parameters
timestamp: The table is rolled back to the latest snapshot committed before this point in time. Format: 'YYYY-MM-DD HH:MM:SS'. An error is returned if no snapshot was committed before it.
Example
Roll back the table to its state before 2025-01-01 00:00:00:
ALTER TABLE iceberg.sales.order
EXECUTE rollback_to_timestamp('2025-01-01 00:00:00');
Set current snapshot
Sets the table's current snapshot to the specified snapshot ID. Unlike rollback_to_snapshot, the snapshot does not need to be an ancestor of the current snapshot, so you can use it to move the table forward again to a snapshot that was rolled back.
set_current_snapshot Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE set_current_snapshot(<snapshot_id>)
Parameters
snapshot_id: ID of the snapshot to set as the current snapshot of the table.
Example
ALTER TABLE iceberg.sales.order
EXECUTE set_current_snapshot(98765);
Cherry pick a snapshot
Cherry picks a specific snapshot and applies it to the current state of the table. This operation creates a new snapshot based on an existing snapshot, while the original snapshot remains unchanged.
cherrypick_snapshot Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE cherrypick_snapshot(<snapshot_id>)
Parameters
snapshot_id: ID of the snapshot which you want to cherry pick.
Example
ALTER TABLE iceberg.sales.order
EXECUTE cherrypick_snapshot(54321);
Branch management
Fast forward a branch to another
Fast-forwards one branch to another branch's latest snapshot. This operation updates the source branch's snapshot to match the target branch's snapshot.
fast_forward Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE fast_forward('<from_branch>', '<to_branch>')
Parameters
from_branch: The branch you want to fast forward. Wrap the branch name in quotes.to_branch: The branch to which you want to fast forward thefrom_branch. Wrap the branch name in quotes.
Example
Fast forward the main branch to the branch test-branch:
ALTER TABLE iceberg.sales.order
EXECUTE fast_forward('main', 'test-branch');
Data maintenance
Rewrite data files
Rewrites data files to optimize file layout. This procedure merges small files to improve query performance and reduce metadata overhead.
rewrite_data_files Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE rewrite_data_files
("key"=value [,"key"=value, ...])
[WHERE <predicate>]
Parameters
rewrite_data_files properties
"key"=value pairs that declare the manual compaction behaviors. Note that you need to wrap the key in double quotes.
min_file_size_bytes
- Description: The upper limit of a small data file. Data files whose size is less than this value will be merged during the compaction.
- Unit: Byte
- Type: Int
- Default: 268,435,456 (256 MB)
batch_size
- Description: The maximum size of data that can be processed in each batch.
- Unit: Byte
- Type: Int
- Default: 10,737,418,240 (10 GB)
rewrite_all
- Description: Whether to rewrite all data files during the compaction, ignoring the parameters that filter data files with specific requirements.
- Unit: -
- Type: Boolean
- Default: false
batch_parallelism
- Description: The number of parallel batches to process during the compaction.
- Unit: -
- Type: Int
- Default: 1
delete_ratio_threshold
- Description: Data files larger than
min_file_size_bytesare also rewritten when the rows deleted by their deletion vector or file-scoped position delete files reach this fraction of their rows. Valid range: [0, 1]. Equality delete files and partition-scoped position delete files do not count towards the ratio. - Unit: -
- Type: Double
- Default: 0.3
delete_file_threshold
- Description: Data files larger than
min_file_size_bytesare also rewritten when at least this number of delete files apply to them. It must be a positive integer. - Unit: -
- Type: Int
- Default: No limit
WHERE clause
- Description: The filter predicate used to specify the partition(s) to be involved in the compaction.
Example
The following example performs manual Compaction on specific partitions in the Iceberg table t1. The partitions are represented by the clause WHERE part_col = 'p1'. In these partitions, data files that are smaller than 134,217,728 bytes (128 MB) will be merged during the Compaction.
ALTER TABLE t1 EXECUTE rewrite_data_files("min_file_size_bytes"= 134217728) WHERE part_col = 'p1';
Metadata management
Expire snapshots
Expires snapshots older than a specific timestamp. This operation deletes the data files of the expired snapshots, helping to manage storage usage.
expire_snapshots Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE expire_snapshots(
[ [older_than =] '<datetime>' ] [, [retain_last =] <int> ]
)
Parameters
older_than
- Description: The timestamp before which snapshots will be removed. If not specified, files older than 5 days (from the current time) will be removed by default. Format: 'YYYY-MM-DD HH:MM:SS'.
- Type: DATETIME
- Required: No
retain_last
- Description: The maximum number of most recent snapshots to retain. The less recent snapshots will be removed when this threshold is reached. If not specified, only one snapshot will be retained by default.
- Type: Integer
- Required: No
Example
Expire snapshots before '2023-12-17 00:14:38' and retain two snapshots:
-- With the parameter key specified:
ALTER TABLE iceberg.sales.order
EXECUTE expire_snapshots(older_than = '2023-12-17 00:14:38', retain_last = 2);
-- With the parameter key unspecified:
ALTER TABLE iceberg.sales.order
EXECUTE expire_snapshots('2023-12-17 00:14:38', 2);
Remove orphan files
Removes orphan files from the table that are not referenced by any valid snapshot and are older than a specified timestamp. This operation helps clean up unused files and reclaim storage space.
remove_orphan_files Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE remove_orphan_files(
[ [older_than =] '<datetime>' ] [, [location =] '<string>' ]
)
Parameters
older_than
- Description: The timestamp before which orphan files will be removed. If not specified, files older than 7 days (from the current time) will be removed by default. Format: 'YYYY-MM-DD HH:MM:SS'. The value must be at least
iceberg_remove_orphan_files_min_retention_seconds(24 hours by default) before the current time. A more recent value is rejected, because deleting files that young can remove data that a concurrent write has not committed yet. - Type: DATETIME
- Required: No
location
- Description: The directory from which you want to remove orphan files. It must be a sub-directory of the table location. If not specified, the table location will be used by default.
- Type: STRING
- Required: No
Example
Remove orphan files older than '2024-01-01 00:00:00' from the sub-directory sub_dir of the table location:
-- With the parameter key specified:
ALTER TABLE iceberg.sales.order
EXECUTE remove_orphan_files(older_than = '2024-01-01 00:00:00', location = 's3://iceberg-bucket/iceberg_db/iceberg_table/sub_dir');
-- With the parameter key unspecified:
ALTER TABLE iceberg.sales.order
EXECUTE remove_orphan_files('2024-01-01 00:00:00', 's3://bucket-test/iceberg_db/iceberg_table/sub_dir');
Rewrite manifests
Rewrites data manifest files and merges them by partition to avoid performance degradation caused by excessive small manifests.
This operation rewrites the data manifests of the current snapshot only.
rewrite_manifests Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE rewrite_manifests()
Example
Rewrite the data manifests of the current snapshot:
ALTER TABLE iceberg.sales.order
EXECUTE rewrite_manifests()
Table management
Add files
Adds data files to an Iceberg table from either a source table or a specific location. This procedure supports Parquet and ORC file formats.
add_files Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE add_files(
[source_table = '<source_table>' | location = '<location>', file_format = '<format>']
[, recursive = <boolean>]
)
Parameters
Either source_table or location must be provided, but not both.
source_table
- Description: The source table from which to add files. Format: 'catalog.database.table'.
- Type: String
- Required: No
location
- Description: The directory path or file path from which to add files.
- Type: String
- Required: No
file_format
- Description: The format of the data files. Supported values: 'parquet', 'orc'.
- Type: String
- Required: No (required when using
location)
recursive
- Description: Whether to recursively scan subdirectories when adding files from a location.
- Type: Boolean
- Default: true
- Required: No
Example
Add files from a source table:
ALTER TABLE iceberg.sales.order
EXECUTE add_files(source_table = 'hive_catalog.sales.source_order');
Add files from a specific location with Parquet format:
ALTER TABLE iceberg.sales.order
EXECUTE add_files(location = 's3://bucket/data/order/', file_format = 'parquet', recursive = true);
Add files from a single file:
ALTER TABLE iceberg.sales.order
EXECUTE add_files(location = 's3://bucket/data/order/data.parquet', file_format = 'parquet');
Add equality delete files
Adds existing files to an Iceberg table as equality delete files. Each row in a file deletes the table rows whose equality columns have the same values. The files are committed in a new snapshot, so they apply to the data already in the table. This procedure supports Parquet and ORC file formats and requires an Iceberg table of format version 2 or later.
Files are discovered from location in the same way as add_files. For a partitioned table, the partition of each file is taken from the <column>=<value> directories in its path, and the delete applies only to the data files in that partition. A file whose path lacks a directory for any partition column is rejected. Only identity partition transforms are supported. For an unpartitioned table, the deletes apply to the whole table. If the current partition spec is partitioned, the procedure fails when the table still has data files written under an older partition spec, because the deletes would not apply to them. When the session variable enable_read_iceberg_equality_delete_with_partition_evolution is set to false (default: true), the query engine does not read equality deletes that span multiple partition specs, so the procedure also fails when the table still has equality delete files written under a partition spec other than the current one. If any column of a file carries an Iceberg field ID, every equality column of that file must carry the table's field ID; otherwise the file is rejected. A file whose equality column is stored in a type that cannot be read as the table column's type without loss is also rejected, for example a string column for an INT table column. A file without field IDs is matched to the table by column name, so do not rename its equality columns after the file is added: the deletes would no longer apply. The procedure does not support a WHERE clause.
add_equality_delete_files Syntax
ALTER TABLE [catalog.][database.]table_name
EXECUTE add_equality_delete_files(
location = '<location>',
file_format = '<format>',
equality_columns = '<column>[, <column> ...]'
[, recursive = <boolean>]
)
Parameters
location
- Description: The directory path or file path from which to add equality delete files.
- Type: String
- Required: Yes
file_format
- Description: The format of the equality delete files. Supported values: 'parquet', 'orc'.
- Type: String
- Required: Yes
equality_columns
- Description: A comma-separated list of the table columns that the deletes match on. Each column must be a top-level column of type
INT,BIGINT,STRING, orDATE, and every file must contain all of these columns. - Type: String
- Required: Yes
recursive
- Description: Whether to recursively scan subdirectories of
location. - Type: Boolean
- Default: true
- Required: No
Example
Delete the rows whose id matches a row in the files under s3://bucket/deletes/order/, for example s3://bucket/deletes/order/dt=2024-01-01/delete.parquet:
ALTER TABLE iceberg.sales.order
EXECUTE add_equality_delete_files(location = 's3://bucket/deletes/order/', file_format = 'parquet', equality_columns = 'id');
Register table
Registers an Iceberg table using a metadata file. This procedure allows you to add an existing Iceberg table to the catalog without migrating data.
register_table Syntax
CALL [catalog.]system.register_table(
database_name = '<database_name>',
table_name = '<table_name>',
metadata_file = '<metadata_file_path>'
)
Parameters
database_name
- Description: The name of the database in which to register the table.
- Type: String
- Required: Yes
table_name
- Description: The name of the table to register.
- Type: String
- Required: Yes
metadata_file
- Description: The path to the Iceberg table metadata file (e.g., metadata.json).
- Type: String
- Required: Yes
Example
Register a table using a metadata file:
CALL iceberg_catalog.system.register_table(
database_name = 'sales',
table_name = 'order',
metadata_file = 's3://bucket/metadata/sales/order/metadata/00001-xxxxx-xxxxx-xxxxx.metadata.json'
);
Or use the current catalog:
CALL system.register_table(
database_name = 'sales',
table_name = 'order',
metadata_file = 's3://bucket/metadata/sales/order/metadata/00001-xxxxx-xxxxx-xxxxx.metadata.json'
);