I'm new to Ignite and following various blogs to understand the configuration required.
As a starter, I have used "movie lens" data (Movies & Ratings) and used Ignite Web Console to generate the required configuration.
Please find the required details about my code below:
1) MySQL Table Structure
CREATE TABLE movies( movie_id INTEGER, movie_name VARCHAR(250), genre VARCHAR(250), CONSTRAINT pk_movie PRIMARY KEY (movie_id) );
CREATE TABLE ratings( user_id INTEGER, movie_id INTEGER, rating FLOAT, timestamp TIMESTAMP, CONSTRAINT pk_rating PRIMARY KEY (user_id, movie_id) );
2) Ignite Config File in Spring Boot Project (Auto Generated by Ignite Web Console)
<?xml version="1.0" encoding="UTF-8"?>
<!-- This file was generated by Ignite Web Console (05/15/2019, 18:37) -->
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd">
<!-- Load external properties file. -->
<bean id="placeholderConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:secret.properties"/>
</bean>
<!-- Data source beans will be initialized from external properties file. -->
<bean id="dsGeneric_Movielens" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="${dsGeneric_Movielens.jdbc.url}"/>
<property name="user" value="${dsGeneric_Movielens.jdbc.username}"/>
<property name="password" value="${dsGeneric_Movielens.jdbc.password}"/>
</bean>
<bean class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="igniteInstanceName" value="ImportedCluster"/>
<property name="discoverySpi">
<bean class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
<property name="ipFinder">
<bean class="org.apache.ignite.spi.discovery.tcp.ipfinder.multicast.TcpDiscoveryMulticastIpFinder">
<property name="addresses">
<list>
<value>127.0.0.1:47500..47510</value>
</list>
</property>
</bean>
</property>
</bean>
</property>
<property name="cacheConfiguration">
<list>
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="MoviesCache"/>
<property name="cacheMode" value="REPLICATED"/>
<property name="atomicityMode" value="ATOMIC"/>
<property name="cacheStoreFactory">
<bean class="org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory">
<property name="dataSourceBean" value="dsGeneric_Movielens"/>
<property name="dialect">
<bean class="org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect">
</bean>
</property>
<property name="types">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcType">
<property name="cacheName" value="MoviesCache"/>
<property name="keyType" value="java.lang.Integer"/>
<property name="valueType" value="com.learnwithmanoj.ignite.model.Movies"/>
<property name="databaseSchema" value="movielens"/>
<property name="databaseTable" value="movies"/>
<property name="keyFields">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.INTEGER"/>
</constructor-arg>
<constructor-arg value="movie_id"/>
<constructor-arg value="int"/>
<constructor-arg value="movieId"/>
</bean>
</list>
</property>
<property name="valueFields">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.VARCHAR"/>
</constructor-arg>
<constructor-arg value="movie_name"/>
<constructor-arg value="java.lang.String"/>
<constructor-arg value="movieName"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.VARCHAR"/>
</constructor-arg>
<constructor-arg value="genre"/>
<constructor-arg value="java.lang.String"/>
<constructor-arg value="genre"/>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</property>
<property name="readThrough" value="true"/>
<property name="writeThrough" value="true"/>
<property name="queryEntities">
<list>
<bean class="org.apache.ignite.cache.QueryEntity">
<property name="keyType" value="java.lang.Integer"/>
<property name="valueType" value="com.learnwithmanoj.ignite.model.Movies"/>
<property name="keyFieldName" value="movieId"/>
<property name="keyFields">
<list>
<value>movieId</value>
</list>
</property>
<property name="fields">
<map>
<entry key="movieName" value="java.lang.String"/>
<entry key="genre" value="java.lang.String"/>
<entry key="movieId" value="java.lang.Integer"/>
</map>
</property>
<property name="aliases">
<map>
<entry key="movieId" value="movie_id"/>
<entry key="movieName" value="movie_name"/>
</map>
</property>
</bean>
</list>
</property>
</bean>
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="RatingsCache"/>
<property name="cacheMode" value="REPLICATED"/>
<property name="atomicityMode" value="ATOMIC"/>
<property name="cacheStoreFactory">
<bean class="org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory">
<property name="dataSourceBean" value="dsGeneric_Movielens"/>
<property name="dialect">
<bean class="org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect">
</bean>
</property>
<property name="types">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcType">
<property name="cacheName" value="RatingsCache"/>
<property name="keyType" value="com.learnwithmanoj.ignite.model.RatingsKey"/>
<property name="valueType" value="com.learnwithmanoj.ignite.model.Ratings"/>
<property name="databaseSchema" value="movielens"/>
<property name="databaseTable" value="ratings"/>
<property name="keyFields">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.INTEGER"/>
</constructor-arg>
<constructor-arg value="user_id"/>
<constructor-arg value="int"/>
<constructor-arg value="userId"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.INTEGER"/>
</constructor-arg>
<constructor-arg value="movie_id"/>
<constructor-arg value="int"/>
<constructor-arg value="movieId"/>
</bean>
</list>
</property>
<property name="valueFields">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.REAL"/>
</constructor-arg>
<constructor-arg value="rating"/>
<constructor-arg value="java.lang.Double"/>
<constructor-arg value="rating"/>
</bean>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">
<constructor-arg>
<util:constant static-field="java.sql.Types.TIMESTAMP"/>
</constructor-arg>
<constructor-arg value="timestamp"/>
<constructor-arg value="java.sql.Timestamp"/>
<constructor-arg value="timestamp"/>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</property>
<property name="readThrough" value="true"/>
<property name="writeThrough" value="true"/>
<property name="queryEntities">
<list>
<bean class="org.apache.ignite.cache.QueryEntity">
<property name="keyType" value="com.learnwithmanoj.ignite.model.RatingsKey"/>
<property name="valueType" value="com.learnwithmanoj.ignite.model.Ratings"/>
<property name="keyFields">
<list>
<value>userId</value>
<value>movieId</value>
</list>
</property>
<property name="fields">
<map>
<entry key="userId" value="java.lang.Integer"/>
<entry key="movieId" value="java.lang.Integer"/>
<entry key="rating" value="java.lang.Double"/>
<entry key="timestamp" value="java.sql.Timestamp"/>
</map>
</property>
<property name="aliases">
<map>
<entry key="userId" value="user_id"/>
<entry key="movieId" value="movie_id"/>
</map>
</property>
<property name="indexes">
<list>
<bean class="org.apache.ignite.cache.QueryIndex">
<property name="name" value="fk_movie_id"/>
<property name="indexType" value="SORTED"/>
<property name="fields">
<map>
<entry key="movieId" value="false"/>
</map>
</property>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</beans>
3) Ignite Config File in Ignite Installation Folder
<?xml version="1.0" encoding="UTF-8"?>
<!-- This file was generated by Ignite Web Console (05/15/2019, 08:52) -->
<beans xmlns="http://www.springframework.org/schema/beans"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:util="http://www.springframework.org/schema/util"
xsi:schemaLocation="http://www.springframework.org/schema/beans
http://www.springframework.org/schema/beans/spring-beans.xsd
http://www.springframework.org/schema/util
http://www.springframework.org/schema/util/spring-util.xsd">
<!-- Load external properties file. -->
<bean id="placeholderConfig" class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
<property name="location" value="classpath:secret.properties"/>
</bean>
<!-- Data source beans will be initialized from external properties file. -->
<bean id="datasource" class="com.mchange.v2.c3p0.ComboPooledDataSource">
<property name="jdbcUrl" value="${datasource.jdbc.url}"/>
<property name="user" value="${datasource.jdbc.username}"/>
<property name="password" value="${datasource.jdbc.password}"/>
</bean>
<bean class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="igniteInstanceName" value="MovieRatingsCluster"/>
<property name="discoverySpi">
<bean class="org.apache.ignite.spi.discovery.tcp.TcpDiscoverySpi">
<property name="ipFinder">
<bean class="org.apache.ignite.spi.discovery.tcp.ipfinder.multicast.TcpDiscoveryMulticastIpFinder">
<property name="addresses">
<list>
<value>127.0.0.1:47500..47510</value>
</list>
</property>
</bean>
</property>
</bean>
</property>
</bean>
</beans>
Problem Statement:
When I start my Spring Boot Application as a stand-alone Ignite node, then all the data from the MySQL database is getting synced into Ignite node as expected. Also, if I try to join between the Ratings and Movies table, it's joining properly and giving the correct result.
Join Query Used:
select r.movie_id, m.movie_name, count(r.rating) as count from "RatingsCache".ratings r inner join "MoviesCache".movies m where r.movie_id = m.movie_id group by r.movie_id order by count desc limit 5;
Now coming to the problem. If I do the same testing using more than one node, then the Join starts to give incorrect results due to the data not being colocated.
Can anyone guide me with the right set of configuration which needs to be added to fix the Join problem in Partition Mode?
I have even tried to add the below config.
<property name="cacheKeyConfiguration">
<list>
<bean class="org.apache.ignite.cache.CacheKeyConfiguration">
<property name="typeName" value="com.learnwithmanoj.ignite.model.RatingsKey" />
<property name="affinityKeyFieldName" value="movie_id" />
</bean>
</list>
</property>
But got the below error.
Caused by: org.apache.ignite.binary.BinaryObjectException: Binary type has different affinity key fields [typeName=com.learnwithmanoj.ignite.model.RatingsKey, affKeyFieldName1=null, affKeyFieldName2=movie_id]
Your solution should work. First you need to clean marshaller/ directory under Ignite home dir on all nodes, then restart those nodes.
You might also need to specify key field name in caps: MOVIE_ID.