Affinity function and partition aware database loading

["steve.hostettler" <st...@gmail.com>]

Hello,

I would like to enable partition aware data loading. I do have a composite
business key in the database (Oracle and SQL Server) that happen to be the
key of the object in the key. The most important part of that key is a
string

I can very easily compute a good affinity from that key, the problem is that
I would like to limit each load to only its subset of the data. Namely, the
data that will end up on the partitions of that node. 

Optimally, I would be able to compute the affinity along with the select
that loads the data from the database. That does not work, because the java
hashcode function is usually not implementable as a select.

In
https://apacheignite.readme.io/docs/data-loading#section-partition-aware-data-loading,
it is recommended to add a field with the partition id but that means that
the data are first loaded in the grid and then loading in the database. This
is not my, data are loaded in the DB through an ETL and then we load it in
the grid.

I do not want to add a technical field in the key (the partition), otherwise
it would mean that the business code would have to deal with it.

At this point, I considered several alternatives but none of them perform
correctly:
- Stored procedure in T-SQL and PL-SQL to compute the partition during the
select but it is horribly slow
- Extracting a number from the composed key in the select : SELECT LENGTH(A)
AS L, ascii(SUBSTR(A, LENGTH(A) - 1))+ascii(SUBSTR(A, LENGTH(A) -
2))+ascii(SUBSTR(A, LENGTH(A) - 3))+ascii(SUBSTR(A, LENGTH(A) - 4)) AS K 
FROM (SELECT 'abcdefgh' AS A FROM DUAL) but this is really ugly
- Using ORA_HASH and its SQLServer equivalent but the algorithm is
proprietary and I cannot use it in the affinity.


Does the community have an opinion on how to best solve that?

Thanks a lot in advance



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

Re: Affinity function and partition aware database loading

[Ilya Kasnacheev <il...@gmail.com>]

Hello!

As far as I could understand, you have affinity data in your rows, but it
is not a column but computable over several columns.

In this case you can create your own AffinityFunction inheriting from
RendezvousAffinityFunction.

In general, creating a new AffinityFunction is a monumental task, but in
this case you can just override partition(Object) method, make this method
return number of partition when passed your row object as input.
Note that it might be in BinaryObject form at this point.

Regards,
-- 
Ilya Kasnacheev


пн, 8 окт. 2018 г. в 15:15, Maxim.Pudov <pu...@gmail.com>:

> Hello,
>
> If you want to benefit from partition aware data loading, you must set up
> an
> affinity key field, without it SQL will make a full scan every time.
> If you don't plan to use SQL on Ignite's side, then you can set up a
> custom
> AffinityFunction
> <
> https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/affinity/AffinityFunction.html>
>
> in your  CacheConfiguration
> <
> https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/CacheConfiguration.html#setAffinity-org.apache.ignite.cache.affinity.AffinityFunction->
>
> , which is not recommened due to potential complexity of this task.
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Affinity function and partition aware database loading

["Maxim.Pudov" <pu...@gmail.com>]

Hello,

If you want to benefit from partition aware data loading, you must set up an
affinity key field, without it SQL will make a full scan every time.
If you don't plan to use SQL on Ignite's side, then you can set up a custom 
AffinityFunction
<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/cache/affinity/AffinityFunction.html>  
in your  CacheConfiguration
<https://ignite.apache.org/releases/latest/javadoc/org/apache/ignite/configuration/CacheConfiguration.html#setAffinity-org.apache.ignite.cache.affinity.AffinityFunction-> 
, which is not recommened due to potential complexity of this task.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/