Updated on 2025-03-17 GMT+08:00

ADD COLUMNS

Function

The ADD COLUMNS command is used to add a column to an existing table.

Syntax

ALTER TABLE Table name ADD COLUMNS(col_spec[, col_spec ...])

Parameter Description

**Table 1** ADD COLUMNS parameters
Parameter	Description
tableName	Table name.
col_spec	Column specifications, consisting of five fields, col_name, col_type, nullable, comment, and col_position. col_name: name of the new column. It is mandatory. To add a sub-column to a nested column, specify the full name of the sub-column in this field. For example: To add sub-column col1 to a nested struct type column column users struct<name: string, age: int>, set this field to users.col1. To add sub-column col1 to a nested map type column member map<string, struct<n: string, a: int>>, set this field to member.value.col1. To add sub-column col2 to a nested array type column arraylike array<struct<a1: string, a2: int>>, set this field to arraylike.element.col2. col_type: type of the new column. It is mandatory. nullable: whether the new column can be null. The value can be left empty. comment: comment of the new column. The value can be left empty. col_position: position where the new column is added. The value can be FIRST or AFTER origin_col. If it is set to FIRST, the new column will be added to the first column of the table. If it is set to AFTER origin_col, the new column will be added after original column origin_col. The value can be left empty. FIRST can be used only when new sub-columns are added to nested columns. Do not use FIRST in top-level columns. There are no restrictions about the usage of AFTER.

Example

alter table h0 add columns(ext0 string);
alter table h0 add columns(new_col int not null comment 'add new column' after col1);
alter table complex_table add columns(col_struct.col_name string comment 'add new column to a struct col' after col_from_col_struct);

Response

You can run the DESCRIBE command to view the new column.

Parent topic: Hudi Schema Evolution and Syntax