How to create table in every Apache ignite cache using JAVA SQL statement?

1
votes

I am able to create ignite cache using below code in java:

    Ignition.setClientMode(true);
    Set<String> set = new HashSet<>();
    set.add("127.0.0.1:48500..48520");

    discoveryMulticastIpFinder.setAddresses(set);

    TcpDiscoverySpi discoverySpi = new TcpDiscoverySpi();
    discoverySpi.setIpFinder(discoveryMulticastIpFinder);

    cfg.setDiscoverySpi(discoverySpi);

    Ignite ignite = Ignition.start(cfg);
    cacheConfiguration = new CacheConfiguration<>(CACHENAME);
    cacheConfiguration.setName(CACHENAME);
    cacheConfiguration.setSqlSchema("PUBLIC");
    cacheConfiguration.setAtomicityMode(CacheAtomicityMode.ATOMIC);
    cache = ignite.getOrCreateCache(cacheConfiguration).withKeepBinary();

    System.out.println("All Available Cache on server : "+ignite.cacheNames());

ignite.cacheNames() is printing all my created caches in the console. Now I want to create table like MYTABLE in each cache.

I have tried :

cache.query(new SqlFieldsQuery("CREATE TABLE IF NOT EXISTS MYTABLE( TAG VARCHAR,TIMECREATED TIMESTAMP,VALUE DOUBLE, PRIMARY KEY(TAG,TIMECREATED) )"));

but this query creates only one table for all caches. I cannot write query something like one below for above scenario .

SELECT * FROM "CACHENAME".MYTABLE WHERE SOME_CONDITION = 1;

I have referred [1]:Apache Ignite : How to list all tables and all Caches question from stackoverflow but this seems to be not helping much to answer my question. 

 Collection<QueryEntity> entities = cacheConfiguration.getQueryEntities();
 System.out.println("All available tables in "+CACHENAME+"cache : "+entities);

Using above code I can see cache name but no table is created inside of that cache.

Ultimately, I want to create one table MYTABLE in each and every cache and I should be able to query using JAVA SQL_QUERY method in ignite:

 SELECT * FROM "CACHE_NAME".MYTABLE WHERE SOME_CONDITION = 1;

Your answers are really valued and appreciated.

Thanks.

3
javasqlignite

3 Answers

1
votes

If you want to have a table per cache, you should define them using CacheConfiguration#indexedTypes or CacheConfiguration#queryEntities properties.

This way you will have the following tables created: "cacheName".TypeName.

You can change name of the tables by changing the QueryEntity#tableName property.

Refer to the following documentation page for more information: https://apacheignite-sql.readme.io/docs/schema-and-indexes

0
votes

If I understand your question correctly:

You can have several tables use the same cache in Apache Ignite (discriminated on the value type) but you can't have one table that uses more than one cache for keeping its data. So the mapping of caches to tables is one-to-many.

Another answer is that CREATE TABLE always creates table in PUBLIC schema. In a schema you can't have more than one table named MYTABLE. You could CREATE TABLE to create a cache with a given name, by specifying CACHE_NAME hint. But you can still only create one MYTABLE.

0
votes

you can specify the cache name in the table creation SQL query. CACHE_NAME=

Syntax: CREATE TABLE employee ( name varchar, phone varchar) WITH "template=partitioned, backups=1, affinityKey=CountryCode, CACHE_NAME=risk;