时间戳

首页 > 解决方案 > 三个 Ignite 缓存的 SQL Union All

java - 三个 Ignite 缓存的 SQL Union All

问题描述

我有三个 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 查询联合三个缓存的适当方法是什么？以及如何传递该查询的参数？

标签： javasqlunionignitegridgain

解决方案

根据错误，您正在尝试使用无效架构查询表：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 "

推荐阅读