spark连接mysql数据库
- 安装启动检查Mysql服务。
netstat -tunlp (3306) - spark 连接mysql驱动程序。
cd /usr/local/hive/lib
ls mysql*
cp mysql-connector-java-5.1.40-bin.jar /usr/local/spark/jars/
cd /usr/local/spark/jars/
ls mysql*
netstat -tunpl
-
- 启动 Mysql shell，新建数据库spark，表student。
mysql -u root -p show databases; create database spark; use spark;create table student(id bigint auto_increment,name char(20),sex char(4),age int (4),primary key(id) using btree);
-
insert into student(name,age,sex) values("Mosse",18,"M"),("Make",20,"M"),("Junny",18,"F"),("Zuly",21,"F");
select * from student;
- spark读取MySQL数据库中的数据
jdbcDF=spark.read\
... .format("jdbc")\
... .option("url","jdbc:mysql://localhost:3306/spark?useUnicode=true&characterEncoding=utf-8")\
... .option("driver","com.mysql.jdbc.Driver")\
... .option("dbtable","student")\
... .option("user","root")\
... .option("password","123456")\
... .load()jdbcDF.show()
-
- spark向MySQL数据库写入数据
-
attr = {"user":"root","password":"123456","driver":"com.mysql.cj.jdbc.Driver"}
- records = jdbcDF.collect()
- records
-
truncate table student;
-
select * from student;
-
spark.createDataFrame(records).write\... .jdbc("jdbc:mysql://localhost:3306/spark?useSSL=false","student","append",attr)
-
select * from student;