Data Definition Language (DDL)

This section walks you through all data definition language (DDL) commands supported by GridGain 9.0.

CREATE TABLE

Creates a new table.

Diagram( Terminal('CREATE'), Terminal('TABLE'), Optional(Terminal('IF NOT EXISTS')), NonTerminal('table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('('), Choice(1, NonTerminal('constraint', {href:'./grammar-reference/#constraint'}), NonTerminal('column_definition', {href:'./grammar-reference/#column_definition'}), Terminal(',')), Terminal(')'), Optional( Sequence( Terminal('COLOCATE'), Optional('BY'), Terminal('('), NonTerminal('column_list', {href:'./grammar-reference/#column_list'}), Terminal(')'), ) ), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Optional( Sequence( Optional('PRIMARY'), Terminal('ZONE'), NonTerminal('zone_name') ) ), Optional( Sequence( Optional('PRIMARY'), Terminal('STORAGE PROFILE'), NonTerminal('profile_name') ) ), Optional( Sequence( Terminal('EXPIRE'), Terminal('AT'), NonTerminal('expiry_column_name') ) ) )

Keywords and parameters:

IF NOT EXISTS - create the table only if a table with the same name does not exist.
COLOCATE BY - colocation key. The key can be composite. Primary key must include colocation key.
ZONE - sets the Distribution Zone. Can be preceded by PRIMARY to signify the primary distribution zone. Can be specified as a case-sensitive string or case-insensitive identifier. Does no need to exists at the moment of table creation, and can be created before writing data.
STORAGE PROFILE - sets the storage profile that will be used to store the table. Must be specified as a case-sensitive string. Can be preceded by PRIMARY to signify the primary storage profile.
EXPIRE AT - allows specifying a column with a point in time when a record should be deleted.

Examples:

Creates a Person table:

CREATE TABLE IF NOT EXISTS Person (
  id int primary key,
  city_id int,
  name varchar,
  age int,
  company varchar
)

Creates a Person table that uses distribution zone MYZONE:

CREATE TABLE IF NOT EXISTS Person (
  id int primary key,
  city_id int,
  name varchar,
  age int,
  company varchar
) ZONE MYZONE

Creates a Person table that uses the default storage profile regardless of the storage profile specified in the distribution zone:

CREATE TABLE IF NOT EXISTS Person (
  id int primary key,
  city_id int,
  name varchar,
  age int,
  company varchar
) PRIMARY ZONE MYZONE STORAGE PROFILE 'default'

Creates a Person table where the records expire at timestamps in the ttl column:

CREATE TABLE IF NOT EXISTS Person (
  id int PRIMARY KEY,
  name varchar,
  ttl timestamp
) EXPIRE AT ttl

Creates a Person table where the default value if the city_id column is 1:

CREATE TABLE IF NOT EXISTS Person (
  id int primary key,
  city_id int default 1,
  name varchar,
  age int,
  company varchar
)

Creates a Person table with the duration column automatically set a week into the future:

CREATE TABLE IF NOT EXISTS Person (
  id int primary key,
  city_id int,
  name varchar,
  age int,
  company varchar,
  duration timestamp default CURRENT_TIMESTAMP + INTERVAL '7' DAYS
)

ALTER TABLE

Modifies the structure of an existing table.

ALTER TABLE IF EXISTS table ADD COLUMN (column1 int, column2 int)

Adds column(s) to an existing table.

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('ADD'), Optional(Terminal('COLUMN')), End({type:'complex'}) )

Diagram( Start({type:'complex'}), NonTerminal('column_definition_or_list', {href:'./grammar-reference/#column_definition_or_list'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.

Examples:

Add a column to the table:

ALTER TABLE Person ADD COLUMN city varchar;

Add a column only if the table exists:

ALTER TABLE IF EXISTS Person ADD number bigint;

Add several columns to the table at once:

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

ALTER TABLE IF EXISTS table DROP COLUMN (column1, column2 int)

Removes column(s) from an existing table. Once a column is removed, it cannot be accessed within queries. This command has the following limitations:

If the column was indexed, the index has to be dropped manually in advance by using the 'DROP INDEX' command.
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.

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('DROP'), Optional(Terminal('COLUMN')), End({type:'complex'}) )

Diagram( Start({type:'complex'}), NonTerminal('column_list', {href:'./grammar-reference/#column_list'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.

Examples:

Drop a column from the table:

ALTER TABLE Person DROP COLUMN city;

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

ALTER TABLE IF EXISTS table ALTER COLUMN column SET DATA TYPE

Changes the data type for the column(s) in an existing table.

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('ALTER COLUMN'), NonTerminal('column_name_or_list', {href:'./grammar-reference/#column_name_or_list'}), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Terminal('SET DATA TYPE'), NonTerminal('data_type'), Optional(Sequence( Terminal('('), Choice (0,'NULLABLE','NOT NULL'), Terminal(')') )))

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.
data_type - a valid data type.

Examples:

Alter a column in the table:

ALTER TABLE Person ALTER COLUMN city SET DATA TYPE varchar;

Supported Transitions

Not all data type transitions are supported. The limitations are listed below:

FLOAT can be transitioned to DOUBLE
INT8, INT16 and INT64 can be transitioned to INT32
TYPE SCALE change is forbidden
TYPE PRECISION increase is allowed for DECIMAL non PK column
TYPE LENGTH increase is allowed for STRING and BYTE_ARRAY non PK column

Other transitions are not supported.

Examples:

Changes the possible range of IDs to BIGINT ranges:

ALTER TABLE Person ALTER COLUMN age SET DATA TYPE BIGINT

Sets the length of a column text to 11:

ALTER TABLE Person ALTER COLUMN Name SET DATA TYPE varchar(11)

ALTER TABLE IF EXISTS table ALTER COLUMN column SET NOT NULL

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('ALTER COLUMN'), NonTerminal('column_name_or_list', {href:'./grammar-reference/#column_name_or_list'}), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Terminal('SET NOT NULL'), End({type:'simple'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.

Supported Transitions

Not all data type transitions are supported. The limitations are listed below:

NULLABLE to NOT NULL transition is forbidden

ALTER TABLE IF EXISTS table ALTER COLUMN column DROP NOT NULL

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('ALTER COLUMN'), NonTerminal('column_name_or_list', {href:'./grammar-reference/#column_name_or_list'}), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Terminal('DROP NOT NULL'), End({type:'simple'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.

Supported Transitions

Not all data type transitions are supported. The limitations are listed below:

NOT NULL to NULLABLE transition is allowed for any non-PK column

ALTER TABLE IF EXISTS table ALTER COLUMN column SET DEFAULT

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('ALTER COLUMN'), NonTerminal('column_name_or_list', {href:'./grammar-reference/#column_name_or_list'}), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Terminal('SET DATA TYPE'), NonTerminal('data_type'), Optional(Sequence( Choice (0,'NULL','NOT NULL'), )), Optional(Sequence( Terminal('DEFAULT'), Sequence(Choice(0, NonTerminal('literal_value') ),), )) )

Keywords and parameters:

IF NOT EXISTS - do not throw an error if a table with the specified table name does not exist.

ALTER TABLE IF EXISTS table ALTER COLUMN column DROP DEFAULT

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('ALTER COLUMN'), NonTerminal('column_name_or_list', {href:'./grammar-reference/#column_name_or_list'}), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Terminal('DROP DEFAULT'), End({type:'simple'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.

ALTER TABLE IF EXISTS SET EXPIRE AT

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('SET'), Terminal('EXPIRE'), Terminal('AT'), NonTerminal('expiry_column_name'), End({type:'complex'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.
expiry_column_name - the name of the table column that is used to track expiry time.

ALTER TABLE IF EXISTS table SET EXPIRE AT

Diagram( Terminal('ALTER TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('EXPIRE'), Terminal('AT'), NonTerminal('expiry_column_name'), End({type:'simple'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.
EXPIRE AT - allows specifying a column with a point in time when a record should be deleted.

ALTER TABLE IF EXISTS table DROP EXPIRE

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the specified table name does not exist.
DROP EXPIRE - disables row expiration for the table.

DROP TABLE

The DROP TABLE command drops an existing table. The table will be marked for deletion and will be removed by garbage collection after the low watermark point is reached. Until the data is removed, it will be available to read-only transactions that check the time before the table was marked for deletion.

Diagram( Terminal('DROP TABLE'), Optional(Terminal('IF EXISTS')), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}) )

Keywords and parameters:

IF EXISTS - do not throw an error if a table with the same name does not exist.

Schema changes applied by this command are persisted on disk. Thus, the changes can survive full cluster restarts.

Examples:

Drop Person table if the one exists:

DROP TABLE IF EXISTS "Person";

CREATE INDEX

Creates a new index.

When you create a new index, it will start building only after all transactions started before the index creation had been completed. Index build will not start if there are any “hung“ transactions in the logical topology of the cluster.

The index status, with the status reason description (e.g., PENDING - “Waiting for transaction ABC to complete”) is reflected in the system view.

Diagram( Terminal('CREATE INDEX'), Optional(Terminal('IF NOT EXISTS')), NonTerminal('name'), Terminal('ON'), NonTerminal('qualified_table_name', {href:'./grammar-reference/#qualified_table_name'}), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Sequence( Choice (0, Sequence( Terminal('USING'), Choice (0, Sequence('TREE',NonTerminal ('sorted_column_list', {href:'./grammar-reference/#sorted_column_list'})), Sequence('HASH',NonTerminal ('column_list', {href:'./grammar-reference/#column_list'})) ), ), NonTerminal ('sorted_column_list', {href:'./grammar-reference/#sorted_column_list'}) )), End({type:'simple'}) )

Keywords and parameters:

IF NOT EXISTS - create the index only if an index with the same name does not exist.
name - name of the index.
ON - create index on the defined table.
USING TREE -if specified, creates a tree index.
USING HASH - if specified, creates a hash index.

Examples:

Create an index department_name_idx for the Person table:

CREATE INDEX IF NOT EXISTS department_name_idx ON Person (department_id DESC, name ASC);

Create a hash index department_name_idx for the Person table:

CREATE INDEX name_surname_idx ON Person USING HASH (name, surname);

Create a tree index department_city_idx for the Person table:

CREATE INDEX department_city_idx ON Person USING TREE (department_id ASC, city_id DESC);

DROP INDEX

Drops an index.

When you drop an index, it stays in the STOPPING status until all transactions started before the DROP INDEX command had been completed (even those that do not affect any of the tables for which the index is being dropped). Upon completion of all transactions described above, the space the dropped index had occupied is freed up only when LWM of the relevant partition becomes greater than the time when the index dropping had been activated. The index status, with the status reason description (e.g., PENDING - “Waiting for transaction ABC to complete”) is reflected in the system view.

Diagram( Terminal('DROP INDEX'), Optional(Terminal('IF EXISTS')), NonTerminal('index_name') )

Keywords and parameters:

index_name - the name of the index.
IF EXISTS - do not throw an error if an index with the specified name does not exist.

Examples:

Drop index if the one exists:

DROP INDEX IF EXISTS department_name_idx;

CREATE CACHE

Creates a new cache.

Diagram( Terminal('CREATE'), Terminal('CACHE'), Optional(Terminal('IF NOT EXISTS')), NonTerminal('cache_name', {href:'./grammar-reference/#qualified_table_name'}), Terminal('('), Choice(1, NonTerminal('constraint', {href:'./grammar-reference/#constraint'}), NonTerminal('column_definition', {href:'./grammar-reference/#column_definition'}), Terminal(',')), Terminal(')'), Optional( Sequence( Terminal('COLOCATE'), Optional('BY'), Terminal('('), NonTerminal('column_list', {href:'./grammar-reference/#column_list'}), Terminal(')'), ) ), End({type:'complex'}) )

Diagram( Start({type:'complex'}), Optional( Sequence( Optional('PRIMARY'), Terminal('ZONE'), NonTerminal('zone_name') ) ), Optional( Sequence( Optional('PRIMARY'), Terminal('STORAGE PROFILE'), NonTerminal('profile_name') ) ), Optional( Sequence( Terminal('WRITE MODE'), Choice(0, NonTerminal('SYNC'), NonTerminal('ASYNC'), ) ) ), Optional( Sequence( Terminal('EXPIRE'), Terminal('AT'), NonTerminal('expiry_column_name') ) ) )

Keywords and parameters:

IF NOT EXISTS - create the cache only if a cache with the same name does not exist.
COLOCATE BY - colocation key. The key can be composite. Primary key must include colocation key. Was affinity_key in GridGain 2.x.
ZONE - sets the Distribution Zone. Can be specified as a case-sensitive string or case-insensitive identifier. Can be preceded by PRIMARY to signify the primary distribution zone.
STORAGE PROFILE - sets the storage profile that will be used to store the table. Must be specified as a case-sensitive string. Can be preceded by PRIMARY to signify the primary storage profile.
WRITE MODE - configures if the data is written to external store. Default value: SYNC. Possible values:
- SYNC - External Cache store writes are synchronous. KeyValueView operations won’t return until external cache store write is complete.
- ASYNC - External Cache store writes are asynchronous and performed in background. KeyValueView operations may return before external cache store write completes.
EXPIRE AT - allows specifying a column with a point in time when a record should be deleted.
expiry_column_name - name of the column that contains values on which the record expiry is based.

Example:

Creates a cache Accounts:

CREATE CACHE Accounts (
    accountNumber INT PRIMARY KEY,
    firstName VARCHAR,
    lastName VARCHAR,
    balance DOUBLE,
    ttl timestamp DEFAULT CURRENT_TIMESTAMP + INTERVAL '2' HOURS
) ZONE CACHES EXPIRE AT ttl;

CREATE SEQUENCE

Creates a new sequence object. You can use sequences to generate a sequence of numeric values in ascending or descending order. You can then use sequence functions to use them in your tables.

Diagram( Terminal('CREATE SEQUENCE'), Optional(Terminal('IF NOT EXISTS')), NonTerminal('name'), Optional(Sequence( Terminal('INCREMENT'), Optional('BY'), NonTerminal('increment_value') )), )

Diagram( Optional(Choice(1, Sequence( Terminal('MINVALUE'), NonTerminal('minimum_value')), Sequence( Terminal('NO MINVALUE')))), Optional(Choice(1, Sequence( Terminal('MAXVALUE'), NonTerminal('maximum_value')), Sequence( Terminal('NO MAXVALUE')))), Optional(Sequence( Terminal('START'), NonTerminal('start_value') )), Optional(Sequence( Terminal('CACHE'), NonTerminal('cache_name') )) )

Keywords and parameters:

IF NOT EXISTS - create the sequence only if a sequence with the same name does not exist.
INCREMENT BY - how much the sequence is incremented by each time. Can be both positive and negative. Default value: 1.
- If positive value is specified, the sequence is ascending.
- If negative value is specified, the sequence is descending.
MINVALUE - the minimum value of the sequence. Default value: 1 for ascending sequences, minimum value supported by data type for descending sequences.
MAXVALUE - the maximum value of the sequence.Default value: maximum value supported by data type for ascending sequences, -1 for descending sequences.
START - the value the sequence starts at. Default value: same as MINVALUE for ascending sequences, or MAXVALUE for descending sequences.
CACHE - the amount of sequence numbers that are pre-allocated and stored in memory. Default value: 1000. When preallocated values run out, next access to any sequence method will preallocate values again. ALTER SEQUENCE ddl command and SETVAL() function reset the cache. Any numbers allocated but not used will be lost, resulting in “holes” in the sequence. All generated values are all distinct, not that they are generated purely sequentially.

Examples:

Creates a basic ascending sequence with default values:

CREATE SEQUENCE IF NOT EXISTS defaultSequence;

Creates an ascending sequence that starts at 10, and increments by 10 up to the maximum value of 1000

CREATE SEQUENCE IF NOT EXISTS ascendingSequence INCREMENT BY 10 START 10 MAXVALUE 100000;

Creates a descending sequence that starts at 15, and decrements by 50 up to the minimum value of -100;

CREATE SEQUENCE IF NOT EXISTS descendingSequence INCREMENT BY -5 START 15 MAXVALUE 15 MINVALUE -10000;

Uses the defaultSequence sequence and the NEXTVAL() function to automatically increment the ID column in the table. When inserting values, you can skip the ID column and it will get incremented automatically:

CREATE SEQUENCE IF NOT EXISTS defaultSequence;

CREATE TABLE IF NOT EXISTS Person (
  id bigint default NEXTVAL('defaultSequence') primary key ,
  city_id bigint,
  name varchar,
  age int,
  company varchar
);

INSERT INTO Person (city_id, name, age, company) values (1, 'John', 30, 'newCorp'), (2, 'Jane', 24, 'oldCorp')

ALTER SEQUENCE

Changes the properties of an existing sequence object.

Diagram( Terminal('ALTER SEQUENCE'), Optional(Terminal('IF EXISTS')), NonTerminal('name'), Optional(Sequence( Terminal('INCREMENT'), Optional('BY'), NonTerminal('increment_value') )), Optional(Sequence( Terminal('MINVALUE'), NonTerminal('minimum_value'))), Optional(Sequence( Terminal('MAXVALUE'), NonTerminal('maximum_value'))), Optional(Sequence( Terminal('START'), Optional('WITH'), NonTerminal('start') )), Optional(Sequence( Terminal('CACHE'), NonTerminal('cache') )))

Keywords and parameters:

IF NOT EXISTS - create the sequence only if a sequence with the same name does not exist.
INCREMENT BY - how much the sequence is incremented by each time. Can be both positive and negative. Default value: 1.
- If positive value is specified, the sequence is ascending.
- If negative value is specified, the sequence is descending.
MINVALUE - the minimum value of the sequence. Default value: 1 for ascending sequences, minimum value supported by data type for descending sequences.
MAXVALUE - the maximum value of the sequence.Default value: maximum value supported by data type for ascending sequences, -1 for descending sequences.
START - the value the sequence starts at. Default value: same as MINVALUE for ascending sequences, or MAXVALUE for descending sequences.
CACHE - the amount of sequence numbers that are pre-allocated and stored in memory. Default value: 1000.

Examples:

Alters the default sequence to have 20000 as maximum value:

ALTER SEQUENCE IF EXISTS defaultSequence MAXVALUE 20000

DROP SEQUENCE

Drops an existing sequence object.

Diagram( Terminal('DROP SEQUENCE'), Optional(Terminal('IF EXISTS')), NonTerminal('name') )

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Dec 18, 2024