You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by manueltg89 <ma...@hotmail.com> on 2020/06/08 07:46:38 UTC
Non Distributed Join between tables
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/
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
I had to set baseline topology to all nodes. Now this works perfectly,
thanks.
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
Thanks!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Non Distributed Join between tables
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <manuel.trinidad.garcia@hotmail.com
>:
> 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/
>
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
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/
Re: Non Distributed Join between tables
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <manuel.trinidad.garcia@hotmail.com
>:
> 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/
>
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
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/
Re: Non Distributed Join between tables
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Do you have a SQL script to reproduce this issue?
Regards,
--
Ilya Kasnacheev
пт, 12 июн. 2020 г. в 09:48, manueltg89 <manuel.trinidad.garcia@hotmail.com
>:
> 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/
>
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
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/
Re: Non Distributed Join between tables
Posted by Craig Gresbrink <CG...@24HourFit.com>.
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 <ma...@hotmail.com>
Sent: Thursday, June 11, 2020 1:05 PM
To: user@ignite.apache.org <us...@ignite.apache.org>
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/
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
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/
Re: Non Distributed Join between tables
Posted by Craig Gresbrink <CG...@24HourFit.com>.
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 <ma...@hotmail.com>
Sent: Thursday, June 11, 2020 12:39 PM
To: user@ignite.apache.org <us...@ignite.apache.org>
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/
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
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/
Re: Non Distributed Join between tables
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <ma...@hotmail.com>:
> 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/
>
Re: Non Distributed Join between tables
Posted by manueltg89 <ma...@hotmail.com>.
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/
Re: Non Distributed Join between tables
Posted by Ilya Kasnacheev <il...@gmail.com>.
Hello!
Do you have a reproducer SQL script to observe that?
Regards,
--
Ilya Kasnacheev
пн, 8 июн. 2020 г. в 10:46, manueltg89 <ma...@hotmail.com>:
> 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/
>