CODEWITHSUNDEEP
mysqldatabaseoracle-databasecachingignite
Daniel Cloudy
Daniel Cloudy

Reputation: 11

Apache Ignite use table names instead of cachename in a query

i have an application made in java that queries a schema many times (about 1000) for each request made by the user. This was initially designed in this way many years ago and currently the code refactor would be too risky for the complexity of the methods. Anyway, in order to leverage the DB effort i thought to introduce an Ignite layer to cache the biggest part of the data queried that is basically static, so i would expect that many of those queries will be faster and not on the DB anymore.

I've configured ignite properly on the server to cache the tables I need, and everything's fine until I tried to query on DBEaver or Squirrel and i discovered that the name of the tables to query on the Ignite DB is what in the ignite configuration is called property name=cacheName".

I don't want to put the hands on the code to change the queries one by one, so i would assume there's a way to keep the queries as the same as those are on the Oracle DB.

Example

In oracle DB i have <Schema_Name>.<Table_Name> and my queries in the code are something like "select * from <Table_Name> where x"

In Ignite schema instead i have

cacheName.<Table_Name>

so in order to query this my query should be transformed in something like

"select * from cacheName.<Table_Name> where x"

Seems like in Ignite the cacheName is considered as a Schema, the problem is that each single table has different schema in this way. Should I consider to refactor all the queries or is there a way to mantain the same query format?

my configuration is something like this

Taken from one table configuration

       <bean class="org.apache.ignite.configuration.CacheConfiguration">
            <property name="name" value="<TableName>Cache"/>
            <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="dsOracle"/>
                    <property name="dialect">
                        <bean class="org.apache.ignite.cache.store.jdbc.dialect.OracleDialect">
                        </bean>
                    </property>

                    <property name="types">
                        <list>
                            <bean class="org.apache.ignite.cache.store.jdbc.JdbcType">
                                <property name="cacheName" value="<TableName>Cache"/>
                                <property name="keyType" value="package.obfuscated.key"/>
                                <property name="valueType" value="package.obfuscated.type"/>
                                <property name="databaseSchema" value="<DBSchemaName>"/>
                                <property name="databaseTable" value="<TableName>"/>

                                <property name="keyFields">
                                    <list>
                                        <bean class="org.apache.ignite.cache.store.jdbc.JdbcTypeField">

. . . [list of table fields] . .

Thanks a lot

Upvotes: 1

Views: 963

Answers (1)

Stephen Darlington
Stephen Darlington

Reputation: 52565

In your cache definition, you can set the SQL Schema:

        var cacheConfiguration = new CacheConfiguration<PersonKey,Person>()
                .setName("PERSON_CACHE")
                .setSqlSchema("MY_SCHEMA")
                .setCacheMode(CacheMode.PARTITIONED)
                .setIndexedTypes(PersonKey.class, Person.class);
        var cache = ignite.<PersonKey,Person>getOrCreateCache(cacheConfiguration);

This creates a table that's visible in SQL as MY_SCHEMA.Person.

Upvotes: 1

Related Questions