Apache Ignite cache to SQL and vice versa

Question

I'm working on a system which will fetch data from a service and put pieces of the response in to a cache and/or into a SQL table.

The cache is needed for consumption by other Java services directly. These services require a more direct connection than the SQL abstraction, so we need to connect directly to the cache.

The table is needed for a JDBC SQL connection to external SQL clients e.g. SQL Workbench, DBeaver, Tableau, 3rd party systems.

My question is how Ignite works regarding caches vs tables. I know it stores its caches as maps similar to other IMDGs. What I guess I don't understand is how that gets turned into a table, or what APIs are available to set/get between the two.

So the question is, how can I take an INSERT from the JDBC/SQL side and query it via the Cache? How can I add() into the Cache and SELECT it from the JDBC/SQL side? If I have a table named "foo", does that also create a cache named "foo"?

Or am I supposed to use one or the other and not bleed between the two? I haven't found many good examples of this, so it seems to be either you use caches or you use tables.

It would be extremely advantageous to have a bridge between the two. We're migrating to Ignite from an H2 implementation where we mushed a Hazelcast cache and H2's SQL together and are hoping Ignite, being built atop H2, has done something similar already.

In particular, I was hoping to use DataStreamers but I'm not finding much in the way of how it relates to the SQL/table side of things.

Answer 1

Ignite cache falls under key-value type of nosql database. You can fire SQL like query from java code to ignite caches as it supports it. For example,

SELECT _KEY, _VAL from "foo".val 

Here, foo is your cache name and val is the value part of key-value pair. As this is all NOSQL, relating it to RDBMS SQL is not so much rational, still we can relate all non primary columns in SQL table to the fields of your value object and primary one to the key part.
So, in datastreamer, you can construct collection of key, value objects and stream it. This internally calls nothing but put operation on cache.
To select in SQL fashon, you can fire query like below-

SqlFieldsQuery query = new SqlFieldsQuery(queryString);
FieldsQueryCursor<List<?>> cursor = cache.query(query);   

There are multiple ways to do this, SqlFieldsQuery is one of that.

Answer 2

This was answered couple times already, basically you need to refer to Query Entities, Indexed Types or key_type/value_type parameters of CREATE TABLE to make it work. I.e. every entry in cache of correct type will be a row of table and vice versa.