How to save multiple entities in a single cache and append records to the existing entity collection in Ignite?

Justin Mathew 0
Viewed 40

This is more about a modelling question and here is what I want to achieve.

I’ve a Database named “DB100” and have entities “TABLE-1” and “TABLE-2”. Both entities are holding thousands of records and records will be added on a daily basis. The user should also be executing join queries on both tables , for e.g. “select a.Id, b.Name from TABLE-1 a JOIN TABLE-2 b where a.Id = b.Id” . What could be the best modelling approach for this requirement in Ignite?

Is the below approach good and possible? Or please share if there is any better way.

var cache = ignite.GetOrCreateCache<string, int>("DB100");

cache[“TABLE-1”] = EntityCollection-1;
cache[“TABLE-2”] = EntityCollection-2;

var sqlp = new SqlFieldsQuery("select a.Id, b.Name from TABLE-1 a JOIN TABLE-2 b where a.Id = b.Id");
                    using (var cursor = cache.Query(sqlp))
                    {
                        foreach (var row in cursor) // throws exception 
                        {
                            
                        }
                    }

If possible, we would like to append the entity collection on daily basis remotely without fetching entire collection to the client side. For this, we’ve tried with Entry Processor but resulted in error possibly due to a mismatch in server and client. Our client code (thick) in C# and server nodes are in C++. Please share any idea, thanks in advance.

gridgain ignite
1 Answers

Actually, you shouldn't use different values in one cache as entity collections. Instead, it would be better to create 2 separate caches with SQL support, which can be done in the next ways:

For example:

CacheConfiguration<Long, CacheEntry1> ccfg = new CacheConfiguration<Long, CacheEntry1>()
    .setName("Cache1")
    .setQueryEntities(
            Arrays.asList(
                    new QueryEntity(Long.class, CacheEntry1.class)
                        .setTableName("Table1")
                        .addQueryField("id", Long.class.getName(), null)
                        .addQueryField("name", String.class.getName(), null)
            )
    );

IgniteCache<Long, CacheEntry1> cache1 = ignite.createCache(ccfg);

And the same should be done for the second cache. After that, you will be able to query data from the caches using SQL. In this case, to add a new record you'll just need to insert it into the cache, without fetching the entire collection to the client side.

Also, in case you would like to use joins you should be aware of data colocation, you can find more details regarding that here and here.

Igor Belyakov0
Related