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.
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.
ReplyDeleteCould you kindly share more details about "removing throw statements from hive-jdbc unsupported method and recompiling" and "create your own BoneCPPlugin" ?
Thanks!