Ignite 2.16.0读写穿透缓存SQL查询未加载数据无效问题咨询

To query your PostgreSQL data directly without loading it into Ignite cache, use the EXTERNAL keyword in your SQL query. This instructs Ignite to bypass the cache and execute the query against your external PostgreSQL store.

Example Query #

SELECT * FROM EXTERNAL your_table LIMIT 10

Why This Works #

By default, Ignite SQL queries only operate on data already present in the cache. The EXTERNAL modifier tells Ignite to route the query to the underlying PostgreSQL database instead, returning results without populating the cache.

Prerequisites #

• Confirm read-through is enabled: You already have this configured, but ensure your cache has readThrough=true set in its configuration.
• Proper QueryEntity mapping: Your cache's QueryEntity must correctly map the cache's key/value types to your PostgreSQL table columns. For example, when using JdbcCacheStore:

  QueryEntity queryEntity = new QueryEntity(Integer.class, YourEntity.class);
  queryEntity.setTableName("your_table");
  queryEntity.addQueryField("id", Integer.class.getName(), null);
  queryEntity.addQueryField("name", String.class.getName(), null);
  // Add all columns matching your PostgreSQL table schema
  

• Custom CacheStore handling: If you're using a custom CacheStore instead of Ignite's built-in JdbcCacheStore, implement the query method to handle SqlFieldsQuery requests. This method needs to translate the Ignite SQL query to PostgreSQL-compatible syntax, execute it against the database, and return the results.

Note #

Results from EXTERNAL queries are not automatically loaded into the cache. If you want to cache the retrieved data afterward, use an INSERT statement:

INSERT INTO your_cache SELECT * FROM EXTERNAL your_table LIMIT 10

内容的提问来源于stack exchange，提问作者Rakesh Balguri