Flink 读写 MySQL DDL方式

package org.onepiece.bigdata.windows.JDBC

import org.apache.flink.api.common.typeinfo.{BasicTypeInfo, TypeInformation}
import org.apache.flink.api.java.io.jdbc.{JDBCAppendTableSink, JDBCInputFormat, JDBCOutputFormat}
import org.apache.flink.table.api.scala.BatchTableEnvironment
import org.apache.flink.table.api.{DataTypes, EnvironmentSettings, Types}
import org.apache.flink.types.Row
import org.apache.flink.api.java.typeutils.RowTypeInfo
import org.apache.flink.streaming.api.scala.StreamExecutionEnvironment
import org.apache.flink.table.api.scala.StreamTableEnvironment

import org.apache.flink.table.api.Table

object mysql_test {

  import org.apache.flink.api.scala.extensions._
  import org.apache.flink.api.scala._

  protected val host_name = "localhost"
  protected val port = 3306
  protected val db_name = "vn09jj5"
  protected val url = s"jdbc:mysql://${host_name}:${port}/${db_name}?useSSL=false&serverTimezone=UTC"

  val url_flink = "jdbc:mysql://localhost:3306/flink_db?useSSL=false&serverTimezone=UTC"

  protected val driver = "com.mysql.cj.jdbc.Driver"
  protected val user = "root"
  protected val password = "sa123ADMIN"


  def getBatchExecutionEnvironment(): ExecutionEnvironment = {
    val benv = ExecutionEnvironment.getExecutionEnvironment
    return benv
  }

  def getStreamExecutionEnvironment(): StreamExecutionEnvironment = {
    val senv = StreamExecutionEnvironment.getExecutionEnvironment
    return senv
  }

  def getEnvironmentSettings(): EnvironmentSettings = {
    val setting = EnvironmentSettings.newInstance()
      .useBlinkPlanner()
      .inStreamingMode()
      .build()
    return setting
  }


def mysql_read_AND_write_with_DDL(): Unit = {
    val senv = getStreamExecutionEnvironment()
    val setting = getEnvironmentSettings()
    val tableEnv = StreamTableEnvironment.create(senv, setting)

    val table_name = "cte_phone_channel"
    val sourceTable =
      s"""
         |create table sourceTable (
         |  phone_Nbr string,
         |  channel   string
         |)
         |with (
         | 'connector.type'='jdbc',
         | 'connector.url'='jdbc:mysql://localhost:3306/vn09jj5?useSSL=false&serverTimezone=UTC',
         | 'connector.username'='${user}',
         | 'connector.driver'='${driver}',
         | 'connector.password'='${password}',
         | 'connector.table'='${table_name}',
         | 'connector.write.flush.max-rows' = '50' -- 默认5000条，为了演示改为1条
         |)
      """.stripMargin
    tableEnv.sqlUpdate(sourceTable)

    val data: Table = tableEnv.sqlQuery("select * from sourceTable")

    //data.printSchema()
    /*
root
 |-- phone_Nbr: STRING
 |-- channel: STRING
    **/
    //data.toAppendStream[(String, String)].print()


    println("outTable----------------------------------")

    val sinkTable =
      """
        |create table sinkTable (
        | phone_Nbr string,
        | channel   string
        |)
        |with (
        | 'connector.type'='jdbc',
        | 'connector.url'='jdbc:mysql://localhost:3306/flink_db?useSSL=false&serverTimezone=UTC',
        | 'connector.username'='root',
        | 'connector.driver'='com.mysql.cj.jdbc.Driver',
        | 'connector.password'='sa123ADMIN',
        | 'connector.table'='sink_phone_channel',
        | 'connector.write.flush.max-rows' = '100'
        |)
      """.stripMargin
    tableEnv.sqlUpdate(sinkTable)

    val query = "select * from sourceTable";
    val outTable = tableEnv.sqlQuery(query)

    //outTable.printSchema()
    //outTable.toAppendStream[(String, String)].print()

    println("sinkTable----------------------------")

    //插入数据表
    outTable
      //.select("phone_Nbr,channel")
      .insertInto("sinkTable")

    senv.execute("table api test")
  }
}