Altering Tables

Altering Tables #

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'
);

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

ALTER TABLE my_table SET PROPERTIES write_buffer_size = '256 MB';

NOTE: Versions below Trino 368 do not support changing/adding table properties.

Rename Table Name #

The following SQL rename the table name to new name.

ALTER TABLE my_table RENAME TO my_table_new;

The simplest sql to call is:

ALTER TABLE my_table RENAME TO my_table_new;

Note that: we can rename paimon table in spark this way:

ALTER TABLE [catalog.[database.]]test1 RENAME to [database.]test2;

But we can’t put catalog name before the renamed-to table, it will throw an error if we write sql like this:

ALTER TABLE catalog.database.test1 RENAME to catalog.database.test2;

ALTER TABLE my_table RENAME TO my_table_new;

ALTER TABLE my_table RENAME TO my_table_new;
If you use object storage, 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.

Removing Table Properties #

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

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

ALTER TABLE my_table UNSET TBLPROPERTIES ('write-buffer-size');

Adding New Columns #

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

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

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

ALTER TABLE my_table ADD COLUMN c1 VARCHAR;

ALTER TABLE my_table ADD COLUMN c1 VARCHAR;

Renaming Column Name #

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

ALTER TABLE my_table RENAME c0 TO c1;

ALTER TABLE my_table RENAME COLUMN c0 TO c1;

ALTER TABLE my_table RENAME COLUMN c0 TO c1;

ALTER TABLE my_table RENAME COLUMN c0 TO c1;

Dropping Columns #

The following SQL drops two columns c1 and c2 from table my_table. In hive catalog, you need to ensure disable hive.metastore.disallow.incompatible.col.type.changes in your hive server, otherwise this operation may fail, throws an exception like The following columns have types incompatible with the existing columns in their respective positions.

ALTER TABLE my_table DROP (c1, c2);

ALTER TABLE my_table DROP COLUMNS (c1, c2);

ALTER TABLE my_table DROP COLUMN c1;

ALTER TABLE my_table DROP COLUMN c1;

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 MyTable DROP PARTITION (`id` = 1);

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

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

For spark sql, you need to specify all the partition columns.

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

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
-- If there are NULL values already, set table option as below to drop those records silently before altering table.
SET 'table.exec.sink.not-null-enforcer' = 'DROP';
ALTER TABLE my_table MODIFY coupon_info FLOAT NOT NULL;

ALTER TABLE my_table ALTER COLUMN coupon_info DROP NOT NULL;
Changing nullable column to NOT NULL is only supported by Flink currently.

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';

ALTER TABLE my_table ALTER COLUMN buy_count 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;

ALTER TABLE my_table ADD COLUMN c INT FIRST;

ALTER TABLE my_table ADD COLUMN 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;

ALTER TABLE my_table ALTER COLUMN col_a FIRST;

ALTER TABLE my_table ALTER COLUMN col_a 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;

ALTER TABLE my_table ALTER COLUMN col_a TYPE DOUBLE;

ALTER TABLE my_table ALTER COLUMN col_a SET DATA TYPE DOUBLE;

Supported Type Conversions.

Input Type Target Type
TINYINT
SMALLINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
BOOLEAN
CHAR
VARCHAR/STRING
SMALLINT
TINYINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
BOOLEAN
CHAR
VARCHAR/STRING
INT
TINYINT
SMALLINT
BIGINT
FLOAT
DOUBLE
DECIMAL
BOOLEAN
CHAR
VARCHAR/STRING
BIGINT
TINYINT
SMALLINT
INT
FLOAT
DOUBLE
DECIMAL
BOOLEAN
CHAR
VARCHAR/STRING
FLOAT
TINYINT
SMALLINT
INT
BIGINT
DOUBLE
DECIMAL
CHAR
VARCHAR/STRING
DOUBLE
TINYINT
SMALLINT
INT
BIGINT
FLOAT
DECIMAL
CHAR
VARCHAR/STRING
DECIMAL
TINYINT
SMALLINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
CHAR
VARCHAR/STRING
BOOLEAN
TINYINT
SMALLINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
CHAR
VARCHAR/STRING
TIMESTAMP/TIMESTAMP WITH LOCAL TIME ZONE
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
DATE
TIME
STRING
DATE
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
STRING
TIME
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
STRING
CHAR/VARCHAR/STRING
CHAR
VARCHAR/STRING
BOOLEAN
TINYINT
SMALLINT
INT
BIGINT
FLOAT
DOUBLE
DECIMAL
TIMESTAMP
TIMESTAMP WITH LOCAL TIME ZONE
DATE
TIME
BINARY
VARBINARY
BINARY
BINARY
VARBINARY
VARBINARY
BINARY
VARBINARY

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
Edit This Page
Apache Paimon is an effort undergoing incubation at The Apache Software Foundation (ASF), sponsored by the Apache Incubator. Incubation is required of all newly accepted projects until a further review indicates that the infrastructure, communications, and decision making process have stabilized in a manner consistent with other successful ASF projects. While incubation status is not necessarily a reflection of the completeness or stability of the code, it does indicate that the project has yet to be fully endorsed by the ASF.
Copyright © 2023 The Apache Software Foundation. Apache Paimon, Paimon, and its feather logo are trademarks of The Apache Software Foundation.