Thursday, May 16, 2013

Play 2.1.1 Jdbc is not compatible with hive-jdbc-0.10.0-cdh4.2.0

If you want to access a Hive/Impala server in Play 2.1.1, you will encounter a strange error saying "Cannot connect to database [xxx]. Unfortunately no further information can help you identify where is wrong. It is strange because you can using the same URL to connect to the server without any problem.
import java.sql.DriverManager

object ImpalaJdbcClient extends App {

  val impalaUrl = "jdbc:hive2://impala-host:21050/;auth=noSasl"

  val driverClass = Class.forName("org.apache.hive.jdbc.HiveDriver")

  val conn = DriverManager.getConnection(impalaUrl)

  val st = conn.createStatement()
  val rs = st.executeQuery("select count(distinct customer_id) from customers where repeat ='Y'")
  while (rs.next()) {
    println("count=%d".format(rs.getLong(1)))
  }

  conn.close
}
By digging Play and Hive-JDBC code, I figured out that Play-jdbc calls a lot of methods what hive-jdbc doesn't support. For those methods, such as setReadOnly and setCatalog, hive-jdbc just simply throws a SQLException saying "Method not supported", then Play-jdbc catch it and report "Cannot connect to database" error, but unfortunately it doesn't include the message of "Method not supported". You can fix it by removing throw statements from hive-jdbc unsupported method and recompiling. Another way is to create your own BoneCPPlugin. Just copy the source code ./src/play-jdbc/src/main/scala/play/api/db/DB.scala and remove the offending method calls:
  • setAutoCommit
  • commit
  • rollback
  • setTransactionIsolation
  • setReadOnly
  • setCatalog
and comment or replace this line
case mode => Logger("play").info("database [" + ds._2 + "] connected at " + dbURL(ds._1.getConnection))
to
case mode => Logger("play").info("database [" + ds._2 + "] connected at " + ds._1)
because dbURL calls conn.getMetaData.getURL and HiveDatabaseMetaData doesn't support getURL. Change dbplugin in app.configuration.getString("dbplugin").filter(_ == "disabled") to something else to avoid conflict with Play's BoneCPPlugin. Then register your own BoneCPPlugin in conf/play.plugins.

1 comment:

  1. Hi Ben, great post! I'm trying to make the latest play 2.2.1 version work with Hive/Impala and came across your blog.

    Could you kindly share more details about "removing throw statements from hive-jdbc unsupported method and recompiling" and "create your own BoneCPPlugin" ?

    Thanks!

    ReplyDelete