Apache Ignite In-Memory SQL Grid (IMDB)

Ignite In-Memory SQL Grid provides in-memory distributed database (IMDB) capabilities to the Ignite platform. The In-Memory SQL Grid is horizontally scalable, fault tolerant and ANSI SQL-99 compliant.

Ignite SQL Grid fully supports all SQL and DML commands. It also supports distributed SQL joins. Moreover, if the data resides in different caches, Ignite allows for cross-cache joins as well.

The In-Memory SQL Grid allows you to interact with the Ignite platform using standard SQL language through Ignite JDBC or ODBC APIs. This provides true cross-platform connectivity even from languages such as PHP, Ruby and more.

SQL Query Examples
                            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));
                            }
                        
DML Examples
                            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));
                        
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 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.