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;

         

posted on 2021-05-31 17:44  桉叶  阅读(41)  评论(0编辑  收藏  举报