Non Distributed Join between tables

classic Classic list List threaded Threaded
16 messages Options
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Non Distributed Join between tables

This post was updated on .
I have three tables in Apache Ignite, each table has a affinity key to other
table, when I make a join between tables with direct relations this works
perfectly, but if I make a non distributed join between three tables this
return empty, is normal this behaviour?, Could I make in another way?

tbl_a      tbl_b       tbl_c
-----      ------       -----
aff_b                    aff_b

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_b.id = tbl_a.fk_id -> All OK

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_b.id = tbl_a.fk_id INNER JOIN tbl_c.fk_id = tbl_b.id -> Return empty results.

Thanks in advance.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Hello!

Do you have a reproducer SQL script to observe that?

Regards,
--
Ilya Kasnacheev


пн, 8 июн. 2020 г. в 10:46, manueltg89 <[hidden email]>:
I have three tables in Apache Ignite, each table has a affinity key to other
table, when I make a join between tables with direct relations this works
perfectly, but if I make a non distributed join between three tables this
return empty, is normal this behaviour?, Could I make in another way?

tbl_a      tbl_b       tbl_c
-----      ------       -----
aff_b                    aff_b

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_a.id = tbl_a.fk_id
= tbl_b.id -> All OK

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_a.id = tbl_a.fk_id
= tbl_b.id INNER JOIN tbl_c.fk_id = tbl_b.id -> Return empty results.

Thanks in advance.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Hello Ilya!,

Thanks for your response. I've created a new project and seems that now It
works correctly, I must have a problem. But I have another doubt with
REPLICATED cache. I think that all nodes must have same data in this mode,
Is It true?, with online tool of Apache Ignite I make a query to selected
node (I have two nodes), in node1 works perfectly but in node2 returns empty
results. Should It return the same results?

Thanks in advance.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Hello!

Yes, if both nodes are in baseline then both nodes should have the same data.

In fact, when you query your cluster, you should get the same result regardless of node which does the query.

Regards,
--
Ilya Kasnacheev


пн, 8 июн. 2020 г. в 21:12, manueltg89 <[hidden email]>:
Hello Ilya!,

Thanks for your response. I've created a new project and seems that now It
works correctly, I must have a problem. But I have another doubt with
REPLICATED cache. I think that all nodes must have same data in this mode,
Is It true?, with online tool of Apache Ignite I make a query to selected
node (I have two nodes), in node1 works perfectly but in node2 returns empty
results. Should It return the same results?

Thanks in advance.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

I had to set baseline topology to all nodes. Now this works perfectly,
thanks.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

In reply to this post by ilya.kasnacheev
Sorry Ilya, in my first post had an error. My structure is the following:

tbl_a      tbl_b       tbl_c
-----        ------           -----
             aff_a         aff_b

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_b.id = tbl_a.fk_id
INNER JOIN tbl_c.fk_id = tbl_b.id -> Return empty results.

Could I to have three partitioned caches here?

Is there any way to make this with join collocated and whitout replicated
cache?

Thanks un advance.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Craig Gresbrink Craig Gresbrink
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Is table c, a child of table b? 

You have:

tbl_a      tbl_b       tbl_c
-----        ------           -----
             aff_a         aff_b

You want:

tbl_a      tbl_b       tbl_c
-----        ------           -----
             aff_a         aff_a

You want a "cosmic parent" in this case it seems that tbl_c is grandchild of A and table C's affinity key should have table A's "id" so that all related data ends up on the same node as the id of table a.

Cheers,
Craig

From: manueltg89 <[hidden email]>
Sent: Thursday, June 11, 2020 12:39 PM
To: [hidden email] <[hidden email]>
Subject: Re: Non Distributed Join between tables
 
CAUTION: This email originated outside 24 Hour Fitness. Do not click links or open attachments unless you recognize the sender and know the content is safe.


Sorry Ilya, in my first post had an error. My structure is the following:

tbl_a      tbl_b       tbl_c
-----        ------           -----
             aff_a         aff_b

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_b.id = tbl_a.fk_id
INNER JOIN tbl_c.fk_id = tbl_b.id -> Return empty results.

Could I to have three partitioned caches here?

Is there any way to make this with join collocated and whitout replicated
cache?

Thanks un advance.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Yes, table c is child of table b. But, It is redundant in my RDBMS, Would
have other solution whitout changing my schema?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Craig Gresbrink Craig Gresbrink
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

I am making a fair amount of assumptions:
  1. aff_b means that table c has an affinity key that includes table b's id plus table c's id. Is that true?
  2. I get that table c has a foreign key to table b.
  3. You are doing puts to your caches, then use the sql query engine with distjoins=false hoping to join the 3 tables. Is that all correct?
We are doing this very thing. We need to differentiate the fk in your rdbms and your affinity key in ignite. In ignite, table/cache c will have:
  1. An object attribute and column of bid (b.id or b_id if that makes more sense). 
  2. An affinity key of aid + cid  (table a's pk/id plus table c's pk/id).
The question becomes, on a put to table/cache c, how do we get a.id since it is not in table c's columns/objectGetters?

Answer:  We created a facade of the ignite thick client and we do puts via our facade. Our facade then runs a query like "select aid from b where b.id = ?" and we pass in the value of c.getbid() for ?. We use the return value from that query to generate the affinity key.

It does say that you need to populate or do puts to table/cache b before c (unless you have another way to get the value a.id when putting object c).

Hopefully this helps but might possibly cause more questions.

Cheers,
Craig


From: manueltg89 <[hidden email]>
Sent: Thursday, June 11, 2020 1:05 PM
To: [hidden email] <[hidden email]>
Subject: Re: Non Distributed Join between tables
 
CAUTION: This email originated outside 24 Hour Fitness. Do not click links or open attachments unless you recognize the sender and know the content is safe.


Yes, table c is child of table b. But, It is redundant in my RDBMS, Would
have other solution whitout changing my schema?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

I'm going to explain a bit more. I use Apache Ignite as front cache to my
RDBMS, I've done automatic RDBMS integration. Table A, Table B, Table C is
a example simplified for my real schema. My schema is much more complex.

I answer the questions:

1. True.

2. True

3. I use thin client with sql queries and DISTRIBUTED_JOINS=false.

I want to know if tbl_a <- tbl_b <- tbl_c, if I have collocated table_a with
table_b and table_b with table_c, then,
Could I make a query with three tables?

I understand that it is necessary to put affinity key from table_c to
table_a, so table_b and table_c have same affinity key. It is unique
solution
or put table_a as replicated and only table_c with affinity key to table_b.
It is true?




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Hello!

Do you have a SQL script to reproduce this issue?

Regards,
--
Ilya Kasnacheev


пт, 12 июн. 2020 г. в 09:48, manueltg89 <[hidden email]>:
I'm going to explain a bit more. I use Apache Ignite as front cache to my
RDBMS, I've done automatic RDBMS integration. Table A, Table B, Table C is
a example simplified for my real schema. My schema is much more complex.

I answer the questions:

1. True.

2. True

3. I use thin client with sql queries and DISTRIBUTED_JOINS=false.

I want to know if tbl_a <- tbl_b <- tbl_c, if I have collocated table_a with
table_b and table_b with table_c, then,
Could I make a query with three tables?

I understand that it is necessary to put affinity key from table_c to
table_a, so table_b and table_c have same affinity key. It is unique
solution
or put table_a as replicated and only table_c with affinity key to table_b.
It is true?




--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Hello!

This seems to be a MySQL script. Can you please provide an Ignite SQL script to demonstrate the issue?

Thanks,
--
Ilya Kasnacheev


пт, 19 июн. 2020 г. в 16:35, manueltg89 <[hidden email]>:
I attach required file SQL.  dbaffinity.sql
<http://apache-ignite-users.70518.x6.nabble.com/file/t2878/dbaffinity.sql



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

This post was updated on .
With the following structure, and partitioned cache in the three tables:

tbl_a      tbl_b       tbl_c
-----        ------           -----
             aff_a         aff_b

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_b.id = tbl_a.fk_id
INNER JOIN tbl_c.fk_id = tbl_b.id

Should it work and return results always?

The question is: If tbl_c is collocated with tbl_b and tbl_b is collocated
with tbl_a, should it be collocated tbl_a with tbl_c?

Note: I think that if tbl_b and tbl_c don't have same affinity key then tbl_c isn't collocated with tbl_b and this return empty results. If this is true, there is no problem here.


--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables

Hello!

If both tbl_a and tbl_c use tbl_b.id == tbl_a.fk_id == tbl_c.fk_id as affinity key, then I assume it would.

Regards,
--
Ilya Kasnacheev


чт, 25 июн. 2020 г. в 19:17, manueltg89 <[hidden email]>:
With the following structure, and partitioned cache in the three tables:

tbl_a      tbl_b       tbl_c
-----        ------           -----
             aff_a         aff_b

When I make: select * from tbl_a INNER JOIN tbl_b ON tbl_b.id = tbl_a.fk_id
INNER JOIN tbl_c.fk_id = tbl_b.id

Should it work and return results always?

The question is: If tbl_c is collocated with tbl_b and tbl_b is collocated
with tbl_a, should it be collocated tbl_a with tbl_c?



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
manueltg89 manueltg89
Reply | Threaded
Open this post in threaded view
|

Re: Non Distributed Join between tables