Apache Ignite Failed to find SQL table

Question

I am working on a spring application to connect to apache Ignite cache to fetch the records. First I run the DataNode caching code(mentioned below) to fetch all the data from the DataBase. Next when I try to run Client Code to query the cache in an different application. I get the error saying "Failed to find SQL table for type: Person"

DataNode caching code:

CacheConfiguration<String, Person> personCache = new CacheConfiguration<String, Person>();
    personCache.setName("person:cache");
    personCache.setRebalanceMode(CacheRebalanceMode.SYNC);
    personCache.setReadThrough(true);
    personCache.setWriteThrough(false);
    personCache.setWriteBehindEnabled(false);
    personCache.setCacheMode(CacheMode.PARTITIONED);
    personCache.setIndexedTypes(String.class, Person.class);
    personCache.setEvictionPolicy(new LruEvictionPolicy<>(100000));
    personCache.setOnheapCacheEnabled(true);
    personCache.setCacheStoreFactory(FactoryBuilder.factoryOf(PersonCacheStore.class));

    configuration.setCacheConfiguration(personCache);
    TcpDiscoverySpi tcpDiscoverySpi = new TcpDiscoverySpi();
    TcpDiscoveryMulticastIpFinder ipFinder = new TcpDiscoveryMulticastIpFinder();
    ipFinder.setAddresses(Arrays.asList("127.0.0.1:47500"));
    tcpDiscoverySpi.setIpFinder(ipFinder);
    configuration.setDiscoverySpi(tcpDiscoverySpi);

    IgniteCache<String, Person> personCache = ignite.getOrCreateCache("person:cache");
    personCache.loadCache(null);

Person:

public class Person implements Serializable {

/**
 * 
 */
private static final long serialVersionUID = 1L;
@QuerySqlField
private String name;
private Date dob;


public Person() {
    super();
}

public Person(String name, Date dob) {
    super();
    this.name = name;
    this.dob = dob;
}

}

PersonCacheStore:

public class PersonCacheStore implements CacheStore<String, Person> {

public Person load(String name) throws CacheLoaderException {
    // code to load data from DB.
}


public void loadCache(IgniteBiInClosure<String, Person> clo, Object... arg1) throws CacheLoaderException {
    // code to load data from DB.

}

}

Client Code to query the cache:

IgniteConfiguration configuration = new IgniteConfiguration();
    configuration.setClientMode(true);
    TcpDiscoverySpi tcpDiscoverySpi = new TcpDiscoverySpi();
    TcpDiscoveryMulticastIpFinder ipFinder = new TcpDiscoveryMulticastIpFinder();
    ipFinder.setAddresses(Arrays.asList("127.0.0.1:47500"));

    tcpDiscoverySpi.setIpFinder(ipFinder);
    configuration.setDiscoverySpi(tcpDiscoverySpi);

    Ignite ignite = Ignition.start(configuration);

    IgniteCache<String, Person> cache = ignite.getOrCreateCache("person:cache");

    SqlQuery<String, Person> qry2 = new SqlQuery<String, Person>(Person.class,
            "select * from Person where name = ?");
    qry2.setArgs("Ram");
    List<Entry<String, Person>> res = cache.query(qry2).getAll();
    for (Entry<String, Person> entry : res) {

    }

Help me out to resolve this issue.

Answer 1

Apache Ignite Version = 2.16.0

While following Apache Ignite examples, I had the similar issue and after spending a lot of time, it turned out I was using incomplete table name.

personCache.setName("person:cache");

This defines your schema name as "person:cache"

Ignite creates a schema for each cache created via one of the programming interfaces or XML configuration. Ref

personCache.setIndexedTypes(String.class, Person.class);

This defines your table name as "Person"

The type name is used as the table name in SQL queries. In this case, our table name will be Person (schema name usage and definition is explained in the Schemas section). Ref

So your query will become

SELECT * from "person:cache".Person where  name = ?"

For sake of completeness, here is my node code

public class Main {
private static final String PERSON_CACHE = "Persons";
public static void main(String[] args) throws Exception {
    // -----------------------------
    try ( Ignite ignite = Ignition.start("config/example-sql.xml")) {
        CacheConfiguration<Long,Person> personCacheCfg = new CacheConfiguration<>();
        personCacheCfg.setCacheMode(CacheMode.PARTITIONED); // Default.
        personCacheCfg.setIndexedTypes(Long.class, Person.class);
        personCacheCfg.setName(PERSON_CACHE);

        try {
            IgniteCache<Long, Person> cache = ignite.createCache(personCacheCfg);

            // Populate caches.
            initialize(cache);
        } catch ( Exception ex ) {
            System.out.println("Exception in main = [" + ex.getMessage() + "]");
        }
        finally {
            ignite.destroyCache(PERSON_CACHE);
        }

        print("SQL queries example finished.");
    }
}
private static void initialize(IgniteCache<Long, Person> personCache) {
    // Clear caches before running the example.
    personCache.clear();

    // People.
    Person p1 = new Person("John", "Doe", 2000, "John Doe has Master Degree.");
    Person p2 = new Person("Jane", "Doe", 1000, "Jane Doe has Bachelor Degree.");
    Person p3 = new Person("John", "Smith", 1000, "John Smith has Bachelor Degree.");
    Person p4 = new Person("Jane", "Smith", 2000, "Jane Smith has Master Degree."); 
    // These Person objects are not collocated with their organizations.
    personCache.put(p1.id, p1);
    personCache.put(p2.id, p2);
    personCache.put(p3.id, p3);
    personCache.put(p4.id, p4);
}

Here is my Python thin client to read the data using SQL

def read_records(client):
    query = '''
        SELECT * FROM \"Persons\".PERSON;
        '''
    cursor = execute_query(client, query, None)
    if cursor is not None:
        for row in cursor:
            print(*row)


def execute_query(client, query, data):
    cursor = None
    try:
        if data is not None:
            print("Query Arguments = [" + str(data) + "]")
            cursor = client.sql(query_str=query, query_args=data)
        else:
            cursor = client.sql(query_str=query)
    except Exception as ex:
        print("Exception executing query = [" + str(ex) + "]")
    return cursor

NOTE: You can also use sqlline to get SQL view of the cache

.\sqlline.bat --verbose=true -u jdbc:ignite:thin://127.0.0.1/

Answer 2

This is the example from documentation : Registering Indexed Types.

Exactly solves your issue.

Answer 3

It's not clear how you create the cache on DataNode. You create a CacheConfiguration object and then add it to Ignite configuration, but it actually looks like this happens after you have started the node. If that's the case, then the cache is created with default settings and therefore is not aware of SQL configuration.

There are two options to fix:

1. Make sure that you fully create IgniteConfiguration (including CacheConfiguration) before calling Ignition.start() with this configuration. Then use ignite.cache("person:cache") to get the cache; if you do it this way instead of using getOrCreateCache, you will get null instead of incorrectly configured cache in case you mess up something, so it would be easier to locate the problem.
2. Do not provide CacheConfiguration as part of IgniteConfiguration, and create the cache using getOrCreateCache providing the configuration object instead of just a name.