Apache Ignite Documentation

GridGain Developer Hub - Apache Ignitetm

Welcome to the Apache Ignite developer hub run by GridGain. Here you'll find comprehensive guides and documentation to help you start working with Apache Ignite as quickly as possible, as well as support if you get stuck.

 

GridGain also provides Community Edition which is a distribution of Apache Ignite made available by GridGain. It is the fastest and easiest way to get started with Apache Ignite. The Community Edition is generally more stable than the Apache Ignite release available from the Apache Ignite website and may contain extra bug fixes and features that have not made it yet into the release on the Apache website.

 

Let's jump right in!

 

Documentation     Ask a Question     Download

 

Javadoc     Scaladoc     Examples

Distributed DDL

Overview

Apache Ignite supports Data Definition Language (DDL) statements for creating and removing SQL tables and indexes at runtime. Both native Apache Ignite SQL APIs as well as JDBC and ODBC drivers can be used for SQL schema modifications.

Full-fledged DDL Support

In the future Apache Ignite releases, you can expect to see support for additional, widely used DDL statements.

CREATE TABLE

Syntax:

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

tableColumn := columnName columnType [PRIMARY KEY]

Presently, every table created this way ends up in PUBLIC schema. Read more in Schema and Indexes section.

Here is an example on how a table can be created:​

CREATE TABLE IF NOT EXISTS Person (
  age int, id int, city_id int, name varchar, company varchar,
  PRIMARY KEY (name, id))
  WITH "template=partitioned,backups=1,affinitykey=city_id"
IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

SqlFieldsQuery query = new SqlFieldsQuery(
    "CREATE TABLE IF NOT EXISTS Person (" +
    " age int, id int, name varchar, company varchar," +
    " PRIMARY KEY (name, id))" +
    " WITH \"template=replicated,backups=5,affinitykey=id\"");

cache.query(query).getAll();
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

try (Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE TABLE IF NOT EXISTS Person (" +
    " age int, id int, name varchar, company varchar," +
    " PRIMARY KEY (name, id))" +
    " WITH \"template=replicated,backups=5,affinitykey=id\"");
}

PRIMARY KEY column specification can also be inlined with the column definition:

CREATE TABLE Person (
  age int, id int PRIMARY KEY, city_id int, name varchar, company varchar)
  WITH "atomicity=transactional,cachegroup=somegroup"
IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

SqlFieldsQuery query = new SqlFieldsQuery(
    "CREATE TABLE Person (" +
  	" age int, id int PRIMARY KEY, name varchar, company varchar)" +
  	" WITH \"atomicity=transactional,cachegroup=somegroup\"");

cache.query(query).getAll();
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

try (Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE TABLE Person (" +
  	" age int, id int PRIMARY KEY, name varchar, company varchar)" +
  	" WITH \"atomicity=transactional,cachegroup=somegroup\"");
}

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

  • A new distributed cache will be created automatically and named SQL_PUBLIC_PERSON. The cache is supposed to store objects of Person type that can correspond to a specific Java, .NET, C++ class or Binary Object object.
  • SQL table with all the parameters set will be defined.
  • The data will be stored in key-value records. The primary key column will be used as object's key and the rest of the fields will belong to the value. It means that you can work with the data using key-value APIs as well.

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.

In the above example, for the Person table, Ignite creates a distributed cache with 1 backup of data and city_id as the affinity key. These extended parameters are Ignite specific that can be passed using the WITH clause. To set other cache configurations for the table, you should use the template parameter and provide the name of the cache configuration previously registered(via xml or code).

Cache Name

CREATE TABLE command creates a cache with a unique name with the following format SQL_{SCHEMA_NAME}_{TABLE}. Presently the schema name is always PUBLIC.

In the example above Person table was created which resulted in the creation of the cache named SQL_PUBLIC_PERSON. Use this cache name if you need to work with it using key-value, compute or other APIs available in Ignite.

Extended Parameters

The following parameters are accepted by the WITH clause of the statement:

Parameter
Possible Values

TEMPLATE=<cache's template name>

Case sensitive name of the cache template registered in Ignite. If the template with the given name does not exist, then use TEMPLATE=PARTITIONED or TEMPLATE=REPLICATED to create the cache with a corresponding mode. The rest of the parameters will be those that are defined in CacheConfiguration object by default.

The templates are registered with Ignite.addCacheConfiguration method.

BACKUPS=<number of backups>

Any positive number.

ATOMICITY=<atomicity mode>

ATOMIC or TRANSACTIONAL.

CACHEGROUP=<group name>

Any cache group name.

AFFINITYKEY=<affinity key column name>

Name of the column set in CREATE TABLE that is a part of PRIMARY KEY constraint.

Additional CREATE TABLE constraints

In the upcoming Apache Ignite releases CREATE TABLE command will support NOT NULL, AUTO INCREMENT and many other constraints and capabilities.

DROP TABLE

Syntax:

DROP TABLE [IF EXISTS] tableName

Here is an example of how a table can be dropped:​

DROP TABLE IF EXISTS "Person"
IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

SqlFieldsQuery query = new SqlFieldsQuery(
    "DROP TABLE IF EXISTS \"Person\"");

cache.query(query).getAll();
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

try (Statement stmt = conn.createStatement()) {
    stmt.execute("DROP TABLE IF EXISTS \"Person\"");
}

CREATE INDEX

Syntax:
CREATE [SPATIAL] INDEX [IF NOT EXISTS] indexName ON tableName (indexColumn, ...)
indexColumn := columnName [ASC|DESC] where tableName is a name of the type stored in a distirbuted cache.

Here is an example of how a simple sorted index can be created:​

CREATE INDEX idx_person_name ON Person (name)
IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

SqlFieldsQuery query = new SqlFieldsQuery(
    "CREATE INDEX idx_person_name ON Person (name)");

cache.query(query).getAll();
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

try (Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE INDEX idx_person_name ON Person (name)");
}

To create a composite index, use a command like the one below:

CREATE INDEX idx_person_name_birth_date ON Person (name ASC, birth_date DESC)
IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

SqlFieldsQuery query = new SqlFieldsQuery(
    "CREATE INDEX idx_person_name_birth_date ON Person (name ASC, birth_date DESC)");

cache.query(query).getAll();
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

try (Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE INDEX idx_person_name_birth_date ON Person (name ASC, birth_date DESC)");
}

Add SPATIAL keyword to define a geo-spatial index:

CREATE SPATIAL INDEX idx_person_address ON Person (address)
IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

SqlFieldsQuery query = new SqlFieldsQuery(
    "CREATE SPATIAL INDEX idx_person_address ON Person (address)");

cache.query(query).getAll();
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

try (Statement stmt = conn.createStatement()) {
    stmt.execute("CREATE SPATIAL INDEX idx_person_address ON Person (address)");
}

DROP INDEX

Syntax:
DROP INDEX [IF EXISTS] indexName

DROP INDEX idx_person_name
IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

SqlFieldsQuery query = new SqlFieldsQuery(
    "DROP INDEX idx_person_name");

cache.query(query).getAll();
// Register JDBC driver
Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

// Open JDBC connection
Connection conn = DriverManager.getConnection(
    "jdbc:ignite:thin://127.0.0.1/");

try (Statement stmt = conn.createStatement()) {
    stmt.execute("DROP INDEX idx_person_name");
}