Can I connect to Impala (SQL engine on Hadoop) from DB Connect?

[melonman]

Hi,

I am looking for a way to connect Splunk to Impara DB to pull data periodically?
Some of structured data are stored in HDFS and already mapped to Impara for analytics, and unstructured data is stored in Splunk.
I want to run SQL from Splunk UI.

Would it be possible?

[rdagan_splunk]

In DBX3 under splunk_app_db_connect/drivers you will only need the file
ImpalaJDBC41.jar
Then create splunk_app_db_connect/drivers/ImpalaJDBC41-libs and put all the rest of the libs under that new directory
For example, commons-codec-1.3.jar, commons-logging-1.1.1.jar, ..

[ohoppe]

That was actually the solution beside another problem I had with the Impala configuration on the Hadoop Cluster. Thanks rdagan.

[rdagan_splunk · Accepted solution]

If you are referencing Impala here is what I had to do to get it to work.

Using Splunk DB Connect and Download Cloudera JDBC driver to Impala: http://www.cloudera.com/downloads/connectors/impala/jdbc/2-5-32.html
Extract the Driver to /opt/splunk/etc/apps/splunk_app_db_connect/bin/lib
Create /opt/splunk/etc/apps/splunk_app_db_connect/local/db_connection_types.conf
[Impala]
displayName = Impala
serviceClass = com.splunk.dbx2.DefaultDBX2JDBC
jdbcDriverClass = com.cloudera.impala.jdbc41.Driver
jdbcUrlFormat = jdbc:impala://:/;AuthMech=0;

[melonman]

Thank you! * Yes, that is Impala I wanted to know about.

[ohoppe]

Hi,

at the moment i am trying to get the impala jdbc driver working with DB connect 3.0.3.

I applied the above configuration and the driver appears in the listing. Adding a new connection results in an error message when saving it.

There was an error processing your request. It has been logged (ID d148a7f864f1079a).

looking up the id in the dbx server log

2017-06-09 13:13:11.706 +0100  [dw-101 - POST /api/connections/status] ERROR io.dropwizard.jersey.errors.LoggingExceptionMapper - Error handling a request: d148a7f864f1079a
java.lang.NoClassDefFoundError: Could not initialize class com.cloudera.impala.impala.core.ImpalaJDBCDriver
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:264)
    at com.cloudera.impala.dsi.core.impl.DSIDriverFactory.createDriver(DSIDriverFactory.java:52)
    at com.cloudera.impala.jdbc.common.AbstractDriver.doInitialize(AbstractDriver.java:471)
    at com.cloudera.impala.jdbc.common.AbstractDriver.connect(AbstractDriver.java:207)
    at com.splunk.dbx.service.driver.DelegatingDriver.connect(DelegatingDriver.java:23)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at com.splunk.dbx.connector.ConnectorFactory.getConnectionWithoutPool(ConnectorFactory.java:260)
    at com.splunk.dbx.connector.ConnectorFactory.getConnection(ConnectorFactory.java:233)
    at com.splunk.dbx.connector.ConnectorFactory.create(ConnectorFactory.java:141)
    at com.splunk.dbx.server.api.service.database.impl.DatabaseMetadataServiceImpl.getStatus(DatabaseMetadataServiceImpl.java:133)
    at com.splunk.dbx.server.api.service.database.impl.DatabaseMetadataServiceImpl.getConnectionStatus(DatabaseMetadataServiceImpl.java:111)
    at com.splunk.dbx.server.api.resource.ConnectionResource.getConnectionStatusOfEntity(ConnectionResource.java:66)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.glassfish.jersey.server.model.internal.ResourceMethodInvocationHandlerFactory$1.invoke(ResourceMethodInvocationHandlerFactory.java:81)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher$1.run(AbstractJavaResourceMethodDispatcher.java:144)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.invoke(AbstractJavaResourceMethodDispatcher.java:161)
    at org.glassfish.jersey.server.model.internal.JavaResourceMethodDispatcherProvider$TypeOutInvoker.doDispatch(JavaResourceMethodDispatcherProvider.java:205)
    at org.glassfish.jersey.server.model.internal.AbstractJavaResourceMethodDispatcher.dispatch(AbstractJavaResourceMethodDispatcher.java:99)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.invoke(ResourceMethodInvoker.java:389)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:347)
    at org.glassfish.jersey.server.model.ResourceMethodInvoker.apply(ResourceMethodInvoker.java:102)
    at org.glassfish.jersey.server.ServerRuntime$2.run(ServerRuntime.java:326)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:271)
    at org.glassfish.jersey.internal.Errors$1.call(Errors.java:267)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:315)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:297)
    at org.glassfish.jersey.internal.Errors.process(Errors.java:267)
    at org.glassfish.jersey.process.internal.RequestScope.runInScope(RequestScope.java:317)
    at org.glassfish.jersey.server.ServerRuntime.process(ServerRuntime.java:305)
    at org.glassfish.jersey.server.ApplicationHandler.handle(ApplicationHandler.java:1154)
    at org.glassfish.jersey.servlet.WebComponent.serviceImpl(WebComponent.java:473)
    at org.glassfish.jersey.servlet.WebComponent.service(WebComponent.java:427)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:388)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:341)
    at org.glassfish.jersey.servlet.ServletContainer.service(ServletContainer.java:228)
    at io.dropwizard.jetty.NonblockingServletHolder.handle(NonblockingServletHolder.java:49)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1689)
    at io.dropwizard.servlets.ThreadNameFilter.doFilter(ThreadNameFilter.java:34)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1676)
    at io.dropwizard.jersey.filter.AllowedMethodsFilter.handle(AllowedMethodsFilter.java:50)
    at io.dropwizard.jersey.filter.AllowedMethodsFilter.doFilter(AllowedMethodsFilter.java:44)
    at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1676)
    at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:581)
    at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1174)
    at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:511)
    at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1106)
    at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
    at com.codahale.metrics.jetty9.InstrumentedHandler.handle(InstrumentedHandler.java:240)
    at io.dropwizard.jetty.RoutingHandler.handle(RoutingHandler.java:51)
    at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:396)
    at io.dropwizard.jetty.BiDiGzipHandler.handle(BiDiGzipHandler.java:68)
    at org.eclipse.jetty.server.handler.RequestLogHandler.handle(RequestLogHandler.java:56)
    at org.eclipse.jetty.server.handler.StatisticsHandler.handle(StatisticsHandler.java:169)
    at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134)
    at org.eclipse.jetty.server.Server.handle(Server.java:524)
    at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:319)
    at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:253)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
    at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:186)
    at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:273)
    at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:95)
    at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148)
    at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136)
    at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671)
    at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589)
    at java.lang.Thread.run(Thread.java:745)

I checked that the DB Connect itself is working correctly by connecting it via JDBC to an Oracle instance. I checked that impala itself is working correctly by using hue to query the impala interface.

Can you or anyone share how you got the JDBC driver running. I don't understand that there is a java.lang.NoClassDefFoundError. I placed the ImpalaJDBC4.jar in the drivers folder and any additional jars shipped with the zip file under jars and as well tried it under drivers as well.

Thanks for any hint.
Oliver