spark分区读写mysql

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/Hello_World0825/article/details/110089567
————————————————
注意：
分区字段必须是整型，日期

package com.xy.use

import java.sql.{
   Connection, DriverManager}
import java.text.SimpleDateFormat
import java.util.Properties

import org.apache.spark.SparkConf
import org.apache.spark.sql.{
   SaveMode, SparkSession}

import scala.collection.mutable.ArrayBuffer
//功能描述
/*
* 分区读mysql,分区写mysql
* */
object DataBaseRead{
   
  def main(args:Array[String]) = {
   
    val url = "jdbc:mysql://ip:3306/x_test"
    val tablename = "tablename"
    val user = "username"
    val password = "password"
    val column = "分区字段名"
    val coltype = "分区字段类型"
    val partition = "分区个数"
    ReadSql(url,tablename,user,password,column,coltype,partition)
  }
  //读取mysql数据库
  def ReadSql(url:String,tablename:String,user:String,password:String,column:String,coltype:String,partition:String) = {
   
    val conf = new SparkConf().setAppName("bigdata test").setMaster("local[*]")
    val spark = SparkSession.builder().config(conf).getOrCreate()
    val prop = new Properties()
    prop.put("driver","com.mysql.jdbc.Driver")
    prop.put("url",url)
    prop.put("dbtable",tablename)
    prop.put("user",user)
    prop.put("password",password)
    //如果分区字段是long类型的，那么在读取mysql的时候，需要多加几个参数：列名，最小值，最大值，分区数
    if(coltype.toLowerCase() == "long"){
   
      val ab = LongTypeConn("com.mysql.jdbc.Driver",url,user,password,column,tablename)
      val lowerNum = ab(0)
      val upperNum = ab(1)
      val longFrame = spark.read.jdbc(
        prop.getProperty("url"),
        prop.getProperty("dbtable"),
        column,lowerNum,upperNum,
        partition.toInt,prop
      )
      longFrame.createOrReplaceTempView("test")

      val r=spark.sql(
        """
          |	SELECT
          |	id,
          |	barrage
          |FROM
          |	test
          |
        """.stripMargin)
//      longFrame.write.mode(SaveMode.Overwrite).json("file:///C:\\Users\\xiaoya-tech\\Desktop\\up")
      r.repartition(5).write
        .format("jdbc")
        .option("url", "ip:3306/x_test")
        .option("truncate", true)
        .option("dbtable", "x_test.test_copy")
        .option("user", "username")
        .option("password", "password")
        //      SaveMode.ErrorIfExists、SaveMode.Append、SaveMode.Overwrite、SaveMode.Ignore
        .mode(SaveMode.Append)
        .save()
    }
    //如果是时间类型的，那么在读取的时候需要多一个参数，就是我们自定义划分的时间区间
    else if(coltype.toLowerCase() == "date"){
   
      val arr = DateTypeConn("com.mysql.jdbc.Driver",url,user,password,column,tablename,partition.toInt)
      val dateFrame = spark.read.jdbc(
        prop.getProperty("url"),
        prop.getProperty("dbtable"),
        arr,prop)
      dateFrame.write.mode(Sav