OPTIMIZE ICEBERG TABLE

Iceberg tables are automatically optimized in the background. However in some situations you may want to manually trigger compaction for a partition or the entire table, for example, you might want to re-write the table data to re-sort it after changing the table sort order.

Upsolver supports manually triggering rewrites in addition to the optimizations the system performs automatically.

Syntax

OPTIMIZE TABLE <table_identifier> REWRITE DATA [WHERE <partition_filter>];

Examples

Optimizing the entire table data

OPTIMIZE TABLE my_table REWRITE DATA;

Optimizing the data for specific partitions

OPTIMIZE TABLE my_table REWRITE DATA WHERE partition_date = '2023-01-01';

Notes

• The OPTIMIZE TABLE command can be resource-intensive, especially when sorting. Usually the automatic rewrites performed by Upsolver are sufficient.

• The optional WHERE clause in the REWRITE DATA option allows for targeted optimization of specific table partitions, which can be useful for large tables where only specific partitions need to be optimized.