Databricks-写入Azure Synapse时出错

Question

我正在尝试使用以下代码将数据写入带有标识字段的Azure Synapse表：

数据砖上的代码

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上目标表的架构

数据块的配置详细信息

数据库运行时7.4（包括Apache Spark 3.0.1、Scala 2.12）

错误消息

Py4JJavaError：调用o457.save.时出错：com.databricks.spark.sqldw.SqlDWSideException：Azure Synapse Analytics无法执行连接器生成的JDBC查询。基础SQLException（s）：-com.microsoft.sqlserver.jdbc.SQLServerException：只有在使用列列表并且IDENTITY_INSERT为ON时，才能指定表中标识列的显式值

代码在databricks运行时6.4 Spark 2.4.5上工作正常，当我尝试升级dbk运行时时，我就会遇到这个错误。我怎样才能让它工作？

Answer 1

您没有额外的行“1 AS IsActiveRow”吗？我在Schema中没有看到这一点

dftraffic = spark.sql('SELECT distinct SourceName\
              ,1 AS IsActiveRow \
             ,"Pipe-123" as pipelineId \
              ,current_timestamp as ADFCreatedDateTime \
              ,current_timestamp as ADFModifiedDateTime \
              from deltaTable)