【大数据开发】FlinkSQL实战

参考：https://cloud.tencent.com/developer/article/1450865

score.txt文件

17-18,詹姆斯-哈登,72,72,35.4,8.8,1.8,0.7,30.4
16-17,拉塞尔-威斯布鲁克,81,81,34.6,10.4,1.6,0.4,31.6
15-16,斯蒂芬-库里,79,79,34.2,6.7,2.1,0.2,30.1
14-15,拉塞尔-威斯布鲁克,67,67,34.4,8.6,2.1,0.2,28.1
13-14,凯文-杜兰特,81,81,38.5,5.5,1.3,0.7,32
12-13,卡梅罗-安东尼,67,67,37,2.6,0.8,0.5,28.7
11-12,凯文-杜兰特,66,66,38.6,3.5,1.3,1.2,28
10-11,凯文-杜兰特,78,78,38.9,2.7,1.1,1,27.7
09-10,凯文-杜兰特,82,82,39.5,2.8,1.4,1,30.1
08-09,德维恩-韦德,79,79,38.6,7.5,2.2,1.3,30.2
07-08,勒布朗-詹姆斯,75,74,40.4,7.2,1.8,1.1,30
06-07,科比-布莱恩特,77,77,40.8,5.4,1.4,0.5,31.6
05-06,科比-布莱恩特,80,80,41,4.5,1.8,0.4,35.4
04-05,阿伦-艾弗森,75,75,42.3,7.9,2.4,0.1,30.7
03-04,特雷西·麦克格雷迪,67,67,39.9,5.5,1.4,0.6,28
02-03,特雷西·麦克格雷迪,75,74,39.4,5.5,1.7,0.8,32.1
01-02,阿伦-艾弗森,60,59,43.7,5.5,2.8,0.2,31.4
00-01,阿伦-艾弗森,71,71,42,4.6,2.5,0.3,31.1
99-00,沙奎尔-奥尼尔,79,79,40,3.8,0.5,3,29.7
98-99,阿伦-艾弗森,48,48,41.5,4.6,2.3,0.1,26.8
97-98,迈克尔-乔丹,82,82,38.8,3.5,1.7,0.5,28.7
96-97,迈克尔-乔丹,82,82,37.9,4.3,1.7,0.5,29.6
95-96,迈克尔-乔丹,82,82,37.7,4.3,2.2,0.5,30.4
94-95,沙奎尔-奥尼尔,79,79,37,2.7,0.9,2.4,29.3
93-94,大卫-罗宾逊,80,80,40.5,4.8,1.7,3.3,29.8
92-93,迈克尔-乔丹,78,78,39.3,5.5,2.8,0.8,32.6
91-92,迈克尔-乔丹,80,80,38.8,6.1,2.3,0.9,30.1
90-91,迈克尔-乔丹,82,82,37,5.5,2.7,1,31.5
89-90,迈克尔-乔丹,82,82,39,6.3,2.8,0.7,33.6
88-89,迈克尔-乔丹,81,81,40.2,8,2.9,0.8,32.5
87-88,迈克尔-乔丹,82,82,40.4,5.9,3.2,1.6,35
86-87,迈克尔-乔丹,82,82,40,4.6,2.9,1.5,37.1
85-86,多米尼克-威尔金斯,78,78,39.1,2.6,1.8,0.6,30.3
84-85,伯纳德-金,55,55,37.5,3.7,1.3,0.3,32.9
83-84,阿德里安-丹特利,79,79,37.8,3.9,0.8,0.1,30.6
82-83,阿历克斯-英格利什,82,82,36.4,4.8,1.4,1.5,28.4
81-82,乔治-格文,79,79,35.7,2.4,1,0.6,32.3

FlinkSQL文件

package day03

import org.apache.flink.api.common.functions.MapFunction
import org.apache.flink.api.scala.{DataSet, ExecutionEnvironment, createTypeInformation}
import org.apache.flink.table.api.Table
import org.apache.flink.table.api.scala.{BatchTableEnvironment, tableConversions}
import org.apache.flink.types.Row
import org.slf4j.{Logger, LoggerFactory}

object FlinkSQL {

    private val logger: Logger = LoggerFactory.getLogger(FlinkSQL.getClass)

    logger.debug("日志信息：")

    def main(args: Array[String]): Unit = {

        // 1.获取执行环境
        val env: ExecutionEnvironment = ExecutionEnvironment.getExecutionEnvironment

        // 2.创建上下文环境
        val tableEnv: BatchTableEnvironment = BatchTableEnvironment.create(env)


        // 3.读取文件
        val source: DataSet[String] = env.readTextFile("file:///F:\\java\\flink\\doc\\sore.txt")

        val input: DataSet[PlayData] = source.map(new MapFunction[String, PlayData] {
            override def map(value: String): PlayData = {
                val play: Array[String] = value.split(",")
                PlayData(play(0), play(1), play(2), play(3).toInt, play(4).toDouble,
                    play(5).toDouble, play(6).toDouble, play(7).toDouble, play(8).toDouble)
            }
        })

        // 4.将source数据注册成表
        val playTable: Table = tableEnv.fromDataSet(input)

        tableEnv.registerDataSet("score", input)

        // 5.执行SQL逻辑代码
        val queryData: Table = tableEnv.sqlQuery(
            """
              |select player, count(season) as num
              |from score
              |group by player
              |order by num desc
              |""".stripMargin)

        // 6.将Table转为DataSet
        val queryDS: DataSet[Row] = queryData.toDataSet[Row]

        // 7.Sink DataSet
        queryDS.print()
    }

}

// 构建球赛数据样例类
case class PlayData(season: String,     // 赛季
                   player: String,      // 球员
                   playNum: String,     // 出场
                   firstCount: Int,     // 首发
                   time: Double,        // 时间
                   assists: Double,     // 助攻
                   steals: Double,      // 抢断
                   blocks: Double,      // 盖帽
                   scores: Double       // 得分
                   )

结果：

迈克尔-乔丹,10
阿伦-艾弗森,4
凯文-杜兰特,4
拉塞尔-威斯布鲁克,2
沙奎尔-奥尼尔,2
科比-布莱恩特,2
特雷西·麦克格雷迪,2
伯纳德-金,1
勒布朗-詹姆斯,1
斯蒂芬-库里,1
詹姆斯-哈登,1
阿德里安-丹特利,1
卡梅罗-安东尼,1
大卫-罗宾逊,1
乔治-格文,1
多米尼克-威尔金斯,1
阿历克斯-英格利什,1
德维恩-韦德,1