You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by tusharnakra <tu...@hotmail.com> on 2016/04/08 21:08:39 UTC
How to load 2 tables in a cache
I have two tables department (dept_id and dept) and employee(emp_id, dept_id,
firstname, lastname) in a database that I want to load into the cache. I
used the schema import tool and it generated the CacheConfig.java,
DepartmentKey.java, Department.java, Employee.java and EmployeeKey.java
files. Now Now how do I load the cache with both these tables?
Here's the snippet:
private static class MySQLDemoStoreFactory<K, V> extends
CacheJdbcPojoStoreFactory<K, V> {
//{@inheritDoc}
@Override public CacheJdbcPojoStore<K, V> create() {
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setURL("jdbc:mysql://localhost/DB");
dataSource.setUser("root");
dataSource.setPassword("pass");
setDataSource(dataSource);
return super.create();
}
}
then in main, to load the cache:
try (Ignite ignite = Ignition.start("examples/config/example-ignite.xml"))
{
// Configure cache store.
CacheConfiguration<EmployeeKey, Employee> cfg =
CacheConfig.cache("EmpCache", new
MySQLDemoStoreFactory<EmployeeKey, Employee>());
try (IgniteCache<EmployeeKey, Employee> cache =
ignite.getOrCreateCache(cfg)) {
// Preload cache from database.
preload(cache);
This will just load it with the Employee table, right? How do I load it with
the Department and the Employee table both?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
Hi @dsetrakyan,
Can you look into my above comment, the error that I'm getting?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4071.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
Could this error be because I haven't done setIndexedType?:
cfg1.setIndexedTypes(OrganizationKey.class, Organization.class);
OR because I have same column name "orgid" in both the tables??
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4133.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by vkulichenko <va...@gmail.com>.
Hi,
Is it possible for you to create a small GitHub project that will reproduce
the issue and share it with us? I'm a bit lost in the amounts of code that
you copy-pasted here :)
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4194.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
[09:35:06,774][ERROR][main][GridMapQueryExecutor] Failed to execute local
query: GridQueryRequest [reqId=1, pageSize=1024, space=PCache,
qrys=[GridCacheSqlQuery [qry=SELECT
"PCache".PERSON._KEY __C0,
"PCache".PERSON._VAL __C1
FROM "PCache".PERSON
WHERE (SALARY > ?1) AND (SALARY <= ?2), params=[0, 1000], paramIdxs=[0, 1],
paramsSize=2, cols={__C0=GridSqlType [type=19, scale=0,
precision=2147483647, displaySize=2147483647, sql=OTHER], __C1=GridSqlType
[type=19, scale=0, precision=2147483647, displaySize=2147483647,
sql=OTHER]}, alias=null]], topVer=AffinityTopologyVersion [topVer=1,
minorTopVer=2], extraSpaces=null, parts=null]
class org.apache.ignite.IgniteCheckedException: Failed to execute SQL query.
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:832)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:855)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:454)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956)
at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$3.iterator(IgniteH2Indexing.java:990)
at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:73)
at org.apache.ignite.organization.Demo.sqlQuery(Demo.java:141)
at org.apache.ignite.organization.Demo.main(Demo.java:90)
Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number
of characters: "0"; SQL statement:
SELECT
"PCache".PERSON._KEY __C0,
"PCache".PERSON._VAL __C1
FROM "PCache".PERSON
WHERE (SALARY > ?1) AND (SALARY <= ?2) [90003-175]
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4175.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by Vladimir Ozerov <vo...@gridgain.com>.
Could you please provide more detailed error description and its stack
trace?
On Wed, Apr 13, 2016 at 7:03 PM, tusharnakra <tu...@hotmail.com>
wrote:
> OK, UPDATE:
>
> This method is working, but how do I add setArgs (I mean, I want to pass
> the
> argument for orgname as "Ignite" i.e. to only print person names in Ignite
> organization. How to do that??? It's giving error if I simply do as given
> on
> the ignite website:
>
> private static void sqlFieldsQueryWithJoin() {
> IgniteCache<AffinityKey<Long>, Person> cache =
> Ignition.ignite().cache("PCache");
> //String oname = "Barclays";
> // Execute query to get names of all employees.
> String sql =
> "select concat(firstName, ' ', lastName), org.orgname " +
> "from Person, \"" + ORG_CACHE + "\".Organization as org " +
> "where Person.orgid = org.orgid";
>
> QueryCursor<List<?>> cursor = cache.query(new
> SqlFieldsQuery(sql));
>
> // In this particular case each row will have one element with full
> name of an employees.
> List<List<?>> res = cursor.getAll();
>
> // Print persons' names and organizations' names.
> print("Names of all employees and organizations they belong to:",
> res);
> }
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4136.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
OK, UPDATE:
This method is working, but how do I add setArgs (I mean, I want to pass the
argument for orgname as "Ignite" i.e. to only print person names in Ignite
organization. How to do that??? It's giving error if I simply do as given on
the ignite website:
private static void sqlFieldsQueryWithJoin() {
IgniteCache<AffinityKey<Long>, Person> cache =
Ignition.ignite().cache("PCache");
//String oname = "Barclays";
// Execute query to get names of all employees.
String sql =
"select concat(firstName, ' ', lastName), org.orgname " +
"from Person, \"" + ORG_CACHE + "\".Organization as org " +
"where Person.orgid = org.orgid";
QueryCursor<List<?>> cursor = cache.query(new
SqlFieldsQuery(sql));
// In this particular case each row will have one element with full
name of an employees.
List<List<?>> res = cursor.getAll();
// Print persons' names and organizations' names.
print("Names of all employees and organizations they belong to:",
res);
}
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4136.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
CacheConfig class:
/*
* Licensed to the Apache Software Foundation (ASF) under one or more
* contributor license agreements. See the NOTICE file distributed with
* this work for additional information regarding copyright ownership.
* The ASF licenses this file to You under the Apache License, Version 2.0
* (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
*
* http://www.apache.org/licenses/LICENSE-2.0
*
* Unless required by applicable law or agreed to in writing, software
* distributed under the License is distributed on an "AS IS" BASIS,
* WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
* See the License for the specific language governing permissions and
* limitations under the License.
*/
package org.apache.ignite.organization;
import java.sql.*;
import java.util.*;
import org.apache.ignite.cache.*;
import org.apache.ignite.cache.store.jdbc.*;
import org.apache.ignite.configuration.*;
/**
* CacheConfig definition.
*
* Code generated by Apache Ignite Schema Import utility: 04/11/2016.
*/
public class CacheConfig {
/**
* Create JDBC type for organization.
*
* @param cacheName Cache name.
* @return Configured JDBC type.
*/
private static JdbcType jdbcTypeOrganization(String cacheName) {
JdbcType jdbcType = new JdbcType();
jdbcType.setCacheName(cacheName);
jdbcType.setDatabaseSchema("ORG");
jdbcType.setDatabaseTable("organization");
jdbcType.setKeyType("org.apache.ignite.organization.OrganizationKey");
jdbcType.setValueType("org.apache.ignite.organization.Organization");
// Key fields for organization.
Collection<JdbcTypeField> keys = new ArrayList<>();
keys.add(new JdbcTypeField(Types.INTEGER, "orgId", int.class,
"orgid"));
jdbcType.setKeyFields(keys.toArray(new JdbcTypeField[keys.size()]));
// Value fields for organization.
Collection<JdbcTypeField> vals = new ArrayList<>();
vals.add(new JdbcTypeField(Types.INTEGER, "orgId", int.class,
"orgid"));
vals.add(new JdbcTypeField(Types.VARCHAR, "orgname", String.class,
"orgname"));
jdbcType.setValueFields(vals.toArray(new
JdbcTypeField[vals.size()]));
return jdbcType;
}
/**
* Create SQL Query descriptor for organization.
*
* @return Configured query entity.
*/
private static QueryEntity queryEntityOrganization() {
QueryEntity qryEntity = new QueryEntity();
qryEntity.setKeyType("org.apache.ignite.organization.OrganizationKey");
qryEntity.setValueType("org.apache.ignite.organization.Organization");
// Query fields for organization.
LinkedHashMap<String, String> fields = new LinkedHashMap<>();
fields.put("orgid", "int");
fields.put("orgname", "String");
qryEntity.setFields(fields);
// Indexes for organization.
Collection<QueryIndex> idxs = new ArrayList<>();
idxs.add(new QueryIndex("orgId", true, "PRIMARY"));
qryEntity.setIndexes(idxs);
return qryEntity;
}
/**
* Create JDBC type for person.
*
* @param cacheName Cache name.
* @return Configured JDBC type.
*/
private static JdbcType jdbcTypePerson(String cacheName) {
JdbcType jdbcType = new JdbcType();
jdbcType.setCacheName(cacheName);
jdbcType.setDatabaseSchema("ORG");
jdbcType.setDatabaseTable("person");
jdbcType.setKeyType("org.apache.ignite.organization.PersonKey");
jdbcType.setValueType("org.apache.ignite.organization.Person");
// Key fields for person.
Collection<JdbcTypeField> keys = new ArrayList<>();
keys.add(new JdbcTypeField(Types.INTEGER, "id", int.class, "id"));
jdbcType.setKeyFields(keys.toArray(new JdbcTypeField[keys.size()]));
// Value fields for person.
Collection<JdbcTypeField> vals = new ArrayList<>();
vals.add(new JdbcTypeField(Types.INTEGER, "id", int.class, "id"));
vals.add(new JdbcTypeField(Types.VARCHAR, "first_name",
String.class, "firstName"));
vals.add(new JdbcTypeField(Types.VARCHAR, "last_name", String.class,
"lastName"));
vals.add(new JdbcTypeField(Types.INTEGER, "orgId", Integer.class,
"orgid"));
jdbcType.setValueFields(vals.toArray(new
JdbcTypeField[vals.size()]));
return jdbcType;
}
/**
* Create SQL Query descriptor for person.
*
* @return Configured query entity.
*/
private static QueryEntity queryEntityPerson() {
QueryEntity qryEntity = new QueryEntity();
qryEntity.setKeyType("org.apache.ignite.organization.PersonKey");
qryEntity.setValueType("org.apache.ignite.organization.Person");
// Query fields for person.
LinkedHashMap<String, String> fields = new LinkedHashMap<>();
fields.put("id", "int");
fields.put("firstName", "String");
fields.put("lastName", "String");
fields.put("orgid", "Integer");
qryEntity.setFields(fields);
// Indexes for person.
Collection<QueryIndex> idxs = new ArrayList<>();
idxs.add(new QueryIndex("id", true, "PRIMARY"));
qryEntity.setIndexes(idxs);
return qryEntity;
}
/**
* Configure cache.
*
* @param cacheName Cache name.
* @param storeFactory Cache store factory.
* @return Cache configuration.
*/
public static <K, V> CacheConfiguration<K, V> cache(String cacheName,
CacheJdbcPojoStoreFactory<K, V> storeFactory) {
if (storeFactory == null)
throw new IllegalArgumentException("Cache store factory cannot
be null.");
CacheConfiguration<K, V> ccfg = new CacheConfiguration<>(cacheName);
ccfg.setCacheStoreFactory(storeFactory);
ccfg.setReadThrough(true);
ccfg.setWriteThrough(true);
// Configure JDBC types.
Collection<JdbcType> jdbcTypes = new ArrayList<>();
jdbcTypes.add(jdbcTypeOrganization(cacheName));
jdbcTypes.add(jdbcTypePerson(cacheName));
storeFactory.setTypes(jdbcTypes.toArray(new
JdbcType[jdbcTypes.size()]));
// Configure query entities.
Collection<QueryEntity> qryEntities = new ArrayList<>();
qryEntities.add(queryEntityOrganization());
qryEntities.add(queryEntityPerson());
ccfg.setQueryEntities(qryEntities);
return ccfg;
}
}
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4128.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by vkulichenko <va...@gmail.com>.
Can you please show your CacheConfig class?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4113.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
OK, I changed the SQL query to: "select Person.firstName "
+ "from Person, \"OrgCache\".Organization where "
+ "Person.orgid = Organization.orgid "
+ "and Organization.orgname = ?");
Getting the following error:
Failed to execute local query: GridQueryRequest [reqId=1, pageSize=1024,
space=PCache, qrys=[GridCacheSqlQuery [qry=SELECT
PERSON.FIRSTNAME __C0
FROM "PCache".PERSON
INNER JOIN "OrgCache".ORGANIZATION
WHERE (ORGANIZATION.ORGNAME = ?1) AND (PERSON.ORGID = ORGANIZATION.ORGID),
params=[Ignite], paramIdxs=[0], paramsSize=1, cols={__C0=GridSqlType
[type=19, scale=0, precision=2147483647, displaySize=2147483647,
sql=OTHER]}, alias=null]], topVer=AffinityTopologyVersion [topVer=1,
minorTopVer=4], extraSpaces=[OrgCache], parts=null]
class org.apache.ignite.IgniteCheckedException: Failed to execute SQL query.
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:832)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:855)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:454)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065)
at
org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572)
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956)
at
org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
at org.apache.ignite.organization.Demo.main(Demo.java:92)
Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-hex
character: "Ignite"; SQL statement:
SELECT
PERSON.FIRSTNAME __C0
FROM "PCache".PERSON
INNER JOIN "OrgCache".ORGANIZATION
WHERE (ORGANIZATION.ORGNAME = ?1) AND (PERSON.ORGID = ORGANIZATION.ORGID)
[90004-175]
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4100.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by vkulichenko <va...@gmail.com>.
Database first_name name should be mapped to firstName in Java object, and
you should use the Java name when executing SQL queries against the data in
memory. Can you try this?
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4084.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
Actually, this is what's happening:
This is the code:
package org.apache.ignite.organization;
import java.util.List;
import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.security.KeyStore.Entry;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.cache.Cache;
import org.apache.ignite.Ignite;
import org.apache.ignite.IgniteCache;
import org.apache.ignite.IgniteException;
import org.apache.ignite.Ignition;
import org.apache.ignite.cache.query.QueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
import org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStore;
import org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory;
import org.apache.ignite.configuration.CacheConfiguration;
import org.apache.ignite.transactions.Transaction;
import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
public class Demo {
static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://localhost/ORG";
static final String USER = "root";
static final String PASS = "mysql";
private static class MySQLDemoStoreFactory<K, V> extends
CacheJdbcPojoStoreFactory<K, V> {
//{@inheritDoc}
@Override public CacheJdbcPojoStore<K, V> create() {
MysqlDataSource dataSource = new MysqlDataSource();
dataSource.setURL("jdbc:mysql://localhost/ORG");
dataSource.setUser("root");
dataSource.setPassword("mysql");
setDataSource(dataSource);
return super.create();
}
}
/**
* Executes demo.
*/
public static void main(String[] args) throws IgniteException {
System.out.println(">>> Start demo...");
// Start Ignite node.
try (Ignite ignite =
Ignition.start("examples/config/example-ignite.xml")) {
CacheConfiguration<PersonKey, Person> cfg =
CacheConfig.cache("PCache", new
MySQLDemoStoreFactory<PersonKey, Person>());
try (IgniteCache<PersonKey, Person> pCache =
ignite.getOrCreateCache(cfg)) {
// Preload cache from database.
preload1(pCache);
}
CacheConfiguration<OrganizationKey, Organization> cfg1 =
CacheConfig.cache("OrgCache", new
MySQLDemoStoreFactory<OrganizationKey, Organization>());
try (IgniteCache<OrganizationKey, Organization> orgCache =
ignite.getOrCreateCache(cfg1)) {
// Preload cache from database.
preload(orgCache);
}
try (IgniteCache<PersonKey, Person> pCache =
ignite.cache("PCache")) {
SqlFieldsQuery sql = new SqlFieldsQuery(
"select Person.first_name "
+ "from Person, \"OrgCache\".Organization where "
+ "Person.orgId = Organization.orgId "
+ "and Organization.orgname = ?");
System.out.println(sql);
// Execute the query and obtain the query result cursor.
try (QueryCursor<List<?>> cursor =
pCache.query(sql.setArgs("Ignite"))) {
for (List<?> row : cursor)
System.out.println("Person name= " + row);
}
}
}
}
private static void preload(IgniteCache<OrganizationKey, Organization>
cache) {
System.out.println();
System.out.println(">>> Loading entries from Organization table.");
// Preload all person keys that are less than or equal to 3.
cache.loadCache(null, OrganizationKey.class.getName(), "select *
from organization");
for (Cache.Entry<OrganizationKey, Organization> org : cache)
System.out.println(">>> Loaded Organization: " + org);
}
private static void preload1(IgniteCache<PersonKey, Person> cache) {
System.out.println();
System.out.println(">>> Loading entries from Person table.");
// Preload all person keys that are less than or equal to 3.
cache.loadCache(null, PersonKey.class.getName(), "select * from
person");
for (Cache.Entry<PersonKey, Person> person : cache)
System.out.println(">>> Loaded Person: " + person);
}
}
And this is the error I'm getting:
Exception in thread "main" javax.cache.CacheException: class
org.apache.ignite.IgniteException: Failed to parse query: select
Person.first_name from Person, "OrgCache".Organization where Person.orgId =
Organization.orgId and Organization.orgname = ?
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:657)
at org.apache.ignite.organization.Demo.main(Demo.java:91)
Caused by: class org.apache.ignite.IgniteException: Failed to parse query:
select Person.first_name from Person, "OrgCache".Organization where
Person.orgId = Organization.orgId and Organization.orgname = ?
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:811)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:648)
... 1 more
Caused by: class org.apache.ignite.IgniteCheckedException: Failed to parse
query: select Person.first_name from Person, "OrgCache".Organization where
Person.orgId = Organization.orgId and Organization.orgname = ?
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1787)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:804)
... 2 more
Caused by: javax.cache.CacheException: Failed to parse query: select
Person.first_name from Person, "OrgCache".Organization where Person.orgId =
Organization.orgId and Organization.orgname = ?
at
org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryTwoStep(IgniteH2Indexing.java:1043)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:806)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor$4.applyx(GridQueryProcessor.java:804)
at
org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36)
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:1769)
... 3 more
Caused by: org.h2.jdbc.JdbcSQLException: Column "PERSON.FIRST_NAME" not
found; SQL statement:
select Person.first_name from Person, "OrgCache".Organization where
Person.orgId = Organization.orgId and Organization.orgname = ? [42122-175]
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4082.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by Dmitriy Setrakyan <ds...@apache.org>.
On Mon, Apr 11, 2016 at 10:29 AM, tusharnakra <tu...@hotmail.com>
wrote:
> Yes, it makes sense! So, is there any way that the SQL JOIN relations can
> work in gridgain for the 2 tables loaded in different cache?
>
Yes, you can find the example for cross-cache join here:
https://apacheignite.readme.io/docs/sql-queries
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4069.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
Yes, it makes sense! So, is there any way that the SQL JOIN relations can
work in gridgain for the 2 tables loaded in different cache?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4069.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by Alexey Kuznetsov <ak...@gridgain.com>.
@tusharnakra
You have 2 tables in MySql - the prefered way in Ignite is to load each
table in separate cache.
When you load data from MySql rows from database automatically transformed
for Java types (POJO) and stored in caches.
Actually I described the same as Vasiliy, but with different words.
Make sense?
On Mon, Apr 11, 2016 at 9:19 PM, tusharnakra <tu...@hotmail.com>
wrote:
> I don't understand what you mean by data types? I'm talking about 2
> database
> tables present in the same MySql database/
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4065.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
Re: How to load 2 tables in a cache
Posted by tusharnakra <tu...@hotmail.com>.
I don't understand what you mean by data types? I'm talking about 2 database
tables present in the same MySql database/
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4065.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How to load 2 tables in a cache
Posted by Vasiliy Sisko <vs...@gridgain.com>.
Hello @tusharnakra
Idiomatic way of Ignite usage is creation of separated cache per data type.
Ignite Schema import is deprecated. You can try to use Web Console, which
already generate separated caches per data types and described at this page
http://ignite.apache.org/addons.html#web-console
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4047.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.