Altering Tables

Use ALTER TABLE for table options and schema evolution, and ALTER DATABASE for database properties. The examples assume a Paimon catalog is selected; see SQL DDL.

Change	Sections
Table metadata	Properties, comments, rename
Columns	Add, rename, drop, type, nullability, position
Partitions	Drop partitions
Event time	Add, drop, or change a watermark
Database metadata	Database properties, location

Changing/Adding Table Properties

The following SQL sets write-buffer-size table property to 256 MB.

ALTER TABLE my_table SET (
    'write-buffer-size' = '256 MB'
);

Removing Table Properties

The following SQL removes write-buffer-size table property.

ALTER TABLE my_table RESET ('write-buffer-size');

Changing/Adding Table Comment

The following SQL changes comment of table my_table to table comment.

ALTER TABLE my_table SET (
    'comment' = 'table comment'
    );

Removing Table Comment

The following SQL removes table comment.

ALTER TABLE my_table RESET ('comment');

Rename Table Name

The following SQL rename the table name to new name.

ALTER TABLE my_table RENAME TO my_table_new;

info

If you use object storage without REST Catalog, such as S3 or OSS, please use this syntax carefully, because the renaming of object storage is not atomic, and only partial files may be moved in case of failure.

Adding New Columns

The following SQL adds two columns c1 and c2 to table my_table.

info

To add a column in a row type, see Changing Column Type.

ALTER TABLE my_table ADD (c1 INT, c2 STRING);

Renaming Column Name

The following SQL renames column c0 in table my_table to c1.

ALTER TABLE my_table RENAME c0 TO c1;

Dropping Columns

The following SQL drops two columns c1 and c2 from table my_table.

ALTER TABLE my_table DROP (c1, c2);

info

To drop a column in a row type, see Changing Column Type.

warning

When using a hive catalog, this operation requires hive.metastore.disallow.incompatible.col.type.changes=false to be set on the Hive Metastore server (in its hive-site.xml, then restart HMS). Setting this key on the Paimon catalog (WITH (...)) or via a Flink SQL SET only configures the client-side HiveConf; the value is not propagated to the remote Hive Metastore service over Thrift, so setting it on the client has no effect.

See HIVE-17832 for the historical discussion.

Otherwise this operation may fail, throws an exception like The following columns have types incompatible with the existing columns in their respective positions.

Dropping Partitions

The following SQL drops the partitions of the paimon table.

For flink sql, you can specify the partial columns of partition columns, and you can also specify multiple partition values at the same time.

ALTER TABLE my_table DROP PARTITION (`id` = 1);

ALTER TABLE my_table DROP PARTITION (`id` = 1, `name` = 'paimon');

ALTER TABLE my_table DROP PARTITION (`id` = 1), PARTITION (`id` = 2);

Changing Column Nullability

The following SQL changes nullability of column coupon_info.

CREATE TABLE my_table (id INT PRIMARY KEY NOT ENFORCED, coupon_info FLOAT NOT NULL);

-- Change column `coupon_info` from NOT NULL to nullable
ALTER TABLE my_table MODIFY coupon_info FLOAT;

-- Change column `coupon_info` from nullable to NOT NULL
-- Verify and clean existing NULL values before changing the schema.
ALTER TABLE my_table SET ('alter-column-null-to-not-null.disabled' = 'false');
ALTER TABLE my_table MODIFY coupon_info FLOAT NOT NULL;

info

Changing a nullable column to NOT NULL is supported by Flink, but is disabled by default in Paimon. The option above explicitly enables it. The operation changes the schema without rewriting existing rows. Flink's table.exec.sink.not-null-enforcer controls null handling for sink writes; it does not clean existing table data.

Changing Column Comment

The following SQL changes comment of column buy_count to buy count.

ALTER TABLE my_table MODIFY buy_count BIGINT COMMENT 'buy count';

Adding Column Position

To add a new column with specified position, use FIRST or AFTER col_name.

ALTER TABLE my_table ADD c INT FIRST;

ALTER TABLE my_table ADD c INT AFTER b;

Changing Column Position

To modify an existent column to a new position, use FIRST or AFTER col_name.

ALTER TABLE my_table MODIFY col_a DOUBLE FIRST;

ALTER TABLE my_table MODIFY col_a DOUBLE AFTER col_b;

Changing Column Type

The following SQL changes type of column col_a to DOUBLE.

ALTER TABLE my_table MODIFY col_a DOUBLE;

Paimon also supports changing columns of row type, array type, and map type.

-- col_a previously has type ARRAY<MAP<INT, ROW(f1 INT, f2 STRING)>>
-- the following SQL changes f1 to BIGINT, drops f2, and adds f3
ALTER TABLE my_table MODIFY col_a ARRAY<MAP<INT, ROW(f1 BIGINT, f3 DOUBLE)>>;

Adding watermark

The following SQL adds a computed column ts from existing column log_ts, and a watermark with strategy ts - INTERVAL '1' HOUR on column ts which is marked as event time attribute of table my_table.

ALTER TABLE my_table ADD (
    ts AS TO_TIMESTAMP(log_ts) AFTER log_ts,
    WATERMARK FOR ts AS ts - INTERVAL '1' HOUR
);

Dropping watermark

The following SQL drops the watermark of table my_table.

ALTER TABLE my_table DROP WATERMARK;

Changing watermark

The following SQL modifies the watermark strategy to ts - INTERVAL '2' HOUR.

ALTER TABLE my_table MODIFY WATERMARK FOR ts AS ts - INTERVAL '2' HOUR;

ALTER DATABASE

The following SQL sets one or more properties in the specified database. If a particular property is already set in the database, override the old value with the new one.

ALTER DATABASE [catalog_name.]db_name SET (key1=val1, key2=val2, ...);

Altering Database Location

The following SQL changes location of database my_database to file:/temp/my_database.

ALTER DATABASE my_database SET ('location' =  'file:/temp/my_database');