使用CLOB错误将JDBC火花到DashDB(DB2) [英] Spark JDBC to DashDB (DB2) with CLOB errors

问题描述

我正在将我的spark应用程序连接到DashDB.目前，我可以加载数据了.

I am working to connect my spark application to DashDB. Currently, I can load my data just fine.

但是，我无法将DataFrame保存到DashDB.

However, I am unable to save a DataFrame to DashDB.

任何见解都会有所帮助.

Any insight will be helpful.

  var jdbcSets = sqlContext.read.format("jdbc").options(Map("url" -> url, "driver" -> driver, "dbtable" -> "setsrankval")).load()
  jdbcSets.registerTempTable("setsOpponentRanked")
  jdbcSets = jdbcSets.coalesce(10)
  sqlContext.cacheTable("setsOpponentRanked")

但是，当我尝试保存大型DataFrame时，出现错误消息:

However, when I try to save large DataFrames, I get the error:

DB2 SQL错误:SQLCODE = -1666，SQLSTATE = 42613，SQLERRMC = CLOB，DRIVER = 4.19.26

我用于保存数据的代码如下:

The code I use to save the data is as follows:

val writeproperties = new Properties()
  writeproperties.setProperty("user", "dashXXXX")
  writeproperties.setProperty("password", "XXXXXX")
  writeproperties.setProperty("rowId", "false")
  writeproperties.setProperty("driver", "com.ibm.db2.jcc.DB2Driver")
  results.write.mode(SaveMode.Overwrite).jdbc(writeurl, "players_stat_temp", writeproperties)

可以在此处查看示例测试数据集:

An example test data set can be seen here:

println("Test set: "+results.first()) 
Test set: ['Damir DZUMHUR','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test','test',null,null,null,null,null,null,null]

DataFrame架构如下:

The DataFrame schema is as follows:

    root
 |-- PLAYER: string (nullable = true)
 |-- set01: string (nullable = true)
 |-- set02: string (nullable = true)
 |-- set12: string (nullable = true)
 |-- set01weakseed: string (nullable = true)
 |-- set01medseed: string (nullable = true)
 |-- set01strongseed: string (nullable = true)
 |-- set02weakseed: string (nullable = true)
 |-- set02medseed: string (nullable = true)
 |-- set02strongseed: string (nullable = true)
 |-- set12weakseed: string (nullable = true)
 |-- set12medseed: string (nullable = true)
 |-- set12strongseed: string (nullable = true)
 |-- set01weakrank: string (nullable = true)
 |-- set01medrank: string (nullable = true)
 |-- set01strongrank: string (nullable = true)
 |-- set02weakrank: string (nullable = true)
 |-- set02medrank: string (nullable = true)
 |-- set02strongrank: string (nullable = true)
 |-- set12weakrank: string (nullable = true)
 |-- set12medrank: string (nullable = true)
 |-- set12strongrank: string (nullable = true)
 |-- minibreak: string (nullable = true)
 |-- minibreakweakseed: string (nullable = true)
 |-- minibreakmedseed: string (nullable = true)
 |-- minibreakstrongseed: string (nullable = true)
 |-- minibreakweakrank: string (nullable = true)
 |-- minibreakmedrank: string (nullable = true)
 |-- minibreakstrongrank: string (nullable = true)

我查看了jdbc DB2Dialect，并发现StringType的代码已映射到CLOB.我想知道以下内容是否有帮助:

I have looked at the jdbc DB2Dialect and see that the code for StringType gets mapped to CLOB. I wonder if the following will help:

private object DB2CustomDialect extends JdbcDialect {
    override def canHandle(url: String): Boolean = url.startsWith("jdbc:db2")
    override def getJDBCType(dt: DataType): Option[JdbcType] = dt match {
            case StringType => Option(JdbcType("VARCHAR(10000)", java.sql.Types.VARCHAR))
            case BooleanType => Option(JdbcType("CHAR(1)", java.sql.Types.CHAR))
            case _ => None
    }
}

推荐答案

通过添加自定义方言，效果很好.

Works well by adding a custom dialect.

JdbcDialects.registerDialect(new DB2CustomDialect())

这篇关于使用CLOB错误将JDBC火花到DashDB(DB2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！