Flink Mysql 操作(scalikejdbc)

Flink Mysql 操作

pom文件

            <dependency>
                <groupId>mysql</groupId>
                <artifactId>mysql-connector-java</artifactId>
                <version>5.1.28</version>
            </dependency>

CREATE TABLE IF NOT EXISTS `student`(
   `id` INT ,
   `name` VARCHAR(100) NOT NULL,
   `age` INT
)


INSERT INTO student
(id, name, age)
VALUES
("2", "fy", 25);

LOW B 版本

MySQL utils

import java.sql.{Connection, DriverManager}

object MySQLUtils {
  def  getConnection() ={
    Class.forName("com.mysql.jdbc.Driver")
    DriverManager.getConnection("","root","")

  }

  def closeConnection(connection:Connection): Unit ={
    if(null != connection){
      connection.close()
    }
  }
}

定义一个类

object Domain {

    case class Student(id: Int, name: String, age: Int)
}

cityosMySQLSource

import com.cityos.bean.Domain.Student
import com.cityos.utils.MySQLUtils
import org.apache.flink.configuration.Configuration
import org.apache.flink.streaming.api.functions.source.{RichSourceFunction, SourceFunction}

import java.sql.{Connection, PreparedStatement}

class cityosMySQLSource  extends  RichSourceFunction[Student]{

  var connection:Connection = _
  var pstmt:PreparedStatement = _

  override def open(parameters: Configuration): Unit = {
    connection = MySQLUtils.getConnection()
    pstmt = connection.prepareStatement("select * from student")
  }

  override def close(): Unit = {
    MySQLUtils.closeConnection(connection)
  }


  override def run(ctx: SourceFunction.SourceContext[Student]): Unit = {
    val rs = pstmt.executeQuery()
    while(rs.next()){
      val student = Student(rs.getInt("id"), rs.getString("name"), rs.getInt("age"))
      ctx.collect(student)
    }
  }
  override def cancel(): Unit = ???
}

sourceApp

import org.apache.flink.streaming.api.scala.StreamExecutionEnvironment
import org.apache.flink.api.scala._

object sourceApp {
  def main(args: Array[String]): Unit = {
    val env = StreamExecutionEnvironment.getExecutionEnvironment

    val stream = env.addSource(new cityosMySQLSource)

    println(stream.parallelism)

    val mapStream = stream.map(x => x.toString)

    println(mapStream.parallelism)

    mapStream.print()

    env.execute(getClass.getCanonicalName)
  }
}

高端版本

        <dependency>
            <groupId>org.scalikejdbc</groupId>
            <artifactId>scalikejdbc-config_2.11</artifactId>
            <version>3.4.0</version>
        </dependency>

application.conf

db.default.driver="com.mysql.jdbc.Driver"
db.default.url="jdbc:mysql://hadoop001:3306/ruozedata"
db.default.user="root"
db.default.password="csz"

# Connection Pool settings
db.default.poolInitialSize=10
db.default.poolMaxSize=20
db.default.connectionTimeoutMillis=1000

# Connection Pool settings
db.default.poolInitialSize=5
db.default.poolMaxSize=7
db.default.poolConnectionTimeoutMillis=1000

sourceApp

import org.apache.flink.streaming.api.scala.StreamExecutionEnvironment
import org.apache.flink.api.scala._

object sourceApp {
  def main(args: Array[String]): Unit = {
    val env = StreamExecutionEnvironment.getExecutionEnvironment

    /**
     * 需要把并行度改掉才能读 ， 否则多次读取
     */
    val stream = env.addSource(new cityMySQLScalikeJDBC).setParallelism(1)

    println(stream.parallelism)

    val mapStream = stream.map(x => x.toString)

    println(mapStream.parallelism)

    mapStream.print()

    env.execute(getClass.getCanonicalName)
  }
}

scalikeJDBC

import com.cityos.bean.Domain.Student
import org.apache.flink.streaming.api.functions.source.{RichParallelSourceFunction, SourceFunction}
import scalikejdbc.{DB, SQL}
import scalikejdbc.config.DBs

class cityMySQLScalikeJDBC extends RichParallelSourceFunction[Student]{
  /**
   * 业务逻辑
   * @param csz
   */
  override def run(ctx: SourceFunction.SourceContext[Student]): Unit = {

    DBs.setupAll()
    DB.readOnly{ implicit session => {
      SQL("SELECT * FROM student").map(rs =>{
        val student = Student(rs.int("id"), rs.string("name"),rs.int("age"))
        ctx.collect(student)
      }).list().apply()
    }

    }
  }

  override def cancel(): Unit = ???
}