All Apps and Add-ons

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

melonman
Motivator

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?

0 Karma
1 Solution

rdagan_splunk
Splunk Employee
Splunk Employee

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;

View solution in original post

rdagan_splunk
Splunk Employee
Splunk Employee

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
SplunkTrust
SplunkTrust

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

0 Karma

rdagan_splunk
Splunk Employee
Splunk Employee

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;

View solution in original post

melonman
Motivator

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

0 Karma

ohoppe
SplunkTrust
SplunkTrust

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

0 Karma