Hello
You have to set your company configuration with affinity key
CacheConfiguration<AffinityKey<String>, Company> companyCfg =
newCacheConfiguration<>("companies");
Get Outlook for iOS<https://aka.ms/o0ukef>
________________________________
From: MJ <[email protected]>
Sent: Monday, June 12, 2023 10:46:23 PM
To: Chandranana Naik via user <[email protected]>
Subject: Ignite Affinity - sql join Question
hi Igniters,
Can you pls advise below ?
It always prints out below WARN message in server side when I am trying to use
the sql join . Anything wrong with my configuration or testing code ? Is it
possible to eliminate that WARN message ?
[ WARN] org.apache.ignite.internal.processors.query.h2.QueryParser - For join
two partitioned tables join condition should contain the equality operation of
affinity keys. Left side: PERSON; right side: COMPANY
Ignite version: 2.13.0
see below test code, which is extracted from
https://ignite.apache.org/docs/latest/data-modeling/affinity-collocation and
added with some additional annotation changes.
public class AffinityCollocationExample2 {
static class Person {
@QuerySqlField(index = true)
private int id;
@QuerySqlField(index = true)
private String companyId;
@QuerySqlField
private String name;
public Person(int id, String companyId, String name) {
this.id = id;
this.companyId = companyId;
this.name = name;
}
public int getId() {
return id;
}
}
static class PersonKey {
private int id;
@AffinityKeyMapped
private String companyId;
public PersonKey(int id, String companyId) {
this.id = id;
this.companyId = companyId;
}
}
static class Company {
@QuerySqlField(index = true)
private String id;
@QuerySqlField
private String name;
public Company(String id, String name) {
this.id = id;
this.name = name;
}
public String getId() {
return id;
}
}
public void configureAffinityKeyWithAnnotation() {
CacheConfiguration<PersonKey, Person> personCfg = new
CacheConfiguration<PersonKey, Person>("persons");
personCfg.setBackups(1);
personCfg.setIndexedTypes(PersonKey.class, Person.class);
personCfg.setSqlSchema("pojo");
CacheConfiguration<String, Company> companyCfg = new
CacheConfiguration<>("companies");
companyCfg.setBackups(1);
companyCfg.setIndexedTypes(String.class, Company.class);
companyCfg.setSqlSchema("pojo");
Ignite ignite = Ignition.start();
IgniteCache<PersonKey, Person> personCache =
ignite.getOrCreateCache(personCfg);
IgniteCache<String, Company> companyCache =
ignite.getOrCreateCache(companyCfg);
Company c1 = new Company("company1", "My company");
Person p1 = new Person(1, c1.getId(), "John");
// Both the p1 and c1 objects will be cached on the same node
personCache.put(new PersonKey(p1.getId(), c1.getId()), p1);
companyCache.put("company1", c1);
// Get the person object
p1 = personCache.get(new PersonKey(1, "company1"));
}
public static void main(String[] args) throws Exception {
AffinityCollocationExample2 main = new AffinityCollocationExample2();
main.configureAffinityKeyWithAnnotation();
System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++");
main.test_sql_join_affinity();
System.exit(0);
}
public void test_sql_join_affinity() throws ClientException, Exception {
String sql = "select a.companyId, b.name from pojo.Person a,
pojo.Company b where a.companyId = b.id";
ClientConfiguration cfg = new
ClientConfiguration().setAddresses("127.0.0.1:10800");
try (IgniteClient client = Ignition.startClient(cfg)) {
SqlFieldsQuery query = new SqlFieldsQuery(sql);
List<List<?>> rets= client.query(query).getAll();
for(List<?> e : (List<List<?>>)rets) {
System.out.println(e);
}
}
}
}
