JavaC#/.NETC++SQL*IntegrationsIgnite for SparkTools
Guides
ExamplesAsk a Question

CREATE TABLE

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

Create a new table and an underlying Ignite 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 the 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. Refer to Description section below for more details.
  • DATA_REGION=<existing data region name> - name of the data region where table entries should be stored. By default, Ignite 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 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 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 creates a new Ignite cache and defines an 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 Ignite.

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 How Ignite SQL Works 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";

See Also

ALTER TABLE , DROP TABLE , CREATE INDEX , DROP INDEX

Updated over 4 years ago