插入和使用PySpark DataFrames和JDBC的UPDATE MySql表 [英] INSERT & UPDATE MySql table using PySpark DataFrames and JDBC

问题描述

我正在尝试使用PySpark SQL DataFrames和JDBC连接在MySql上插入和更新一些数据.

I'm trying to insert and update some data on MySql using PySpark SQL DataFrames and JDBC connection.

我已经成功使用SaveMode.Append插入了新数据.是否可以通过PySpark SQL更新现有数据并在MySql表中插入新数据?

I've succeeded to insert new data using the SaveMode.Append. Is there a way to update the existing data and insert new data in MySql Table from PySpark SQL?

我要插入的代码是:

myDataFrame.write.mode(SaveMode.Append).jdbc(JDBCurl,mySqlTable,connectionProperties)

如果我更改为SaveMode.覆盖它会删除整个表并创建一个新表，我正在寻找类似"ON DUPLICATE KEY UPDATE"的内容.在MySql中可用

If I change to SaveMode.Overwrite it deletes the full table and creates a new one, I'm looking for something like the "ON DUPLICATE KEY UPDATE" available in MySql

在此方面的任何帮助都将受到高度赞赏.

Any help on this is highly appreciated.

推荐答案

1. 在Mysql中以create view <viewName> as select ...<tableName>
的方式创建视图
2. 在mysql中创建触发器，以在插入后使用-
更新

1. Create a view in Mysql as create view <viewName> as select ...<tableName>
2. Create trigger in mysql to update after insert using -

CREATE TRIGGER trigger_name
    AFTER INSERT
    ON <viewName> FOR EACH ROW
BEGIN
    -- statements
    -- INSERT ... ON DUPLICATE KEY UPDATE Statement
END$$  

ref- https://www.mysqltutorial.org/mysql-triggers/mysql-after-insert-trigger/

1. 写入数据以从Spark查看<viewName>

这篇关于插入和使用PySpark DataFrames和JDBC的UPDATE MySql表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！