是否有一种方法可以从数据库中截断远程mssql表?

Question

我试图找出如何/如果可能的话，在远程sql服务器上从databricks执行截断表命令。我正在使用ETL脚本的databricks，但它正在加载到远程ms sql服务器。

原始脚本截断表，然后重复追加。它像这样截断它:

engine.execution_options(autocommit=True).execute("TRUNCATE TABLE my_table;")

我不知道如何使用pyspark复制。我试图避免这样做:

first_iteration = True
for item in items_to_query:
df = f(...)
if first_iteration:
df.write.option("mode","overwrite")....
first_iteration = False
else:
df.write.option("mode","append")...

如果我能有像

这样的东西就更好了

truncate_remote_table("table","database")
for item in items_to_query:
df = f(...)
df.write.option("mode","append")....

我希望我解释得很清楚。如果你想推荐完全不同的做法，那也没关系。只是我和很多人一起工作，他们(有理由)害怕/很容易害怕将脚本移动到数据块，所以我真的希望在每个步骤中尽可能少地改变。风雨无阻。

我在谷歌上搜索过，但搜索结果似乎总是从现有的数据框架开始，然后让它做一个mode="覆盖"截断表。没有什么只是一个简单的"TRUNCATE table"。命令。

Answer 1

正如在其他帖子中提到的那样，很少有方法可以做到这一点:

• 直接使用JDBC来执行代码。在PySpark中，您需要通过JVM网关(来自此回答):

driver_manager = spark._sc._gateway.jvm.java.sql.DriverManager
connection = driver_manager.getConnection(mssql_url, mssql_user, mssql_pass)
connection.prepareCall("TRUNCATE TABLE my_table").execute()
connection.close()

• 在overwrite模式下使用空Dataframe截断，因为它是由@rainingdistros指向的。唯一需要注意的是，默认情况下它使用drop/create new方法，因此您将丢失索引。但是这可以通过将truncate选项设置为true而不是默认的false来控制(参见文档):

# get a dataframe with table schema
df = spark.read.jdbc(....)
# truncate the table
df.limit(0).write.mode("overwrite").option("truncate", "true").jdbc(...)

Answer 2

你有几个选择…

选项01:通过JDBC连接截断表

您可以创建到目标SQL Server表的JDBC连接。按照下面的链接，您应该能够将TRUNCATE语句传递给JDBC连接。用truncate语句替换QUERY语句。即使它是一个截断语句，也要确保提交。

https://www.tutorialspoint.com/jdbc/jdbc-delete-records.htm

选项02:下推截断查询

根据Peter Pan的回答，你可以使用相同的逻辑将truncate语句推入SQL Server表。将语句别名为dbtable。

https://stackoverflow.com/a/58629994/13280838

选项03:使用空数据帧截断

按照下面的链接如何截断一个表在PySpark?

可以创建一个空数据框，然后用它截断表。请注意，当截断没有像我预期的那样工作时，有一些警告。此外，根据我有限的知识，使用覆盖而不使用截断-删除表和索引不会重新创建，所以在使用时请小心。

在实际的DEV表中实现任何东西之前，请务必尝试使用一次性临时表进行测试。希望能有所帮助。

Answer 3

我首先要说，除了SQL Server本身，我对你提到的任何技术都一无所知。但是在SQL Server上的每个数据库中，都有一个叫做sp_executesql的系统存储过程，它允许你构造一个SQL字符串并执行它。

如果你能够调用远程SQL Server上的存储过程，那么你可以调用sp_executesql 'USE DatabaseName; TRUNCATE TABLE my_table;'来截断任何你想要的表。由于数据库名称是在查询中的USE子句中指定的，因此您可以从服务器上的任何数据库执行sp_executesql过程的任何实例。