Apache Ignite automatically load cache from 3rd party database (postgresql with sql schema)

user_16858636 0
Viewed 392

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.

ignite caching postgresql
3 Answers

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.

user_112726850

To do this you need to provide you own implementation of CacheStoreAdapter and update the Ignite configuration to use these classes.

Here is a basic example that should get you started.

user_106603070

Have you ever used the Web Console provided by GridGain? This tool allows the Web UI to load data from postgresql connected via JDBC to Ignite. (However, the web console is not currently provided, so you will have to look for it.) With Apache Ignite alone, the above method seems to be the answer to your inquiry. In the paid version of GridGain, there is a way to load additional data.

user_134160900
Related