gpt4 book ai didi

sql - 使用 Azure Databricks 和 Scala 从 Azure Sql 表中删除行

转载 作者:行者123 更新时间:2023-12-03 00:24:18 25 4
gpt4 key购买 nike

我将 Azure Databricks 与 Scala 结合使用,我的目标是从 Azure SQL 表中删除一些行。

为了实现此目的,我使用 JDBC 进行下推查询,如下所示:

val pushdown_query = s"(DELETE FROM ${table_name} WHERE dump_date = '2020-01-07') temp"
val res = spark.read.jdbc(jdbcUrl, pushdown_query, connectionProperties)

但是,我收到以下错误:

com.microsoft.sqlserver.jdbc.SQLServerException: A nested INSERT, UPDATE, DELETE, or MERGE statement must have an OUTPUT clause.

我在下推查询中添加了 OUTPUT 子句来解决这个问题:

val pushdown_query = s"(DELETE FROM ${table_name} OUTPUT DELETED.dump_date WHERE dump_date = '2020-01-07') temp"

但现在我收到以下错误:

com.microsoft.sqlserver.jdbc.SQLServerException: A nested INSERT, UPDATE, DELETE, or MERGE statement is not allowed in a SELECT statement that is not the immediate source of rows for an INSERT statement.

我做错了什么?我怎样才能实现这个目标?有更好的办法吗?

最佳答案

我还没有找到使用 Spark 从 Azure SQL 中删除行的方法,但我已经使用 Java 库在 Scala 中实现了自己的函数:

import java.util.Properties
import java.sql.Connection
import java.sql.DatabaseMetaData
import java.sql.DriverManager
import java.sql.SQLException
import java.sql.Date
import java.time.LocalDate


// Set credentials
var jdbcUsername = "X"
var jdbcPassword = dbutils.secrets.get("X", "Y")

// Chech that the JDBC driver is available
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")

// Create the JDBC URL
var jdbcHostname = "X"
var jdbcPort = 1433
var jdbcDatabase = "X"
val jdbcUrl = s"jdbc:sqlserver://${jdbcHostname}:${jdbcPort};database=${jdbcDatabase};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30"

def delete_dump_date(table_name:String, dump_date:String){

val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
var connObj:Connection = null
var number_of_rows_deleted:Int = 0
try{
Class.forName(driverClass);
connObj = DriverManager.getConnection(jdbcUrl, jdbcUsername, jdbcPassword);
val statement = connObj.prepareStatement(String.format("DELETE FROM %s WHERE dump_date=?", table_name))
try{
statement.setDate(1, Date.valueOf(LocalDate.parse(dump_date)));
number_of_rows_deleted = statement.executeUpdate();
}
finally{
statement.close();
println(number_of_rows_deleted + " rows deleted.")
}
}
catch {
case e:SQLException => e.printStackTrace();
}
finally{
connObj.close();
}
}

您可以调用该函数:

delete_dump_date(table_name, '2020-01-07')

关于sql - 使用 Azure Databricks 和 Scala 从 Azure Sql 表中删除行,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/61338190/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com