Question: Ignite Affinity Colocation queries

[ratadepally]

Hi

We currently have a Ignite cluster enabled with Persistence and have multiple tables which have parent child relationship.
we are performing some joins to get parent - child data and have below questions with respect to affinity key.

1. Can we have multiple affinity keys defined for a table/Cache. if so are there any examples on how to achieve this?
2. Is there any way to set the affinity key when we have any hierarchy such as Parent -> Child -> Child2 relation. Couldn't find any example on how to achieve this.
3. when an Affinity key is defined using DDL statement, Is it possible to update it later using Alter statements or any other approach?
4. How to enable non-colocated joins or distributed joins?
5. Is it required to have affinity key defined on data Insert as we might have different schemas and affinity keys can vary?

Any suggestions will be appreciated.

[ptupitsyn]

1. Can we have multiple affinity keys defined for a table/Cache

Simple answer: no, don't do that.

Details: affinity key is defined per type. Multiple types can be stored in one cache. Multiple SQL tables can be defined in one cache. I would not recommend any of that, keep it 1 cache = 1 table = 1 type.

2. Is there any way to set the affinity key when we have any hierarchy such as Parent -> Child -> Child2

All children (direct and indirect) should use the same affinity key as the parent, so that the entire family is colocated. Example: https://ignite.apache.org/docs/latest/data-modeling/affinity-collocation#configuring-affinity-key

3. when an Affinity key is defined using DDL statement, Is it possible to update it later using Alter statements or any other approach?

AFAIK, no

4. How to enable non-colocated joins or distributed joins?

https://ignite.apache.org/docs/latest/SQL/distributed-joins

5. Is it required to have affinity key defined on data Insert as we might have different schemas and affinity keys can vary?

Yes. Ignite uses the affinity key to calculate the target partition. You can't change this for the given row after insert.