从 azure 数据块中删除 azure sql 数据库行 [英] Delete azure sql database rows from azure databricks

问题描述

我在 Azure SQL 数据库中有一个表，我想根据某些条件从中删除选定的行，或者从 Azure Databricks 中删除整个表.目前我正在使用 JDBC 的 truncate 属性来截断整个表而不删除它，然后用新的数据帧重写它.

I have a table in Azure SQL database from which I want to either delete selected rows based on some criteria or entire table from Azure Databricks. Currently I am using the truncate property of JDBC to truncate the entire table without dropping it and then re-write it with new dataframe.

df.write \
     .option('user', jdbcUsername) \
     .option('password', jdbcPassword) \
     .jdbc('<connection_string>', '<table_name>', mode = 'overwrite', properties = {'truncate' : 'true'} )

但是我不想每次都截断和覆盖整个表，而是使用删除命令.我也无法使用下推查询来实现这一点.对此的任何帮助将不胜感激.

But going forward I don't want to truncate and overwrite the entire table every time but rather use delete command. I was not able to achieve this using pushdown query either. Any help on this would be greatly appreciated.

推荐答案

您也可以下拉到 Scala 来执行此操作，因为已经安装了 SQL Server JDBC 驱动程序.EG:

You can also drop down to scala to do this, as the SQL Server JDBC driver is already installed. EG:

%scala

import java.util.Properties
import java.sql.DriverManager

val jdbcUsername = "xxxxx"
val jdbcPassword = "xxxxxx"
val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"

// Create the JDBC URL without passing in the user and password parameters.
val jdbcUrl = s"jdbc:sqlserver://xxxxxx.database.windows.net:1433;database=AdventureWorks;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"

// Create a Properties() object to hold the parameters.

val connectionProperties = new Properties()

connectionProperties.put("user", s"${jdbcUsername}")
connectionProperties.put("password", s"${jdbcPassword}")
connectionProperties.setProperty("Driver", driverClass)

val connection = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword)
val stmt = connection.createStatement()
val sql = "delete from sometable where someColumn > 4"

stmt.execute(sql)
connection.close()

这篇关于从 azure 数据块中删除 azure sql 数据库行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！