flink算子（四）

TableAPI和sql

table 的创建方式

# dataset 和datastream 隐式转化（case class）
# sink table
# source table
# externalCatalog table
# table api 用法
val orders = tableEnv.scan("Orders") 
val revenue = orders .filter('cCountry === "FRANCE") .groupBy('cID, 'cName') .select('cID, 'cName, 'revenue.sum AS 'revSum)
# sql 用法
tableEnv.sqlQuery("select name,sum(age) from t_table group by name")

dataset 和datastream 与tableAPI和sql之间的转换

注意

• 注册表，不能优化，可以内联查询多次。
• table 转化dataset 和datastream，有Row,pojo,case class,tuple方式
• dataset和datastream 封装转化成tuple ,可以注册成表。

转化	dataset	datastream	tableAPI	sql query
dataset	-	-	totable隐式转化，fromDataSet	registerDataSet注册表
datastream	-	-	totable隐式转化，fromDataStream	registerDataStream注册表
tableAPI	toDataSet[Row]	toAppendStream[Row]	-	registerTable
sql query	toDataSet(String, Int)	toRetractStream	scan	-

# dataset和datastream 注册成 sqlquery table
//register the DataStream as Table "myTable" with fields "f0","f1" 
tableEnv.registerDataStream("myTable", stream)
// register the DataStream as table "myTable2" with fields "myLong", "myString" 
tableEnv.registerDataStream("myTable2", stream, 'myLong, 'myString)
# dataset和datastream 转化成table
// convert the DataStream into a Table with default fields '_1, '_2 
val table1: Table = tableEnv.fromDataStream(stream) 
// convert the DataStream into a Table with fields 'myLong, 'myString
 val table2: Table = tableEnv.fromDataStream(stream, 'myLong, 'myString) 
# table转转成dataset和datastream 
// convert the Table into a DataSet of Row 
val dsRow: DataSet[Row] = tableEnv.toDataSet[Row](table) 
// convert the Table into a DataSet of Tuple2[String, Int]
 val dsTuple: DataSet[(String, Int)] = tableEnv.toDataSet[(String, Int)](table) 
// convert the Table into an append DataStream of Row 
val dsRow: DataStream[Row] = tableEnv.toAppendStream[Row](table)
 // convert the Table into an append DataStream of Tuple2[String, Int]
  val dsTuple: DataStream[(String, Int)] dsTuple = tableEnv.toAppendStream[(String, Int)](table) 
# 注册表转和table API 转化
 // SQL to Table API 
 tableEnv.registerTable("table1", ...) 
 val tapiResult = tableEnv.scan("table1").select(...)
 // from Table API to SQL 
 val projTable: Table = tableEnv.scan("X").select(...) 
 tableEnv.registerTable("projectedTable", projTable)

测试案列

import org.apache.flink.streaming.api.scala._
import org.apache.flink.table.api.TableEnvironment
import org.apache.flink.table.api.scala._
object TableApiTest {
  def main(args: Array[String]): Unit = {
    val sEnv=StreamExecutionEnvironment.getExecutionEnvironment
    val tEnv=TableEnvironment.getTableEnvironment(sEnv)
    // datastream=>tableAPI
    val orderA=sEnv.fromCollection(Seq(
      Order(1L,"beer",3),
      Order(1L,"diaper",4),
      Order(3L,"rubber",2))).toTable(tEnv)
    val orderB = sEnv.fromCollection(Seq(
      Order(2L, "pen", 3),
      Order(2L, "rubber", 3),
      Order(4L, "beer", 1)))
    //datastream=>sqlquery
     tEnv.registerDataStream("orderTable",orderB)
     //tableAPI操作
//   val result=orderA.unionAll("")
//     .select('user,'product,'amount)
//       .where('amount >2).toAppendStream[Order]
//    result.print()
 //sql query操作
    val result1=tEnv.sqlQuery(s"SELECT * FROM $orderA WHERE amount > 2 UNION ALL " +
      "SELECT * FROM orderTable WHERE amount > 2")
     // table=》datastream 输出
    result1.toAppendStream[Order].print()
    sEnv.execute("tableStream")
  }
  case class Order(user:Long,product:String,amount:Int)
}