SparkSQL通过Mysql创建DataFrame

一、数据源

CREATE TABLE student(
    id int not null primary key,
    name varchar(20),
	age int(20),
	city varchar(20),
	score double(20,2)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;


insert into student(id,name,age,city,score) values(1,'张飞',21,'北京',80.0);
insert into student(id,name,age,city,score) values(2,'关羽',23,'北京',82.0);
insert into student(id,name,age,city,score) values(3,'赵云',20,'上海',88.6);
insert into student(id,name,age,city,score) values(4,'刘备',26,'上海',83.0);
insert into student(id,name,age,city,score) values(5,'曹操',30,'深圳',90.0);

二、SparkSQL通过Mysql创建DataFrame代码

package blog

import java.util.Properties

import org.apache.log4j.{Level, Logger}
import org.apache.spark.sql.{DataFrame, SparkSession}

/**
 * @author: 余辉  
 * @blog: https://blog.csdn.net/silentwolfyh
 * @create: 2019-12-31 10:20
 * @description:
 **/
object DF02_Create_Mysql {

  Logger.getLogger("org").setLevel(Level.WARN)

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

    /** *
     * Exception in thread "main" java.sql.SQLException: No suitable driver
     *
     * 解决办法：
     *
     * <dependency>
     * <groupId>mysql</groupId>
     * <artifactId>mysql-connector-java</artifactId>
     * <version>5.1.39</version>
     * </dependency>
     */
    val spark: SparkSession = SparkSession.builder()
      .appName("SparkUtils")
      .master("local[*]")
      .getOrCreate()

    val pro = new Properties()
    pro.setProperty("user", "root")
    pro.setProperty("password", "123456")
    val frame: DataFrame = spark.read.jdbc("jdbc:mysql://localhost:3306/bigdata", "student", pro)
    frame.printSchema()
    frame.show()
  }
}

三、结果展示