Apache Spark - Apache Ignite SQL - Table "INTEGER" not found

Question

I'm executing the below example Apache Spark - Ignite program

It is creating the cache in Ignite Cluster. I can view the data from Visor terminal also.

But when running the SQL query its failing with below error.

>>> Transforming values stored in Ignite Shared RDD...
(3,1.7320508075688772)
(1027,32.046840717924134)
(2051,45.28796749689701)
(3075,55.452682532047085)
(4099,64.02343321003646)
>>> Executing SQL query over Ignite Shared RDD...
Exception in thread "main" javax.cache.CacheException: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to parse query: select _val from Integer where _val < 100 and _val > 9 
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:807)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:765)
    at org.apache.ignite.spark.IgniteRDD.sql(IgniteRDD.scala:147)
    at ScalarSharedRDDExample$.delayedEndpoint$ScalarSharedRDDExample$1(ScalarSharedRDDExample.scala:65)
    at ScalarSharedRDDExample$delayedInit$body.apply(ScalarSharedRDDExample.scala:18)
    at scala.Function0$class.apply$mcV$sp(Function0.scala:34)
    at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12)
    at scala.App$$anonfun$main$1.apply(App.scala:76)
    at scala.App$$anonfun$main$1.apply(App.scala:76)
    at scala.collection.immutable.List.foreach(List.scala:381)
    at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:35)
    at scala.App$class.main(App.scala:76)
    at ScalarSharedRDDExample$.main(ScalarSharedRDDExample.scala:18)
    at ScalarSharedRDDExample.main(ScalarSharedRDDExample.scala)
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to parse query: select _val from Integer where _val < 100 and _val > 9 
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1293)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813)
    at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293)
    at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820)
    at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:795)
    ... 13 more
Caused by: org.h2.jdbc.JdbcSQLException: Table "INTEGER" not found; SQL statement:
select _val from Integer where _val < 100 and _val > 9  [42102-195]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:345)
    at org.h2.message.DbException.get(DbException.java:179)
    at org.h2.message.DbException.get(DbException.java:155)
    at org.h2.command.Parser.readTableOrView(Parser.java:5506)
    at org.h2.command.Parser.readTableFilter(Parser.java:1260)
    at org.h2.command.Parser.parseSelectSimpleFromPart(Parser.java:1940)
    at org.h2.command.Parser.parseSelectSimple(Parser.java:2089)
    at org.h2.command.Parser.parseSelectSub(Parser.java:1934)
    at org.h2.command.Parser.parseSelectUnion(Parser.java:1749)
    at org.h2.command.Parser.parseSelect(Parser.java:1737)
    at org.h2.command.Parser.parsePrepared(Parser.java:448)
    at org.h2.command.Parser.parse(Parser.java:320)
    at org.h2.command.Parser.parse(Parser.java:292)
    at org.h2.command.Parser.prepareCommand(Parser.java:257)
    at org.h2.engine.Session.prepareLocal(Session.java:573)
    at org.h2.engine.Session.prepareCommand(Session.java:514)
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1204)
    at org.h2.jdbc.JdbcPreparedStatement.<init>(JdbcPreparedStatement.java:73)
    at org.h2.jdbc.JdbcConnection.prepareStatement(JdbcConnection.java:288)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.prepareStatement(IgniteH2Indexing.java:398)
    at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1273)
    ... 19 more

How to resolve this error please?

Update 1:

Following configuration didn't resolve the error.

// cache config 
val cacheCfg = new CacheConfiguration[Int, Int]() 
cacheCfg.setName("sharedRDD") 
cacheCfg.setIndexedTypes(classOf[Integer], classOf[Integer])
val transformedValues = igniteContext.fromCache(cacheCfg)

Update 2:

Equivalent Java version is working fine, but not Scala program.

SharedRDDExample.java

I've checked scala example and everything works fine. Are you sure that you didn't change anything? Looks like you changed config file or config file name. make sure that you use "examples/config/spark/example-shared-rdd.xml" config in scala example. — Evgenii Zhuravlev

Evgenii Zhuravlev Evgenii Zhuravlev · Accepted Answer · 2017-08-28T11:40:30

For accessing cache from SQL you need to configure indexed types, for example:

        cacheCfg.setIndexedTypes(Integer.class, Integer.class);

Take a look at this example

Apache Spark - Apache Ignite SQL - Table "INTEGER" not found

Update 1:

Update 2:

1 Answers