Mailing-List: contact dev-help@spark.apache.org; run by ezmlm
Precedence: bulk
MIME-Version: 1.0
In-Reply-To: 
 <CABcA1ruHx=U29ZZKaFV_bmBCj+5fxN7rL+33u6O82SE10YFEZg@mail.gmail.com>
References: 
 <CAG+ckK-WRjF_a39V07vwCNyW-BSdYbmP9jftUFZNV=hJ80Oakw@mail.gmail.com>
	<CANs_gpLixUV6CAnBcibkD9tWNRFrHdO1yzd0qXggPeOvGU6EGg@mail.gmail.com>
	<CAG+ckK-jU7--65gQ7ZnHE8GrM+gz9xNPgQNeouP6E+qV5oUJ=w@mail.gmail.com>
	<CABcA1ruHx=U29ZZKaFV_bmBCj+5fxN7rL+33u6O82SE10YFEZg@mail.gmail.com>
Date: Tue, 1 Mar 2016 11:48:39 -0500
Message-ID: 
 <CAG+ckK8M8Uf8vM27KROXgokTVk-cAFPMPzSRKAcC0bRyRhRFbg@mail.gmail.com>
Subject: Re: SPARK-SQL: Pattern Detection on Live Event or Archived Event Data
From: Jerry Lam <chilinglam@gmail.com>
To: Henri Dubois-Ferriere <henridf@gmail.com>
Cc: =?UTF-8?Q?Herman_van_H=C3=B6vell_tot_Westerflier?=
 <hvanhovell@questtec.nl>,
	Spark dev list <dev@spark.apache.org>
Content-Type: multipart/alternative; boundary=001a114318c88620da052cff89a3

--001a114318c88620da052cff89a3
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi Henri,

Finally, there is a good reason for me to use Flink! Thanks for sharing
this information. This is exactly the solution I'm looking for especially
the ticket references a paper I was reading a week ago. It would be nice if
Flink adds support SQL because this makes business analyst (traders as
well) a way to express it.

Best Regards,

Jerry

On Tue, Mar 1, 2016 at 11:34 AM, Henri Dubois-Ferriere <henridf@gmail.com>
wrote:

> fwiw Apache Flink just added CEP. Queries are constructed programmaticall=
y
> rather than in SQL, but the underlying functionality is similar.
>
> https://issues.apache.org/jira/browse/FLINK-3215
>
> On 1 March 2016 at 08:19, Jerry Lam <chilinglam@gmail.com> wrote:
>
>> Hi Herman,
>>
>> Thank you for your reply!
>> This functionality usually finds its place in financial services which
>> use CEP (complex event processing) for correlation and pattern matching.
>> Many commercial products have this including Oracle and Teradata Aster D=
ata
>> MR Analytics. I do agree the syntax a bit awkward but after you understa=
nd
>> it, it is actually very compact for expressing something that is very
>> complex. Esper has this feature partially implemented (
>> http://www.espertech.com/esper/release-5.1.0/esper-reference/html/match-=
recognize.html
>> ).
>>
>> I found the Teradata Analytics documentation best to describe the usage
>> of it. For example (note npath is similar to match_recognize):
>>
>> SELECT last_pageid, MAX( count_page80 )
>>  FROM nPath(
>>  ON ( SELECT * FROM clicks WHERE category >=3D 0 )
>>  PARTITION BY sessionid
>>  ORDER BY ts
>>  PATTERN ( 'A.(B|C)*' )
>>  MODE ( OVERLAPPING )
>>  SYMBOLS ( pageid =3D 50 AS A,
>>            pageid =3D 80 AS B,
>>            pageid <> 80 AND category IN (9,10) AS C )
>>  RESULT ( LAST ( pageid OF ANY ( A,B,C ) ) AS last_pageid,
>>           COUNT ( * OF B ) AS count_page80,
>>           COUNT ( * OF ANY ( A,B,C ) ) AS count_any )
>>  )
>>  WHERE count_any >=3D 5
>>  GROUP BY last_pageid
>>  ORDER BY MAX( count_page80 )
>>
>> The above means:
>> Find user click-paths starting at pageid 50 and passing exclusively
>> through either pageid 80 or pages in category 9 or category 10. Find the
>> pageid of the last page in the path and count the number of times page 8=
0
>> was visited. Report the maximum count for each last page, and sort the
>> output by the latter. Restrict to paths containing at least 5 pages. Ign=
ore
>> pages in the sequence with category < 0.
>>
>> If this query is written in pure SQL (if possible at all), it requires
>> several self-joins. The interesting thing about this feature is that it
>> integrates SQL+Streaming+ML in one (perhaps potentially graph too).
>>
>> Best Regards,
>>
>> Jerry
>>
>>
>> On Tue, Mar 1, 2016 at 9:39 AM, Herman van H=C3=B6vell tot Westerflier <
>> hvanhovell@questtec.nl> wrote:
>>
>>> Hi Jerry,
>>>
>>> This is not on any roadmap. I (shortly) browsed through this; and this
>>> looks like some sort of a window function with very awkward syntax. I t=
hink
>>> spark provided better constructs for this using dataframes/datasets/nes=
ted
>>> data...
>>>
>>> Feel free to submit a PR.
>>>
>>> Kind regards,
>>>
>>> Herman van H=C3=B6vell
>>>
>>> 2016-03-01 15:16 GMT+01:00 Jerry Lam <chilinglam@gmail.com>:
>>>
>>>> Hi Spark developers,
>>>>
>>>> Will you consider to add support for implementing "Pattern matching in
>>>> sequences of rows"? More specifically, I'm referring to this:
>>>> http://web.cs.ucla.edu/classes/fall15/cs240A/notes/temporal/row-patter=
n-recogniton-11.pdf
>>>>
>>>> This is a very cool/useful feature to pattern matching over live
>>>> stream/archived data. It is sorted of related to machine learning beca=
use
>>>> this is usually used in clickstream analysis or path analysis. Also it=
 is
>>>> related to streaming because of the nature of the processing (time ser=
ies
>>>> data mostly). It is SQL because there is a good way to express and opt=
imize
>>>> the query.
>>>>
>>>> Best Regards,
>>>>
>>>> Jerry
>>>>
>>>
>>>
>>
>

--001a114318c88620da052cff89a3
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi Henri,<div><br></div><div>Finally, there is a good reas=
on for me to use Flink! Thanks for sharing this information. This is exactl=
y the solution I&#39;m looking for especially the ticket references a paper=
 I was reading a week ago. It would be nice if Flink adds support SQL becau=
se this makes business analyst (traders as well) a way to express it.=C2=A0=
</div><div><br></div><div>Best Regards,</div><div><br></div><div>Jerry=C2=
=A0</div></div><div class=3D"gmail_extra"><br><div class=3D"gmail_quote">On=
 Tue, Mar 1, 2016 at 11:34 AM, Henri Dubois-Ferriere <span dir=3D"ltr">&lt;=
<a href=3D"mailto:henridf@gmail.com" target=3D"_blank">henridf@gmail.com</a=
>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margin:0 =
0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr">fwiw=
 Apache Flink just added CEP. Queries are constructed programmatically rath=
er than in SQL, but the underlying functionality is similar.<div><br></div>=
<div><a href=3D"https://issues.apache.org/jira/browse/FLINK-3215" target=3D=
"_blank">https://issues.apache.org/jira/browse/FLINK-3215</a><br></div></di=
v><div class=3D"HOEnZb"><div class=3D"h5"><div class=3D"gmail_extra"><br><d=
iv class=3D"gmail_quote">On 1 March 2016 at 08:19, Jerry Lam <span dir=3D"l=
tr">&lt;<a href=3D"mailto:chilinglam@gmail.com" target=3D"_blank">chilingla=
m@gmail.com</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div di=
r=3D"ltr">Hi Herman,<div><br></div><div>Thank you for your reply!</div><div=
>This functionality usually finds its place in financial services which use=
 CEP (complex event processing) for correlation and pattern matching. Many =
commercial products have this including Oracle and Teradata Aster Data MR A=
nalytics. I do agree the syntax a bit awkward but after you understand it, =
it is actually very compact for expressing something that is very complex. =
Esper has this feature partially implemented (<a href=3D"http://www.esperte=
ch.com/esper/release-5.1.0/esper-reference/html/match-recognize.html" targe=
t=3D"_blank">http://www.espertech.com/esper/release-5.1.0/esper-reference/h=
tml/match-recognize.html</a>).=C2=A0</div><div><br></div><div>I found the T=
eradata Analytics documentation best to describe the usage of it. For examp=
le (note npath is similar to match_recognize):</div><div><br></div><div><di=
v>SELECT last_pageid, MAX( count_page80 )</div><div>=C2=A0FROM nPath(</div>=
<div>=C2=A0ON ( SELECT * FROM clicks WHERE category &gt;=3D 0 )</div><div>=
=C2=A0PARTITION BY sessionid</div><div>=C2=A0ORDER BY ts</div><div>=C2=A0PA=
TTERN ( &#39;A.(B|C)*&#39; )</div><div>=C2=A0MODE ( OVERLAPPING )</div><div=
>=C2=A0SYMBOLS ( pageid =3D 50 AS A,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0pageid =3D 80 AS B,</div><div>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0pageid &lt;&gt; 80 AND category IN (9,10) AS C )</div><div>=C2=A0=
RESULT ( LAST ( pageid OF ANY ( A,B,C ) ) AS last_pageid,</div><div>=C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0 COUNT ( * OF B ) AS count_page80,</div><div>=C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 COUNT ( * OF ANY ( A,B,C ) ) AS count_any )=
</div><div>=C2=A0)</div><div>=C2=A0WHERE count_any &gt;=3D 5</div><div>=C2=
=A0GROUP BY last_pageid</div><div>=C2=A0ORDER BY MAX( count_page80 )</div><=
/div><div><br></div><div>The above means:</div><div>Find user click-paths s=
tarting at pageid 50 and passing exclusively through either pageid 80 or
pages in category 9 or category 10. Find the pageid of the last page in the=
 path and count the
number of times page 80 was visited. Report the maximum count for each last=
 page, and sort the
output by the latter. Restrict to paths containing at least 5 pages. Ignore=
 pages in the sequence
with category &lt; 0.<br></div><div><br></div><div>If this query is written=
 in pure SQL (if possible at all), it requires several self-joins. The inte=
resting thing about this feature is that it integrates SQL+Streaming+ML in =
one (perhaps potentially graph too).</div><div><br></div><div>Best Regards,=
</div><div><br></div><div>Jerry</div><div><br></div></div><div><div><div cl=
ass=3D"gmail_extra"><br><div class=3D"gmail_quote">On Tue, Mar 1, 2016 at 9=
:39 AM, Herman van H=C3=B6vell tot Westerflier <span dir=3D"ltr">&lt;<a hre=
f=3D"mailto:hvanhovell@questtec.nl" target=3D"_blank">hvanhovell@questtec.n=
l</a>&gt;</span> wrote:<br><blockquote class=3D"gmail_quote" style=3D"margi=
n:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir=3D"ltr">=
<div>Hi Jerry,</div><div><br></div><div>This is not on any roadmap. I (shor=
tly) browsed through this; and this looks like some sort of a window functi=
on with very awkward syntax. I think spark provided better constructs for t=
his using dataframes/datasets/nested data...=C2=A0</div><div><br></div><div=
>Feel free to submit a PR.</div><div><br></div><div>Kind regards,<br></div>=
<div class=3D"gmail_extra"><div><div><div dir=3D"ltr"><div dir=3D"ltr"><div=
><br></div><div>Herman van H=C3=B6vell</div></div></div></div></div><div><d=
iv>
<br><div class=3D"gmail_quote">2016-03-01 15:16 GMT+01:00 Jerry Lam <span d=
ir=3D"ltr">&lt;<a href=3D"mailto:chilinglam@gmail.com" target=3D"_blank">ch=
ilinglam@gmail.com</a>&gt;</span>:<br><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div d=
ir=3D"ltr">Hi Spark developers,<div><br></div><div>Will you consider to add=
 support for implementing &quot;Pattern matching in sequences of rows&quot;=
? More specifically, I&#39;m referring to this:=C2=A0<a href=3D"http://web.=
cs.ucla.edu/classes/fall15/cs240A/notes/temporal/row-pattern-recogniton-11.=
pdf" target=3D"_blank">http://web.cs.ucla.edu/classes/fall15/cs240A/notes/t=
emporal/row-pattern-recogniton-11.pdf</a></div><div><br></div><div>This is =
a very cool/useful feature to pattern matching over live stream/archived da=
ta. It is sorted of related to machine learning because this is usually use=
d in clickstream analysis or path analysis. Also it is related to streaming=
 because of the nature of the processing (time series data mostly). It is S=
QL because there is a good way to express and optimize the query.=C2=A0</di=
v><div><br></div><div>Best Regards,</div><div><br></div><div>Jerry</div></d=
iv>
</blockquote></div><br></div></div></div></div>
</blockquote></div><br></div>
</div></div></blockquote></div><br></div>
</div></div></blockquote></div><br></div>

--001a114318c88620da052cff89a3--