如何使用 PySpark 的 JDBC 覆盖数据而不会丢失架构? [英] How to overwrite data with PySpark's JDBC without losing schema?

问题描述

我有一个 DataFrame，我愿意将它写入 PostgreSQL 数据库.如果我只是使用覆盖"模式，例如:

I have a DataFrame that I'm willing to write it to a PostgreSQL database. If I simply use the "overwrite" mode, like:

df.write.jdbc(url=DATABASE_URL, table=DATABASE_TABLE, mode="overwrite", properties=DATABASE_PROPERTIES)

重新创建表并保存数据.但问题是我想将 PRIMARY KEY 和 Indexes 保留在表中.所以，我想要么只覆盖数据，保留表架构，要么在之后添加主键约束和索引.任何一个都可以用 PySpark 完成吗?还是我需要连接到 PostgreSQL 并执行命令来自己添加索引?

The table is recreated and the data is saved. But the problem is that I'd like to keep the PRIMARY KEY and Indexes in the table. So, I'd like to either overwrite only the data, keeping the table schema or to add the primary key constraint and indexes afterward. Can either one be done with PySpark? Or do I need to connect to the PostgreSQL and execute the commands to add the indexes myself?

推荐答案

mode="overwrite" 的默认行为是先删除表，然后用新数据重新创建它.您可以通过包含 option("truncate", "true") 来截断数据，然后推送您自己的数据:

The default behavior for mode="overwrite" is to first delete the table, then recreate it with the new data. You can instead truncate the data by including option("truncate", "true") and then push your own:

df.write.option("truncate", "true").jdbc(url=DATABASE_URL, table=DATABASE_TABLE, mode="overwrite", properties=DATABASE_PROPERTIES)

这样，您就不会重新创建表，因此它不应对您的架构进行任何修改.

This way, you are not recreating the table so it shouldn't make any modifications to your schema.

这篇关于如何使用 PySpark 的 JDBC 覆盖数据而不会丢失架构?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！