sparksql写入mysql （包括Druid连接池的使用方法）

 

注意：sparksql写入mysql，没有表会默认创建表，但String类型的字段会变成mysql中的Text字段，如果想变成varchar,最好事先，通过jdbc或者mysql的方式，提前先创建好对应的表和字段的类型

sparksql写入mysql(dataframe方式）

//获取prop

def getProp(user:String,password:String,driver:String,url:String)={

val prop = new java.util.Properties

prop.setProperty("user","root")

prop.setProperty("password","root")

prop.setProperty("driver","com.mysql.jdbc.Driver")

prop.setProperty("url","jdbc:mysql://IP地址:端口号数据库名?useUnicode=true&characterEncoding=utf8")

prop

 

}

//写入mysql的方法

sqlContext.sql("select * from person")

.write

.mode(Append)

.jdbc(prop.getProperty("url"),"stu",prop)

注解：sqlContext是spark 2.0以前的写法，2.0以后可以使用sparkSession

person，是dataframe注册的临时表,mode 是写入的模式，append是追加

,stu是要写入的mysql的表名，prop就是上个方法的返回值

 

 

spark写入mysql (Druid的连接池使用方法)

首先要配置druid 和mysql的依赖，在maven的资源库里，可以找到相关的依赖

Druid：https://mvnrepository.com/artifact/com.alibaba/druid

MySQL：https://mvnrepository.com/artifact/mysql/mysql-connector-java

将依赖写到pom文件中

 

创建Druid的连接池类

package com.lenovo.ai.lenobot.utils;

import com.alibaba.druid.pool.DruidDataSource;

import com.alibaba.druid.pool.DruidDataSourceFactory;

import scala.annotation.serializable;

 

import javax.sql.DataSource;

import java.sql.Connection;

import java.sql.ResultSet;

import java.sql.SQLException;

import java.sql.Statement;

import java.util.Properties;

 

public class DataSourceUtils extends serializable{

private static DruidDataSource ds;

static {

try {

Properties info = new Properties();

info.load(DataSourceUtils.class.getResourceAsStream("/db.properties"));

ds =(DruidDataSource) DruidDataSourceFactory.createDataSource(info);

}catch (Exception e) {

e.printStackTrace();

}

}

public static DataSource getDataSource() {

return ds;

}

 

public static Connection getConnection() {

try {

 

return ds.getConnection();

}catch (SQLException e) {

throw new RuntimeException(e);

}

}

 

public static void close(Connection conn, Statement stmt, ResultSet rs) {

if (rs!=null) {

try {

rs.close();

}catch (SQLException e) {

e.printStackTrace();

}

}

if (stmt!=null) {

try {

stmt.close();

}catch (SQLException e) {

e.printStackTrace();

}

 

}

if (conn!=null) {

try {

conn.close();

}catch (SQLException e) {

e.printStackTrace();

}

 

}

 

}

public static void close(Connection conn, Statement stmt) {

close(conn, stmt, null);

}

public static void close(Connection conn) {

close(conn, null, null);

}

 

}

 

注解："/db.properties" 是数据库连接信息的prop文件

文件内容：

driverClassName=com.mysql.jdbc.Driver

url=jdbc:mysql://ip地址:端口/数据库名

username=root

password=root

initialSize=5

maxActive=10

maxWait=3000

maxIdle=6

minIdle=3

 

 

主方法调用的例子

 

 

import org.apache.spark.sql.types.{IntegerType, StringType, StructField, StructType}

import org.apache.spark.{SparkConf, SparkContext}

import org.apache.spark.sql.{Row, SQLContext}

 

object Test{

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

val conf=new SparkConf().setAppName("test").setMaster("local[1]")

val sc=new SparkContext(conf)

val sqlContext = new SQLContext(sc) // spark 1.6的写法,spark2.0以上可以用sparksession

 

val people=sc.textFile("E://hello.txt")

val rowRDD= people.map(x=>

x.split(",")

).map(y=>

Row(y(0).toInt,y(1),y(2).toInt,y(3),y(4).toInt)

)

val schema = StructType(

Seq(

StructField("id",IntegerType,true),

StructField("name",StringType,true),

StructField("age",IntegerType,true) ,

StructField("xueli",StringType,true),

StructField("salary",IntegerType,true)

)

)

val df = sqlContext.createDataFrame(rowRDD,schema)

df.foreachPartition(p=>{

@transient val conn = DataSourceUtils.getConnection //在这里活得连接，防止出现不能序列化的问题，需要在foreach中获得连接

p.foreach(x=>{

val sql="insert into stu(id,name,age,xueli,salary) values("+

"'"+x.getInt(0)+"'," +

"'"+x.getString(1)+"'," +

"'"+x.getInt(2)+"'," +

"'"+x.getString(3)+"'," +

"'"+ x.getInt(4)+"'"+

")"

val stmt = conn.createStatement

stmt.executeUpdate(sql)

 

})

 

DataSourceUtils.close(conn)

})

 

 

 

}

 

}