Realtime Compute for Apache Flink：Queries語句

Flink全託管相容Apache Flink的Queries語句。以下BNF-grammar描述了支援的流批SQL特性的超集。

query:
    values
  | WITH withItem [ , withItem ]* query
  | {
        select
      | selectWithoutFrom
      | query UNION [ ALL ] query
      | query EXCEPT query
      | query INTERSECT query
    }
    [ ORDER BY orderItem [, orderItem ]* ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY]

withItem:
    name
    [ '(' column [, column ]* ')' ]
    AS '(' query ')'

orderItem:
    expression [ ASC | DESC ]

select:
    SELECT [ ALL | DISTINCT ]
    { * | projectItem [, projectItem ]* }
    FROM tableExpression
    [ WHERE booleanExpression ]
    [ GROUP BY { groupItem [, groupItem ]* } ]
    [ HAVING booleanExpression ]
    [ WINDOW windowName AS windowSpec [, windowName AS windowSpec ]* ]

selectWithoutFrom:
    SELECT [ ALL | DISTINCT ]
    { * | projectItem [, projectItem ]* }

projectItem:
    expression [ [ AS ] columnAlias ]
  | tableAlias . *

tableExpression:
    tableReference [, tableReference ]*
  | tableExpression [ NATURAL ] [ LEFT | RIGHT | FULL ] JOIN tableExpression [ joinCondition ]

joinCondition:
    ON booleanExpression
  | USING '(' column [, column ]* ')'

tableReference:
    tablePrimary
    [ matchRecognize ]
    [ [ AS ] alias [ '(' columnAlias [, columnAlias ]* ')' ] ]

tablePrimary:
    [ TABLE ] tablePath [ dynamicTableOptions ] [systemTimePeriod] [[AS] correlationName]
  | LATERAL TABLE '(' functionName '(' expression [, expression ]* ')' ')'
  | [ LATERAL ] '(' query ')'
  | UNNEST '(' expression ')'

tablePath:
    [ [ catalogName . ] databaseName . ] tableName

systemTimePeriod:
    FOR SYSTEM_TIME AS OF dateTimeExpression

dynamicTableOptions:
    /*+ OPTIONS(key=val [, key=val]*) */

key:
    stringLiteral

val:
    stringLiteral

values:
    VALUES expression [, expression ]*

groupItem:
    expression
  | '(' ')'
  | '(' expression [, expression ]* ')'
  | CUBE '(' expression [, expression ]* ')'
  | ROLLUP '(' expression [, expression ]* ')'
  | GROUPING SETS '(' groupItem [, groupItem ]* ')'

windowRef:
    windowName
  | windowSpec

windowSpec:
    [ windowName ]
    '('
    [ ORDER BY orderItem [, orderItem ]* ]
    [ PARTITION BY expression [, expression ]* ]
    [
        RANGE numericOrIntervalExpression {PRECEDING}
      | ROWS numericExpression {PRECEDING}
    ]
    ')'

matchRecognize:
    MATCH_RECOGNIZE '('
    [ PARTITION BY expression [, expression ]* ]
    [ ORDER BY orderItem [, orderItem ]* ]
    [ MEASURES measureColumn [, measureColumn ]* ]
    [ ONE ROW PER MATCH ]
    [ AFTER MATCH
      ( SKIP TO NEXT ROW
      | SKIP PAST LAST ROW
      | SKIP TO FIRST variable
      | SKIP TO LAST variable
      | SKIP TO variable )
    ]
    PATTERN '(' pattern ')'
    [ WITHIN intervalLiteral ]
    DEFINE variable AS condition [, variable AS condition ]*
    ')'

measureColumn:
    expression AS alias

pattern:
    patternTerm [ '|' patternTerm ]*

patternTerm:
    patternFactor [ patternFactor ]*

patternFactor:
    variable [ patternQuantifier ]

patternQuantifier:
    '*'
  | '*?'
  | '+'
  | '+?'
  | '?'
  | '??'
  | '{' { [ minRepeat ], [ maxRepeat ] } '}' ['?']
  | '{' repeat '}'

標識符

對於標識符（表名，列名，函數名），Flink 採用了和Java相似的文法策略：

• 不管標識符是否被反引號標識，該標識符是大小寫敏感的。

• 標識符的匹配是大小寫敏感的。

和Java不同的是，Flink SQL支援標識符包含非英文或數位字元，例如，以下是符合標準的。

SELECT a AS `my field` FROM t

字串常量

Flink SQL使用單引號來表示字串常量，而非使用雙引號來表示，例如：

SELECT 'Hello World' 

為了在字串表示單引號，您可以使用兩個單引號來轉義。例如：

Flink SQL> SELECT 'Hello World', 'It''s me';
+-------------+---------+
|      EXPR$0 |  EXPR$1 |
+-------------+---------+
| Hello World | It's me |
+-------------+---------+
1 row in set

Flink SQL支援在字串常量中包含unicode值，您可以通過以下方式聲明：

• 使用反斜線作為預設轉義符

  SELECT U&'\263A' 

• 使用自訂符號作為轉義符

  SELECT U&'#263A' UESCAPE '#' -- 使用'#'作為轉義符

Apache Flink V1.15 Queries語句詳情如下表所示。

Query操作運行時資訊說明

在流模式下，我們根據是否包含更新訊息將處理的流資料分為更新流（包含更新訊息）和非更新流（只包含INSERT類型訊息的稱為非更新流），例如CDC源就是Flink整合自外部的更新流，另外Query內部的一些操作也可能產生更新資料，如分組彙總（Group Aggregation）、Top-N計算等。能產生更新事件的操作通常會使用狀態（State），我們一般將這類操作稱為狀態運算元。值得注意的是，並非所有的狀態運算元都支援處理更新流，例如，Over彙總（Over Aggregation）和Interval Join目前還不支援將更新流作為輸入。

以下表格資訊基於VVR-6.0.x 及以上版本整理，包括了Query操作對應的運行時運算元名稱、運算元是否使用了狀態（State）、是否支援處理更新流、是否產生更新。