Re: Ignite Affinity - sql join Question

[MJ]

No luck.  With the change its throwing exception BinaryObjectImpl cannot 
be cast to java.lang.String  when it tried to put a new entey into 
"companies" cache.


Beside, there is already the @AffinityKeyMapped annotation on companyId field 
of PersioKey class.  AffinityKey needs to be marked on sql join's both 
left and right sides?


Thanks,
MJ



---Original---
From: "Jiang Jacky"<jiang0...@gmail.com&gt;
Date: Tue, Jun 13, 2023 12:34 PM
To: "user@ignite.apache.org"<user@ignite.apache.org&gt;;
Subject: Re: Ignite Affinity - sql join Question


  
  Hello
 You have to set your company configuration with affinity key
 
 
 
 
 CacheConfiguration<AffinityKey<String&gt;, 
Company&gt;&nbsp;companyCfg&nbsp;=&nbsp;newCacheConfiguration<&gt;("companies");
 
  
 
 
 
 Get Outlook for iOS
 
 
 
 From: MJ <6733...@qq.com&gt;
 Sent: Monday, June 12, 2023 10:46:23 PM
 To: Chandranana Naik via user <user@ignite.apache.org&gt;
 Subject: Ignite Affinity - sql join Question &nbsp;
 
  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 .&nbsp; Anything wrong with my configuration or testing  code ? Is 
it possible to eliminate that WARN message ?
 
&nbsp;
 
[ 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
 
&nbsp;
 
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. 
 
&nbsp;
 
public class AffinityCollocationExample2 {
 
&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;static class Person {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @QuerySqlField(index = true)
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; private int id;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @QuerySqlField(index = true)
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; private String companyId;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @QuerySqlField
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; private String name;
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public Person(int id, String 
companyId, String name) {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; this.id  = 
id;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
this.companyId  = companyId;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; this.name  = 
name;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public int getId() {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return id;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
 
&nbsp;&nbsp;&nbsp; }
 
&nbsp;
 
&nbsp;&nbsp;&nbsp; static class PersonKey {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; private int id;
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @AffinityKeyMapped
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; private String companyId;
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public PersonKey(int id, String 
companyId) {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; this.id  = 
id;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
this.companyId  = companyId;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
 
&nbsp;&nbsp;&nbsp; }
 
&nbsp;
 
&nbsp;&nbsp;&nbsp; static class Company {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;@QuerySqlField(index  = true)
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; private String id;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; @QuerySqlField
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; private String name;
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public Company(String id, String 
name) {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; this.id  = 
id;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; this.name  = 
name;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; public String getId() {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; return id;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
 
&nbsp;&nbsp;&nbsp; }
 
&nbsp;
 
&nbsp;&nbsp;&nbsp; public void configureAffinityKeyWithAnnotation() {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CacheConfiguration<PersonKey, 
Person&gt; personCfg = new CacheConfiguration<PersonKey, Person&gt;("persons");
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; personCfg.setBackups(1);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
personCfg.setIndexedTypes(PersonKey.class,  Person.class);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; personCfg.setSqlSchema("pojo");
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; CacheConfiguration<String, 
Company&gt; companyCfg = new CacheConfiguration<&gt;("companies");
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; companyCfg.setBackups(1);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
companyCfg.setIndexedTypes(String.class,  Company.class);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; companyCfg.setSqlSchema("pojo");
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Ignite ignite = Ignition.start();
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IgniteCache<PersonKey, Person&gt; 
personCache = ignite.getOrCreateCache(personCfg);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; IgniteCache<String, Company&gt; 
companyCache = ignite.getOrCreateCache(companyCfg);
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Company c1 = new Company("company1", 
"My company");
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Person p1 = new Person(1, 
c1.getId(), "John");
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // Both the p1 and c1 objects will 
be cached on the same node
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; personCache.put(new  
PersonKey(p1.getId(), c1.getId()), p1);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; companyCache.put("company1", c1);
 
&nbsp;
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; // Get the person object
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; p1 = personCache.get(new  
PersonKey(1, "company1"));
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;}
 
&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;public static void main(String[] args) throws Exception 
{
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AffinityCollocationExample2 main = 
new AffinityCollocationExample2();
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
main.configureAffinityKeyWithAnnotation();
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;System.out.println("+++++++++++++++++++++++++++++++++++++++++++++++++++");
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; main.test_sql_join_affinity();
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; System.exit(0);
 
&nbsp;&nbsp;&nbsp; }
 
&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;public void test_sql_join_affinity() throws 
ClientException, Exception {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; String sql = "select a.companyId, 
b.name from pojo.Person a,&nbsp; pojo.Company b where a.companyId = b.id";
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ClientConfiguration cfg = new 
ClientConfiguration().setAddresses("127.0.0.1:10800");
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;try  (IgniteClient client = 
Ignition.startClient(cfg))  {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
SqlFieldsQuery query = new SqlFieldsQuery(sql);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;List<List<?&gt;&gt;
 rets= client.query(query).getAll();
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
for(List<?&gt; e : (List<List<?&gt;&gt;)rets)  {
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
 System.out.println(e);
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; }
 
&nbsp;&nbsp;&nbsp; }
 
}
 
&nbsp;
 
&nbsp;
 
&nbsp;