Apache Ignite SQL Documentation

The Apache Ignite SQL Developer Hub

Welcome to the Apache Ignite SQL developer hub. You'll find comprehensive guides and documentation to help you start working with Apache Ignite SQL as quickly as possible, as well as support if you get stuck. Let's jump right in!

Get Started    

ALTER TABLE

Modify the structure of an existing table.

ALTER TABLE [IF EXISTS] tableName {alter_specification}
  
alter_specification:
    ADD [COLUMN] {[IF NOT EXISTS] tableColumn | (tableColumn [,...])}
  | DROP [COLUMN] {[IF EXISTS] columnName | (columnName [,...])}
  | {LOGGING | NOLOGGING}
  
tableColumn := columnName columnType

Scope of ALTER TABLE

Presently, Ignite only supports addition and removal of columns. In the upcoming releases the syntax and capabilities of the command will be expanded further.

Parameters

  • tableName - name of the table.
  • tableColumn - name and type of the column to be added to the table.
  • columnName - name of the column to be added or removed.
  • IF EXISTS - if applied to TABLE, do not throw an error if a table with the specified table name does not exist. If applied to COLUMN, do not throw an error if a column with the specified name does not exist.
  • IF NOT EXISTS - do not throw an error if a column with the same name already exists.
  • LOGGING - enable write-ahead logging for the table. Write-ahead logging in enabled by default. The command is relevant only if Ignite persistence is used.
  • NOLOGGING - disable write-ahead logging for the table. The command is relevant only if Ignite persistence is used.

Description

ALTER TABLE ADD adds a new column or several columns to a previously created table. Once a column is added, it can be accessed using DML commands and indexed with the CREATE INDEX statement.

ALTER TABLE DROP removes an existing column or multiple columns from a table. Once a column is removed, it cannot be accessed within queries. The following specificities and limitations have to be considered:

  • The command does not remove actual data from the cluster which means that if the column 'name' is dropped, the value of the 'name' will still be stored in the cluster. This limitation is to be addressed in the next releases.
  • If the column was indexed, the index has to be dropped manually using the 'DROP INDEX' command.
  • It is not possible to remove a column that is a primary key or a part of such a key.
  • It is not possible to remove a column if it represents the whole value stored in the cluster. The limitation is relevant for primitive values.

Ignite stores data in the form of key-value pairs and all the new columns will belong to the value. It's not possible to change a set of columns of the key (PRIMARY KEY).

Both DDL and DML commands targeting the same table are blocked for a short time until ALTER TABLE is in progress.

Schema changes applied by this command are persisted on disk if Ignite persistence is enabled. Thus, the changes can survive full cluster restarts.

Examples

Add a column to the table:

ALTER TABLE Person ADD COLUMN city varchar;

Add a new column to the table only if a column with the same name does not exist:

ALTER TABLE City ADD COLUMN IF NOT EXISTS population int;

Add a column​ only if the table exists:

ALTER TABLE IF EXISTS Missing ADD number long;

Add several columns to the table at once:

ALTER TABLE Region ADD COLUMN (code varchar, gdp double);

Drop a column from the table:

ALTER TABLE Person DROP COLUMN city;

Drop a column from the table only if a column with the same name does exist:

ALTER TABLE Person DROP COLUMN IF EXISTS population;

Drop a column only if the table exists:

ALTER TABLE IF EXISTS Person DROP COLUMN number;

Drop several columns from the table at once:

ALTER TABLE Person DROP COLUMN (code, gdp);

Disable write-ahead logging:

ALTER TABLE Person NOLOGGING

See Also