Hello! In general, when you want to use SQL API you need to configure a table first. There are several options for doing it: - you can use create table statement [1] - configuring QueryEntities [2]
When you create cache with Create statement, the DEFAULT SQL schema is used, otherwise, it would be a CacheName [3] [1] – https://www.gridgain.com/docs/8.7.6/developers-guide/SQL/sql-key-value-storage [2] - https://www.gridgain.com/docs/8.7.6/developers-guide/SQL/sql-api [3] - https://apacheignite-sql.readme.io/docs/schemas#section-cache-and-schema-names From: vinod.jv Sent: Thursday, October 31, 2019 8:36 AM To: user@ignite.apache.org Subject: javax.cache.CacheException: Failed to find SQL table for type: XXXXXX I am using ignite 2.7 version and getting the below exception while querying the ignite cache: 19/10/30 05:33:14 ERROR yarn.ApplicationMaster: User class threw exception: javax.cache.CacheException: Failed to find SQL table for type: CanonicalNameOAVO javax.cache.CacheException: Failed to find SQL table for type: CanonicalNameOAVO This is my code to start ignite: def getIgnite(): Ignite = { val clusterName = clusterProperties.getProperty("IGNITE_CLUSTER_NAME") // https://apacheignite.readme.io/v2.7/docs/clients-vs-servers Ignition.setClientMode(true) try { val ignite = Ignition.ignite(clusterName); if ( clusterName == ignite.name() ) { logInfo("@@@@ Found and returning client for cluster: " + clusterName) return ignite } } catch { case e: Exception => e.printStackTrace() } val configFilePath = clusterProperties.getProperty("IGNITE_XML_CONFIG") logInfo("@@@@ configFilePath: " + configFilePath) val configInputStream = FileSystem.get(new Configuration()).open(new Path(configFilePath)); logInfo("@@@@ Starting Ignite Client") return Ignition.start(configInputStream) } I am loading data into the cache and data get loaded successfully and cache size also get printed as below: INFO dataloader.IgniteDataLoader: @@@@ OA_ALTERNATE_NAME_CACHE SIZE => 51016471 Below code is for accessing the cache: def createOfficialNameOACache: IgniteCache[String, CanonicalNameOAVO] = { val orgCacheCfg: CacheConfiguration[String, CanonicalNameOAVO] = new CacheConfiguration[String, CanonicalNameOAVO](OA_OFFICIAL_NAME_CACHE) orgCacheCfg.setIndexedTypes(classOf[String], classOf[CanonicalNameOAVO]) orgCacheCfg.setCacheMode(CacheMode.PARTITIONED) orgCacheCfg.setAtomicityMode(CacheAtomicityMode.ATOMIC) orgCacheCfg.setBackups(3) getIgnite().getOrCreateCache(orgCacheCfg) } But while trying to query the cache I am getting the exception. Below code is for querying the cache: val companyName = "SUFFOLK CONSTRUCTION CO INC" val queryString = "orgName = '" + companyName + "'" val companyNameQuery = new SqlQuery[String, CanonicalNameOAVO](classOf[CanonicalNameOAVO], queryString) val queryCursor = igniteDataLoader.createOfficialNameOACache.query(companyNameQuery) val queryResults = Future { queryCursor.getAll() } try { val companyResults = extractCananicalNameOAVO(queryResults) logInfo(s"Ignite Results returned - $companyResults") } catch { case exe: Exception => logInfo(s"Official Name exact search timed out for companyName") queryCursor.close() Vector.empty } The code throwing exception at query() method for the below line: val queryCursor = igniteDataLoader.createOfficialNameOACache.query(companyNameQuery) 19/10/30 05:33:14 INFO dataloader.IgniteServerDataLoader: getExactOfficialName1 - 'SqlQuery [type=CanonicalNameOAVO, alias=null, sql=orgName = 'SUFFOLK CONSTRUCTION CO INC', args=null, timeout=0, distributedJoins=false, replicatedOnly=false]' 19/10/30 05:33:14 INFO dataloader.IgniteDataLoader: @@@@ Found and returning client for cluster: JalaaDaala 19/10/30 05:33:14 ERROR yarn.ApplicationMaster: User class threw exception: javax.cache.CacheException: Failed to find SQL table for type: CanonicalNameOAVO javax.cache.CacheException: Failed to find SQL table for type: CanonicalNameOAVO at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:697) at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:376) at un.api.dataloader.IgniteServerDataLoader$.loadOAData(IgniteServerDataLoader.scala:70) at un.api.StartStandalone$.startIgniteAndDataloading(StartStandalone.scala:49) at un.api.StartStandalone$delayedInit$body.apply(StartStandalone.scala:13) at scala.Function0$class.apply$mcV$sp(Function0.scala:40) at scala.runtime.AbstractFunction0.apply$mcV$sp(AbstractFunction0.scala:12) at scala.App$$anonfun$main$1.apply(App.scala:71) at scala.App$$anonfun$main$1.apply(App.scala:71) at scala.collection.immutable.List.foreach(List.scala:318) at scala.collection.generic.TraversableForwarder$class.foreach(TraversableForwarder.scala:32) at scala.App$class.main(App.scala:71) at un.api.StartStandalone$.main(StartStandalone.scala:12) at un.api.StartStandalone.main(StartStandalone.scala) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:498) at org.apache.spark.deploy.yarn.ApplicationMaster$$anon$2.run(ApplicationMaster.scala:567) Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to find SQL table for type: CanonicalNameOAVO at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSql(IgniteH2Indexing.java:1843) at org.apache.ignite.internal.processors.query.GridQueryProcessor$7.applyx(GridQueryProcessor.java:2289) at org.apache.ignite.internal.processors.query.GridQueryProcessor$7.applyx(GridQueryProcessor.java:2287) at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2707) at org.apache.ignite.internal.processors.query.GridQueryProcessor.queryDistributedSql(GridQueryProcessor.java:2286) at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySql(GridQueryProcessor.java:2267) at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:682) Could someone please suggest how to resolve the issue. -- Sent from: http://apache-ignite-users.70518.x6.nabble.com/
