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);
            }

    }

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'` — CᴴᴀZ, Aug 09 '21 at 08:34

score 8 · Accepted Answer · answered Mar 27 '17 at 15:00

8

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.

answered Mar 27 '17 at 15:00

Pavel Tupitsyn

8,393
3
22
44

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 – Sushil Mar 27 '17 at 17:12
What is the select statement that you execute? How do you start the cache? – Pavel Tupitsyn Mar 27 '17 at 17:20
Basically before execute a Select statement, I want to confirm that Table is present in cache. – Sushil Mar 27 '17 at 18:22
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 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. – drobin Jul 13 '17 at 18:03
@drobin do you have query entities configured in CacheConfiguration? – Pavel Tupitsyn Jul 14 '17 at 07:55
@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. – drobin Jul 19 '17 at 19:20
@drobin 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. – Pavel Tupitsyn Jul 20 '17 at 13:38

score 2 · Answer 2 · answered Apr 13 '20 at 10:04

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));                           
                }

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 + "'"` — CᴴᴀZ, Aug 09 '21 at 08:29

score 0 · Answer 3 · answered Apr 26 '17 at 18:17

0

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

answered Apr 26 '17 at 18:17

Chris Ociepa

694
6
12

5

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)` – drobin Jul 13 '17 at 17:21

Apache Ignite : How to list all tables and all Caches

3 Answers3

Linked