Apache.Ignite.NET Query Key/Value data via ODBC

Question

Ignite has (amongst others) the use cases "in-memory key/value store" and "SQL via ODBC". I wonder if these use cases can be combined.

Let's use this model:

class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

Add a 'Person' to the key/value store:

using (var ignite = Ignition.Start())
{
    var cache = ignite.GetOrCreateCache<int, Person>("person-cache");
    cache.Put(1, new Person { Name = "Test", Age=7 });
}

Now, can I do something like

SELECT * FROM Person; 

via an ODBC connection? If yes, code samples preferably in C# / Ignite.NET are very much appreciated. Or is it not possible because are we talking about entirely separate data-stores / caches?

Answer 1

Sure, you can do it with Apache Ignite. There are three things to do:

1. update your Person model with the desired SQL fields:

   class Person
   {
       [QuerySqlField(IsIndexed = true)]
       public string Name { get; set; }
   
       [QuerySqlField]
       public int Age { get; set; }
   }
   

2. Add SQL query types (Person type) to CacheConfiguration
   

3. Don't forget to use a schema name (= Cache name) with quotation marks when accessing a table:
   

       using (var ignite = Ignition.Start())
       {
           var cache = ignite.GetOrCreateCache<int, Person>(new CacheConfiguration("person-cache", typeof(Person)));
   
           cache.Put(1, new Person { Name = "Test", Age = 7 });
   
           var dbConnection = new OdbcConnection(@"DRIVER={Apache Ignite};ADDRESS=localhost:10800");
           try
           {
               dbConnection.Open();
   
               using (var dbCommand = dbConnection.CreateCommand())
               {
                   dbCommand.CommandText = @"SELECT * FROM ""person-cache"".Person WHERE Name=?";
                   dbCommand.Parameters.Add("Name", OdbcType.Char).Value = "Test";
                   var dbReader = dbCommand.ExecuteReader();
   
                   var fCount = dbReader.FieldCount;
                   while (dbReader.Read())
                   {
                       for (var i = 0; i < fCount; i++)
                       {
                           var val = dbReader.GetString(i);
                           var name = dbReader.GetName(i);
   
                           Console.WriteLine(name + "=" + val);
                       }
                   }
                   dbReader.Close();
               }
           }
           catch (OdbcException ex)
           {
               Console.WriteLine(ex);
           }
   
           Console.ReadKey();
       }
   

Thank you, Alexey