Spark编程:使用Spark SQL 连接数据库 2020.11.01

1.基础工作

1.安装mysql

sudo apt-get update
sudo apt-get install mysql-server

2.启动和关闭mysql服务器

service mysql start
service mysql stop

3.进入mysql界面(若密码问题请参考以下链接)

若因为密码问题,一般是安装mysql时候没有设置密码导致,或者是忘记密码:请单击此处

	$ mysql -u root -p
	#屏幕会提示你输入密码

2.创建数据库和表

mysql> create database spark;
mysql> use spark;
mysql> create table student (id int(4), name char(20), gender char(4), age int(4));
mysql> insert into student values(1,'Xueqian','F',23);
mysql> insert into student values(2,'Weiliang','M',24);
mysql> select * from student;

3.spark进行数据库连接

3.1 进入spark时写附加命令(前提是记得建所需要的包进行解压到spark的jars目录下)

./bin/spark-shell  \
 --jars /usr/local/spark/jars/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar \
 --driver-class-path /usr/local/spark/jars/mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar

3.2 进行连接

注意修改成自己的密码

val jdbcDF = spark.read.format("jdbc").
option("url","jdbc:mysql://localhost:3306/spark").
option("driver","com.mysql.jdbc.Driver").
option("dbtable", "student").
option("user", "root").
option("password", "210374520").
load()

3.3 连接测试

jdbcDF.show()