Apache Ignite® comes with a ANSI-99 compliant, horizontally scalable, and fault-tolerant SQL engine that allows you to interact with Ignite as with a regular SQL database using JDBC, ODBC drivers, or native SQL APIs available for Java, C#, C++, Python, and other programming languages.
Ignite supports all DML commands, including SELECT, UPDATE, INSERT, and DELETE queries as well as a subset of DDL commands relevant for distributed systems.
Ignite fully supports distributed joins for advanced querying needs. A distributed join is a SQL statement with a join clause that combines two or more tables. If the tables are joined on the partitioning column (affinity or primary key), the join is called a co-located join. Otherwise, if the tables were not co-located initially, then Ignite does the join in a non-colocated fashion. Co-located joins avoid data shuffling between nodes and minimize network usage, thus, performing much faster than a non-colocated counterpart.
SQL and In-Memory Mode
Apache Ignite can function in a pure in-memory mode when all the data and indexes are located solely in memory. In this mode, Ignite SQL shows the highest performance since all the data is served from memory with no usage of the disk tier at all.
SQL and Native Persistence
In this mode, Ignite persists 100% of data and indexes in the native persistence while caching as much as possible in memory. Ignite SQL engine does not require to cache an entire data set in memory to operate correctly. If the engine finds that a record is not cached, then it will read the record from disk. Your application only executes SQL queries, and Ignite gets the records from both memory and disk automatically.
On cluster restarts, Ignite reads data and indexes from disk, eliminating the need for memory warm-up, which significantly decreases the time of any potential downtimes.
SQL and 3rd Party Databases
Ignite can be used as a caching layer for external databases such as RDBMS, NoSQL, or Hadoop. In this mode, the Ignite SQL engine requires caching all the data needed for SQL queries in memory since the engine currently does not support federated queries.
If federated queries between Ignite and an external database are required, then you can consider Ignite integration for Spark, where the DataFrames API can join the data stored in Ignite and other systems.