我正试图用以下代码将数据写入带有标识字段的azuresynapse表
数据框上的代码
def get_jdbc_connection(host, sqlDatabase, user, password):
jdbcHostname = "{}.database.windows.net".format(host)
jdbc_url = "jdbc:sqlserver://{}:1433;database={};user={}@{};password={};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;".format(jdbcHostname, sqlDatabase, user, host, password)
url = "jdbc:sqlserver://{}:1433;database={};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;".format(jdbcHostname, sqlDatabase)
return (jdbc_url,url )
def write_adw(spark, df_target_adw, jdbc_url, table, tempDir, option_mode, pre_Actions ):
df_target_adw.write.format("com.databricks.spark.sqldw") \
.option("url", jdbc_url) \
.option("useAzureMSI", "true") \
.option("preActions", pre_Actions) \
.option("dbTable", table) \
.option("tempDir", tempDir) \
.mode(option_mode) \
.save()
dftraffic = spark.sql('SELECT distinct SourceName\
,1 AS IsActiveRow \
,"Pipe-123" as pipelineId \
,current_timestamp as ADFCreatedDateTime \
,current_timestamp as ADFModifiedDateTime \
from deltaTable)
# write to ADW
(jdbc_url, url_adw) = get_jdbc_connection(host, sqlDatawarehouse,user, password)
target_table = 'TargetTable_name'
option_mode= "append"
pre_Actions= " SELECT GETDATE()"
write_adw(spark, dftraffic, jdbc_url, target_table, tempDir, option_mode, pre_Actions )
adw上目标表的架构
列名数据typesourcesidint identity(1,1)not nullsource namevarchar(20)not nullpipelineidvarchar(20)not nulladfcreateddatetimedatetime not nulladfmodifieddatetimedatetime not null
databricks的配置详细信息
databricks runtime 7.4(包括apache spark 3.0.1、scala 2.12)
错误消息
py4jjavaerror:调用o457.save时出错:com.databricks.spark.sqldw.sqldwsideexception:azure synapse analytics无法执行连接器生成的jdbc查询。基础sqlexception:-com.microsoft.sqlserver.jdbc.sqlserverexception:只有在使用列列表并且启用identity\u insert时,才能为表中的标识列指定显式值
在databricks runtime 6.4 spark 2.4.5上,代码运行得很好,当我尝试升级dbk runtime时,就遇到了这个错误。我怎样才能让它工作?
暂无答案!
目前还没有任何答案,快来回答吧!