GridGain Developers Hub
GitHub logo GridGain iso GridGain.com
GridGain Software Documentation

Data Definition Language (DDL)

This page encompasses all data definition language (DDL) commands supported by GridGain.

CREATE TABLE

Create a new table and an underlying GridGain cache.

CREATE TABLE [IF NOT EXISTS] tableName (tableColumn [, tableColumn]...
[, PRIMARY KEY (columnName [,columnName]...)])
[WITH "paramName=paramValue [,paramName=paramValue]..."]

tableColumn := columnName columnType [DEFAULT defaultValue] [PRIMARY KEY]

Parameters

  • tableName - name of the table.

  • tableColumn - name and type of a column to be created in the new table.

  • columnName - name of a previously defined column.

  • DEFAULT - specifies a default value for the column. Only constant values are accepted.

  • IF NOT EXISTS - create the table only if a table with the same name does not exist.

  • PRIMARY KEY - specifies a primary key for the table that can consist of a single column or multiple columns.

  • WITH - accepts additional parameters not defined by ANSI-99 SQL:

    • TEMPLATE=<cache’s template name> - case-sensitive​ name of a cache template registered in Ignite to use as a configuration for the distributed cache that is deployed by the CREATE TABLE command. A template is an instance of the CacheConfiguration class registered with Ignite.addCacheConfiguration in the cluster. Use predefined TEMPLATE=PARTITIONED or TEMPLATE=REPLICATED templates to create the cache with the corresponding replication mode. The rest of the parameters will be those that are defined in the CacheConfiguration object. By default, TEMPLATE=PARTITIONED is used if the template is not specified explicitly.

    • BACKUPS=<number of backups> - sets the number of backup copies of data. If neither this nor the TEMPLATE parameter is set, then the cache will be created with 0 backup copies.

    • ATOMICITY=<ATOMIC | TRANSACTIONAL | TRANSACTIONAL_SNAPSHOT> - sets atomicity mode for the underlying cache. If neither this nor the TEMPLATE parameter is set, then the cache will be created with the ATOMIC mode enabled. If TRANSACTIONAL_SNAPSHOT is specified, the table will support transactions.

    • WRITE_SYNCHRONIZATION_MODE=<PRIMARY_SYNC | FULL_SYNC | FULL_ASYNC> - sets the write synchronization mode for the underlying cache. If neither this nor the TEMPLATE parameter is set, then the cache will be created with FULL_SYNC mode enabled.

    • CACHE_GROUP=<group name> - specifies the group name the underlying cache belongs to.

    • AFFINITY_KEY=<affinity key column name> - specifies an affinity key name which is a column of the PRIMARY KEY constraint.

    • CACHE_NAME=<custom name of the new cache> - the name of the underlying cache created by the command.

    • DATA_REGION=<existing data region name> - name of the data region where table entries should be stored. By default, GridGain stores all the data in a default region.

    • KEY_TYPE=<custom name of the key type> - sets the name of the custom key type that is used from the key-value APIs in Ignite. The name should correspond to a Java, .NET, or C++ class, or it can be a random one if BinaryObjects is used instead of a custom class. The number of fields and their types in the custom key type has to correspond to the PRIMARY KEY. Refer to the Description section below for more details.

    • VALUE_TYPE=<custom name of the value type of the new cache> - sets the name of a custom value type that is used from the key-value and other non-SQL APIs in GridGain. The name should correspond to a Java, .NET, or C++ class, or it can be a random one if BinaryObjects is used instead of a custom class. The value type should include all the columns defined in the CREATE TABLE command except for those listed in the PRIMARY KEY constraint. Refer to the Description section below for more details.

    • WRAP_KEY=<true | false> - this flag controls whether a single column PRIMARY KEY has to be wrapped in the BinaryObjects format or not. By default, this flag is set to false. This flag does not have any effect on the PRIMARY KEY with multiple columns; it always gets wrapped regardless of the value of the parameter.

    • WRAP_VALUE=<true | false> - this flag controls whether a single column value of a primitive type has to be wrapped in the BinaryObjects format or not. By default, this flag is set to true. This flag does not have any effect on the value with multiple columns; it always gets wrapped regardless of the value of the parameter. Set this parameter to false if you have a single column value and do not plan to add additional columns to the table. Note that once the parameter is set to false, you will not be able to use the ALTER TABLE ADD COLUMN command for this specific table.

Description

The CREATE TABLE command creates a new GridGain cache and defines a SQL table on top of it. The cache stores the data in the form of key-value pairs while the table allows processing the data with SQL queries.

The table will reside in the schema specified in the connection parameters. If no schema is specified, the PUBLIC schema will be used. See Schemas for more information about schemas in GridGain.

Note that the CREATE TABLE operation is synchronous and blocks the execution of other DDL commands that are issued while CREATE TABLE is still in progress. The execution of DML commands is not affected and can be performed in parallel.

If you wish to access the data using the key-value APIs, then setting the CACHE_NAME, KEY_TYPE, and VALUE_TYPE parameters may be useful for the following reasons:

  • When the CREATE TABLE command is executed, the name of the cache is generated with the following format- SQL_{SCHEMA_NAME}_{TABLE}. Use the CACHE_NAME parameter to override the default name.

  • Additionally, the command creates two new binary types - for the key and value respectively. Ignite generates the names of the types randomly including a UUID string. This complicates the usage of these 'types' from a non-SQL API. Use KEY_TYPE and VALUE_TYPE to override the names with custom ones corresponding to your business model objects.

Read more about the database architecture on the SQL Introduction page.

Examples

Create Person table:

CREATE TABLE IF NOT EXISTS Person (
  id int,
  city_id int,
  name varchar,
  age int,
  company varchar,
  PRIMARY KEY (id, city_id)
) WITH "template=partitioned,backups=1,affinity_key=city_id, key_type=PersonKey, value_type=MyPerson";

Once the CREATE TABLE command gets executed, the following happens:

  • A new distributed cache will be created and named SQL_PUBLIC_PERSON. This cache will store objects of Person type that corresponds to a specific Java, .NET, C++ class or BinaryObject. Furthermore, the key type (PersonKey) and value type (MyPerson) are defined explicitly assuming the data is to be processed by key-value and other non-SQL APIs.

  • SQL table/schema with all the parameters will be defined.

  • The data will be stored in the form of key-value pairs. The PRIMARY KEY columns will be used as the object’s key; the rest of the columns will belong to the value.

  • Distributed cache related parameters are passed in the WITH clause of the statement. If the WITH clause is omitted, then the cache will be created with default parameters set in the CacheConfiguration object.

The example below shows how to create the same table with PRIMARY KEY specified in the column definition, and overriding some cache related parameters:

CREATE TABLE Person (
  id int PRIMARY KEY,
  city_id int,
  name varchar,
  age int,
  company varchar
) WITH "atomicity=transactional,cachegroup=somegroup";

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

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. Consider the following notes and limitations:

  • 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

DROP TABLE

Drop the specified table and an underlying cache.

DROP TABLE [IF EXISTS] tableName

Parameters

  • tableName - the name of the table.

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

Description

The DROP TABLE command drops a table previously created in GridGain. The underlying distributed cache with all the data in it will be destroyed too.

Both DDL and DML commands targeting the same table are blocked while the DROP TABLE is in progress. Once the table is dropped all the pending commands will fail with appropriate errors.

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

Examples

Drop Person table if the one exists:

DROP TABLE IF EXISTS "Person";

CREATE INDEX

Create an index on the specified table.

CREATE [SPATIAL] INDEX [[IF NOT EXISTS] indexName] ON tableName
    (columnName [ASC|DESC] [,...]) [(index_option [...])]

index_option := {INLINE_SIZE size | PARALLEL parallelism_level}

Parameters

  • indexName - the name of the index to be created.

  • ASC - specifies ascending sort order (default).

  • DESC - specifies descending sort order.

  • SPATIAL - create the spatial index. Presently, only geometry types are supported.

  • IF NOT EXISTS - do not throw an error if an index with the same name already exists. The database checks indexes' names only, and does not consider columns types or count.

  • index_option - additional options for index creation:

    • INLINE_SIZE - specifies index inline size in bytes. Depending on the size, GridGain will place the whole indexed value or a part of it directly into index pages, thus omitting extra calls to data pages and increasing queries' performance. The index inlining is enabled by default and the size is pre-calculated automatically based on a table structure. To disable inlining, set the size to 0 (not recommended). Refer to the Index Inlining section for more details.

    • PARALLEL - specifies the number of threads to be used in parallel for index creation. The more threads are set the faster the index will be created and built. If the value exceeds the number of CPUs, then it will be decreased to the number of cores.​ If the parameter is not specified, then the number of threads will be calculated as 25% of the CPU cores available.

Description

CREATE INDEX constructs a new index on the specified table. Regular indexes are stored in the internal B+tree data structures. The B+tree gets distributed across the cluster along with the actual data. A cluster node stores a part of the index for the data it owns.

If CREATE INDEX is executed in runtime on live data then the database will iterate over the specified columns synchronously indexing them. The rest of the DDL commands targeting the same table are blocked until CREATE INDEX is in progress. DML command execution is not affected and can be performed in parallel.

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

Index Inlining

Index inline size is internally limited to 2048 bytes. Larger values are silently reduced to 2048.

If the INLINE_SIZE parameter is not set, GridGain will calculate the index size based on a table structure as follows: - If a table does not include variable-size columns like CHAR, VARCHAR, or BINARY, then the engine will use the sum of the size of all the columns, adding an extra byte per column. If the variable-size columns are present in the table definition, then the index inline size is derived from the IGNITE_MAX_INDEX_PAYLOAD_SIZE system property, which has a default value of 10 bytes.

Indexes Tradeoffs

There are multiple things you should consider when choosing indexes for your application.

  • Indexes are not free. They consume memory, and each index needs to be updated separately, thus the performance of write operations might drop if too many indexes are created. On top of that, if a lot of indexes are defined, the optimizer might make more mistakes by choosing the wrong index while building the execution plan.

  • Indexes are just sorted data structures (B+tree). If you define an index for the fields (a,b,c) then the records will be sorted first by a, then by b and only then by c.

  • Indexes on single fields are no better than group indexes on multiple fields starting with the same field (index on (a) is no better than (a,b,c)). Thus it is preferable to use group indexes.

  • When INLINE_SIZE option is specified, indexes holds a prefix of field data in the B+tree pages. This improves search performance by doing less row data retrievals, however substantially increases size of the tree (with a moderate increase in tree height) and reduces data insertion and removal performance due to excessive page splits and merges. It’s a good idea to consider page size when choosing inlining size for the tree: each B-tree entry requires 16 + inline-size bytes in the page (plus header and extra links for the page).

Examples

Create a regular index :

CREATE INDEX title_idx ON books (title);

Create a descending index only if it does not exist :

CREATE INDEX IF NOT EXISTS name_idx ON persons (firstName DESC);

Create a compound index :

CREATE INDEX city_idx ON sales (country, city);

Create an index specifying data inline size:

CREATE INDEX fast_city_idx ON sales (country, city) INLINE_SIZE 60;

Create a geospatial​ index:

CREATE SPATIAL INDEX idx_person_address ON Person (address);

Indexes in Other Programming Languages

Java, .NET, and C++ users can define indexes with special configuration classes and annotations as described here.

DROP INDEX

Drop an index on the specified table.

DROP INDEX [IF EXISTS] indexName

Parameters

indexName - the name of the index to drop. IF EXISTS - do not throw an error if an index with the specified name does not exist. The database checks indexes' names only not considering column types or count.

Description

DROP INDEX removes a previously created index.

DDL commands targeting the same table are blocked until DROP INDEX is in progress. DML command execution is not affected and can be performed in parallel.

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

Examples

Drop an index:

DROP INDEX idx_person_name;

CREATE USER

Creates a user with a given name and password.

CREATE USER userName WITH PASSWORD 'password';

Parameters

userName - new user’s name. The name cannot be longer than 60 bytes in UTF8 encoding. password - new user’s password. An empty password is not allowed.

Description

The command creates a user with a given name and password.

A new user can only be created using a superuser account. GridGain creates the superuser account under the name ignite and password ignite on the first cluster start-up. Presently, you can’t rename the superuser account nor grant its privileges to any other account.

To create a case-sensitive username, use the quotation (") SQL identifier.

Examples

Create a new user using test as a name and password:

CREATE USER test WITH PASSWORD 'test';

Create a case-sensitive username:

CREATE USER "TeSt" WITH PASSWORD 'test'

ALTER USER

Changes an existing user’s password:

ALTER USER userName WITH PASSWORD 'newPassword';

Parameters

userName - existing user’s name. newPassword - the new password to set for the user’s account.

Description

The command changes an existing user’s password.

The password can be updated by the superuser (ignite, see CREATE USER for more details) or by the user themself.

Examples

Updating user’s password:

ALTER USER test WITH PASSWORD 'test123';

DROP USER

Removes an existing user:

DROP USER userName;

Parameters

userName - a name of the user to remove.

Description

The command removes an existing user.

The user can be removed only by the superuser (ignite, see CREATE USER for more details).

Examples

Removing a user:

DROP USER test;