Search code examples
javasqlcachingignitedbeaver

Cannot query existing Ignite cache using SQL tooling

I am trying to query an Apache Ignite cache (version 2.2) ,which I have created via a Java script:

    TcpDiscoverySpi spi = new TcpDiscoverySpi();
    TcpDiscoveryVmIpFinder ipFinder=new TcpDiscoveryMulticastIpFinder();
    List<String> adresses=new ArrayList<String>();
    adresses.add("127.0.0.1:48500..48520");
    ipFinder.setAddresses(adresses);
    spi.setIpFinder(ipFinder);

    IgniteConfiguration cfg=new IgniteConfiguration().setDiscoverySpi(spi).setClientMode(true);

    CacheConfiguration cache_conf=new CacheConfiguration<String,Custom_Class>().setCacheMode(CacheMode.PARTITIONED).setAtomicityMode(CacheAtomicityMode.ATOMIC).setBackups(1).
            setIndexedTypes(String.class,Custom_Class.class).setName("Spark_Ignite");

    Ignite ignite=Ignition.getOrStart(cfg);

    ignite.getOrCreateCache(cache_conf);

    System.out.println("[INFO] CACHE CREATED");
    ignite.close();

I am using DBeaver to perform a simple SQL query to this cache.

The problem is, when I try to do a query, I get this error:

SELECT * FROM Custom_Class;

 Table "Custom_Class" not found; SQL statement:SELECT * FROM Custom_Class

Same if I run this query:

SELECT * FROM Spark_Ignite;

 Table "Spark_Ignite" not found; SQL statement:SELECT * FROM Spark_Ignite

However, if I follow the instructions mentioned here: https://apacheignite-sql.readme.io/docs/sql-tooling , I get the query results with no issue.

I ran ignitevisor.sh,and indeed all the caches are there, and all of them have records:

enter image description here

What might be wrong here?

Thank you.

UPDATE

Using the quotes as mentioned in the answer I was able to query the table, but it shows no records, while ignitevisor shows 63. This is the script I use for the class:

public class Custom_Class implements Serializable {
@QuerySqlField(index = true)
private String a;

@QuerySqlField(index = true)
private String b;

@QuerySqlField(index = true)
private String c;

@QuerySqlField(index = true)
private String d;

@QuerySqlField(index = true)
private String e;

@QuerySqlField(index = true)
private String f;

@QuerySqlField(index = true)
private String g;

@QuerySqlField(index = true)
private String h;
}
Solution

  • In order to be able to use cache with SQL, it needs to be either:

    • Created using CREATE TABLE DML. In this case, schema is PUBLIC, which is usually the default.
    • Have indexedTypes specified in its cacheConfiguration, along with annotations on those types. In this case, schema is "cacheName", quotes matter, and table name is VALUETYPEINCAPS. If key is primitive type its field name is _key, if value is primitive it will be _val, they are excluded from *.
    • Have queryEntities specified in its cacheConfiguration. In this case, you can specify table name and column names for primitive types, but schema is "cacheName".

    If cache was created without SQL support, you can't add it other than by destroying and recreating cache.

    I can see your cache have indexed types. Now, how about:

    INSERT INTO "Spark_Ignite".CUSTOM_CLASS(_key, id) VALUES ('foo', 1);
SELECT _key, * FROM "Spark_Ignite".CUSTOM_CLASS;

    You can also try invoking !tables in the sqlline tool that comes with Apache Ignite.