FlinkSQL内置了这么多函数你都使用过吗？-sqlserver if函数的使用方法

前言

Flink Table 和 SQL 内置了很多 SQL 中支持的函数;如果有无法满足的需要，则可以实现用户自定义的函数(UDF)来解决。

一、系统内置函数

Flink Table API 和 SQL 为用户提供了一组用于数据转换的内置函数。SQL 中支持的很多函数，Table API 和 SQL 都已经做了实现，其它还在快速开发扩展中。

以下是一些典型函数的举例，全部的内置函数，可以参考官网介绍。

类型	TableApi	SQLAPI
比较函数	ANY1 === ANY2	value1 = value2
比较函数	NY1 > ANY2	value1 > value2
逻辑函数	BOOLEAN1 \|\| BOOLEAN2	boolean1 OR boolean2
逻辑函数	BOOLEAN.isFalse	boolean IS FALSE
逻辑函数	!BOOLEAN	NOT boolean
算术函数	NUMERIC1 + NUMERIC2	numeric1 + numeric2
算术函数	NUMERIC1.power(NUMERIC2)	POWER(numeric1, numeric2)
字符串函数	STRING1 + STRING2	string1 \|\| string2
字符串函数	STRING.upperCase()	UPPER(string)
字符串函数	STRING.charLength()	CHAR_LENGTH(string)
时间函数	STRING.toDate	DATE string
时间函数	STRING.toTimestamp	TIMESTAMP string
时间函数	currentTime()	CURRENT_TIME
时间函数	NUMERIC.days	INTERVAL string range
时间函数	NUMERIC.minutes
聚合函数	FIELD.count	COUNT(*)
聚合函数	FIELD.sum0	SUM([ ALL \| DISTINCT ] expression)
聚合函数		RANK()
聚合函数		ROW_NUMBER()

二、Flink UDF

用户定义函数(User-defined Functions，UDF)是一个重要的特性，因为它们显著地扩展了查询(Query)的表达能力。一些系统内置函数无法解决的需求，我们可以用 UDF 来自定义实现。

2.1 注册用户自定义函数 UDF

在大多数情况下，用户定义的函数必须先注册，然后才能在查询中使用。不需要专门为Scala 的 Table API 注册函数。

函数通过调用 registerFunction()方法在 TableEnvironment 中注册。当用户定义的函数被注册时，它被插入到 TableEnvironment 的函数目录中，这样 Table API 或 SQL 解析器就可以识别并正确地解释它。

2.2 标量函数(Scalar Functions)

用户定义的标量函数，可以将 0、1 或多个标量值，映射到新的标量值。

为了定义标量函数，必须在 org.apache.flink.table.functions 中扩展基类 Scalar Function，并实现(一个或多个)求值(evaluation，eval)方法。标量函数的行为由求值方法决定，求值方法必须公开声明并命名为 eval(直接 def 声明，没有 override)。求值方法的参数类型和返回类型，确定了标量函数的参数和返回类型。

在下面的代码中，我们定义自己的 HashCode 函数，在 TableEnvironment 中注册它，并在查询中调用它。

准备数据

sensor_1,1547718199,35.8 
sensor_6,1547718201,15.4 
sensor_7,1547718202,6.7 
sensor_10,1547718205,38.1 
sensor_1,1547718206,32 
sensor_1,1547718208,36.2 
sensor_1,1547718210,29.7 
sensor_1,1547718213,30.9 
1.
2.
3.
4.
5.
6.
7.
8.

代码如下

package udf 
 
import org.apache.flink.streaming.api.scala._ 
import org.apache.flink.table.api.DataTypes 
import org.apache.flink.table.api.scala._ 
import org.apache.flink.table.descriptors.{Csv, FileSystem, Schema} 
import org.apache.flink.table.functions.ScalarFunction 
import org.apache.flink.types.Row 
 
/** 
* @Package udf 
* @File ：FlinkSqlUdfHashCode.java 
* @author 大数据老哥 
* @date 2020/12/29 21:58 
* @version V1.0 
*/ 
object FlinkSqlUdfHashCode { 
 def main(args: Array[String]): Unit = { 
   //1.构建运行环境 
   val env = StreamExecutionEnvironment.getExecutionEnvironment 
   env.setParallelism(1) // 设置并行度为1 
   //2.构建TableEnv 
   val tableEnv = StreamTableEnvironment.create(env) 
   //3.构建数据源 
   tableEnv.connect(new FileSystem().path("./data/sensor.txt")) 
     .withFormat(new Csv()) 
     .withSchema(new Schema() 
       .field("id", DataTypes.STRING()) 
       .field("timestamp", DataTypes.INT()) 
       .field("temperature", DataTypes.DOUBLE()) 
     ).createTemporaryTable("sensor") 
   // 转为表 
   val tableSensor = tableEnv.from("sensor") 
   // 床架转换对象 
   val code = new HashCode() 
   //使用tableAPI 进行测试 
   val tableRes = tableSensor.select('id, code('id)) 
   tableEnv.registerFunction("code",code) // 注册udf 
   val tableSql = tableEnv.sqlQuery( 
     """ 
       |select 
       |id, 
       |code(id) 
       |from 
       |sensor 
       |""".stripMargin) 
   // 输出 
   tableRes.toAppendStream[Row].print("tableAPI") 
   tableSql.toAppendStream[Row].print("tableSql") 
 
   env.execute("FlinkSqlUdfHashCode") 
 } 
 
 class HashCode() extends ScalarFunction { 
   def eval(s: String): String = { 
     s.hashCode.toString 
   } 
 } 
} 
运行结果 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.

2.3 表函数(Table Functions)

与用户定义的标量函数类似，用户定义的表函数，可以将 0、1 或多个标量值作为输入参数;

与标量函数不同的是，它可以返回任意数量的行作为输出，而不是单个值。为了定义一个表函数，必须扩展 org.apache.flink.table.functions 中的基类 TableFunction并实现(一个或多个)求值方法。表函数的行为由其求值方法决定，求值方法必须是 public的，并命名为 eval。求值方法的参数类型，决定表函数的所有有效参数。

返回表的类型由 TableFunction 的泛型类型确定。求值方法使用 protected collect(T)方法发出输出行。

在 Table API 中，Table 函数需要与.joinLateral 或.leftOuterJoinLateral 一起使用。

joinLateral 算子，会将外部表中的每一行，与表函数(TableFunction，算子的参数是它的表达式)计算得到的所有行连接起来。

而 leftOuterJoinLateral 算子，则是左外连接，它同样会将外部表中的每一行与表函数计算生成的所有行连接起来;并且，对于表函数返回的是空表的外部行，也要保留下来。

在 SQL 中，则需要使用 Lateral Table()，或者带有 ON TRUE 条件的左连接。

下面的代码中，我们将定义一个表函数，在表环境中注册它，并在查询中调用它。

数据准备

hello|word,hello|spark 
hello|Flink,hello|java,hello|大数据老哥 
1.
2.

编写代码

package udf 
 
import org.apache.flink.streaming.api.scala._ 
import org.apache.flink.table.api.scala._ 
import org.apache.flink.table.functions.TableFunction 
import org.apache.flink.types.Row 
 
/** 
 * @Package udf 
 * @File ：FlinkSqlUDFTableFunction.java 
 * @author 大数据老哥 
 * @date 2020/12/29 23:10 
 * @version V1.0 
 */ 
object FlinkSqlUDFTableFunction { 
  def main(args: Array[String]): Unit = { 
    //1.构建运行环境 
    val env = StreamExecutionEnvironment.getExecutionEnvironment 
    env.setParallelism(1) // 设置并行度为1 
    //2.构建TableEnv 
    val tableEnv = StreamTableEnvironment.create(env) 
    //3.构建数据源 
    val data = env.readTextFile("./data/words.txt") 
    // 解析数据 
    val wordData: DataStream[String] = data.flatMap(_.split(",")) 
    // 类型转换 
    val tableWord = tableEnv.fromDataStream(wordData,'id) 
    // 调用TableFunction 
    val split = new Split() 
    // Table API 方式一 
    val resTable1 = tableWord. 
      joinLateral(split('id) as('word,'length)) 
      .select('id,'word,'length ) 
    //  Table API  方式二 
    val resTable2 = tableWord. 
      leftOuterJoinLateral(split('id) as('word,'length)) 
      .select('id,'word,'length ) 
    // 将数据注册成表 
     tableEnv.createTemporaryView("sensor",tableWord) 
     tableEnv.registerFunction("split",split) 
 
    // SQL 方式一 
    val tableSQL1 = tableEnv.sqlQuery( 
      """ 
        |select 
        |id, 
        |word, 
        |length 
        |from 
        |sensor ,LATERAL TABLE(split(id)) AS newsensor(word, length) 
        |""".stripMargin) 
    //  SQL 方式二 
    val TableSQL2 = tableEnv.sqlQuery( 
      """ 
        |select 
        |id, 
        |word, 
        |length 
        |from 
        |sensor 
        | LEFT JOIN LATERAL TABLE(split(id)) AS newsensor(word, length) ON TRUE 
        |""".stripMargin) 
    // 调用数据 
    resTable1.toAppendStream[Row].print("resTable1") 
    resTable2.toAppendStream[Row].print("resTable2") 
    tableSQL1.toAppendStream[Row].print("tableSQL1") 
    TableSQL2.toAppendStream[Row].print("TableSQL2") 
 
 
    env.execute("FlinkSqlUDFTableFunction") 
  } 
 
  class Split() extends TableFunction[(String,Int)] { 
    def eval(str: String): Unit = { 
      str.split("\\|").foreach( 
        word => collect((word, word.length)) 
      ) 
    } 
  } 
} 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.

2.4 聚合函数(Aggregate Functions)

用户自定义聚合函数(User-Defined Aggregate Functions，UDAGGs)可以把一个表中的数据，聚合成一个标量值。用户定义的聚合函数，是通过继承 AggregateFunction 抽象类实现的。

上图中显示了一个聚合的例子。

假设现在有一张表，包含了各种饮料的数据。该表由三列(id、name 和 price)、五行组成数据。现在我们需要找到表中所有饮料的最高价格，即执行 max()聚合，结果将是一个数值。AggregateFunction 的工作原理如下：

首先，它需要一个累加器，用来保存聚合中间结果的数据结构(状态)。可以通过调用 AggregateFunction 的 createAccumulator()方法创建空累加器。
随后，对每个输入行调用函数的 accumulate() 方法来更新累加器。
处理完所有行后，将调用函数的 getValue() 方法来计算并返回最终结果。AggregationFunction 要求必须实现的方法：

除了上述方法之外，还有一些可选择实现的方法。其中一些方法，可以让系统执行查询更有效率，而另一些方法，对于某些场景是必需的。例如，如果聚合函数应用在会话窗口(session group window)上下文中，则 merge()方法是必需的。

retract()
merge()
resetAccumulator()

接下来我们写一个自定义AggregateFunction,计算一个每个price的平均值。

数据准备

1,Latte,6 
2,Milk,3 
3,Breve,5 
4,Mocha,8 
5,Tea,4 
1.
2.
3.
4.
5.

代码如下

package udf 
 
import org.apache.calcite.rel.`type`.{RelDataType, RelDataTypeFactory} 
import org.apache.flink.streaming.api.scala._ 
import org.apache.flink.table.api.DataTypes 
import org.apache.flink.table.api.scala._ 
import org.apache.flink.table.descriptors.{Csv, FileSystem, Schema} 
import org.apache.flink.table.functions.AggregateFunction 
import org.apache.flink.types.Row 
 
import java.util 
 
/** 
 * @Package udf 
 * @File ：FlinkSQUDFAggregateFunction.java 
 * @author 大数据老哥 
 * @date 2020/12/30 22:06 
 * @version V1.0 
 */ 
object FlinkSQUDFAggregateFunction { 
  def main(args: Array[String]): Unit = { 
    //1.构建运行环境 
    val env = StreamExecutionEnvironment.getExecutionEnvironment 
    env.setParallelism(1) // 设置并行度为1 
    //2.构建TableEnv 
    val tableEnv = StreamTableEnvironment.create(env) 
    //3.构建数据源 
    tableEnv.connect(new FileSystem().path("./data/datas")) 
      .withFormat(new Csv) 
      .withSchema(new Schema() 
        .field("id", DataTypes.STRING()) 
        .field("name", DataTypes.STRING()) 
        .field("price", DataTypes.DOUBLE()) 
      ).createTemporaryTable("datas") 
    val AvgTemp = new AvgTemp() 
    val table = tableEnv.from("datas") 
 
    val resTableApi = table.groupBy('id) 
      .aggregate(AvgTemp('price) as 'sumprice) 
      .select('id, 'sumprice) 
 
    tableEnv.registerFunction("avgTemp",AvgTemp) 
    val tablesql = tableEnv.sqlQuery( 
      """ 
        |select 
        |id ,avgTemp(price) 
        |from datas group by id 
        |""".stripMargin) 
    resTableApi.toRetractStream[Row].print("resTableApi") 
    tablesql.toRetractStream[Row].print("tablesql") 
    env.execute("FlinkSQUDFAggregateFunction") 
  } 
 
  class AvgTempAcc { 
    var sum: Double = 0.0 
    var count: Int = 0 
  } 
 
  class AvgTemp extends AggregateFunction[Double, AvgTempAcc] { 
    override def getValue(acc: AvgTempAcc): Double = { 
      acc.sum / acc.count 
    } 
 
    override def createAccumulator(): AvgTempAcc = new AvgTempAcc() 
  } 
 
  def accumulate(accumulator: AvgTempAcc, price: Double): Unit = { 
    accumulator.sum += price 
 
    accumulator.count += 1 
  } 
 
} 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.

2.5表聚合函数(Table Aggregate Functions)

户定义的表聚合函数(User-Defined Table Aggregate Functions，UDTAGGs)，可以把一个表中数据，聚合为具有多行和多列的结果表。这跟 AggregateFunction 非常类似，只是之前聚合结果是一个标量值，现在变成了一张表。

比如现在我们需要找到表中所有饮料的前 2 个最高价格，即执行 top2()表聚合。我们需要检查 5 行中的每一行，得到的结果将是一个具有排序后前 2 个值的表。用户定义的表聚合函数，是通过继承 TableAggregateFunction 抽象类来实现的。TableAggregateFunction 的工作原理如下：

为首先，它同样需要一个累加器(Accumulator)，它是保存聚合中间结果的数据结构。通过调用 TableAggregateFunction 的 createAccumulator()方法可以创建空累加器。
为随后，对每个输入行调用函数的 accumulate()方法来更新累加器。
为处理完所有行后，将调用函数的 emitValue()方法来计算并返回最终结果。除了上述方法之外，还有一些可选择实现的方法。
retract()
merge()
resetAccumulator()
emitValue()
emitUpdateWithRetract()

接下来我们写一个自定义 TableAggregateFunction，用来提取每个 price 最高的两个平均值。

数据准备

1,Latte,6 
2,Milk,3 
3,Breve,5 
4,Mocha,8 
5,Tea,4 
1.
2.
3.
4.
5.

代码如下

package udf 
 
import org.apache.flink.streaming.api.scala._ 
import org.apache.flink.table.api.DataTypes 
import org.apache.flink.table.api.scala._ 
import org.apache.flink.table.descriptors.{Csv, FileSystem, Schema} 
import org.apache.flink.table.functions.TableAggregateFunction 
import org.apache.flink.types.Row 
import org.apache.flink.util.Collector 
import udf.FlinkSQUDFAggregateFunction.AvgTemp 
 
/** 
 * @Package udf 
 * @File ：FlinkSqlUDFTableAggregateFunction.java 
 * @author 大数据老哥 
 * @date 2020/12/30 22:53 
 * @version V1.0 
 */ 
object FlinkSqlUDFTableAggregateFunction { 
  def main(args: Array[String]): Unit = { 
    //1.构建运行环境 
    val env = StreamExecutionEnvironment.getExecutionEnvironment 
    env.setParallelism(1) // 设置并行度为1 
    //2.构建TableEnv 
    val tableEnv = StreamTableEnvironment.create(env) 
    //3.构建数据源 
    tableEnv.connect(new FileSystem().path("./data/datas")) 
      .withFormat(new Csv) 
      .withSchema(new Schema() 
        .field("id", DataTypes.STRING()) 
        .field("name", DataTypes.STRING()) 
        .field("price", DataTypes.DOUBLE()) 
      ).createTemporaryTable("datas") 
    val table = tableEnv.from("datas") 
    val temp = new Top2Temp() 
    val tableApi = table.groupBy('id) 
      .flatAggregate(temp('price) as('tmpprice, 'rank)) 
      .select('id, 'tmpprice, 'rank) 
    tableEnv.registerFunction("temp",temp) 
 
 
    tableApi.toRetractStream[Row].print() 
 
    env.execute("FlinkSqlUDFTableAggregateFunction") 
  } 
 
  class Top2TempAcc { 
    var highestPrice: Double = Int.MinValue 
    var secodeHighestPrice: Double = Int.MinValue 
  } 
 
  class Top2Temp extends TableAggregateFunction[(Double, Int), Top2TempAcc] { 
    override def createAccumulator(): Top2TempAcc = new Top2TempAcc 
 
    def accumulate(acc: Top2TempAcc, temp: Double): Unit = { 
      if (temp > acc.highestPrice) { 
        acc.secodeHighestPrice = acc.highestPrice 
        acc.highestPrice = temp 
      } else if (temp > acc.secodeHighestPrice) { 
        acc.highestPrice = temp 
      } 
    } 
 
    def emitValue(acc: Top2TempAcc, out: Collector[(Double, Int)]): Unit = { 
      out.collect(acc.highestPrice, 1) 
      out.collect(acc.secodeHighestPrice, 2) 
    } 
  } 
 
} 
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.

总结

好了今天的内容就分享到这里了。上述主要讲解了一个系统自己带的函数，但是往往企业中不光只需要这些函数，有好多需求是本身函数是无法完成的。这时候就要用到我们的自定义函数了。他可以根据我们自己的需要进行编写代码来实现我们想要的功能。

本文转载自微信公众号「大数据老哥」，可以通过以下二维码关注。转载本文请联系大数据老哥公众号。