You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Goncalo Carvalho <gl...@gmail.com> on 2021/01/11 11:43:52 UTC
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,
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
Re: Continuous Queries and SQL
Posted by Goncalo Carvalho <gl...@gmail.com>.
Hi Craig,
Nice. Thank you for this detail.
Cheers,
Goncalo
On Mon, 11 Jan 2021 at 20:15, Craig Gresbrink <CG...@24hourfit.com>
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 <il...@gmail.com>
> *Sent:* Monday, January 11, 2021 7:34 AM
> *To:* user@ignite.apache.org
> *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 <gl...@gmail.com>:
>
> 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
RE: Continuous Queries and SQL
Posted by Craig Gresbrink <CG...@24HourFit.com>.
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 <il...@gmail.com>
Sent: Monday, January 11, 2021 7:34 AM
To: user@ignite.apache.org
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 <gl...@gmail.com>>:
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
Re: Continuous Queries and SQL
Posted by Ilya Kasnacheev <il...@gmail.com>.
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 <gl...@gmail.com>:
> 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
>
>