Getting Started

Overview

Today, SQL is still a very popular language for data definition, data manipulation and querying in database management systems. Although often associated with Relational database systems, it is now used far more widely with many non-Relational database systems also supporting SQL to varying degrees. Furthermore, there is a huge market for a wide-range of SQL-based tools that can provide visualization, reports and business intelligence. These use standards such as ODBC and JDBC to connect to data sources.

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

The following examples will use a schema consisting of two tables. These tables are used to hold information about a city and the people that live there. The assumption is that a city may have many people and people will live in only one city. This is a one-to-many (1:m) relationship.

Connectivity

The getting started is designed to be used with a SQL tool of your choice or from source code. For instance, if you prefer the tools way then refer to SQL Tooling page to see a quick example on how to set up the SQL tool.

If you wish to work from the source code, the following code examples show how to use the JDBC and ODBC drivers and open a connection:

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

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

// Combining connect string
std::string connectStr = "DRIVER={Apache Ignite};SERVER=localhost;PORT=10800;SCHEMA=PUBLIC;";

SQLCHAR outstr[ODBC_BUFFER_SIZE];
SQLSMALLINT outstrlen;

// Connecting to ODBC server
SQLRETURN ret = SQLDriverConnect(dbc, NULL, reinterpret_cast<SQLCHAR*>(&connectStr[0]), static_cast<SQLSMALLINT>(connectStr.size()),
outstr, sizeof(outstr), &outstrlen, SQL_DRIVER_COMPLETE);

The JDBC connection uses the thin driver and connects to localhost (127.0.0.1). Ensure that the file ignite-core.jar is in the classpath of an application or tool. See the JDBC Driver documentation for more details.

The ODBC connection is to localhost, port 10800. See the ODBC Driver documentation for more details.

No matter of the approach you choose, open a preferred command line tool, go to {apache-ignite-version}/bin, and execute the ignite.sh or ignite.bat script in order to start one or more cluster nodes:

./ignite.sh

ignite.bat

Create Tables

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

Let's create a database schema for our cities and people objects. Here are examples of how the two tables can be created:

CREATE TABLE City (
  id LONG PRIMARY KEY, name VARCHAR)
  WITH "template=replicated"

CREATE TABLE Person (
  id LONG, name VARCHAR, city_id LONG, PRIMARY KEY (id, city_id))
  WITH "backups=1, affinityKey=city_id"

// Create database tables
try (Statement stmt = conn.createStatement()) {

    // Create table based on REPLICATED template
    stmt.executeUpdate("CREATE TABLE City (" + 
    " id LONG PRIMARY KEY, name VARCHAR) " +
    " WITH \"template=replicated\"");

    // Create table based on PARTITIONED template with one backup
    stmt.executeUpdate("CREATE TABLE Person (" +
    " id LONG, name VARCHAR, city_id LONG, " +
    " PRIMARY KEY (id, city_id)) " +
    " WITH \"backups=1, affinityKey=city_id\"");
}

SQLHSTMT stmt;

// Allocate a statement handle
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

// Create table based on REPLICATED template
SQLCHAR query1[] = "CREATE TABLE City ("
  "id LONG PRIMARY KEY, name VARCHAR) "
  "WITH \"template=replicated\"";
SQLSMALLINT queryLen1 = static_cast<SQLSMALLINT>(sizeof(query1));

SQLExecDirect(stmt, query, queryLen);

// Create table based on PARTITIONED template with one backup
SQLCHAR query2[] = "CREATE TABLE Person ( "
    "id LONG, name VARCHAR, city_id LONG "
    "PRIMARY KEY (id, city_id)) "
    "WITH \"backups=1, affinityKey=city_id\"";
SQLSMALLINT queryLen2 = static_cast<SQLSMALLINT>(sizeof(query2));

SQLExecDirect(stmt, query, queryLen);

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

A new distributed cache is created automatically using the name of the table as its name. The caches store objects of type city and person that can correspond to specific Java, .NET, C++ class or Binary Object objects.
An 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 an object's key and the remaining fields will belong to the value. This means that you can also work with the data using the key-value APIs.

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 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). See extended parameters section for more details.

In many cases it is beneficial to collocate different cache keys together if they will be accessed together. Quite often, business logic will require access to more than one cache key. By collocating them together we can ensure that all keys with the same affinityKey will be cached on the same processing node, hence avoiding costly network trips to fetch data from remote nodes.

In the example schema, we have City and Person objects and we want to collocate Person objects with City objects for where a person lives. To achieve this, we use the WITH clause and specify affinityKey=city_id as shown above.

Create Indexes

Now, let's define several indexes in order to accelerate query lookup. Here are examples of how the indexes can be created:

CREATE INDEX idx_city_name ON City (name)

CREATE INDEX idx_person_name ON Person (name)

// Create indexes
try (Statement stmt = conn.createStatement()) {

    // Create an index on the City table
    stmt.executeUpdate("CREATE INDEX idx_city_name ON City (name)");

    // Create an index on the Person table
    stmt.executeUpdate("CREATE INDEX idx_person_name ON Person (name)");
}

SQLHSTMT stmt;

// Allocate a statement handle
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

// Create an index on the City table
SQLCHAR query[] = "CREATE INDEX idx_city_name ON City (name)";

SQLSMALLINT queryLen = static_cast<SQLSMALLINT>(sizeof(query));

SQLRETURN ret = SQLExecDirect(stmt, query, queryLen);

// Create an index on the Person table
SQLCHAR query2[] = "CREATE INDEX idx_person_name ON Person (name)";

SQLSMALLINT queryLen2 = static_cast<SQLSMALLINT>(sizeof(query2));

ret = SQLExecDirect(stmt, query2, queryLen2);

Inserting Data

Next, in order to query data, we need to load some values into the two tables. Here are several examples of how data can be inserted into the tables:

INSERT INTO City (id, name) VALUES (1, 'Forest Hill');
INSERT INTO City (id, name) VALUES (2, 'Denver');
INSERT INTO City (id, name) VALUES (3, 'St. Petersburg');

INSERT INTO Person (id, name, city_id) VALUES (1, 'John Doe', 3);
INSERT INTO Person (id, name, city_id) VALUES (2, 'Jane Roe', 2);
INSERT INTO Person (id, name, city_id) VALUES (3, 'Mary Major', 1);
INSERT INTO Person (id, name, city_id) VALUES (4, 'Richard Miles', 2);

// Populate City table
try (PreparedStatement stmt =
conn.prepareStatement("INSERT INTO City (id, name) VALUES (?, ?)")) {

    stmt.setLong(1, 1L);
    stmt.setString(2, "Forest Hill");
    stmt.executeUpdate();

    stmt.setLong(1, 2L);
    stmt.setString(2, "Denver");
    stmt.executeUpdate();

    stmt.setLong(1, 3L);
    stmt.setString(2, "St. Petersburg");
    stmt.executeUpdate();
}

// Populate Person table
try (PreparedStatement stmt =
conn.prepareStatement("INSERT INTO Person (id, name, city_id) VALUES (?, ?, ?)")) {

    stmt.setLong(1, 1L);
    stmt.setString(2, "John Doe");
    stmt.setLong(3, 3L);
    stmt.executeUpdate();

    stmt.setLong(1, 2L);
    stmt.setString(2, "Jane Roe");
    stmt.setLong(3, 2L);
    stmt.executeUpdate();

    stmt.setLong(1, 3L);
    stmt.setString(2, "Mary Major");
    stmt.setLong(3, 1L);
    stmt.executeUpdate();

    stmt.setLong(1, 4L);
    stmt.setString(2, "Richard Miles");
    stmt.setLong(3, 2L);
    stmt.executeUpdate();
}

SQLHSTMT stmt;

// Allocate a statement handle
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

// Populate City table
SQLCHAR query1[] = "INSERT INTO City (id, name) VALUES (?, ?)";

SQLRETURN ret = SQLPrepare(stmt, query1, static_cast<SQLSMALLINT>(sizeof(query1)));

char name[1024];

int32_t key = 1;
strncpy(name, "Forest Hill", sizeof(name));
ret = SQLExecute(stmt);

key = 2;
strncpy(name, "Denver", sizeof(name));
ret = SQLExecute(stmt);

key = 3;
strncpy(name, "Denver", sizeof(name));
ret = SQLExecute(stmt);

// Populate Person table
SQLCHAR query2[] = "INSERT INTO Person (id, name, city_id) VALUES (?, ?, ?)";

ret = SQLPrepare(stmt, query2, static_cast<SQLSMALLINT>(sizeof(query2)));

key = 1;
strncpy(name, "John Doe", sizeof(name));
int32_t city_id = 3;
ret = SQLExecute(stmt);

key = 2;
strncpy(name, "Jane Roe", sizeof(name));
city_id = 2;
ret = SQLExecute(stmt);

key = 3;
strncpy(name, "Mary Major", sizeof(name));
city_id = 1;
ret = SQLExecute(stmt);

key = 4;
strncpy(name, "Richard Miles", sizeof(name));
city_id = 2;
ret = SQLExecute(stmt);

// Connecting to the cluster.
Ignite ignite = Ignition.start();

// Getting a reference to an underlying cache created for City table above.
IgniteCache<Long, City> cityCache = ignite.cache("SQL_PUBLIC_CITY");

// Getting a reference to an underlying cache created for Person table above.
IgniteCache<PersonKey, Person> personCache = ignite.cache("SQL_PUBLIC_PERSON");

// Inserting entries into City.
SqlFieldsQuery query = new SqlFieldsQuery(
    "INSERT INTO City (id, name) VALUES (?, ?)");

cityCache.query(query.setArgs(1, "Forest Hill")).getAll();
cityCache.query(query.setArgs(2, "Denver")).getAll();
cityCache.query(query.setArgs(3, "St. Petersburg")).getAll();

// Inserting entries into Person.
query = new SqlFieldsQuery(
    "INSERT INTO Person (id, name, city_id) VALUES (?, ?, ?)");

personCache.query(query.setArgs(1, "John Doe", 3)).getAll();
personCache.query(query.setArgs(2, "Jane Roe", 2)).getAll();
personCache.query(query.setArgs(3, "Mary Major", 1)).getAll();
personCache.query(query.setArgs(4, "Richard Miles", 2)).getAll();

Querying Data

Once we have loaded some data, we can perform queries so that we can answer some questions. Here are examples of how data can be selected, including joining across two tables:

SELECT *
FROM City

SELECT name
FROM City
WHERE id = 1

SELECT p.name, c.name
FROM Person p, City c
WHERE p.city_id = c.id

// Get data using an SQL join sample.
try (Statement stmt = conn.createStatement()) {
    try (ResultSet rs =
    stmt.executeQuery("SELECT p.name, c.name " +
    " FROM Person p, City c " +
    " WHERE p.city_id = c.id")) {

      System.out.println("Query result:");

      while (rs.next())
         System.out.println(">>>    " + rs.getString(1) +
            ", " + rs.getString(2));
    }
}

SQLHSTMT stmt;

// Allocate a statement handle
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

// Get data using an SQL join sample.
SQLCHAR query[] = "SELECT p.name, c.name "
  "FROM Person p, City c "
  "WHERE p.city_id = c.id";

SQLSMALLINT queryLen = static_cast<SQLSMALLINT>(sizeof(query));

SQLRETURN ret = SQLExecDirect(stmt, query, queryLen);

// Connecting to the cluster.
Ignite ignite = Ignition.start();

// Getting a reference to an underlying cache created for City table above.
IgniteCache<Long, City> cityCache = ignite.cache("SQL_PUBLIC_CITY");

// Querying data from the cluster using a distributed JOIN.
SqlFieldsQuery query = new SqlFieldsQuery("SELECT p.name, c.name " +
    " FROM Person p, City c WHERE p.city_id = c.id");

FieldsQueryCursor<List<?>> cursor = cityCache.query(query);

Iterator<List<?>> iterator = cursor.iterator();

System.out.println("Query result:");

while (iterator.hasNext()) {
    List<?> row = iterator.next();

    System.out.println(">>>    " + row.get(0) + ", " + row.get(1));
}

Modifying Data

Sometimes data may change and we can perform an update operation to modify existing values. Here are examples of how data can be updated:

UPDATE City
SET name = 'Foster City'
WHERE id = 2

// Update
try (Statement stmt = conn.createStatement()) {

    // Update City
    stmt.executeUpdate("UPDATE City SET name = 'Foster City' WHERE id = 2");
}

SQLHSTMT stmt;

// Allocate a statement handle
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

// Update City
SQLCHAR query[] = "UPDATE City SET name = 'Foster City' WHERE id = 2"
  
SQLSMALLINT queryLen = static_cast<SQLSMALLINT>(sizeof(query));

SQLRETURN ret = SQLExecDirect(stmt, query, queryLen);

// Updating a city entry.
SqlFieldsQuery query = new SqlFieldsQuery(
    "UPDATE City SET name = 'Foster City' WHERE id = 2");

cityCache.query(query).getAll();

Removing Data

Finally, we may need to remove objects from our database. Here are examples of how data can be deleted:

DELETE FROM Person
WHERE name = 'John Doe'

// Delete
try (Statement stmt = conn.createStatement()) {

    // Delete from Person
    stmt.executeUpdate("DELETE FROM Person WHERE name = 'John Doe'");
}

SQLHSTMT stmt;

// Allocate a statement handle
SQLAllocHandle(SQL_HANDLE_STMT, dbc, &stmt);

// Delete from Person
SQLCHAR query[] = "DELETE FROM Person WHERE name = 'John Doe'"
  
SQLSMALLINT queryLen = static_cast<SQLSMALLINT>(sizeof(query));

SQLRETURN ret = SQLExecDirect(stmt, query, queryLen);

// Removing a person.
SqlFieldsQuery query = new SqlFieldsQuery(
    "DELETE FROM Person WHERE name = 'John Doe'");

personCache.query(query).getAll();

Examples

Some complete SQL code examples used in this getting started guide are available on GitHub.