Apache Ignite - querying a cache through an sql driver

3
votes

I created a cache in Ignite and now I'm attempting to query it via sqlline provided in the ignite bundle. In the documentation it only talks about creating tables, indexes, querying those tables, etc.. but nothing about querying caches created via ignite.getOrCreateCache() in this manner. Given that ignite is so feature rich I assume this is possible.

What am I missing?

Notes:

  • Ignite Version = 2.3.0
  • SqlFieldsQuery via the ignite Sql mechanism is working fine within my code.
  • This looks very similar to this issue but my distribution only has one h2 jar, h2-1.4.196.jar, although I am using spring-boot

java code to create the cache

cacheConfig.setName("eventCache");
cacheConfig.setTypes(TenantKey.class, EventCachePojo.class);
cacheConfig.setIndexedTypes(TenantKey.class, EventCachePojo.class);
Cache<String, EventCachePojo> cache = ignite.getOrCreateCache(cacheConfig);

Then running sqlline

# i tried jdbcUrl jdbc:ignite:thin://127.0.0.1/ and jdbc:ignite:thin://127.0.0.1/eventCache
$ ./sqlline.sh --color=true --verbose=true -u jdbc:ignite:thin://127.0.0.1/
issuing: !connect jdbc:ignite:thin://127.0.0.1/ '' '' org.apache.ignite.IgniteJdbcThinDriver
Connecting to jdbc:ignite:thin://127.0.0.1/
Connected to: Apache Ignite (version 2.3.0#20171028-sha1:8add7fd5)
Driver: Apache Ignite Thin JDBC Driver (version 2.3.0#20171028-sha1:8add7fd5)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline version 1.3.0
0: jdbc:ignite:thin://127.0.0.1/> !tables
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+-----------------------------+
|           TABLE_CAT            |          TABLE_SCHEM           |           TABLE_NAME           |           TABLE_TYPE           |            REMARKS          |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+-----------------------------+
|                                | eventCache                     | EVENTCACHEPOJO                 | TABLE                          |                             |
|                                | eventCache                     | EVENTCACHEPOJO                 | TABLE                          |                             |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+-----------------------------+
0: jdbc:ignite:thin://127.0.0.1/> select count(*) from eventCache.EVENTCACHEPOJO;
Error: Failed to parse query: select count(*) from eventCache.EVENTCACHEPOJO (state=42000,code=0)
java.sql.SQLException: Failed to parse query: select count(*) from eventCache.EVENTCACHEPOJO
    at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:671)
    at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
    at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
    at sqlline.Commands.execute(Commands.java:823)
    at sqlline.Commands.sql(Commands.java:733)
    at sqlline.SqlLine.dispatch(SqlLine.java:795)
    at sqlline.SqlLine.begin(SqlLine.java:668)
    at sqlline.SqlLine.start(SqlLine.java:373)
    at sqlline.SqlLine.main(SqlLine.java:265)
0: jdbc:ignite:thin://127.0.0.1/> select count(*) from eventCache.EVENTCACHEPOJO;
Error: Failed to parse query: select count(*) from eventCache.EVENTCACHEPOJO (state=42000,code=0)
java.sql.SQLException: Failed to parse query: select count(*) from eventCache.EVENTCACHEPOJO
    at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:671)
    at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
    at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
    at sqlline.Commands.execute(Commands.java:823)
    at sqlline.Commands.sql(Commands.java:733)
    at sqlline.SqlLine.dispatch(SqlLine.java:795)
    at sqlline.SqlLine.begin(SqlLine.java:668)
    at sqlline.SqlLine.start(SqlLine.java:373)
    at sqlline.SqlLine.main(SqlLine.java:265)

0: jdbc:ignite:thin://127.0.0.1/> select count(*) from EVENTCACHEPOJO;
Error: Failed to parse query: select count(*) from EVENTCACHEPOJO (state=42000,code=0)
java.sql.SQLException: Failed to parse query: select count(*) from EVENTCACHEPOJO
    at org.apache.ignite.internal.jdbc.thin.JdbcThinConnection.sendRequest(JdbcThinConnection.java:671)
    at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute0(JdbcThinStatement.java:130)
    at org.apache.ignite.internal.jdbc.thin.JdbcThinStatement.execute(JdbcThinStatement.java:299)
    at sqlline.Commands.execute(Commands.java:823)
    at sqlline.Commands.sql(Commands.java:733)
    at sqlline.SqlLine.dispatch(SqlLine.java:795)
    at sqlline.SqlLine.begin(SqlLine.java:668)
    at sqlline.SqlLine.start(SqlLine.java:373)
    at sqlline.SqlLine.main(SqlLine.java:265)
2
ignite

2 Answers

4
votes

i finally figured it out. This Post led me to the answer. The schemaName and table need to be quoted per h2 db.

Here is the syntax:

$ ./sqlline.sh --color=true --verbose=true -u jdbc:ignite:thin://127.0.0.1/
issuing: !connect jdbc:ignite:thin://127.0.0.1/ '' '' org.apache.ignite.IgniteJdbcThinDriver
Connecting to jdbc:ignite:thin://127.0.0.1/
Connected to: Apache Ignite (version 2.3.0#20171028-sha1:8add7fd5)
Driver: Apache Ignite Thin JDBC Driver (version 2.3.0#20171028-sha1:8add7fd5)
Autocommit status: true
Transaction isolation: TRANSACTION_REPEATABLE_READ
sqlline version 1.3.0
0: jdbc:ignite:thin://127.0.0.1/> !tables
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+---------+
|           TABLE_CAT            |          TABLE_SCHEM           |           TABLE_NAME           |           TABLE_TYPE           |            REMARKS             |         |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+---------+
|                                | eventCache                     | EVENTCACHEPOJO                 | TABLE                          |                                |         |
|                                | eventCache                     | EVENTCACHEPOJO                 | TABLE                          |                                |         |
+--------------------------------+--------------------------------+--------------------------------+--------------------------------+--------------------------------+---------+
0: jdbc:ignite:thin://127.0.0.1/> select count(*) from "eventCache"."EVENTCACHEPOJO";
+--------------------------------+
|            COUNT(*)            |
+--------------------------------+
| 2619705                        |
+--------------------------------+
3
votes

Here are a few options to connect to a specific schema in Ignite:

  1. jdbc:ignite:thin://172.16.0.5/mySchema - will connect to "MYSCHEMA" (case insensitive)
  2. jdbc:ignite:thin://172.16.0.5/"mySchema" - will connect to "mySchema" (case sensitive)