I am currently building an application with different ignite configurations. Right now, I am exploring the cache features of Ignite. My goal is to use the Ignite only as a cache. The data should be stored in a 3rd party database (postgres). I use the tpc-h data schema in my database. So, it's not a key-value-store, but ordinary sql.
Current situation: I have a postgres database (database-name: "db") with a "Nation" table. The table is filled with a few entries (Postgres Table "Nation"). I also have already built up an Ignite-Cluster in my Google-Kubernetes-Engine. I use my own Ignite-Container-Image. It's basically the official one with the postgresql-driver added to the classpath. My ignite-configuration looks like this:
<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-3.1.xsd
http://www.springframework.org/schema/util http://www.springframework.org/schema/util/spring-util-3.1.xsd">
<!-- Data source bean -->
<bean id="postgresDataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
<property name="driverClassName" value="org.postgresql.Driver" />
<property name="url" value="jdbc:postgresql://postgres-service:5432/db" />
<property name="username" value="#################" />
<property name="password" value="#################" />
</bean>
<!-- Ignite Configuration -->
<bean class="org.apache.ignite.configuration.IgniteConfiguration">
<property name="cacheConfiguration">
<list>
<!-- Configuration for NationCache -->
<bean class="org.apache.ignite.configuration.CacheConfiguration">
<property name="name" value="NationCache"/>
<property name="cacheMode" value="PARTITIONED"/>
<property name="atomicityMode" value="ATOMIC"/>
<property name="cacheStoreFactory">
<bean class="org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory">
<property name="dataSourceBean" value="postgresDataSource"/>
<property name="dialect">
<bean class="org.apache.ignite.cache.store.jdbc.dialect.BasicJdbcDialect"/>
</property>
<property name="types">
<list>
<bean class="org.apache.ignite.cache.store.jdbc.JdbcType">
<property name="cacheName" value="NationCache"/>
<property name="keyType" value="java.lang.Integer"/>
<property name="valueType" value="PathToClass.Nation"/>
<!--Specify the schema if applicable -->
<property name="databaseSchema" value="public"/>
<property name="databaseTable" value="nation"/>
<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="n_nationkey"/>
<constructor-arg value="int"/>
<constructor-arg value="n_nationkey"/>
</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.INTEGER"/>
</constructor-arg>
<constructor-arg value="n_nationkey"/>
<constructor-arg value="int"/>
<constructor-arg value="n_nationkey"/>
</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="n_name"/>
<constructor-arg value="java.lang.String"/>
<constructor-arg value="n_name"/>
</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="n_regionkey"/>
<constructor-arg value="int"/>
<constructor-arg value="n_regionkey"/>
</bean>
</list>
</property>
</bean>
</list>
</property>
</bean>
</property>
<property name="readThrough" value="true"/>
<property name="writeThrough" value="true"/>
<!-- Configure query entities if you want to use SQL queries -->
<property name="queryEntities">
<list>
<bean class="org.apache.ignite.cache.QueryEntity">
<property name="keyType" value="java.lang.Integer"/>
<property name="valueType" value="PathToClass.Nation"/>
<property name="keyFieldName" value="n_nationkey"/>
<property name="keyFields">
<list>
<value>n_nationkey</value>
</list>
</property>
<property name="fields">
<map>
<entry key="n_nationkey" value="java.lang.Integer"/>
<entry key="n_name" value="java.lang.String"/>
<entry key="n_regionkey" value="java.lang.Integer"/>
</map>
</property>
</bean>
</list>
</property>
</bean>
<!-- Provide similar configurations for other caches/tables -->
</list>
</property>
</bean>
</beans>
Questions: How can I automatically load my cache with the data from the postgresql database? Is there a way to load the cache via cmd in the container?
Further explanation: As far as I have understood the caching feature of Ignite, I HAVE TO load the sql data to the cache to be able to work with it (doing selects, etc.). I haven't found a way to load the data to cache without writing a separate java project, that loads the cache via IgniteCache#loadCache(). That worked fine, but I had to start it manually... There must be a way to load the cache more easily than writing a separate java project just for loading the cache. Besides this project I didn't have to work with java and could work with the container alone.
Unfortunately, there is no built-in mechanism to trigger the #loadCache method from outside of the cluster therefore it's required to write some auxiliary code. But, once it's written you might just wrap it into s compute task and invoke it using, say, REST API.