09 spark连接mysql数据库

spark连接mysql数据库

1. 安装启动检查Mysql服务。
   netstat -tunlp (3306)
2. 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

3. 

    

    

4. 启动 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);

5. 

   insert into student(name,age,sex) values("Mosse",18,"M"),("Make",20,"M"),("Junny",18,"F"),("Zuly",21,"F");

    select * from student;

    

    

6. 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()

7. 

    

    

8. spark向MySQL数据库写入数据

9. attr = {"user":"root","password":"123456","driver":"com.mysql.cj.jdbc.Driver"}

10. records = jdbcDF.collect()
11. records
12. 

    truncate table student;

13. select * from student;

14.  

     

    spark.createDataFrame(records).write\... .jdbc("jdbc:mysql://localhost:3306/spark?useSSL=false","student","append",attr)

    

15. select * from student;