Apache Ignite : How to list all tables and all Caches

Question

Is there any way to list all tables present in a specific Cache and list all caches present on a Apache Ignite Server?

----------------------------------UPDATED-------------------------- Hi, I am running following code to know Cache name and list all tables present in my cache. This program list outs all cache name present on server. However table listing is printed as blank collection. Meanwhile SQL query present in example is working fine.

public static void main(String[] args) throws Exception {
        System.out.println("Run Spring example!!");
        Ignition.setClientMode(true);
        IgniteConfiguration cfg = new IgniteConfiguration();
        cfg.setIncludeEventTypes( EVTS_CACHE);
        cfg.setPeerClassLoadingEnabled(true);
        TcpDiscoveryMulticastIpFinder discoveryMulticastIpFinder = new TcpDiscoveryMulticastIpFinder();
        Set<String> set = new HashSet<>();

        set.add("hostname:47500..47509");

        discoveryMulticastIpFinder.setAddresses(set);

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

        cfg.setDiscoverySpi(discoverySpi);

        cfg.setPeerClassLoadingEnabled(true);
        cfg.setIncludeEventTypes(EVTS_CACHE);
        Ignite ignite = Ignition.start(cfg);

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

        CacheConfiguration<String, BinaryObject> cacheConfiguration = new CacheConfiguration<>(CACHE_NAME);

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

        cacheConfiguration.setIndexedTypes(String.class, BinaryObject.class);
        //cacheConfiguration.setCacheMode(CacheMode.PARTITIONED);

        IgniteCache<String, BinaryObject> cache = ignite.getOrCreateCache(cacheConfiguration).withKeepBinary();

        System.out.println();





            QueryCursor<List<?>> query = cache.query(new SqlFieldsQuery("select Field1 from table1 where Field1='TEST'"));
            List<List<?>> all = query.getAll();
            for (List<?> l : all) {
                System.out.println(l);
            }

    }

Inside

My requirement was to get tables for a given Ignite schema. I queried SYS schema to get the tables: SELECT TABLE_NAME FROM SYS.TABLES WHERE SCHEMA_NAME = 'PUBLIC' – Sibyls

Answer 1

Get all cache names: Ignite.cacheNames(). Then use Ignite.cache(String) to get the cache instance.

Get SQL tables:

CacheConfiguration ccfg = cache.getConfiguration(CacheConfiguration.class);
Collection<QueryEntity> entities = ccfg.getQueryEntities();

Each query entity represents a table.

Concerning

Hi @PavelTupitsyn, Thanks for helping me. Ignite.cacheNames() is working fine. However ccfg.getQueryEntities() is giving me blank collection and I am able to execute "Select statement" on table so tables are present in cache. Thanks – Inside

What is the select statement that you execute? How do you start the cache? – Concerning

Basically before execute a Select statement, I want to confirm that Table is present in cache. – Inside

I get the same results as Sushil - the code suggested in this answer produces a list of cacheNames just fine, but after doing a: IgniteCache<Integer, ?> cache = ignite.cache(cacheName); and calling cache.getconfiguration(...) from an ignite client node connected to my 4 node ignite grid, I get nothing back on tables: Cache entity: null . In addition, calls to cache.metrics() doesn't appear to return any data either. – Caesalpiniaceous

@Caesalpiniaceous do you have query entities configured in CacheConfiguration? – Concerning

@PavelTupitsyn Not sure I understand. I am using the equivalent of the "Person" class per the Ignite documentation with annotations. The QueryEntity section of the documentation implies that QueryEntities are an alternative way to define fields and indexes to using the Person class in the Configuration By Annotations section. Shouldn't either method ultimately return "tables" for a cache ? Doesn't seem like the methods should be mutually exclusive with respect to meta data returned. – Caesalpiniaceous

@Caesalpiniaceous SQL in Ignite requires configured CacheConfiguration.QueryEntities. Annotations are one way to define fields and indexes, this is correct. But QueryEntity is still needed; you just don't set fields manually - they are taken from annotations. – Concerning

Answer 2

You can read using h2 query. SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA is the cache name

ClientConfiguration cfg = new ClientConfiguration().setAddresses(host+":"+port).
                        setUserName(username).setUserPassword(pwd); 
private static IgniteClient igniteClient = Ignition.startClient(cfg);
private static ClientCache<Integer, String>
 cache=igniteClient.getOrCreateCache(cacheName); 
QueryCursor<List<?>> cursor =cache.query(new SqlFieldsQuery("SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='"+cacheName+"'"));
                for (List<?> row : cursor) {
                    System.out.println(row.get(0));                           
                }

Combs

This didn't work with Ignite 2.8.1, I had to use SYS schema instead. SQL: "SELECT TABLE_NAME FROM SYS.TABLES WHERE CACHE_NAME = '" + cacheName + "'" – Sibyls

Answer 3

You can get all cache names using Ignite.cacheNames(). In order to get all table names you can use SHOW TABLES command:

QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery("SHOW TABLES FROM \""+CACHE_NAME+"\""));
for (List<?> row : cursor) {
    System.out.println(row.get(0));
}

More details about the SHOW command you can find here: http://www.h2database.com/html/grammar.html#show

Cryo

When I tried the "SHOW TABLES" approach with Ignite 2.0, I receive the following exception: Caused by: class org.apache.ignite.IgniteException: Unsupported query: Unexpected Table implementation [cls=MetaTable] at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.assert0(GridSqlQueryParser.java:1198) at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTable(GridSqlQueryParser.java:454) at org.apache.ignite.internal.processors.query.h2.sql.GridSqlQueryParser.parseTableFilter(GridSqlQueryParser.java:407) – Caesalpiniaceous