Continuous Queries and SQL

classic Classic list List threaded Threaded
4 messages Options
Goncalo Carvalho Goncalo Carvalho
Reply | Threaded
Open this post in threaded view
|

Continuous Queries and SQL

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,


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

ilya.kasnacheev ilya.kasnacheev
Reply | Threaded
Open this post in threaded view
|

Re: Continuous Queries and SQL

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 <[hidden email]>:
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,


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

Craig Gresbrink Craig Gresbrink
Reply | Threaded
Open this post in threaded view
|

RE: Continuous Queries and SQL

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 <[hidden email]>
Sent: Monday, January 11, 2021 7:34 AM
To: [hidden email]
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 <[hidden email]>:

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,

 

 

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

 


CQIssuingQueryBackToGrid.png (135K) Download Attachment
Goncalo Carvalho Goncalo Carvalho
Reply | Threaded
Open this post in threaded view
|

Re: Continuous Queries and SQL

Hi Craig,

Nice. Thank you for this detail.

Cheers,

Goncalo

On Mon, 11 Jan 2021 at 20:15, Craig Gresbrink <[hidden email]> 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 <[hidden email]>
Sent: Monday, January 11, 2021 7:34 AM
To: [hidden email]
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 <[hidden email]>:

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,

 

 

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

 



--