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)
}