| Author |
Message
|
| bhavya.work |
 Posted: Wed Feb 24, 2016 1:02 pm Post subject: Implementing Select Distinct or Group by in ESQL |
 |
|
Newbie
Joined: 07 Feb 2016
Posts: 3
|
First Post, sorry if I break any forum rules.
Goal : Split messages with distinct values. In example below x/xtypes/xtype/xtypeName is the distinct and group by element
Input Mesage:
| Code: |
<x>
<xtypes>
<xtype>
<xtypeID>1</xtypeID>
<xtypeName>John</xtypeName>
<xtypeValue>value1</xtypeValue>
<xtypeMoreValue>mv1</xtypeMoreValue>
</xtype>
<xtype>
<xtypeID>2</xtypeID>
<xtypeName>Bob</xtypeName>
<xtypeValue>value2</xtypeValue>
<xtypeMoreValue>mv2</xtypeMoreValue>
</xtype>
<xtype>
<xtypeID>3</xtypeID>
<xtypeName>John</xtypeName>
<xtypeValue>value3</xtypeValue>
<xtypeMoreValue>mv3</xtypeMoreValue>
</xtype>
<xtype>
<xtypeID>4</xtypeID>
<xtypeName>Michael</xtypeName>
<xtypeValue>value4</xtypeValue>
<xtypeMoreValue>mv4</xtypeMoreValue>
</xtype>
</xtypes>
</x> |
Propagate three messages as follows
First:
| Code: |
<x>
<xtypes>
<xtype>
<xtypeID>1</xtypeID>
<xtypeName>John</xtypeName>
<xtypeValue>value1</xtypeValue>
<xtypeMoreValue>mv1</xtypeMoreValue>
</xtype>
<xtype>
<xtypeID>3</xtypeID>
<xtypeName>John</xtypeName>
<xtypeValue>value3</xtypeValue>
<xtypeMoreValue>mv3</xtypeMoreValue>
</xtype>
</xtypes>
</x>
|
Second:
| Code: |
<x>
<xtypes>
<xtype>
<xtypeID>2</xtypeID>
<xtypeName>Bob</xtypeName>
<xtypeValue>value2</xtypeValue>
<xtypeMoreValue>mv2</xtypeMoreValue>
</xtype>
</xtypes>
</x> |
Third:
| Code: |
<x>
<xtypes>
<xtype>
<xtypeID>4</xtypeID>
<xtypeName>Michael</xtypeName>
<xtypeValue>value4</xtypeValue>
<xtypeMoreValue>mv4</xtypeMoreValue>
</xtype>
</xtypes>
</x>
|
My approach Algorithm :
Using Distinct : Find distinct elements in the xpath above .
Loop on xtypes.type if curLoopName = forLoopName then copy xtype tree to output root.
So thoughts on how to do this? I tried some other approaches but nothing worth sharing.
I tried putting a link for one of the implementation I found on this forun but forum rules doesn't allow posting link for new users. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Feb 24, 2016 1:09 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
You can't do SELECT DISTINCT in ESQL Select, as far as I remember.
There is an ESQL implementation of quicksort somewhere. You could sort your tree by x/xtypes/xtype/xtypeName and then loop over it again and propagate as you find differences.
Or you could create a tree in the XMLNSC domain in Environment or Local Environment, and use the x/xtypes/xtype/xtypeName as the top level element of the tree... something like
set NEXT CHILD OF OutputLocalEnvironment.XMLNSC.DistinctNames.{InputRoot.XMLNSC.x.xtypes.xtype.xtypeName} = curRef
for some reference curRef looping over the input tree.
Then you would have elements like OutputLocalEnvrionment.XMLNSC.DistinctNames.John with the xtypes for John under it.
Then loop over the DistinctNames tree and build your output tree from that.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| bhavya.work |
| Posted: Wed Feb 24, 2016 1:24 pm Post subject: |
|
|
Newbie
Joined: 07 Feb 2016
Posts: 3
|
You remember right. There is no group by and distinct. It is clearly mentioned in documentation.
I think the approach of creating distinctName tree in env will probably work. Will have to try out. Just two problems I foresee with this approach though.
1) the actual input message is very large. This is just one xtypes section. there is ytpes,ztypes etc.. which need to be copied. I guess, I can work around it somehow.
2) the xtypeName can be blank sometimes and that is a valid message as well. Will have to work around that too.. somehow.
Will try and report if success. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Feb 24, 2016 1:35 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The approach with using DistinctNames is more or less treating the env tree as a hash structure. As long as all of the "" are supposed to be in the same message, this will still work (except you might need to test to see if it's blank and then use a placebo value "blank" or etc.)
If all of your xname,yname,zname stuff is under the same child, you can just copy the whole child tree.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| timber |
| Posted: Wed Feb 24, 2016 2:28 pm Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| Quote: |
| First Post, sorry if I break any forum rules. |
Well, actually you did, and sincere thanks for breaking at least two rules that most people follow. You actually cared about the person trying to understand your problem, and you explained the problem fully at the first attempt. 
Your problem is tricky because you don't know the full set of xtypes until the entire message has been parsed. So you need to hold the entire (potentially huge) message tree in memory at the same time.
I might be tempted to experiment with a Collector node. It will store all of the messages on a queue ( not in memory ) until the collections are complete or the Control terminal is triggered. The correlator would obviously be the xtypeName. Worth a try, maybe? |
|
| Back to top |
|
|
| bhavya.work |
| Posted: Wed Feb 24, 2016 9:28 pm Post subject: |
|
|
Newbie
Joined: 07 Feb 2016
Posts: 3
|
| timber wrote: |
| Quote: |
| First Post, sorry if I break any forum rules. |
Well, actually you did, and sincere thanks for breaking at least two rules that most people follow. You actually cared about the person trying to understand your problem, and you explained the problem fully at the first attempt.
|
Hehe, Yeah first post but I am one of the biggest forum lurkers out there. So, I think from the perspective of the end user and know what to write to get an answer.
Just a quick update I got it to work with some version of what jeff suggested. I am still refining it to optimize for my need. Once done I promise I will come around and post the solution for some other soul who may struggle with this. Too busy right now.
Timber, some day i will try playing with collectors I have never played with them before and can't risk doing POC too much in my plate right now.
Thanks both of you. |
|
| Back to top |
|
|
| maurito |
| Posted: Thu Feb 25, 2016 1:30 am Post subject: |
|
|
Partisan
Joined: 17 Apr 2014
Posts: 358
|
| bhavya.work wrote: |
| timber wrote: |
| Quote: |
| First Post, sorry if I break any forum rules. |
Well, actually you did, and sincere thanks for breaking at least two rules that most people follow. You actually cared about the person trying to understand your problem, and you explained the problem fully at the first attempt.
|
Hehe, Yeah first post but I am one of the biggest forum lurkers out there. So, I think from the perspective of the end user and know what to write to get an answer.
Just a quick update I got it to work with some version of what jeff suggested. I am still refining it to optimize for my need. Once done I promise I will come around and post the solution for some other soul who may struggle with this. Too busy right now.
Timber, some day i will try playing with collectors I have never played with them before and can't risk doing POC too much in my plate right now.
Thanks both of you. |
If I were you I would follow timber's advice and let the collector node do all the hard work for you. At the end of the collector node you would receive one collected message for each xtypeName, containing the relevant sub trees for that xtypeName.
| Quote: |
Timber, some day i will try playing with collectors I have never played with them before and can't risk doing POC too much in my plate right now.
|
This is a good reason for using collector now. You will learn a very useful technique and it will save you lots of time and hassle. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Feb 25, 2016 5:59 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
The difficulty with using the collector node is that it is asychronous and could take a much larger time to create the output messages.
On the other hand, it will be more efficient in memory use, and more obvious in the flow what's going on.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| krish418 |
| Posted: Sat Aug 19, 2017 6:37 am Post subject: |
|
|
Newbie
Joined: 19 Aug 2017
Posts: 1
|
Bhavya,
I am in a same situation. can you please post your code for finding distinct vales and grouping the xml. Thank you |
|
| Back to top |
|
|
| mqjeff |
| Posted: Sat Aug 19, 2017 6:58 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| krish418 wrote: |
Bhavya,
I am in a same situation. can you please post your code for finding distinct vales and grouping the xml. Thank you |
ESQL Select does not support ORDER or GROUP by or DISTINCT.
Read the rest of this thread for other suggestions.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| paintpot |
| Posted: Mon Aug 21, 2017 5:19 am Post subject: |
|
|
Centurion
Joined: 19 Sep 2005
Posts: 112
Location: UK
|
Try using PASSTHRU for more complicated SQL:
"The main use of the PASSTHRU function is to issue complex SELECTs, not currently supported by the integration node, to databases. (Examples of complex SELECTs not currently supported by the integration node are those containing GROUP BY or HAVING clauses.)" |
|
| Back to top |
|
|
|
|
