Cannot query existing Ignite cache using SQL tooling

Question

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:

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;
}

alamar · Accepted Answer · 2018-04-03 17:25:21Z

7

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.

edited Apr 3, 2018 at 17:25

answered Apr 3, 2018 at 17:16

alamar

19.5k4 gold badges71 silver badges107 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

manuel mourato Over a year ago

thanks, I will accept your answer. as putting the cache name inside "" worked, and I can query the cache. However for some reason all my records are blank, do you know why this might be? As the ignitevisor states, I should see 63 records

alamar Over a year ago

@manuelmourato this should be because your cache doesn't contain String to Custom_Class, but some other types. Double check the types. Does INSERT work?

manuel mourato Over a year ago

I have created another question with the specifics on how I insert data to the cache, would be grateful if you took a look, thank you: stackoverflow.com/questions/49652724/…

Denis Mekhanikov · Accepted Answer · 2018-04-03 17:19:20Z

1

You should either set CacheConfiguration#sqlSchema property to PUBLIC, or use a quoted name of the cache as a schema name in DBeaver.

Refer to the following page in the documentation for more information: https://apacheignite-sql.readme.io/docs/schema-and-indexes

answered Apr 3, 2018 at 17:19

Denis Mekhanikov

3,59110 silver badges13 bronze badges

7 Comments

alamar Over a year ago

I'm not actually sure you can set schema to PUBLIC. I remember this was a no-no.

Denis Mekhanikov Over a year ago

@alamar, yes, you can. This is what I always do actually :)

manuel mourato Over a year ago

@Denis, thank you for your answer, indeed it was the quotes that were missing. However, I don't see any records when I query, and it clearly says in ignitevisor that there are 63 records in that table, Do you know why this might be?

Denis Mekhanikov Over a year ago

@manuelmourato Can you provide contents of your Custom_Class and code, that you use to populate the cache with data? I think, your issue could be caused by missing QuerySqlField annotations on the classes' fields.

manuel mourato Over a year ago

@Denis I've updated the question with the contents, but I made sure to add QuerySqlField before every field, so I dont belive that that is the problem...

|

Collectives™ on Stack Overflow

Cannot query existing Ignite cache using SQL tooling

2 Answers 2

3 Comments

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related