Apache Ignite Bianry Cache store Sql Read Through Feature

[debashissinha <de...@gmail.com>]

Hi ,

I am quite new to Ignite and was trying out different features. One of the
features I was trying out was the read-through feature for cache . I am
using ignite version 2.5 on local standalone mode. I am using third party
persistence for data storage. For me mysql is the persistent storage and am
connecting the same to ignite cache using binaryobject store.  

I was able to load create and load data in cache from mysql and also query
the cache using sql. For this I have overridden the loadcache method and did
my custom implementation. 

I was able to load cache with data from mysql and also query the cache using
sql joins.

I have two tables in mysql
Person
id int
name varchar(10)
age   int
city_id int

City
id int
cityname varchar(50)

So the challenge I am facing is before querying from the cache i had to load
all data to cache from mysql using a cache.loadcache() call. So if I dont do
that query doesnt return anyting and that I have understood  by studying
different forum.

I was interested in a feature where if say there is a scenario whereby there
are 5 records in "Person" with city_id ranging from 1-5 and four records in
City  with id ranging from 1-4 and do a join it should read from the cache
if there is a record in city cache for city table with id=5 if it is not
there then will got mysql and check in city city table for record with id=5
. 

If the same is found will be retrieved and loaded to cache and then the join
query will be executed on it .

To make it more simple lets say we have 4 records loaded in cache for city
table and now one more record has been added to mysql and if i query with
id=5 on city cache it will first look for the same in cache if not found
then will  got to mysql . if record is present it will fetch the same and
put it in cache and also execute the query on cache.

I have seen this with key value pair . But am interested to know if there is
a way of achieving this automatic read through for sql query on cache using
any combination possible.

Thanks in advance
Regards
Debashis






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

Re: Apache Ignite Bianry Cache store Sql Read Through Feature

[debashissinha <de...@gmail.com>]

Hi ,
Thanks a lot for your help



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

Re: Apache Ignite Bianry Cache store Sql Read Through Feature

["slava.koptilin" <sl...@gmail.com>]

Hi,

I think that the best way to handle this scenario is to update the
underlying database through Ignite.
Other approaches have obvious drawbacks (at least one): there is always a
time interval between calling cache.get() and executing SQL query, so it
cannot be guaranteed that you will see the latest data.

In case of real-time updates is not a mandatory requirement, I would suggest
implementing an Ignite Service [1] that will update your cache(s) on some
schedule.

[1] https://apacheignite.readme.io/docs/cluster-singletons

Thanks.




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

Re: Apache Ignite Bianry Cache store Sql Read Through Feature

[debashissinha <de...@gmail.com>]

Hi Slava,

I have found a way around for this. Not sure about the performance
impediments though.... What I am doing is I am creating a 
CachedStoreAdapter which takes in a Map<String,Object> and returns a
List<Map&lt;String,String>>. So the implementation is something like this I
pass in a sql statement via map and also the necessary query params into the
cache load method by calling cache.get(Map<String,Object>). 

If the query params and  result is there in the cache it does not look into
the db . But if the queryparams is not there it goes to the load method
fires the sql statement extracting it from the map that is passed to the
load method. Then returns the map containing the resultset object values. I
am not using BinaryBuilder here . Because for sql query to work on binary
object we need to load the cache first hand and read through is not
available . Hence as we are working on key value pair and the sql cant be
fired on the cache so am returning a map.

The Implementation is as below.

CacheExample class

CacheConfiguration<Map&lt;String,Object>,List<Map&lt;String,String>>>
joinconfig = new CacheConfiguration();
joinconfig.setName("JOINSCHEMA");
joinConfig.setCacheStoreSessionListnerFactories(new
Factory<CacheStoreSessionListner>(){

@Override
public CacheStoreSessionListener create(){

CacheJdbcStoreSessionListner lsnr =  new CacheJdbcStoreSessionListner();
MySqlDataSource mysqlDS= new MySqlDataSource();
mysqlDS.setUrl("jdbc:mysql://localhost:3306/dbname");
mysqlDS.setUser("username");
mysqlDS.setPassword("password");

lsnr.setDataSource(mysqlDS);
return lsnr;
}
});

joinConfig.setCacheStoreFactory(FactoryBuilder.factoryOf(CacheJdbcResultStore.class));
joinConfig.setReadThrough(true);
Ignite ignite = Ignition.start();

Map<String,Object> queryParams = new HashMap<String,Object>();

String sqlstmnt ="select c.id, p.name,c.city,p.salary FROM PERSON AS P ,
CITY AS C WHERE P.city_id = c.id and c.id=?";

Map<String, Integer>  queryParamsVal = new HashMap<String,Integer>();
queryparams.put("sql",sqlstmnt );

Map<String,Integer> queryParamsVal = new HashMap<String,Integer>();
queryParamsVal.put("id",1);

queryParams.put("paramval",queryParamsVal);

IgniteCache<Map&lt;String,Object>, List<Map&lt;String,String>>> joinCache =
ignite.getOrCreateCache(joinConfig).withkeepBinary();

///first get call to cache will go inside load method as data is not there
System.out.println("First Call to cach will go through to load
"+Arrays.asList(joinCache.get(queryParams)));
System.out.println("Second call to cache will not go to load as data is
already there"+Arrays.asList(joinCache.get(queryParams)));

queryParamsVal.put("id",2);

queryParams.put("paramval",queryParamsVal);

System.out.println("Now data is changed in query params so will again go to
load method in cache");

System.out.println("First Call to cach will go through to load
"+Arrays.asList(joinCache.get(queryParams)));
System.out.println("Second call to cache will not go to load as data is
already there"+Arrays.asList(joinCache.get(queryParams)));




CachedJDBcresultStore.java

public class CacheJdbcResultStore extends
CacheStoreAdapter<Map&lt;String,Object>,List<Map&lt;String,String>>>{

public List<Map&lt;String,String>> load(Map<String,Object> args) throws
CacheLoaderException{

try (Connection con = connection()){
try(PreparedStatement st =
con.prepareStatement(args.get("sql").toString()))){
Map<String,Integer> paramval =
(HashMap)<String,Integer>args.get("paramval");
st.setInt(1,(Integer)paramVal.get("id"));

ResultSet rs = st.executeQuery();

List ls = new ArrayList<>();

while(rs.next()){
Map valueMap = new HashMap<>();

valueMap.put("city",rs.getString("city"));
valueMap.put("name",rs.getString("name"));
valueMap.put("id",rs.getInt("id"));
valueMap.put("salary",rs.getDouble("salary"));

ls.add(valueMap);

}
return ls
}
}
}

}


}
// add all other unimplemented method

private Connection connection(){

class.forName("com.mysql.jdbc.Driver").newInstance();

Connection con = DriverManager.getConnection("jdbcurl");

conn.setAutoCommit(true);

return conn
}
}

Kindly let  me know what can be the performance or limitations of using this
approach. However proper readthorugh is achived indirectly via sql by this
approach.

Thanks in advance
Debashis Sinha 




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

Re: Apache Ignite Bianry Cache store Sql Read Through Feature

[Вячеслав Коптилин <sl...@gmail.com>]

Hi,

I could not find something related to that in Jira. So, it seems there are
no plans to implement this feature.

Thanks,
S.

чт, 12 июл. 2018 г. в 20:50, debashissinha <de...@gmail.com>:

> Hi Salva,
>
> Many Thanks for your advise . So it seems there is no real time way to do
> that . However I was also wondering whether datstreamer can be of any Help
> .
> Just wanted to know if any kind of feature is planned for future release.
>
> Thanks in advance
> Debashis
>
>
>
> --
> Sent from: http://apache-ignite-users.70518.x6.nabble.com/
>

Re: Apache Ignite Bianry Cache store Sql Read Through Feature

[debashissinha <de...@gmail.com>]

Hi Salva,

Many Thanks for your advise . So it seems there is no real time way to do
that . However I was also wondering whether datstreamer can be of any Help .
Just wanted to know if any kind of feature is planned for future release.

Thanks in advance
Debashis 



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

Re: Apache Ignite Bianry Cache store Sql Read Through Feature

["slava.koptilin" <sl...@gmail.com>]

Hello Debashis,

Yep, you have to call `loadCache()` method to initially pull your data from
3rd party persistence to Apache Ignite's cache. If the underlying database
is updated directly, then you have to propagate that updates to Ignite
somehow. Ignite does not provide such integration/capability out of the box.

Perhaps, that topic will be helpful for you:
http://apache-ignite-users.70518.x6.nabble.com/Any-references-Syncing-Ignite-and-Oracle-DB-with-Oracle-GoldenGate-updates-from-DB-to-ignite-tt20715.html

Thanks,
S.



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