Modifying an Iceberg Table

Modifying Table Columns

Using the ALTER TABLE command with capabilities like ADD COLUMN, RENAME COLUMN TO, ALTER COLUMN, and DROP COLUMNS, you can perform schema evolution tasks such as adding, renaming, updating, reordering, or removing columns in Iceberg tables.

Iceberg assigns each column a unique fieldId. When writing data, this fieldId is embedded in the file's schema. During reads, the metadata maps columns using fieldId, enabling reading post-schema evolution. For non-Iceberg files lacking fieldId, mapping relies on column names. If column names are reused during schema evolution, it may cause type mismatches, read failures, or retrieval of outdated data.

Example:

Create an Iceberg partitioned table.

CREATE TABLE t_iceberg(a int, b array<struct<x:int, y:int>>) partition by (c int) store as iceberg;

• Adding columns:

  Iceberg allows adding one or more columns to a table or nested fields with complex types. Specify the column name, type, optional comments, and position. Comments cannot be added when inserting into complex types.

  -- Add to a table.
  alter table t_iceberg add column d int, e float comment 'this is e', f array<int> after a, g struct<x:int,y:int> comment 'this is g' before b;
  -- Add to a nested field with complex types. Use element to specify the sub-type of the array.
  alter table t_iceberg add column b.element.z int after x, b.element.a before z;

• Renaming columns:

  You can rename both table columns and nested fields with complex types.

  alter table t_iceberg rename column a to newa;
  alter table t_iceberg rename column b.element.x to newx;

• Updating columns:

  Iceberg supports updating types, comments, and positions for table columns, as well as types and positions for nested fields.

  alter table t_iceberg alter column a type bigint comment 'this is a' after c, c type bigint comment 'this is c' after a, b.element.y type bigint after first;

  Currently, only upgrades from lower-precision to higher-precision types are allowed:

  • Integer types: From SMALLINT to INT or BIGINT, or from INT to BIGINT.
  • Floating-point types: From FLOAT4 to FLOAT8.
  • Numeric types: Increase precision but not scale.
  • String types: Increase length of VARCHAR or CHAR.
    

    Floating-point storage is imprecise. When upgrading from FLOAT4 to FLOAT8, decimal precision may change, affecting results.

• Dropping columns:

  Iceberg supports dropping table columns or nested fields within complex types. However, you cannot delete partition columns or remove all columns from a table or all fields from a complex type.

  alter table t_iceberg drop columns (a, b.x);

Modifying Table Properties

You can modify the properties of a table using the SET/UNSET TABLEPROPERTIES capability of the ALTER TABLE syntax.

Example:

1
2

ALTER TABLE iceberg_ext SET TABLEPROPERTIES ('write.metadata.delete-after-commit.enabled' = 'false');
ALTER TABLE iceberg_ext UNSET TABLEPROPERTIES ('write.metadata.delete-after-commit.enabled');