Distributed Memory-Centric SQL Database

Apache Ignite comes with ANSI-99 compliant, horizontally scalable and fault-tolerant distributed SQL database. You can interact with Ignite as you would with any other SQL storage, using standard JDBC or ODBC connectivity. Ignite also provides native SQL APIs for Java, .NET and C++ developers for better performance.

Unlike other distributed SQL databases, Ignite memory-centric storage treats both memory and disk as active storage tiers. The disk tier, a.k.a. native persistence, is disabled by default, in which case Ignite becomes a pure in-memory database (IMDB).

SQL Joins

Ignite can join data in both collocated and non-collocated fashions. When collocated, JOINs are executed on the nodes where the data is stored, avoiding expensive serialization and network trips. Such approach provides the best scalability and performance in distributed databases.

SQL and In-Memory Mode

Apache Ignite can function in a pure in-memory mode in which all data and indexes are stored entirely in RAM. In this mode, you can achieve the maximum performance possible because the data is never written to disk. As with any other in-memory database, you can configure backup copies of the data to prevent possible data loss if cluster nodes fail.

SQL and Native Persistence

In this mode, Ignite native persistence serves as the primary storage that can survive cluster failures and restarts. 100% of data and indexes are persisted to disk and the same or smaller amount is cached in memory. When memory resources are scarce, indexes will take priority over data in which case indexes will be cached first. This policy helps to run high-performant SQL queries even if RAM is limited. If a value or index is missing in memory, Ignite will query on-disk data.

On cluster restart, Ignite reads data and indexes from disk eliminating the need for memory warm-up. This process significantly decreases any potential downtime.

SQL and 3rd Party Databases

Ignite can be used as a caching layer (aka. data grid) above an existing 3rd party database - RDBMS, NoSQL, or HDFS. This mode is used to accelerate the underlying database.

When a 3rd party database is configured, Ignite retrieves data from the memory, and inserts data into both the memory and the database. Therefore, in this mode Ignite requires preloading data into caches for SELECT queries; however, it propagates all updates - INSERT, UPDATE, DELETE - to the database.

Want to learn more? See Ignite SQL Capabilities

Connectivity

The following code examples show how to use the JDBC and ODBC drivers and open a connection:


                            Class.forName("org.apache.ignite.IgniteJdbcThinDriver");

                            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=Person;";

                            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)
                        

Ignite Java, .NET or C++ APIs can be used as alternatives to the JDBC and ODBC drivers.

SQL Tables Creation

Ignite supports Data Definition Language (DDL) statements for creating and removing SQL tables and indexes at runtime.

The following examples show how to create tables:​

                            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 table
                            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 query[] = "CREATE TABLE City ( "
                                "id LONG PRIMARY KEY, name VARCHAR) "
                                "WITH \"template=replicated\"";
                            SQLSMALLINT queryLen = static_cast<SQLSMALLINT>(sizeof(queryLen));

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

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

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

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). Read more about extended parameters.

See Ignite DDL documentation for more details.

Data Modification

To modify data stored in Ignite, use standard DML statements like INSERT, UPDATE, or DELETE.

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


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


                            DELETE FROM City
                            WHERE name = 'Foster City'
                        
                            IgniteCache<Long, City> cache = ignite.cache("SQL_PUBLIC_CITY");

                            // Insert data
                            cache.query(new SqlFieldsQuery("INSERT INTO City(id, name) " +
                                " values (1, 'Forest Hill'), (2, 'Denver')"));

                            // Update data
                            cache.query(new SqlFieldsQuery("UPDATE City set name = ? " +
                                "WHERE id = ?").setArgs("Foster City", 2L));

                            // Delete data
                            cache.query(new SqlFieldsQuery("DELETE FROM City " +
                                "WHERE id = ?").setArgs(2L));
                        
                            // 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();
                            }

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

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

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

                                stmt.executeUpdate("DELETE FROM City WHERE name = 'Foster City'");
                            }

                        
                            SQLHSTMT stmt;

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

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

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

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

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

                            // Update City
                            SQLCHAR query[] = "UPDATE City SET name = 'Foster City' WHERE id = 2"

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

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

                            // Delete from City
                            SQLCHAR query[] = "DELETE FROM City WHERE name = 'Foster City'"

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

                            SQLRETURN ret = SQLExecDirect(stmt, query, queryLen);
                        
                            ICache<long, City> cache = ignite.GetCache<long, City>("SQL_PUBLIC_CITY");

                            // Insert data
                            cache.QueryFields(new SqlFieldsQuery("INSERT INTO City(id, name) " +
                                " values (1, 'Forest Hill'), (2, 'Denver')"));

                            // Update data
                            cache.QueryFields(new SqlFieldsQuery("UPDATE City set name = ? " +
                                "WHERE id = ?", "Foster City", 2));

                            // Delete data
                            cache.QueryFields(new SqlFieldsQuery("DELETE FROM City " +
                                "WHERE id = ?", 2));
                        
                            Cache<int64_t, City> cache = ignite.GetCache<int64_t, City>("SQL_PUBLIC_CITY");

                            // Insert data
                            cache.Query(SqlFieldsQuery("INSERT INTO City(id, name) "
                                " values (1, 'Forest Hill'), (2, 'Denver')"));

                            // Update data
                            query = SqlFieldsQuery("UPDATE City set name = ? WHERE id = ?");
                            query.AddArgument("Foster City");
                            query.AddArgument(2LL);
                            cache.Query(query);

                            // Delete data
                            query = SqlFieldsQuery("DELETE FROM City WHERE id = ?");
                            query.AddArgument(2LL);
                            cache.Query(query);
                        

See Ignite DML documentation for more details.

Querying Data

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.

                            SELECT p.name, c.name
                            FROM Person p, City c
                            WHERE p.city_id = c.id
                        
                            IgniteCache<PersonKey, Person> personCache = ignite.cache("SQL_PUBLIC_PERSON");

                            // SQL join on Person and City.
                            SqlFieldsQuery sql = new SqlFieldsQuery(
                            "SELECT p.name, c.name  "
                                + "FROM Person as p, City as c "
                                + "WHERE p.city_id = c.id");

                            // Execute the query and obtain the query result cursor.
                            try (QueryCursor<List<?>> cursor =  personCache.query(sql)) {
                                for (List<?> row : cursor)
                                    System.out.println("Person name & city=" + row.get(0));
                            }
                        
                            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 results:");

                                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
                            SQLCHAR query[] = "SELECT p.name, c.name "
                              "FROM Person p, City c "
                              "WHERE p.city_id = c.id";

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

                            SQLRETURN ret = SQLExecDirect(stmt, query, queryLen);
                        
							ICache<PersonKey, Person> personCache = ignite.GetCache<PersonKey, Person>("SQL_PUBLIC_PERSON");

							// SQL join on Person and City.
							SqlFieldsQuery sql = new SqlFieldsQuery(
							"SELECT p.name, c.name  "
								+ "FROM Person as p, City as c "
								+ "WHERE p.city_id = c.id");

							// Execute the query and obtain the query result cursor.
							foreach (IList row in personCache.QueryFields(sql))
							{
								Console.WriteLine($"Person '{row[0]}' from city '{row[1]}'");
							}
                        
                            Cache<PersonKey, Person> personCache = ignite.GetCache<int64_t, Person>("SQL_PUBLIC_PERSON");

                            // SQL join on Person and City.
                            SqlFieldsQuery sql("SELECT p.name, c.name "
                                    "FROM Person as p, City as c "
                                    "WHERE p.city_id = c.id");

                            // Execute the query and obtain the query result cursor.
                            FieldsQueryCursor cursor = personCache.Query(sql));

                            while (cursor.HasNext())
                            {
                                QueryFieldsRow row = cursor.GetNext();

                                std::cout << "Person: " << row.GetNext<std::string>() << ", "
                                          << "City: " << row.GetNext<std::string>() << std::endl;
                            }
                        

See SELECT queries documentation for more details.

GitHub Examples

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

SQL Database 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.

SQL Tooling

You can process data stored in the Ignite cluster using a SQL tool of your choice - DBeaver, Pentaho, Tableau, Web Console, Apache Zeppelin and more.