alpinegizmo commented on a change in pull request #7070: [FLINK-10625] Documentation for MATCH_RECOGNIZE clause URL: https://github.com/apache/flink/pull/7070#discussion_r233896842
########## File path: docs/dev/table/streaming/match_recognize.md ########## @@ -0,0 +1,843 @@ +--- +title: 'Detecting Patterns in Tables' +nav-parent_id: streaming_tableapi +nav-title: 'Detecting Patterns' +nav-pos: 5 +is_beta: true +--- +<!-- +Licensed to the Apache Software Foundation (ASF) under one +or more contributor license agreements. See the NOTICE file +distributed with this work for additional information +regarding copyright ownership. The ASF licenses this file +to you under the Apache License, Version 2.0 (the +"License"); you may not use this file except in compliance +with the License. You may obtain a copy of the License at + + http://www.apache.org/licenses/LICENSE-2.0 + +Unless required by applicable law or agreed to in writing, +software distributed under the License is distributed on an +"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY +KIND, either express or implied. See the License for the +specific language governing permissions and limitations +under the License. +--> + +It is a common use-case to search for a set of event patterns, especially in case of data streams. Flink +comes with a [complex event processing (CEP) library]({{ site.baseurl }}/dev/libs/cep.html) which allows for pattern detection in event streams. Furthermore, Flink's +SQL API provides a relational way of expressing queries with a large set of built-in functions and rule-based optimizations that can be used out of the box. + +In December 2016, the International Organization for Standardization (ISO) released a new version of the SQL standard which includes _Row Pattern Recognition in SQL_ ([ISO/IEC TR 19075-5:2016](https://standards.iso.org/ittf/PubliclyAvailableStandards/c065143_ISO_IEC_TR_19075-5_2016.zip)). It allows Flink to consolidate CEP and SQL API using the `MATCH_RECOGNIZE` clause for complex event processing in SQL. + +A `MATCH_RECOGNIZE` clause enables the following tasks: +* Logically partition and order the data that is used with `PARTITION BY` and `ORDER BY` clauses. +* Define patterns of rows to seek using the `PATTERN` clause. These patterns use a syntax similar to that of regular expressions. +* Specify logical conditions required to map a row to a row pattern variable in the `DEFINE` clause. +* Define measures, which are expressions usable in other parts of the SQL query, in the `MEASURES` clause. + +The following example illustates the syntax for basic pattern recognition: + +{% highlight sql %} +SELECT T.aid, T.bid, T.cid +FROM MyTable +MATCH_RECOGNIZE ( + PARTITION BY userid + ORDER BY proctime + MEASURES + A.id AS aid, + B.id AS bid, + C.id AS cid + PATTERN (A B C) + DEFINE + A AS name = 'a', + B AS name = 'b', + C AS name = 'c' +) AS T +{% endhighlight %} + +This page will explain each keyword in more detail and will illustrate more complex examples. + +<span class="label label-danger">Attention</span> The `MATCH_RECOGNIZE` clause implementation in Flink is just a subset of the bigger pattern recognition standard. Only the features that are documented in the following sections are supported so far. Since the development is still in an early phase, please also take a look at the [known limitations](#known-limitations). + +* This will be replaced by the TOC +{:toc} + +Introduction and Examples +------------------------- + +### Installation Guide + +The pattern recognition feature uses the Apache Flink's CEP library internally. In order to be able to use the `MATCH_RECOGNIZE` clause, +the library needs to be added as a dependency to your Maven project. + +{% highlight xml %} +<dependency> + <groupId>org.apache.flink</groupId> + <artifactId>flink-cep{{ site.scala_version_suffix }}</artifactId> + <version>{{ site.version }}</version> +</dependency> +{% endhighlight %} + +Alternatively, you can also add the dependency to the cluster classpath (see the [dependency section]({{ site.baseurl}}/projectsetup/dependencies.html) for more information). + +If you want to use the `MATCH_RECOGNIZE` clause in the [SQL Client]({{ site.baseurl}}/dev/table/sqlClient.html), +you don't have to do anything as all the dependencies are included by default. + +### SQL Semantics + +Every `MATCH_RECOGNIZE` query consists of the following clauses: + +* [PARTITION BY](#partitioning) - defines the logical partitioning of the table; similar to a `GROUP BY` operation. +* [ORDER BY](#order-of-events) - specifies how the incoming rows should be ordered; this is essential as patterns depend on an order. +* [MEASURES](#define--measures) - defines output of the clause; similar to a `SELECT` clause. +* [ONE ROW PER MATCH](#output-mode) - output mode which defines how many rows per match should be produced. +* [AFTER MATCH SKIP](#after-match-strategy) - allows to specify where the next match should start; this is also a way to control how many distinct matches a single event can belong to. +* [PATTERN](#defining-pattern) - allows constructing patterns that will be searched for using a _regular expression_-like syntax. +* [DEFINE](#define--measures) - this section defines conditions on rows that should be met in order to be qualified to the corresponding pattern variable. + +<span class="label label-danger">Attention</span> Currently, the `MATCH_RECOGNIZE` clause can only be applied to an [append table](dynamic_tables.html#update-and-append-queries). Furthermore, it always produces +an append table as well. + +### Examples + +For our examples, we assume that a table `Ticker` has been registered. The table contains prices of stocks at a particular point in time. Each row represents an updated characteristic of the ticker. Review comment: I'm not sure what "Each row represents an updated characteristic of the ticker." is trying to say. Does this mean that each row updates one or more fields, and there are no duplicate rows? Or that only one field can be updated at a time? ---------------------------------------------------------------- This is an automated message from the Apache Git Service. To respond to the message, please log on GitHub and use the URL above to go to the specific comment. For queries about this service, please contact Infrastructure at: us...@infra.apache.org With regards, Apache Git Services
