databricks-写入azure synapse时出错

anauzrmj  于 2021-07-14  发布在  Spark
关注(0)|答案(0)|浏览(268)

我正试图用以下代码将数据写入带有标识字段的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时,就遇到了这个错误。我怎样才能让它工作?

暂无答案!

目前还没有任何答案,快来回答吧!

相关问题