SQL簡述

SQL是Structured Query Language的縮寫，最初是由美國電腦科學家Donald D. Chamberlin和Raymond F. Boyce在20世紀70年代早期從 Early History of SQL 中瞭解關係模型後在IBM開發的。該版本最初稱為[SEQUEL: A Structured English Query Language]（結構化英語查詢語言），旨在操縱和檢索儲存在IBM原始準關聯式資料庫管理系統System R中的資料。SEQUEL後來改為SQL，因為“SEQUEL”是英國Hawker Siddeley飛機公司的商標。我們看看這款用於特技飛行的英國皇家空軍豪客Siddeley Hawk T.1A (Looks great):

第一款SQL資料庫

在20世紀70年代後期，Oracle公司(當時叫 Relational Software，Inc.)開發了基於SQL的RDBMS，並希望將其出售給美國海軍，Central Intelligence代理商和其他美國政府機構。 1979年6月，Oracle 公司為VAX計算機推出了第一個商業化的SQL實現，即Oracle V2。

ANSI-SQL標準的採用

直到1986年，ANSI和ISO標準組正式採用了標準的"資料庫語言SQL"語言定義。該標準的新版本釋出於1989,1992,1996,1999,2003,2006,2008,2011，以及最近的2016。Apache Flink SQL 核心運算元的語義設計也參考了1992 、2011等ANSI-SQL標準。

SQL操作及擴充套件

SQL是專為查詢包含在關聯式資料庫中的資料而設計的，是一種基於SET操作的宣告性程式語言，而不是像C語言一樣的指令式程式設計語言。但是，各大關聯式資料庫廠商在遵循ANSI-SQL標準的同時又對標準SQL進行擴充套件，由基於SET(無重複元素)的操作擴充套件到基於BAG(有重複元素)的操作，並且新增了過程程式語言功能，如：Oracle的PL/SQL, DB2的SQL PL，MySQL - SQL/PSM以及SQL Server的T-SQL等等。
隨著時間的推移ANSI-SQL規範不斷完善，所涉及的功能不斷豐富，比如在ANSI-2011中又增加了Temporal Table的標準定義，Temporal Table的標準在結構化關係資料儲存上新增了時間維度資訊，這使得關聯式資料庫中不僅可以對當前資料進行查詢操作，根據時間版本資訊也可以對歷史資料進行操作。這些不斷豐富的功能極大增強了SQL的應用領域。

大資料計算領域對SQL的應用

離線計算（批計算）

提及大資料計算領域不得不說MapReduce計算模型，MapReduce最早是由Google公司研究提出的一種面向大規模資料處理的平行計算模型和方法，併發於2004年發表了論文Simplified Data Processing on Large Clusters。
論文發表之後Apache 開源社群參考Google MapReduce，基於Java設計開發了一個稱為Hadoop的開源MapReduce平行計算框架。很快得到了全球學術界和工業界的普遍關注，並得到推廣和普及應用。
但利用Hadoop進行MapReduce的開發，需要開發人員精通Java語言，並瞭解MapReduce的執行原理，這樣在一定程度上提高了MapReduce的開發門檻，所以在開源社群又不斷湧現了一些為了簡化MapReduce開發的開源框架，其中Hive就是典型的代表。HSQL可以讓使用者以類SQL的方式描述MapReduce計算，比如原本需要幾十行，甚至上百行才能完成的wordCount，使用者一條SQL語句就能完成了，這樣極大的降低了MapReduce的開發門檻，進而也成功的將SQL應用到了大資料計算領域當中來。

實時計算（流計算）

SQL不僅僅被成功的應用到了離線計算，SQL的易用性也吸引了流計算產品，目前最熱的Spark，Flink也紛紛支援了SQL，尤其是Flink支援的更加徹底，整合了Calcite，完全遵循ANSI-SQL標準。Apache Flink在low-level API上面用DataSet支援批計算，用DataStream支援流計算，但在High-Level API上面利用SQL將流與批進行了統一，使得使用者編寫一次SQL既可以在流計算中使用，又可以在批計算中使用，為既有流計算業務，又有批計算業務的使用者節省了大量開發成本。

SQL高效能與簡潔性

效能

SQL經過傳統資料庫領域幾十年的不斷打磨，查詢優化器已經能夠極大的優化SQL的查詢效能，Apache Flink 應用Calcite進行查詢優化，複用了大量資料庫查詢優化規則，在效能上不斷追求極致，能夠讓使用者關心但不用擔心效能問題。如下圖(Alibaba 對 Apache Flink 進行架構優化後的元件棧）

相對於DataStream而言，SQL會經過Optimization模組透明的為使用者進行查詢優化，使用者專心編寫自己的業務邏輯，不用擔心效能，卻能得到最優的查詢效能!

簡潔

就簡潔性而言，SQL與DataSet和DataStream相比具有很大的優越性，我們先用一個WordCount示例來直觀的檢視使用者的程式碼量：

• DataStream/DataSetAPI

... //省略初始化程式碼
// 核心邏輯
text.flatMap(new WordCount.Tokenizer()).keyBy(new int[]{0}).sum(1);

// flatmap 程式碼定義
public static final class Tokenizer implements FlatMapFunction<String, Tuple2<String, Integer>> {
public Tokenizer() {
}

public void flatMap(String value, Collector<Tuple2<String, Integer>> out) {
String[] tokens = value.toLowerCase().split("\\W+");
String[] var4 = tokens;
int var5 = tokens.length;

for(int var6 = 0; var6 < var5; ++var6) {
String token = var4[var6];
if (token.length() > 0) {
out.collect(new Tuple2(token, 1));
}
}

}
}複製程式碼

• SQL

複製程式碼

...//省略初始化程式碼
SELECT word, COUNT(word) FROM tab GROUP BY word;複製程式碼

我們直觀的體會到相同的統計功能使用SQL的簡潔性。

Flink SQL Job的組成

我們做任何資料計算都離不開讀取原始資料，計算邏輯和寫入計算結果資料三部分，當然基於Apache Flink SQL編寫的計算Job也離不開這三個部分，如下所示：

如上所示，一個完整的Apache Flink SQL Job 由如下三部分：

• Source Operator - Soruce operator是對外部資料來源的抽象, 目前Apache Flink內建了很多常用的資料來源實現，比如上圖提到的Kafka。

• Query Operators - 查詢運算元主要完成如圖的Query Logic，目前支援了Union，Join，Projection,Difference, Intersection以及window等大多數傳統資料庫支援的操作。

• Sink Operator - Sink operator 是對外結果表的抽象，目前Apache Flink也內建了很多常用的結果表的抽象，比如上圖提到的Kafka。

相對於DataStream而言，SQL會經過Optimization模組透明的為使用者進行查詢優化，使用者專心編寫自己的業務邏輯，不用擔心效能，卻能得到最優的查詢效能!

簡潔

就簡潔性而言，SQL與DataSet和DataStream相比具有很大的優越性，我們先用一個WordCount示例來直觀的檢視使用者的程式碼量：

• DataStream/DataSetAPI

... //省略初始化程式碼
// 核心邏輯
text.flatMap(new WordCount.Tokenizer()).keyBy(new int[]{0}).sum(1);

// flatmap 程式碼定義
public static final class Tokenizer implements FlatMapFunction<String, Tuple2<String, Integer>> {
        public Tokenizer() {
        }

        public void flatMap(String value, Collector<Tuple2<String, Integer>> out) {
            String[] tokens = value.toLowerCase().split("\\W+");
            String[] var4 = tokens;
            int var5 = tokens.length;

            for(int var6 = 0; var6 < var5; ++var6) {
                String token = var4[var6];
                if (token.length() > 0) {
                    out.collect(new Tuple2(token, 1));
                }
            }

        }
    }複製程式碼

• SQL

...//省略初始化程式碼
SELECT word, COUNT(word) FROM tab GROUP BY word;複製程式碼

我們直觀的體會到相同的統計功能使用SQL的簡潔性。

Flink SQL Job的組成

我們做任何資料計算都離不開讀取原始資料，計算邏輯和寫入計算結果資料三部分，當然基於Apache Flink SQL編寫的計算Job也離不開這三個部分，如下所示：

如上所示，一個完整的Apache Flink SQL Job 由如下三部分：

• Source Operator - Soruce operator是對外部資料來源的抽象, 目前Apache Flink內建了很多常用的資料來源實現，比如上圖提到的Kafka。
• Query Operators - 查詢運算元主要完成如圖的Query Logic，目前支援了Union，Join，Projection,Difference, Intersection以及window等大多數傳統資料庫支援的操作。
• Sink Operator - Sink operator 是對外結果表的抽象，目前Apache Flink也內建了很多常用的結果表的抽象，比如上圖提到的Kafka。

Flink SQL 核心運算元

目前Flink SQL支援Union，Join，Projection,Difference, Intersection以及Window等大多數傳統資料庫支援的操作，接下來為大家分別進行簡單直觀的介紹。

環境

為了很好的體驗和理解Apache Flink SQL運算元我們需要先準備一下測試環境，我們選擇IDEA，以ITCase測試方式來進行體驗。IDEA 安裝這裡不佔篇幅介紹了，相信大家能輕鬆搞定！我們進行功能體驗有兩種方式，具體如下：

原始碼方式

對於開源愛好者可能更喜歡原始碼方式理解和體驗Apache Flink SQL功能，那麼我們需要下載原始碼並匯入到IDEA中：

• 下載原始碼：

// 下載原始碼
git clone https://github.com/apache/flink.git study
// 進入原始碼目錄
cd study
// 拉取穩定版release-1.6
git fetch origin release-1.6:release-1.6
//切換到穩定版
git checkout release-1.6
//將依賴安裝到本地mvn倉庫，耐心等待需要一段時間
mvn clean install -DskipTests複製程式碼

• 匯入到IDEA
  將Flink原始碼匯入到IDEA過程這裡不再佔用篇幅，匯入後確保在IDEA中可以執行 org.apache.flink.table.runtime.stream.sql.SqlITCase 並測試全部通過，即證明體驗環境已經完成。如下圖所示：

如上圖執行測試後顯示測試通過，我們就可以繼續下面的Apache Flink SQL功能體驗了。

依賴Flink包方式

我們還有一種更簡單直接的方式，就是新建一個mvn專案，並在pom中新增如下依賴：

 <properties>
    <table.version>1.6-SNAPSHOT</table.version>
  </properties>

  <dependencies>
    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-table_2.11</artifactId>
      <version>${table.version}</version>
    </dependency>

    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-scala_2.11</artifactId>
      <version>${table.version}</version>
    </dependency>

    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-streaming-scala_2.11</artifactId>
      <version>${table.version}</version>
    </dependency>

    <dependency>
      <groupId>org.apache.flink</groupId>
      <artifactId>flink-streaming-java_2.11</artifactId>
      <version>${table.version}</version>
    </dependency>

    <dependency>
      <groupId>JUnit</groupId>
      <artifactId>JUnit</artifactId>
      <version>4.12</version>
    </dependency>

  </dependencies>複製程式碼

完成環境準備後，我們開始準備測試資料和寫一個簡單的測試類。

示例資料及測試類

測試資料

• customer_tab 表 - 客戶表儲存客戶id，客戶姓名和客戶描述資訊。欄位及測試資料如下：

• order_tab 表 - 訂單表儲存客戶購買的訂單資訊，包括訂單id，訂單時間和訂單描述資訊。 欄位節測試資料如下：

• Item_tab
  商品表, 攜帶商品id，商品型別，出售時間，價格等資訊，具體如下：

• PageAccess_tab
  頁面訪問表，包含使用者ID，訪問時間，使用者所在地域資訊，具體資料如下：

• PageAccessCount_tab
  頁面訪問表，訪問量，訪問時間，使用者所在地域資訊，具體資料如下：

• PageAccessSession_tab
  頁面訪問表，訪問量，訪問時間，使用者所在地域資訊，具體資料如下：

測試類

我們建立一個SqlOverviewITCase.scala 用於接下來介紹Flink SQL運算元的功能體驗。程式碼如下：

import org.apache.flink.api.scala._
import org.apache.flink.runtime.state.StateBackend
import org.apache.flink.runtime.state.memory.MemoryStateBackend
import org.apache.flink.streaming.api.TimeCharacteristic
import org.apache.flink.streaming.api.functions.sink.RichSinkFunction
import org.apache.flink.streaming.api.functions.source.SourceFunction
import org.apache.flink.streaming.api.functions.source.SourceFunction.SourceContext
import org.apache.flink.streaming.api.scala.StreamExecutionEnvironment
import org.apache.flink.streaming.api.watermark.Watermark
import org.apache.flink.table.api.TableEnvironment
import org.apache.flink.table.api.scala._
import org.apache.flink.types.Row
import org.junit.rules.TemporaryFolder
import org.junit.{Rule, Test}

import scala.collection.mutable
import scala.collection.mutable.ArrayBuffer

class SqlOverviewITCase {
  val _tempFolder = new TemporaryFolder

  @Rule
  def tempFolder: TemporaryFolder = _tempFolder

  def getStateBackend: StateBackend = {
    new MemoryStateBackend()
  }

  // 客戶表資料
  val customer_data = new mutable.MutableList[(String, String, String)]
  customer_data.+=(("c_001", "Kevin", "from JinLin"))
  customer_data.+=(("c_002", "Sunny", "from JinLin"))
  customer_data.+=(("c_003", "JinCheng", "from HeBei"))


  // 訂單表資料
  val order_data = new mutable.MutableList[(String, String, String, String)]
  order_data.+=(("o_001", "c_002", "2018-11-05 10:01:01", "iphone"))
  order_data.+=(("o_002", "c_001", "2018-11-05 10:01:55", "ipad"))
  order_data.+=(("o_003", "c_001", "2018-11-05 10:03:44", "flink book"))

  // 商品銷售表資料
  val item_data = Seq(
    Left((1510365660000L, (1510365660000L, 20, "ITEM001", "Electronic"))),
    Right((1510365660000L)),
    Left((1510365720000L, (1510365720000L, 50, "ITEM002", "Electronic"))),
    Right((1510365720000L)),
    Left((1510365780000L, (1510365780000L, 30, "ITEM003", "Electronic"))),
    Left((1510365780000L, (1510365780000L, 60, "ITEM004", "Electronic"))),
    Right((1510365780000L)),
    Left((1510365900000L, (1510365900000L, 40, "ITEM005", "Electronic"))),
    Right((1510365900000L)),
    Left((1510365960000L, (1510365960000L, 20, "ITEM006", "Electronic"))),
    Right((1510365960000L)),
    Left((1510366020000L, (1510366020000L, 70, "ITEM007", "Electronic"))),
    Right((1510366020000L)),
    Left((1510366080000L, (1510366080000L, 20, "ITEM008", "Clothes"))),
    Right((151036608000L)))

  // 頁面訪問表資料
  val pageAccess_data = Seq(
    Left((1510365660000L, (1510365660000L, "ShangHai", "U0010"))),
    Right((1510365660000L)),
    Left((1510365660000L, (1510365660000L, "BeiJing", "U1001"))),
    Right((1510365660000L)),
    Left((1510366200000L, (1510366200000L, "BeiJing", "U2032"))),
    Right((1510366200000L)),
    Left((1510366260000L, (1510366260000L, "BeiJing", "U1100"))),
    Right((1510366260000L)),
    Left((1510373400000L, (1510373400000L, "ShangHai", "U0011"))),
    Right((1510373400000L)))

  // 頁面訪問量表資料2
  val pageAccessCount_data = Seq(
    Left((1510365660000L, (1510365660000L, "ShangHai", 100))),
    Right((1510365660000L)),
    Left((1510365660000L, (1510365660000L, "BeiJing", 86))),
    Right((1510365660000L)),
    Left((1510365960000L, (1510365960000L, "BeiJing", 210))),
    Right((1510366200000L)),
    Left((1510366200000L, (1510366200000L, "BeiJing", 33))),
    Right((1510366200000L)),
    Left((1510373400000L, (1510373400000L, "ShangHai", 129))),
    Right((1510373400000L)))

  // 頁面訪問表資料3
  val pageAccessSession_data = Seq(
    Left((1510365660000L, (1510365660000L, "ShangHai", "U0011"))),
    Right((1510365660000L)),
    Left((1510365720000L, (1510365720000L, "ShangHai", "U0012"))),
    Right((1510365720000L)),
    Left((1510365720000L, (1510365720000L, "ShangHai", "U0013"))),
    Right((1510365720000L)),
    Left((1510365900000L, (1510365900000L, "ShangHai", "U0015"))),
    Right((1510365900000L)),
    Left((1510366200000L, (1510366200000L, "ShangHai", "U0011"))),
    Right((1510366200000L)),
    Left((1510366200000L, (1510366200000L, "BeiJing", "U2010"))),
    Right((1510366200000L)),
    Left((1510366260000L, (1510366260000L, "ShangHai", "U0011"))),
    Right((1510366260000L)),
    Left((1510373760000L, (1510373760000L, "ShangHai", "U0410"))),
    Right((1510373760000L)))

  def procTimePrint(sql: String): Unit = {
    // Streaming 環境
    val env = StreamExecutionEnvironment.getExecutionEnvironment
    val tEnv = TableEnvironment.getTableEnvironment(env)

    // 將order_tab, customer_tab 註冊到catalog
    val customer = env.fromCollection(customer_data).toTable(tEnv).as('c_id, 'c_name, 'c_desc)
    val order = env.fromCollection(order_data).toTable(tEnv).as('o_id, 'c_id, 'o_time, 'o_desc)

    tEnv.registerTable("order_tab", order)
    tEnv.registerTable("customer_tab", customer)

    val result = tEnv.sqlQuery(sql).toRetractStream[Row]
    val sink = new RetractingSink
    result.addSink(sink)
    env.execute()
  }

  def rowTimePrint(sql: String): Unit = {
    // Streaming 環境
    val env = StreamExecutionEnvironment.getExecutionEnvironment
    env.setStreamTimeCharacteristic(TimeCharacteristic.EventTime)
    env.setStateBackend(getStateBackend)
    env.setParallelism(1)
    val tEnv = TableEnvironment.getTableEnvironment(env)

    // 將item_tab, pageAccess_tab 註冊到catalog
    val item =
      env.addSource(new EventTimeSourceFunction[(Long, Int, String, String)](item_data))
      .toTable(tEnv, 'onSellTime, 'price, 'itemID, 'itemType, 'rowtime.rowtime)

    val pageAccess =
      env.addSource(new EventTimeSourceFunction[(Long, String, String)](pageAccess_data))
      .toTable(tEnv, 'accessTime, 'region, 'userId, 'rowtime.rowtime)

    val pageAccessCount =
      env.addSource(new EventTimeSourceFunction[(Long, String, Int)](pageAccessCount_data))
      .toTable(tEnv, 'accessTime, 'region, 'accessCount, 'rowtime.rowtime)

    val pageAccessSession =
      env.addSource(new EventTimeSourceFunction[(Long, String, String)](pageAccessSession_data))
      .toTable(tEnv, 'accessTime, 'region, 'userId, 'rowtime.rowtime)

    tEnv.registerTable("item_tab", item)
    tEnv.registerTable("pageAccess_tab", pageAccess)
    tEnv.registerTable("pageAccessCount_tab", pageAccessCount)
    tEnv.registerTable("pageAccessSession_tab", pageAccessSession)

    val result = tEnv.sqlQuery(sql).toRetractStream[Row]
    val sink = new RetractingSink
    result.addSink(sink)
    env.execute()

  }

  @Test
  def testSelect(): Unit = {
    val sql = "替換想要測試的SQL"
    // 非window 相關用 procTimePrint(sql)
    // Window 相關用 rowTimePrint(sql)
  }

}

// 自定義Sink
final class RetractingSink extends RichSinkFunction[(Boolean, Row)] {
  var retractedResults: ArrayBuffer[String] = mutable.ArrayBuffer.empty[String]

  def invoke(v: (Boolean, Row)) {
    retractedResults.synchronized {
      val value = v._2.toString
      if (v._1) {
        retractedResults += value
      } else {
        val idx = retractedResults.indexOf(value)
        if (idx >= 0) {
          retractedResults.remove(idx)
        } else {
          throw new RuntimeException("Tried to retract a value that wasn't added first. " +
                                       "This is probably an incorrectly implemented test. " +
                                       "Try to set the parallelism of the sink to 1.")
        }
      }
    }
    retractedResults.sorted.foreach(println(_))
  }
}

// Water mark 生成器
class EventTimeSourceFunction[T](
  dataWithTimestampList: Seq[Either[(Long, T), Long]]) extends SourceFunction[T] {
  override def run(ctx: SourceContext[T]): Unit = {
    dataWithTimestampList.foreach {
      case Left(t) => ctx.collectWithTimestamp(t._2, t._1)
      case Right(w) => ctx.emitWatermark(new Watermark(w))
    }
  }

  override def cancel(): Unit = ???
}複製程式碼

Select

SELECT 用於從資料集/流中選擇資料，語法遵循ANSI-SQL標準，語義是關係代數中的投影(Projection),對關係進行垂直分割，消去某些列, 如下圖所示:

SQL 示例

從customer_tab選擇使用者姓名，並用內建的CONCAT函式拼接客戶資訊，如下：

SELECT c_name, CONCAT(c_name, ' come ', c_desc) as desc  FROM customer_tab;複製程式碼

Result

特別說明

大家看到在 SELECT 不僅可以使用普通的欄位選擇，還可以使用ScalarFunction,當然也包括User-Defined Function，同時還可以進行欄位的alias設定。其實SELECT可以結合聚合，在GROUPBY部分會進行介紹,一個比較特殊的使用場景是攜帶 DISTINCT 關鍵字，示例如下：

SQL 示例

在訂單表查詢所有的客戶id，消除重複客戶id, 如下：

SELECT DISTINCT c_id FROM order_tab;複製程式碼

Result

WHERE

WHERE 用於從資料集/流中過濾資料，與SELECT一起使用，語法遵循ANSI-SQL標準，語義是關係代數的Selection，根據某些條件對關係做水平分割，即選擇符合條件的記錄，如下所示：

SQL 示例

在customer_tab查詢客戶id為c_001和c_003的客戶資訊，如下：

SELECT c_id, c_name, c_desc FROM customer_tab WHERE c_id = 'c_001' OR c_id = 'c_003';複製程式碼

Result

特別說明

我們發現WHERE是對滿足一定條件的資料進行過濾，WHERE支援=, <, >, <>, >=, <=以及AND， OR等表示式的組合，最終滿足過濾條件的資料會被選擇出來。並且 WHERE 可以結合IN,NOT IN聯合使用，具體如下：

SQL 示例 (IN 常量)

使用 IN 在customer_tab查詢客戶id為c_001和c_003的客戶資訊，如下：

SELECT c_id, c_name, c_desc FROM customer_tab WHERE c_id IN ('c_001', 'c_003');複製程式碼

Result

SQL 示例 (IN 子查詢)

使用 IN和 子查詢 在customer_tab查詢已經下過訂單的客戶資訊，如下：

SELECT c_id, c_name, c_desc FROM customer_tab WHERE c_id IN (SELECT c_id FROM order_tab);複製程式碼

Result

IN/NOT IN 與關係代數

如上介紹IN是關係代數中的Intersection， NOT IN是關係代數的Difference， 如下圖示意：

• IN(Intersection)
  
• NOT IN(Difference)
  

GROUP BY

GROUP BY 是對資料進行分組的操作，比如我需要分別計算一下一個學生表裡面女生和男生的人數分別是多少，如下：

SQL 示例

將order_tab資訊按customer_tab分組統計訂單數量，簡單示例如下：

SELECT c_id, count(o_id) as o_count FROM order_tab GROUP BY c_id;複製程式碼

Result

特別說明

在實際的業務場景中，GROUP BY除了按業務欄位進行分組外，很多時候使用者也可以用時間來進行分組(相當於劃分視窗)，比如統計每分鐘的訂單數量：

SQL 示例

按時間進行分組，查詢每分鐘的訂單數量，如下：

   SELECT SUBSTRING(o_time, 1, 16) AS o_time_min, count(o_id) AS o_count FROM order_tab GROUP BY SUBSTRING(o_time, 1, 16)複製程式碼

Result

說明：如果我們時間欄位是timestamp型別，建議使用內建的 DATE_FORMAT 函式。

UNION ALL

UNION ALL 將兩個表合併起來，要求兩個表的欄位完全一致，包括欄位型別、欄位順序,語義對應關係代數的Union，只是關係代數是Set集合操作，會有去重複操作，UNION ALL 不進行去重，如下所示：

SQL 示例

我們簡單的將customer_tab查詢2次，將查詢結果合併起來，如下：

SELECT c_id, c_name, c_desc  FROM customer_tab 
UNION ALL 
SELECT c_id, c_name, c_desc  FROM customer_tab複製程式碼

Result

特別說明

UNION ALL 對結果資料不進行去重，如果想對結果資料進行去重，傳統資料庫需要進行UNION操作。

UNION

UNION 將兩個流給合併起來，要求兩個流的欄位完全一致，包括欄位型別、欄位順序，並其UNION 不同於UNION ALL，UNION會對結果資料去重,與關係代數的Union語義一致，如下：

SQL 示例

我們簡單的將customer_tab查詢2次，將查詢結果合併起來，如下：

SELECT c_id, c_name, c_desc  FROM customer_tab 
UNION 
SELECT c_id, c_name, c_desc  FROM customer_tab複製程式碼

我們發現完全一樣的表資料進行 UNION之後，資料是被去重的，UNION之後的資料並沒有增加。

Result

特別說明

UNION 對結果資料進行去重，在實際的實現過程需要對資料進行排序操作，所以非必要去重情況請使用UNION ALL操作。

JOIN

JOIN 用於把來自兩個表的行聯合起來形成一個寬表，Apache Flink支援的JOIN型別：

• JOIN - INNER JOIN
• LEFT JOIN - LEFT OUTER JOIN
• RIGHT JOIN - RIGHT OUTER JOIN
• FULL JOIN - FULL OUTER JOIN

JOIN與關係代數的Join語義相同，具體如下：

SQL 示例 (JOIN)

INNER JOIN只選擇滿足ON條件的記錄，我們查詢customer_tab 和 order_tab表，將有訂單的客戶和訂單資訊選擇出來，如下：

SELECT * FROM customer_tab AS c JOIN order_tab AS o ON o.c_id = c.c_id複製程式碼

Result

SQL 示例 (LEFT JOIN)

LEFT JOIN與INNER JOIN的區別是當右表沒有與左邊相JOIN的資料時候，右邊對應的欄位補NULL輸出，語義如下：

對應的SQL語句如下(LEFT JOIN)：

SELECT ColA, ColB, T2.ColC, ColE FROM TI LEFT JOIN T2 ON T1.ColC = T2.ColC ; 複製程式碼

• 細心的讀者可能發現上面T2.ColC是新增了字首T2了，這裡需要說明一下，當兩張表有欄位名字一樣的時候，我需要指定是從那個表裡面投影的。

我們查詢customer_tab 和 order_tab表，將客戶和訂單資訊選擇出來如下：

SELECT * FROM customer_tab AS c LEFT JOIN order_tab AS o ON o.c_id = c.c_id複製程式碼

Result

特別說明

RIGHT JOIN 相當於 LEFT JOIN 左右兩個表互動一下位置。FULL JOIN相當於 RIGHT JOIN 和 LEFT JOIN 之後進行UNION ALL操作。

Window

在Apache Flink中有2種型別的Window，一種是OverWindow，即傳統資料庫的標準開窗，每一個元素都對應一個視窗。一種是GroupWindow，目前在SQL中GroupWindow都是基於時間進行視窗劃分的。

Over Window

Apache Flink中對OVER Window的定義遵循標準SQL的定義語法。
按ROWS和RANGE分類是傳統資料庫的標準分類方法，在Apache Flink中還可以根據時間型別(ProcTime/EventTime)和視窗的有限和無限(Bounded/UnBounded)進行分類，共計8種型別。為了避免大家對過細分類造成困擾，我們按照確定當前行的不同方式將OVER Window分成兩大類進行介紹，如下:

• ROWS OVER Window - 每一行元素都視為新的計算行，即，每一行都是一個新的視窗。
• RANGE OVER Window - 具有相同時間值的所有元素行視為同一計算行，即，具有相同時間值的所有行都是同一個視窗。

Bounded ROWS OVER Window

Bounded ROWS OVER Window 每一行元素都視為新的計算行，即，每一行都是一個新的視窗。

語義

我們以3個元素(2 PRECEDING)的視窗為例，如下圖:

上圖所示視窗 user 1 的 w5和w6， user 2的 視窗 w2 和 w3，雖然有元素都是同一時刻到達，但是他們仍然是在不同的視窗，這一點有別於RANGE OVER Window。

語法

Bounded ROWS OVER Window 語法如下：

SELECT 
    agg1(col1) OVER(
     [PARTITION BY (value_expression1,..., value_expressionN)] 
     ORDER BY timeCol
     ROWS 
     BETWEEN (UNBOUNDED | rowCount) PRECEDING AND CURRENT ROW) AS colName, 
... 
FROM Tab1複製程式碼

• value_expression - 進行分割槽的字表示式；
• timeCol - 用於元素排序的時間欄位；
• rowCount - 是定義根據當前行開始向前追溯幾行元素。

SQL 示例

利用item_tab測試資料，我們統計同類商品中當前和當前商品之前2個商品中的最高價格。

SELECT  
    itemID,
    itemType, 
    onSellTime, 
    price,  
    MAX(price) OVER (
        PARTITION BY itemType 
        ORDER BY onSellTime 
        ROWS BETWEEN 2 preceding AND CURRENT ROW) AS maxPrice
  FROM item_tab複製程式碼

Result

Bounded RANGE OVER Window

Bounded RANGE OVER Window 具有相同時間值的所有元素行視為同一計算行，即，具有相同時間值的所有行都是同一個視窗。

語義

我們以3秒中資料(INTERVAL '2' SECOND)的視窗為例，如下圖：

注意: 上圖所示視窗 user 1 的 w6， user 2的 視窗 w3，元素都是同一時刻到達,他們是在同一個視窗，這一點有別於ROWS OVER Window。

語法

Bounded RANGE OVER Window的語法如下：

SELECT 
    agg1(col1) OVER(
     [PARTITION BY (value_expression1,..., value_expressionN)] 
     ORDER BY timeCol
     RANGE 
     BETWEEN (UNBOUNDED | timeInterval) PRECEDING AND CURRENT ROW) AS colName, 
... 
FROM Tab1複製程式碼

• value_expression - 進行分割槽的字表示式；
• timeCol - 用於元素排序的時間欄位；
• timeInterval - 是定義根據當前行開始向前追溯指定時間的元素行；

SQL 示例

我們統計同類商品中當前和當前商品之前2分鐘商品中的最高價格。

SELECT  
    itemID,
    itemType, 
    onSellTime, 
    price,  
    MAX(price) OVER (
        PARTITION BY itemType 
        ORDER BY rowtime 
        RANGE BETWEEN INTERVAL '2' MINUTE preceding AND CURRENT ROW) AS maxPrice
  FROM item_tab複製程式碼

Result（Bounded RANGE OVER Window）

特別說明

OverWindow最重要是要理解每一行資料都確定一個視窗，同時目前在Apache Flink中只支援按時間欄位排序。並且OverWindow開窗與GroupBy方式資料分組最大的不同在於，GroupBy資料分組統計時候，在SELECT中除了GROUP BY的key，不能直接選擇其他非key的欄位，但是OverWindow沒有這個限制，SELECT可以選擇任何欄位。比如一張表table(a,b,c,d)4個欄位，如果按d分組求c的最大值，兩種寫完如下:

• GROUP BY - SELECT d, MAX(c) FROM table GROUP BY d
• OVER Window = SELECT a, b, c, d, MAX(c) OVER(PARTITION BY d, ORDER BY ProcTime())
  如上 OVER Window 雖然PARTITION BY d,但SELECT 中仍然可以選擇 a,b,c欄位。但在GROUPBY中，SELECT 只能選擇 d 欄位。

Group Window

根據視窗資料劃分的不同，目前Apache Flink有如下3種Bounded Winodw:

• Tumble - 滾動視窗，視窗資料有固定的大小，視窗資料無疊加；
• Hop - 滑動視窗，視窗資料有固定大小，並且有固定的視窗重建頻率，視窗資料有疊加；
• Session - 會話視窗，視窗資料沒有固定的大小，根據視窗資料活躍程度劃分視窗，視窗資料無疊加。

說明： Aapche Flink 還支援UnBounded的 Group Window，也就是全域性Window，流上所有資料都在一個視窗裡面，語義非常簡單，這裡不做詳細介紹了。

Tumble

語義

Tumble 滾動視窗有固定size，視窗資料不重疊,具體語義如下：

語法

Tumble 滾動視窗對應的語法如下：

SELECT 
    [gk],
    [TUMBLE_START(timeCol, size)], 
    [TUMBLE_END(timeCol, size)], 
    agg1(col1), 
    ... 
    aggn(colN)
FROM Tab1
GROUP BY [gk], TUMBLE(timeCol, size)複製程式碼

• [gk] - 決定了流是Keyed還是/Non-Keyed;
• TUMBLE_START - 視窗開始時間;
• TUMBLE_END - 視窗結束時間;
• timeCol - 是流表中表示時間欄位；
• size - 表示視窗的大小，如 秒，分鐘，小時，天。

SQL 示例

利用pageAccess_tab測試資料，我們需要按不同地域統計每2分鐘的淘寶首頁的訪問量(PV)。

SELECT  
    region,
    TUMBLE_START(rowtime, INTERVAL '2' MINUTE) AS winStart,  
    TUMBLE_END(rowtime, INTERVAL '2' MINUTE) AS winEnd,  
    COUNT(region) AS pv
FROM pageAccess_tab 
GROUP BY region, TUMBLE(rowtime, INTERVAL '2' MINUTE)複製程式碼

Result

Hop

Hop 滑動視窗和滾動視窗類似，視窗有固定的size，與滾動視窗不同的是滑動視窗可以通過slide引數控制滑動視窗的新建頻率。因此當slide值小於視窗size的值的時候多個滑動視窗會重疊。

語義

Hop 滑動視窗語義如下所示：

語法

Hop 滑動視窗對應語法如下：

SELECT 
    [gk], 
    [HOP_START(timeCol, slide, size)] ,  
    [HOP_END(timeCol, slide, size)],
    agg1(col1), 
    ... 
    aggN(colN) 
FROM Tab1
GROUP BY [gk], HOP(timeCol, slide, size)複製程式碼

• [gk] 決定了流是Keyed還是/Non-Keyed;
• HOP_START - 視窗開始時間;
• HOP_END - 視窗結束時間;
• timeCol - 是流表中表示時間欄位；
• slide - 是滑動步伐的大小；
• size - 是視窗的大小，如 秒，分鐘，小時，天；

SQL 示例

利用pageAccessCount_tab測試資料，我們需要每5分鐘統計近10分鐘的頁面訪問量(PV).

SELECT  
  HOP_START(rowtime, INTERVAL '5' MINUTE, INTERVAL '10' MINUTE) AS winStart,  
  HOP_END(rowtime, INTERVAL '5' MINUTE, INTERVAL '10' MINUTE) AS winEnd,  
  SUM(accessCount) AS accessCount  
FROM pageAccessCount_tab 
GROUP BY HOP(rowtime, INTERVAL '5' MINUTE, INTERVAL '10' MINUTE)複製程式碼

Result

Session

Seeeion 會話視窗 是沒有固定大小的視窗，通過session的活躍度分組元素。不同於滾動視窗和滑動視窗，會話視窗不重疊,也沒有固定的起止時間。一個會話視窗在一段時間內沒有接收到元素時，即當出現非活躍間隙時關閉。一個會話視窗 分配器通過配置session gap來指定非活躍週期的時長.

語義

Session 會話視窗語義如下所示：

語法

Seeeion 會話視窗對應語法如下：

SELECT 
    [gk], 
    SESSION_START(timeCol, gap) AS winStart,  
    SESSION_END(timeCol, gap) AS winEnd,
    agg1(col1),
     ... 
    aggn(colN)
FROM Tab1
GROUP BY [gk], SESSION(timeCol, gap)複製程式碼

• [gk] 決定了流是Keyed還是/Non-Keyed;
• SESSION_START - 視窗開始時間；
• SESSION_END - 視窗結束時間；
• timeCol - 是流表中表示時間欄位；
• gap - 是視窗資料非活躍週期的時長；

SQL 示例

利用pageAccessSession_tab測試資料，我們按地域統計連續的兩個訪問使用者之間的訪問時間間隔不超過3分鐘的的頁面訪問量(PV).

SELECT  
    region, 
    SESSION_START(rowtime, INTERVAL '3' MINUTE) AS winStart,  
    SESSION_END(rowtime, INTERVAL '3' MINUTE) AS winEnd, 
    COUNT(region) AS pv  
FROM pageAccessSession_tab
GROUP BY region, SESSION(rowtime, INTERVAL '3' MINUTE)複製程式碼

Result

UDX

Apache Flink 除了提供了大部分ANSI-SQL的核心運算元，也為使用者提供了自己編寫業務程式碼的機會，那就是User-Defined Function,目前支援如下三種 User-Defined Function：

• UDF - User-Defined Scalar Function
• UDTF - User-Defined Table Function
• UDAF - User-Defined Aggregate Funciton

UDX都是使用者自定義的函式，那麼Apache Flink框架為啥將自定義的函式分成三類呢？是根據什麼劃分的呢？Apache Flink對自定義函式進行分類的依據是根據函式語義的不同，函式的輸入和輸出不同來分類的，具體如下：

UDF

• 定義
  使用者想自己編寫一個字串聯接的UDF，我們只需要實現ScalarFunction#eval()方法即可，簡單實現如下：

object MyConnect extends ScalarFunction {
  @varargs
  def eval(args: String*): String = {
    val sb = new StringBuilder
    var i = 0
    while (i < args.length) {
      if (args(i) == null) {
        return null
      }
      sb.append(args(i))
      i += 1
    }
    sb.toString
  }
}複製程式碼

• 使用

...
 val fun = MyConnect
 tEnv.registerFunction("myConnect", fun)
 val sql = "SELECT myConnect(a, b) as str FROM tab"
...複製程式碼

UDTF

• 定義
  使用者想自己編寫一個字串切分的UDTF，我們只需要實現TableFunction#eval()方法即可，簡單實現如下：

ScalarFunction#eval()`

class MySplit extends TableFunction[String] {
  def eval(str: String): Unit = {
    if (str.contains("#")){
      str.split("#").foreach(collect)
    }
  }

  def eval(str: String, prefix: String): Unit = {
    if (str.contains("#")) {
      str.split("#").foreach(s => collect(prefix + s))
    }
  }
}複製程式碼

• 使用

...
val fun = new MySplit()
tEnv.registerFunction("mySplit", fun)
val sql = "SELECT c, s FROM MyTable, LATERAL TABLE(mySplit(c)) AS T(s)"
...複製程式碼

UDAF

• 定義
  UDAF 要實現的介面比較多，我們以一個簡單的CountAGG為例，做簡單實現如下：

/** The initial accumulator for count aggregate function */
class CountAccumulator extends JTuple1[Long] {
  f0 = 0L //count
}

/**
  * User-defined count aggregate function
  */
class MyCount
  extends AggregateFunction[JLong, CountAccumulator] {

  // process argument is optimized by Calcite.
  // For instance count(42) or count(*) will be optimized to count().
  def accumulate(acc: CountAccumulator): Unit = {
    acc.f0 += 1L
  }

  // process argument is optimized by Calcite.
  // For instance count(42) or count(*) will be optimized to count().
  def retract(acc: CountAccumulator): Unit = {
    acc.f0 -= 1L
  }

  def accumulate(acc: CountAccumulator, value: Any): Unit = {
    if (value != null) {
      acc.f0 += 1L
    }
  }

  def retract(acc: CountAccumulator, value: Any): Unit = {
    if (value != null) {
      acc.f0 -= 1L
    }
  }

  override def getValue(acc: CountAccumulator): JLong = {
    acc.f0
  }

  def merge(acc: CountAccumulator, its: JIterable[CountAccumulator]): Unit = {
    val iter = its.iterator()
    while (iter.hasNext) {
      acc.f0 += iter.next().f0
    }
  }

  override def createAccumulator(): CountAccumulator = {
    new CountAccumulator
  }

  def resetAccumulator(acc: CountAccumulator): Unit = {
    acc.f0 = 0L
  }

  override def getAccumulatorType: TypeInformation[CountAccumulator] = {
    new TupleTypeInfo(classOf[CountAccumulator], BasicTypeInfo.LONG_TYPE_INFO)
  }

  override def getResultType: TypeInformation[JLong] =
    BasicTypeInfo.LONG_TYPE_INFO
}複製程式碼

• 使用

...
val fun = new MyCount()
tEnv.registerFunction("myCount", fun)
val sql = "SELECT myCount(c) FROM MyTable GROUP BY  a"
...複製程式碼

Source&Sink

上面我們介紹了Apache Flink SQL核心運算元的語法及語義，這部分將選取Bounded EventTime Tumble Window為例為大家編寫一個完整的包括Source和Sink定義的Apache Flink SQL Job。假設有一張淘寶頁面訪問表(PageAccess_tab)，有地域，使用者ID和訪問時間。我們需要按不同地域統計每2分鐘的淘寶首頁的訪問量(PV). 具體資料如下：

Source 定義

自定義Apache Flink Stream Source需要實現StreamTableSource, StreamTableSource中通過StreamExecutionEnvironment 的addSource方法獲取DataStream, 所以我們需要自定義一個 SourceFunction, 並且要支援產生WaterMark，也就是要實現DefinedRowtimeAttributes介面。

Source Function定義

支援接收攜帶EventTime的資料集合，Either的資料結構，Right表示WaterMark和Left表示資料:

class MySourceFunction[T](dataWithTimestampList: Seq[Either[(Long, T), Long]]) 
  extends SourceFunction[T] {
  override def run(ctx: SourceContext[T]): Unit = {
    dataWithTimestampList.foreach {
      case Left(t) => ctx.collectWithTimestamp(t._2, t._1)
      case Right(w) => ctx.emitWatermark(new Watermark(w))
    }
  }
  override def cancel(): Unit = ???
}複製程式碼

定義 StreamTableSource

我們自定義的Source要攜帶我們測試的資料，以及對應的WaterMark資料，具體如下:

class MyTableSource extends StreamTableSource[Row] with DefinedRowtimeAttributes {

  val fieldNames = Array("accessTime", "region", "userId")
  val schema = new TableSchema(fieldNames, Array(Types.SQL_TIMESTAMP, Types.STRING, Types.STRING))
  val rowType = new RowTypeInfo(
    Array(Types.LONG, Types.STRING, Types.STRING).asInstanceOf[Array[TypeInformation[_]]],
    fieldNames)

  // 頁面訪問表資料 rows with timestamps and watermarks
  val data = Seq(
    Left(1510365660000L, Row.of(new JLong(1510365660000L), "ShangHai", "U0010")),
    Right(1510365660000L),
    Left(1510365660000L, Row.of(new JLong(1510365660000L), "BeiJing", "U1001")),
    Right(1510365660000L),
    Left(1510366200000L, Row.of(new JLong(1510366200000L), "BeiJing", "U2032")),
    Right(1510366200000L),
    Left(1510366260000L, Row.of(new JLong(1510366260000L), "BeiJing", "U1100")),
    Right(1510366260000L),
    Left(1510373400000L, Row.of(new JLong(1510373400000L), "ShangHai", "U0011")),
    Right(1510373400000L)
  )

  override def getRowtimeAttributeDescriptors: util.List[RowtimeAttributeDescriptor] = {
    Collections.singletonList(new RowtimeAttributeDescriptor(
      "accessTime",
      new ExistingField("accessTime"),
      PreserveWatermarks.INSTANCE))
  }

  override def getDataStream(execEnv: StreamExecutionEnvironment): DataStream[Row] = {
    execEnv.addSource(new MySourceFunction[Row](data)).setParallelism(1).returns(rowType)
  }

  override def getReturnType: TypeInformation[Row] = rowType

  override def getTableSchema: TableSchema = schema

}複製程式碼

Sink 定義

我們簡單的將計算結果寫入到Apache Flink內建支援的CSVSink中，定義Sink如下：

def getCsvTableSink: TableSink[Row] = {
    val tempFile = File.createTempFile("csv_sink_", "tem")
    // 列印sink的檔案路徑，方便我們檢視執行結果
    println("Sink path : " + tempFile)
    if (tempFile.exists()) {
      tempFile.delete()
    }
    new CsvTableSink(tempFile.getAbsolutePath).configure(
      Array[String]("region", "winStart", "winEnd", "pv"),
      Array[TypeInformation[_]](Types.STRING, Types.SQL_TIMESTAMP, Types.SQL_TIMESTAMP, Types.LONG))
  }複製程式碼

構建主程式

主程式包括執行環境的定義，Source/Sink的註冊以及統計查SQL的執行，具體如下：

def main(args: Array[String]): Unit = {
    // Streaming 環境
    val env = StreamExecutionEnvironment.getExecutionEnvironment
    val tEnv = TableEnvironment.getTableEnvironment(env)

    // 設定EventTime
    env.setStreamTimeCharacteristic(TimeCharacteristic.EventTime)

    //方便我們查出輸出資料
    env.setParallelism(1)

    val sourceTableName = "mySource"
    // 建立自定義source資料結構
    val tableSource = new MyTableSource

    val sinkTableName = "csvSink"
    // 建立CSV sink 資料結構
    val tableSink = getCsvTableSink

    // 註冊source
    tEnv.registerTableSource(sourceTableName, tableSource)
    // 註冊sink
    tEnv.registerTableSink(sinkTableName, tableSink)

    val sql =
      "SELECT  " +
      "  region, " +
      "  TUMBLE_START(accessTime, INTERVAL '2' MINUTE) AS winStart," +
      "  TUMBLE_END(accessTime, INTERVAL '2' MINUTE) AS winEnd, COUNT(region) AS pv " +
      " FROM mySource " +
      " GROUP BY TUMBLE(accessTime, INTERVAL '2' MINUTE), region"

    tEnv.sqlQuery(sql).insertInto(sinkTableName);
    env.execute()
  }複製程式碼

執行並檢視執行結果

執行主程式後我們會在控制檯得到Sink的檔案路徑，如下：

Sink path : /var/folders/88/8n406qmx2z73qvrzc_rbtv_r0000gn/T/csv_sink_8025014910735142911tem複製程式碼

Cat 方式檢視計算結果，如下：

jinchengsunjcdeMacBook-Pro:FlinkTableApiDemo jincheng.sunjc$ cat /var/folders/88/8n406qmx2z73qvrzc_rbtv_r0000gn/T/csv_sink_8025014910735142911tem
ShangHai,2017-11-11 02:00:00.0,2017-11-11 02:02:00.0,1
BeiJing,2017-11-11 02:00:00.0,2017-11-11 02:02:00.0,1
BeiJing,2017-11-11 02:10:00.0,2017-11-11 02:12:00.0,2
ShangHai,2017-11-11 04:10:00.0,2017-11-11 04:12:00.0,1複製程式碼

表格化如上結果：

上面這個端到端的完整示例也可以應用到本篇前面介紹的其他運算元示例中，只是大家根據Source和Sink的Schema不同來進行相應的構建即可！

總結

本篇概要的向大家介紹了SQL的由來，Apache Flink SQL 大部分核心功能，並附帶了具體的測試資料和測試程式，最後以一個End-to-End的示例展示瞭如何編寫Apache Flink SQL的Job收尾。本篇著重向大家介紹Apache Flink SQL的使用，後續我們再繼續探究每個運算元的實現原理。