This documentation is for an unreleased version of Apache Paimon. We recommend you use the latest stable version.
Altering Tables #
Changing/Adding Table Properties #
The following SQL sets write-buffer-size
table property to 256 MB
.
ALTER TABLE my_table SET TBLPROPERTIES (
'write-buffer-size' = '256 MB'
);
Removing Table Properties #
The following SQL removes write-buffer-size
table property.
ALTER TABLE my_table UNSET TBLPROPERTIES ('write-buffer-size');
Changing/Adding Table Comment #
The following SQL changes comment of table my_table
to table comment
.
ALTER TABLE my_table SET TBLPROPERTIES (
'comment' = 'table comment'
);
Removing Table Comment #
The following SQL removes table comment.
ALTER TABLE my_table UNSET TBLPROPERTIES ('comment');
Rename Table Name #
The following SQL rename the table name to new name.
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;
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.
Adding New Columns #
The following SQL adds two columns c1
and c2
to table my_table
.
ALTER TABLE my_table ADD COLUMNS (
c1 INT,
c2 STRING
);
The following SQL adds a nested column f3
to a struct type.
-- column v previously has type STRUCT<f1: STRING, f2: INT>
ALTER TABLE my_table ADD COLUMN v.f3 STRING;
The following SQL adds a nested column f3
to a struct type, which is the element type of an array type.
-- column v previously has type ARRAY<STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table ADD COLUMN v.element.f3 STRING;
The following SQL adds a nested column f3
to a struct type, which is the value type of a map type.
-- column v previously has type MAP<INT, STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table ADD COLUMN v.value.f3 STRING;
Renaming Column Name #
The following SQL renames column c0
in table my_table
to c1
.
ALTER TABLE my_table RENAME COLUMN c0 TO c1;
The following SQL renames a nested column f1
to f100
in a struct type.
-- column v previously has type STRUCT<f1: STRING, f2: INT>
ALTER TABLE my_table RENAME COLUMN v.f1 to f100;
The following SQL renames a nested column f1
to f100
in a struct type, which is the element type of an array type.
-- column v previously has type ARRAY<STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table RENAME COLUMN v.element.f1 to f100;
The following SQL renames a nested column f1
to f100
in a struct type, which is the value type of a map type.
-- column v previously has type MAP<INT, STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table RENAME COLUMN v.value.f1 to f100;
Dropping Columns #
The following SQL drops two columns c1
and c2
from table my_table
.
ALTER TABLE my_table DROP COLUMNS (c1, c2);
The following SQL drops a nested column f2
from a struct type.
-- column v previously has type STRUCT<f1: STRING, f2: INT>
ALTER TABLE my_table DROP COLUMN v.f2;
The following SQL drops a nested column f2
from a struct type, which is the element type of an array type.
-- column v previously has type ARRAY<STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table DROP COLUMN v.element.f2;
The following SQL drops a nested column f2
from a struct type, which is the value type of a map type.
-- column v previously has type MAP<INT, STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table DROP COLUMN v.value.f2;
Dropping Partitions #
The following SQL drops the partitions of the paimon table. For spark sql, you need to specify all the partition columns.
ALTER TABLE my_table DROP PARTITION (`id` = 1, `name` = 'paimon');
Adding Partitions #
The following SQL adds the partitions of the paimon table. For spark sql, you need to specify all the partition columns, only with metastore configured metastore.partitioned-table=true.
ALTER TABLE my_table ADD PARTITION (`id` = 1, `name` = 'paimon');
Changing Column Comment #
The following SQL changes comment of column buy_count
to buy count
.
ALTER TABLE my_table ALTER COLUMN buy_count COMMENT 'buy count';
Adding Column Position #
ALTER TABLE my_table ADD COLUMN c INT FIRST;
ALTER TABLE my_table ADD COLUMN c INT AFTER b;
Changing Column Position #
ALTER TABLE my_table ALTER COLUMN col_a FIRST;
ALTER TABLE my_table ALTER COLUMN col_a AFTER col_b;
Changing Column Type #
ALTER TABLE my_table ALTER COLUMN col_a TYPE DOUBLE;
The following SQL changes the type of a nested column f2
to BIGINT
in a struct type.
-- column v previously has type STRUCT<f1: STRING, f2: INT>
ALTER TABLE my_table ALTER COLUMN v.f2 TYPE BIGINT;
The following SQL changes the type of a nested column f2
to BIGINT
in a struct type, which is the element type of an array type.
-- column v previously has type ARRAY<STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table ALTER COLUMN v.element.f2 TYPE BIGINT;
The following SQL changes the type of a nested column f2
to BIGINT
in a struct type, which is the value type of a map type.
-- column v previously has type MAP<INT, STRUCT<f1: STRING, f2: INT>>
ALTER TABLE my_table ALTER COLUMN v.value.f2 TYPE BIGINT;