Spark2.4.8读写MySQL数据库表

- 一、创建数据库表
- 二、测试数据
- 三、编写程序
- 四、运行结果

一、创建数据库表

创建edu_teacher表，语句如下：

CREATE TABLE `edu_teacher` (
  `id` char(19) NOT NULL COMMENT '讲师ID',
  `name` varchar(20) NOT NULL COMMENT '讲师姓名',
  `intro` varchar(500) NOT NULL DEFAULT '' COMMENT '讲师简介',
  `career` varchar(500) DEFAULT NULL COMMENT '讲师资历,一句话说明讲师',
  `level` int(10) unsigned NOT NULL COMMENT '头衔 1高级讲师 2首席讲师',
  `avatar` varchar(255) DEFAULT NULL COMMENT '讲师头像',
  `sort` int(10) unsigned NOT NULL DEFAULT '0' COMMENT '排序',
  `is_deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '逻辑删除 1（true）已删除， 0（false）未删除',
  `gmt_create` datetime NOT NULL COMMENT '创建时间',
  `gmt_modified` datetime NOT NULL COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='讲师'

二、测试数据

edu_teacher.csv，其内容如下，大家也可自行设置：

1010101100101011002	姜夔2	近年主持国家自然科学基金（6项）、江苏省重大科技成果转化项目（5项）、江苏省产学研前瞻性联合研究项目（3项）、省工业科技支撑、省高技术、省自然科学基金等省部级及其企业的主要科研项目40多个，多个项目在企业成功转化，产生了较好的经济、社会和环境效益。积极开展产学研科技合作，并与省内16家企业建立了江苏省研究生工作站，其中6家为江苏省优秀研究生工作站	高级	1	https://guli-file-190513.oss-cn-beijing.aliyuncs.com/avatar/default.jpg	0	0	2019-10-30 14:18:46	2019-11-12 13:36:36
1189389726308489802	刘备2	高级讲师简介	高级讲师资历	2	https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/10/30/de47ee9b-7fec-43c5-8173-13c5f7f689b2.png	1	0	2019-10-30 11:53:03	2019-10-30 11:53:03
1189390295668697022	张飞2	高级讲师简介	高级讲师	2	https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/10/30/b8aa36a2-db50-4eca-a6e3-cc6e608355e0.png	2	0	2019-10-30 11:55:19	2019-11-12 13:37:52
1189426437879858752	关羽2	高级讲师简介	高级讲师	1	https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/11/08/e44a2e92-2421-4ea3-bb49-46f2ec96ef88.png	0	0	2019-10-30 14:18:56	2019-11-12 13:37:35
1189426464969953392	赵云2	高级讲师简介	高级讲师	1	https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/10/30/65423f14-49a9-4092-baf5-6d0ef9686a85.png	0	1	2019-10-30 14:19:02	2019-11-12 13:37:18
1192249914830557642	黄忠2	高级讲师简介	高级讲师	1	https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/11/07/91871e25-fd83-4af6-845f-ea8d471d825d.png	0	0	2019-11-07 09:18:25	2019-11-12 13:37:01
1192327476071157872	李逵2	1111	11	1	https://online-teach-file.oss-cn-beijing.aliyuncs.com/teacher/2019/11/08/5805c6cd-c8ad-4a77-aafd-d2e083bfd8a4.png	0	1	2019-11-07 14:26:37	2019-11-11 16:26:26
1195337453291292182	test2	sdfsdf	sdfdf	1	https://guli-file-190513.oss-cn-beijing.aliyuncs.com/avatar/default.jpg	0	1	2019-11-15 21:47:12	2019-11-15 21:47:27
1323301279400573542	宋江2	大数据高级讲师	大数据高级讲师	2	https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/03/2326f645cccb45a28c62aa26f1f687d5file.png	1	0	2020-11-03 00:29:45	2020-11-03 00:29:45
1323302262900659312	李青2	大数据首席讲师	大数据首席讲师	2	https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/03/101477ccd8ed42b8b359eba4cc7c2169file.png	1	0	2020-11-03 00:33:41	2020-11-03 00:33:41
1330094057552696342	武松2	高级讲师	高级讲师	1	https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/11/21/10a83aba03c54e659a044b1c9d6b3e8efile.png	1	0	2020-11-21 18:21:50	2020-11-21 18:21:50
1335567943265694472	林冲2	热爱教育，热爱技术，热爱科技，热爱生活	首席大数据讲师	2	https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/6f1f5a0bd4d44c22920f90caf7749a55file.png	1	0	2020-12-06 20:53:07	2020-12-07 16:52:38
1335868632881141672	燕青2	深耕专升计算机专业N年，具有十分丰富的教学经验与备考经验	深耕专升计算机专业N年，具有十分丰富的教学经验与备考经验	2	https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/2734844e361446a48f4fb283360cb805file.png	1	0	2020-12-07 16:48:06	2020-12-07 16:48:06
1335869319424306542	杨志2	深耕专升本艺考专业，擅长平面设计、室内设计等，具有十分丰富的教学与备考经验	深耕专升本艺考专业，擅长平面设计、室内设计等，具有十分丰富的教学与备考经验	2	https://suben-user-avatar.oss-cn-shenzhen.aliyuncs.com/2020/12/07/ad64d0a85f794badb805d36c15a691e2file.png	1	0	2020-12-07 16:51:54	2020-12-07 16:51:54

三、编写程序

创建maven项目，在pom.xml中如下依赖：


        2.12.15
        2.4.8
        2.12
        2.7.3
    

    
        
            org.apache.spark
            spark-core_${spark.artifact.version}
            ${spark.version}
        
        
            org.apache.spark
            spark-sql_2.12
            2.4.8
        
        
            org.apache.hadoop
            hadoop-client
            ${hadoop.version}
        
        
        
            org.scala-lang
            scala-library
            ${scala.version}
        
        
            mysql
            mysql-connector-java
            5.1.6
        
    

    
        
        src/main/scala
        src/test/scala

        
            
                org.apache.maven.plugins
                maven-compiler-plugin
                3.8.1
                
                    1.8
                    1.8
                
            
            
            
                net.alchim31.maven
                scala-maven-plugin
                4.5.4
                
                    
                        
                            compile
                            testCompile
                        
                    
                
                
                    ${scala.version}

创建object对象，代码如下：

import org.apache.spark.sql.{Dataframe, SparkSession}
import org.apache.spark.rdd.RDD

import java.util.Properties

//定义一个case class来代表表的schema结构
case class EduTeacher(id:String,
                      name:String,
                      intro:String,
                      career: String,
                      level:Int,
                      avatar:String,
                      sort:Int,
                      is_deleted:Int,
                      gmt_create:String,
                      gmt_modified:String)

object SparkSQLDemo3 {
  //定义MySQL是链接方式及其用户名和密码
  val pros = new Properties()
  pros.setProperty("user","suben") // MySQL的用户名
  pros.setProperty("password","123456") // MySQL的密码

  val url = "jdbc:mysql://niit01:3306/itcollege?useUnicode=true&characterEncoding=UTF-8"
  val tableName = "edu_teacher"

  def main(args: Array[String]): Unit = {
    val spark = SparkSession.builder().master("local").appName("SparkSQLDemo2").getOrCreate()
    // 写MySQL: 将sparksql分析之后的结果存储到MySQL中表
     writeToMySQL(spark)
    // 读MySQL：sparksql读取MySQL中表的数据，验证下是否正常写入
    readFromMySQL(spark)
    // 关闭资源
    spark.stop()
  }

  def readFromMySQL(spark: SparkSession): Unit ={
    // 读取MySQL表
    val frame: Dataframe = spark.read.jdbc(url, tableName, pros)
    frame.show()
  }

  def writeToMySQL(spark: SparkSession): Unit ={
    //通过SparkSession创建SparkContext，读入数据
    val studentRDD:RDD[Array[String]] = spark.sparkContext.textFile("d:\TestDatas\edu_teacher.csv").map {_.split("t")}
    //将数据的RDD和Case Class的schema关联
    val dataRDD:RDD[EduTeacher] = studentRDD.map {
      data => EduTeacher(data(0),data(1),data(2),
        data(3),data(4).toInt,data(5),data(6).toInt,data(7).toInt,
        data(8),data(9))
    }
    //把dataRDD转换成Dataframe，需要导入隐式转换规则
    import spark.sqlContext.implicits._
    val studentDF:Dataframe = dataRDD.toDF()
    //注册表或者视图
    studentDF.createOrReplaceTempView("teacher")
    //查询分析年龄大于24的学生
    val resultDF:Dataframe = spark.sql("select * from teacher where name <> 'test'")
    resultDF.write.mode("append").jdbc(url,tableName,pros)
  }
}

四、运行结果

写数据库表：读取edu_teacher.csv中内容，将其过滤掉name=test的数据，将过滤后的数据写入数据库表中，表中会增加数据
读数据：将MySQL数据库中的表的数据前20条记录读取出来，打印在控制台

Spark2.4.8读写MySQL数据库表

大数据系统相关栏目本月热门文章