Data compaction

From Hive and Impala, you can compact Iceberg tables and optimize them for read operations. Compaction is an essential table maintenance activity that creates a new snapshot, which contains the table content in a compact form.

Frequent updates and row-level modifications on Iceberg tables can result in many small data files and delete files, which have to be merged-on-read. This degrades the query performance over time. You can use the following Hive and Impala SQL statements to compact Iceberg tables and optimize the table for reading.

Impala syntax🔗

OPTIMIZE TABLE [db_name.]table_name [FILE_SIZE_THRESHOLD_MB=<value>];

The FILE_SIZE_THRESHOLD_MB enables you to specify the maximum size of files (in MB) that should be considered for compaction. Data files larger than the specified limit are rewritten only if they are referenced from delete files.

Impala example🔗

OPTIMIZE TABLE ice_table FILE_SIZE_THRESHOLD_MB=100;

Hive syntax🔗

ALTER TABLE [database_name.]table_name COMPACT 'compaction_type' [AND WAIT];

OPTIMIZE TABLE [database_name.]table_name REWRITE DATA;

Hive example🔗

ALTER TABLE ice_table COMPACT 'MAJOR';

OPTIMIZE TABLE ice_table REWRITE DATA;

important

When Cloudera Data Warehouse and Cloudera Data Hub are deployed in the same environment and use the same Hive Metastore (HMS) instance, the Cloudera Data Hub compaction workers can inadvertently pick up Iceberg compaction tasks. Since Iceberg compaction is not yet supported in the latest Cloudera Data Hub version, the compaction tasks will fail when they are processed by the Cloudera Data Hub compaction workers.

In such a scenario where both Cloudera Data Warehouse and Cloudera Data Hub share the same HMS instance and there is requirement to run both Hive ACID and Iceberg compaction jobs, it is recommended that you use the Cloudera Data Warehouse environment for these jobs. If you want to run only Hive ACID compaction tasks, you can choose to use either the Cloudera Data Warehouse or Cloudera Data Hub environments.

If you want to run the compaction jobs without changing the environment, it is recommended that you use Cloudera Data Warehouse. To avoid interference from Cloudera Data Hub, change the value of the hive.compactor.worker.threads Hive Server (HS2) property in Cloudera Data Hub to '0'. This ensures that the compaction jobs are not processed by Cloudera Data Hub. You can find this property in Cloudera Manager > Hive > Configuration.

To perform table optimization, ensure that the following prerequisites are met:

The user performing compaction must have the 'ALL' permissions on the table, which can be set through Ranger.
Impala can only write Parquet files, therefore the write.format.default table property must be set to parquet. Hive can write both Parquet and ORC file formats.
Impala cannot compact tables with complex data types.
Impala cannot compact views.

The OPTIMIZE TABLE statement rewrites the entire table, performing the following tasks:

Compact small files into larger files
Merge delete files created due to previously run DELETE and UPDATE operations
Rewrite all files, converting them to the latest table schema
Rewrite all partitions according to the latest partition specification
Compact tables with partition evolution

When an Iceberg table is optimized, a new snapshot is created where all the old files of the table are replaced with newly written files. Note that if the FILE_SIZE_THRESHOLD_MB option is specified, only the files meeting the file size threshold limit are replaced. The old table state and old files can still be queried using time travel, because the rewritten data and delete files are not removed from the system. This can lead to the accumulation of unused files that belong to old snapshots. Use the Expire Snapshots feature to permanently remove the old files from the file system.