Spark连接mysql的几种方式

博客《2020大数据面试真题(持续更新)》已迁移至微信公众号！！！     喜欢的朋友可以关注

最新面试题将会在微信公众号更新！

关注公众号即可获得免费学习资源，获得免费指导！！！

 

1.sparkcore-jdbc

// 获取mysql的连接  写入本地的mysql
    val url = "jdbc:mysql://hdp-01:3306/test?characterEncoding=utf-8"
    var conn: Connection = null
    var pstm1: PreparedStatement = null
    var pstm2: PreparedStatement = null
    try {
      conn = DriverManager.getConnection(url, "root", "123456")
      // 创建表
      pstm1 = conn.prepareStatement("create table if not exists province  (province varchar(20),cnts int)")
      pstm1.execute()
      pstm2 = conn.prepareStatement("insert into province values(?,?)")
      // 遍历iterator
      // 循环赋值 并入库
      pstm2.setString(1, pro)
      pstm2.setInt(2, cnt)
      pstm2.execute()
      
    } catch {
      // 错误打印要加上
      case e: Exception => e.printStackTrace()
    } finally {
      if (pstm1 != null) pstm1.close()
      if (pstm2 != null) pstm2.close()
      if (conn != null) conn.close()
    }

2.sparksql写入mysql

2.1 连接信息代码里面写

    val url = "jdbc:mysql://hdp-01:3306/test?characterEncoding=utf-8"
    val conn: Properties = new Properties()
    conn.setProperty("user","root")
    conn.setProperty("password","123456")
    //本地运行库不加  集群运行必须加
    conn.setProperty("driver","com.mysql.jdbc.Driver")
    result.write.mode(SaveMode.Overwrite).jdbc(url,"v_test",conn)

2.2  连接信息写在配置文件里面

db.url="jdbc:mysql://hdp-01:3306/test?characterEncoding=utf-8"
db.driver="db.user=""com.mysql.jdbc.Driver"
db.password="123456"
db.user="root"

读取配置文件信息

2.2.1

    val prop = new Properties()
    val stream: InputStream = this.getClass.getClassLoader.getResourceAsStream("application.conf")
    prop.load(stream)
    val dbUrl = prop.getProperty("db.url")
    println(dbUrl)

2.2.2 

需要先引入一个jar包

<!--导入 configFactory的jar包-->
<dependency>
    <groupId>com.typesafe</groupId>
    <artifactId>config</artifactId>
    <version>1.3.0</version>
</dependency>

    //默认加载配置文件application.conf（.json   .properties）
    val config: Config = ConfigFactory.load()
    println(config.getString("db.url"))

3.scalalike连接

3.1 导包

 <dependency>
            <groupId>org.scalikejdbc</groupId>
            <artifactId>scalikejdbc_2.11</artifactId>
            <version>2.5.0</version>
        </dependency>

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

3.2 配置文件 application.conf

db.default.url="jdbc:mysql://bigdata1:3306/test?characterEncoding=utf-8"
db.default.driver="com.mysql.jdbc.Driver"
db.default.password="11111111111"
db.default.user="root"

// 加载数据库配置信息
// 默认加载db.default.*
DBs.setup()
// 加载db.wedoctor配置信息
DBs.setup('wedoctor')

3.3 使用

package com.wedoctor.sparkstreaming.scalike

import org.apache.log4j.{Level, Logger}
import scalikejdbc.{DB, SQL}
import scalikejdbc.config.DBs

object ScalikeJdbcDemo {
  Logger.getLogger("org").setLevel(Level.ERROR)

  def main(args: Array[String]): Unit = {
     DBs.setup() //加载默认设置

    //读取数据
    val result: List[(String, Integer)] = DB.readOnly { implicit session =>
      SQL("select * from province").map(rs => {
        //按照字段名称
        val province: String = rs.string("province")
        //get方法取值
        val cnts: Integer = rs.get[Integer](2)
        //返回结果用元组封装
        (province, cnts)
      }).list().apply()
    }
    //result.foreach(println)

    //指定条件查询
    val result2 = DB.readOnly { implicit session =>
      SQL("select * from province where province=?").bind("北京").map(rs => {
        val str: String = rs.string("province")
        (str,rs.int(2))
      }).list().apply()
    }
    //result2.foreach(print)

    //事务控制
    DB.localTx{implicit session=>
      SQL("insert into province(province, cnts) values(?,?)")
        .bind("wedoctor", 22).update().apply()
     // 2 / 0  // autoCommit API 不支持事务
      SQL("insert into province(province, cnts) values(?,?)")
        .bind("wedoctor", 33).update().apply()
    }


  }
}