Distributed SQL Database

Apache Ignite incorporates distributed SQL database capabilities as a part of its platform. The database is horizontally scalable, fault tolerant and SQL ANSI-99 compliant. It supports all SQL, DDL, and DML commands including SELECT, UPDATE, INSERT, MERGE, and DELETE queries. It also provides support for a subset of DDL commands relevant for distributed databases.

Data sets as well as indexes can be stored both in RAM and on disk thanks to the durable memory architecture. This allows executing distributed SQL operations across different memory layers achieving in-memory performance with durability of disk.

You can interact with Apache Ignite using SQL language via natively developed APIs for Java, .NET and C++, or via the Ignite JDBC or ODBC drivers. This provides a true cross-platform connectivity from languages such as PHP, Ruby and more.

Distributed DDL Examples

Ignite supports Data Definition Language (DDL) statements for creating and removing SQL tables and indexes at runtime. Currently Ignite supports CREATE and DROP statements for tables and indexes, as shown below.

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

                            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();
                        
                            IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

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

                            cache.query(query).getAll();
                        
                            IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

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

                            cache.query(query).getAll();
                        
                            IgniteCache<PersonKey, Person> cache = ignite.cache("Person");

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

                            cache.query(query).getAll();
                        
Distributed DML Examples

Ignite supports modifying data stored in-memory with well-known DML statements like INSERT, UPDATE, or DELETE. This allows you to use Apache Ignite as a distributed SQL database fully relying on its SQL abilities.

                            IgniteCache<Long, Person> cache = ignite.cache("personCache");

                            cache.query(new SqlFieldsQuery("INSERT INTO Person(_key, firstName, " +
                                "lastName) values (1, 'John', 'Smith'), (5, 'Mary', 'Jones')"));
                        
                            IgniteCache<Long, Person> cache = ignite.cache("personCache");

                            cache.query(new SqlFieldsQuery("UPDATE Person set lastName = ? " +
                                "WHERE _key >= ?").setArgs("Jones", 2L));
                        
                            IgniteCache<Long, Person> cache = ignite.cache("personCache");

                            cache.query(new SqlFieldsQuery("DELETE FROM Person " +
                                "WHERE _key >= ?").setArgs(2L));
                        
SQL Query Examples

Ignite supports free-form SQL queries and joins that are fully distributed and fault-tolerant.The SQL syntax is ANSI-99 compliant which means that you can use any kind of SQL functions, aggregations, groupings or joins, defined by the specification, as a part of an SQL query.

                            IgniteCache<Long, Person> cache = ignite.cache("personCache");

                            SqlQuery sql = new SqlQuery(Person.class, "salary > ?");

                            // Find all persons earning more than 1,000.
                            try (QueryCursor<Entry<Long, Person>> cursor = cache.query(sql.setArgs(1000))) {
                                for (Entry<Long, Person> e : cursor)
                                    System.out.println(e.getValue().toString());
                            }
                        
                            IgniteCache<Long, Person> cache = ignite.cache("personCache");

                            // SQL join on Person and Organization.
                            SqlQuery sql = new SqlQuery(Person.class,
                                "from Person as p, \"orgCache\".Organization as org"
                                + "where p.orgId = org.id "
                                + "and lower(org.name) = lower(?)");

                            // Find all persons working for Ignite organization.
                            try (QueryCursor<Entry<Long, Person>> cursor = cache.query(sql.setArgs("Ignite"))) {
                                for (Entry<Long, Person> e : cursor)
                                    System.out.println(e.getValue().toString());
                            }
                        
                            IgniteCache<AffinityKey<Long>, Person> cache = ignite.cache("personCache");

                            // SQL clause query with join over non-collocated data.
                            String joinSql =
                                "from Person, \"orgCache\".Organization as org " +
                                "where Person.orgId = org.id " +
                                "and lower(org.name) = lower(?)";

                            SqlQuery qry = new SqlQuery<AffinityKey<Long>, Person>(Person.class, joinSql)
                                .setArgs("ApacheIgnite");

                            // Enable non-collocated joins for the query.
                            qry.setDistributedJoins(true);

                            // Execute the query to find out employees for specified organization.
                            System.out.println("Following people are 'ApacheIgnite' employees (distributed    join): " +
                                cache.query(qry).getAll());
                        
                            IgniteCache<Long, Person> cache = ignite.cache("personCache");

                            // Execute query to get names of all employees.
                            SqlFieldsQuery sql = new SqlFieldsQuery(
                                "select concat(firstName, ' ', lastName) from Person");

                            // Iterate over the result set.
                            try (QueryCursor<List<?>> cursor = cache.query(sql) {
                                for (List<?> row : cursor)
                                    System.out.println("personName=" + row.get(0));
                            }
                        
GitHub Examples

Also see SQL queries examples and DML example available on GitHub.

SQL Grid Features

Feature Description
SQL Queries

Ignite supports free-form SQL queries without any limitations. The SQL syntax is ANSI-99 compliant which means that you can use any kind of SQL functions, aggregations, groupings or joins.

SQL queries in Ignite are fully distributed and perform in a fault-tolerant manner that guarantees consistent query results regardless of cluster topology changes.

Distributed DDL

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.

Distributed DML

Apache Ignite SQL Grid not only allows selecting data from the Data Grid, using SQL ANSI-99 syntax, but it also makes it possible to modify that data with well-known DML statements like INSERT, MERGE, UPDATE, and DELETE.

Collocated SQL Joins

SQL joins can be performed on collocated data stored across multiple caches. Since all the cache entries with the same affinity key are stored on the same processing node, these queries do not require expensive network trips to fetch the data from remote nodes, and hence, are extermely fast. Joins can also be performed between PARTITIONED and REPLICATED caches.

Non-Collocated SQL Joins

In Ignite, the default implementation of SQL join requires all the participating caches to be collocated. However, for cases where it is extremely difficult to collocate all the data, Ignite offers non-collocated SQL joins. Data can be distributed in any way across the cluster, and Ignite will figure out where to fetch the results from.

SQL Indexes

For SQL queries, Ignite supports in-memory indexing to facilitate fast data lookups. If you are caching your data in off-heap memory, then query indexes will also be cached in off-heap memory. Ignite allows configuring single column indexes as well as group indexes. It also provides support for custom indexing.

JDBC/ODBC Driver

Ignite is shipped with JDBC/ODBC Driver that allows you to retrieve distributed data from cache using standard SQL queries and JDBC/ODBC API.

Geospacial Support

Ignite supports querying and indexing geometry data types such as points, lines, and polygons considering the spatial relationship between these geometries.

.NET & C++ Support

Ignite.NET and Ignite C++ are buit on top of Ignite. This allows you to perform almost all SQL Grid operations including SQL and DML commands as well as distributed joins.

Integrations

Using the Ignite JDBC/ODBC driver, SQL Grid can integrate with a variety of tools, languages and technologies - Apache Zeppelin, Tableau and PHP Data Objects.