SQL Union全部用于三个Ignite缓存

Question

我有三个Ignite缓存，我想同时查询这三个缓存。我已经试过这个方法了。

public static final String EVENT_VIEW_RESERVED_EVENT_CACHE = "event_view_reserved_event_mc_%d";
public static final String UNCONFIRMED_NON_URGENT_EVENT_CACHE = "unconfirmed_event_mc_%d";
public static final String UNCONFIRMED_URGENT_EVENT_CACHE = "unconfirmed_event_urgent_mc_%d";
IgniteCache<String, Annotations> annotationsCache = ignite.cache(String.format(ANNOTATION_CACHE, orderId));
IgniteCache<String, ConfirmedEvent> confirmedEventCache = ignite.cache(String.format(CONFIRMED_EVENT_CACHE, mcId));
IgniteCache<String, UnconfirmedEvent> unconfirmedEventCache = ignite.cache(String.format(UNCONFIRMED_NON_URGENT_EVENT_CACHE,mcId));
IgniteCache<String, UnconfirmedEvent> unconfirmedUrgentEventCache = ignite.cache(String.format(UNCONFIRMED_URGENT_EVENT_CACHE,mcId));
EVENT_GET_RHYTHM_BY_ID = "Select timestamp from ConfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = %s UNION ALL " +
"Select timestamp from " + String.format(UNCONFIRMED_NON_URGENT_EVENT_CACHE,mcId) + ".UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = %s " +
"Select timestamp from " + String.format(UNCONFIRMED_URGENT_EVENT_CACHE,mcId) + ".UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = %s " + " order by startTime";
sql_Afib = new SqlFieldsQuery(String.format(EVENT_GET_RHYTHM_BY_ID, AnnotationConverter.StringToRhythmValue(AFIB), AnnotationConverter.StringToRhythmValue(AFIB),
AnnotationConverter.StringToRhythmValue(AFIB))).setArgs(orderId, endTimestamp, startTimestamp,
orderId, endTimestamp, startTimestamp, orderId, endTimestamp, startTimestamp);
List<Long> EventsEndTime = new ArrayList<>();
try (QueryCursor<List<?>> cursor = confirmedEventCache.query(sql_Afib)) {
for (List<?> row : cursor) {

EventsEndTime.add(row.get(0));

}
}

在简单的sql查询是这样的。

private static final String sql = "select timestamp from ClassName1 UNION ALL select timestamp from table2.ClassName2 UNION ALL select timestamp from table3.ClassName3"; SqlFieldsQuery sqlQ = new SqlFieldsQuery(sql); try (QueryCursor<List<?>> cursor = cache1.query(sqlQ)) { .... 

但是我得到这个错误。

CacheException: Failed to parse query. Schema "UNCONFIRMED_EVENT_MC_79" not found; SQL statement:
Select _val from ConfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = 7 UNION ALL Select _val from unconfirmed_event_mc_79.UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = 7 Select _val from unconfirmed_event_urgent_mc_79.UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = 7  order by startTime [90079-199]
javax.cache.CacheException: Failed to parse query. Schema "UNCONFIRMED_EVENT_MC_79" not found; SQL statement:
Select _val from ConfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = 7 UNION ALL Select _val from unconfirmed_event_mc_79.UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = 7 Select _val from unconfirmed_event_urgent_mc_79.UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = 7  order by startTime [90079-199]
at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:828)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxyImpl.query(IgniteCacheProxyImpl.java:761)
at org.apache.ignite.internal.processors.cache.GatewayProtectedCacheProxy.query(GatewayProtectedCacheProxy.java:385)

从一个sql查询中联合三个缓存的合适方法是什么?以及如何传递该查询的参数?

Answer 1

根据错误，您正在尝试使用无效模式查询表:Schema "UNCONFIRMED_EVENT_MC_79" not found;，而不是UNION ALL操作。

看起来你的表定义在不同的缓存中。试着这样做:把这个组合分成3个部分，然后单独检查。特别检查这个查询:

"Select timestamp from " + String.format(UNCONFIRMED_NON_URGENT_EVENT_CACHE,mcId) + ".UnconfirmedEvent where orderId = ? and startTime < ? and endTime > ? and type = %s "