Hi Craig, Nice. Thank you for this detail.
Cheers, Goncalo On Mon, 11 Jan 2021 at 20:15, Craig Gresbrink <[email protected]> wrote: > Goncalo, > > > > We are using Continuous queries and can do this sort of thing. See > attached image. > > > > We have a single jvm (per cache in this case subscription) called a CQ > Listener where we deploy a war. > > > > This listener war registers a remote filter on the server nodes to detect > changes (3.2). In the remote filter we have access to the old and new > values of the row that has changed as you would in a database trigger. > > > > The listener war creates a local listener on the cq listener jvm. The > remote filter on the server node(s) calls back to the local listener (3.3). > > > > The local listener has a thick client so it can execute sql queries across > multiple caches/objects in the Grid. That is how I’d suggest you accomplish > this. > > > > Additional info that may be useful or that you see it in the attached > image: > > > > 1. If we can do any filtering or conditions on just the > insterted/updated row itself we do this in the remote filter operating on > binary objects (so the server nodes don’t have our java domain objects). > You could likely run sql queries, across multiple caches/objects in remote > filters. but we prefer that our server nodes know as little as possible > about our domain/tables/objects and just follow the pattern that we run > queries via thick clients in client nodes of the grid. > > 2. Additional filtering as alluded to above can be done by the > local listener by querying back to the grid, which I think is an option in > your case. (Not shown but imaging a 3.3.1 arrow to the grid initiated by > 3.3) > > 3. The listener war also has an initial query that runs when we > bounce the listener tier to get any data changes done while the listener is > down > > a. It can not access old and new values. > > i. Most > use cases don’t need that, but in one case we wanted to know if the > subscription external status when from something other than terminated, to > terminated on an updated. so that is why we are implementing a history > cache by way of service grid and then we will change our initial query to > go against the history cache. > > 1. We have not yet implemented the subscription history cache > populate by way of service grid yet, so this part is just a proposed design. > > b. We have a lastRan cache that has a lastRanDate (not shown) and > the local listener updates that with the updatedDate of each object it > processes (think of a 3.3.1 arrow back to the grid to do a put on each > object). That is what becomes our updatedDate > ? date in the where clause > when our initial query runs in 3.1. > > 4. Also, we can conditionally decide to put a message on our active > MQ messaging system to asynchronously update other systems. > > 5. From there we can consume the message and do whatever we want, > we could query back to the grid and run sql queries, if we wanted to. So we > could do additional sql querying that way, but we do it in the local > listener if the data is in the grid. This because. why create useless NoOp > messages on our message queue and process them only to do nothing. > > > > Hope this helps and Cheers, > > Craig > > > > *From:* Ilya Kasnacheev <[email protected]> > *Sent:* Monday, January 11, 2021 7:34 AM > *To:* [email protected] > *Subject:* Re: Continuous Queries and SQL > > > > *CAUTION:* This email originated outside 24 Hour Fitness. Do not click > links or open attachments unless you recognize the sender and know the > content is safe. > > > > Hello! > > > > Currently, the only way to do efficient parent-child join is SQL since it > has secondary indexes. I guess you need to trigger SQL from continuous > query. > > > > Regards, > > -- > > Ilya Kasnacheev > > > > > > пн, 11 янв. 2021 г. в 14:44, Goncalo Carvalho <[email protected]>: > > Hi, > > > > I'm looking for advice on what is currently possible in terms of using > continuous queries. > > > > We're developing a server application that makes use of several joins > (using SqlFieldsQuery) across several caches to return results. > > > > We'd like to be able to implement some means to return (asynchronously) > updates to clients if the caches get updated. > > Continuous queries allow us to listen to updates but we can't really run > our sql within them. We'd need to react to an update with an extra > SqlFieldsQuery (after we listened to the update). > > > > There was some discussion about implementing materialised views, > > > > > http://apache-ignite-developers.2346864.n4.nabble.com/Using-materialised-views-for-queries-with-joins-td35197.html > > > > although it doesn't appear to have had any traction. > > > > Is there a best way of doing this? We can issue our query periodically or > do the above two-step process but we're just trying to understand if > there's something else we should consider. > > > > Kind regards, > > > > Goncalo > > > > -- http://www.cryogenicgraphics.com
