Enabling sql on broker

Tommaso_Parisi · March 28, 2019, 11:48am

I have been trying to enable the sql feature on a cluster running 0.12.3 but I have had no success until now. Is there someone who can help me?

I have set the following properties in the broker’s runtime.properties (I have set them also on common.runtime.properties just to be sure)

druid.sql.enable=true
druid.sql.http.enable=true

I have also checked that at startup the broker sees the correct value for these properties

2019-03-28T11:32:19,844 INFO [main] io.druid.cli.CliBroker - * druid.service: druid/broker
2019-03-28T11:32:19,844 INFO [main] io.druid.cli.CliBroker - * druid.sql.enable: true
2019-03-28T11:32:19,845 INFO [main] io.druid.cli.CliBroker - * druid.sql.http.enable: true

Then I tried to perform a POST to the endpoint druid/v2/sql but I obtain a “405 Method not allowed” error

curl -v -X POST -H ‘Content-Type: application/json’ -d ‘{“query”:“select count(*) from data_source”}’ ‘http://127.0.0.1:8080/druid/v2/sql/’

About to connect() to 127.0.0.1 port 8080 (#0)
Trying 127.0.0.1…
Connected to 127.0.0.1 (127.0.0.1) port 8080 (#0)

POST /druid/v2/sql/ HTTP/1.1
User-Agent: curl/7.29.0
Host: 127.0.0.1:8080
Accept: /
Content-Type: application/json
Content-Length: 47

upload completely sent off: 47 out of 47 bytes
< HTTP/1.1 405 Method Not Allowed
< Date: Thu, 28 Mar 2019 11:40:10 GMT
< Allow: DELETE,OPTIONS
< Content-Length: 0
< Server: Jetty(9.3.19.v20170502)
<
Connection #0 to host 127.0.0.1 left intact

I have set the log level to debug on the broker and this is what I see when I perform the request

2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.Server - REQUEST POST /druid/v2/sql/ on HttpChannelOverHttp@398631e6{r=1,c=false,a=DISPATCHED,uri=//127.0.0.1:8080/druid/v2/sql/}
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.handler.gzip.GzipHandler - org.eclipse.jetty.server.handler.gzip.GzipHandler@62d1dc3c handle Request(POST //127.0.0.1:8080/druid/v2/sql/)@7865d069 in null
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.handler.ContextHandler - scope null||/druid/v2/sql/ @ o.e.j.s.ServletContextHandler@46f902e0{/,null,AVAILABLE}
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.handler.ContextHandler - context=||/druid/v2/sql/ @ o.e.j.s.ServletContextHandler@46f902e0{/,null,AVAILABLE}
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.session - sessionManager=org.eclipse.jetty.server.session.HashSessionManager@59c70ceb
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.session - session=null
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.servlet.ServletHandler - servlet ||/druid/v2/sql/ -> org.eclipse.jetty.servlet.DefaultServlet-57545c3f@3d378541==org.eclipse.jetty.servlet.DefaultServlet,jsp=null,order=-1,inst=true
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.servlet.ServletHandler - chain=io.druid.server.security.SecuritySanityCheckFilter-2a39aa2b->io.druid.server.security.AuthenticationWrappingFilter-19e0dffe->io.druid.server.security.AllowOptionsResourceFilter-3976ebfa->io.druid.server.security.PreResponseAuthorizationCheckFilter-1192b58e->com.google.inject.servlet.GuiceFilter-4f8d86e4->org.eclipse.jetty.servlet.DefaultServlet-57545c3f@3d378541==org.eclipse.jetty.servlet.DefaultServlet,jsp=null,order=-1,inst=true
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.servlet.ServletHandler - call filter io.druid.server.security.SecuritySanityCheckFilter-2a39aa2b
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.servlet.ServletHandler - call filter io.druid.server.security.AuthenticationWrappingFilter-19e0dffe
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.servlet.ServletHandler - call filter io.druid.server.security.AllowOptionsResourceFilter-3976ebfa
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.servlet.ServletHandler - call filter io.druid.server.security.PreResponseAuthorizationCheckFilter-1192b58e
2019-03-28T11:19:48,889 DEBUG [qtp1510162775-158] org.eclipse.jetty.servlet.ServletHandler - call filter com.google.inject.servlet.GuiceFilter-4f8d86e4
2019-03-28T11:19:48,892 DEBUG [qtp1510162775-158] com.sun.jersey.spi.container.ContainerResponse - Mapped exception to response: 405
javax.ws.rs.WebApplicationException
at com.sun.jersey.server.impl.uri.rules.TerminatingRule.accept(TerminatingRule.java:66) ~[jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.server.impl.uri.rules.ResourceClassRule.accept(ResourceClassRule.java:108) ~[jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.server.impl.uri.rules.RightHandPathRule.accept(RightHandPathRule.java:147) ~[jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.server.impl.uri.rules.RootResourceClassesRule.accept(RootResourceClassesRule.java:84) ~[jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1542) ~[jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.server.impl.application.WebApplicationImpl._handleRequest(WebApplicationImpl.java:1473) [jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1419) [jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.server.impl.application.WebApplicationImpl.handleRequest(WebApplicationImpl.java:1409) [jersey-server-1.19.3.jar:1.19.3]
at com.sun.jersey.spi.container.servlet.WebComponent.service(WebComponent.java:409) [jersey-servlet-1.19.3.jar:1.19.3]
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:558) [jersey-servlet-1.19.3.jar:1.19.3]
at com.sun.jersey.spi.container.servlet.ServletContainer.service(ServletContainer.java:733) [jersey-servlet-1.19.3.jar:1.19.3]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:790) [javax.servlet-api-3.1.0.jar:3.1.0]
at com.google.inject.servlet.ServletDefinition.doServiceImpl(ServletDefinition.java:286) [guice-servlet-4.1.0.jar:?]
at com.google.inject.servlet.ServletDefinition.doService(ServletDefinition.java:276) [guice-servlet-4.1.0.jar:?]
at com.google.inject.servlet.ServletDefinition.service(ServletDefinition.java:181) [guice-servlet-4.1.0.jar:?]
at com.google.inject.servlet.ManagedServletPipeline.service(ManagedServletPipeline.java:91) [guice-servlet-4.1.0.jar:?]
at com.google.inject.servlet.FilterChainInvocation.doFilter(FilterChainInvocation.java:85) [guice-servlet-4.1.0.jar:?]
at com.google.inject.servlet.ManagedFilterPipeline.dispatch(ManagedFilterPipeline.java:120) [guice-servlet-4.1.0.jar:?]
at com.google.inject.servlet.GuiceFilter.doFilter(GuiceFilter.java:135) [guice-servlet-4.1.0.jar:?]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759) [jetty-servlet-9.3.19.v20170502.jar:9.3.19.v20170502]
at io.druid.server.security.PreResponseAuthorizationCheckFilter.doFilter(PreResponseAuthorizationCheckFilter.java:84) [druid-server-0.12.3.jar:0.12.3]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759) [jetty-servlet-9.3.19.v20170502.jar:9.3.19.v20170502]
at io.druid.server.security.AllowOptionsResourceFilter.doFilter(AllowOptionsResourceFilter.java:76) [druid-server-0.12.3.jar:0.12.3]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759) [jetty-servlet-9.3.19.v20170502.jar:9.3.19.v20170502]
at io.druid.server.security.AllowAllAuthenticator$1.doFilter(AllowAllAuthenticator.java:85) [druid-server-0.12.3.jar:0.12.3]
at io.druid.server.security.AuthenticationWrappingFilter.doFilter(AuthenticationWrappingFilter.java:60) [druid-server-0.12.3.jar:0.12.3]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759) [jetty-servlet-9.3.19.v20170502.jar:9.3.19.v20170502]
at io.druid.server.security.SecuritySanityCheckFilter.doFilter(SecuritySanityCheckFilter.java:86) [druid-server-0.12.3.jar:0.12.3]
at org.eclipse.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1759) [jetty-servlet-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:582) [jetty-servlet-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:224) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1180) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:512) [jetty-servlet-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:185) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1112) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:141) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.handler.gzip.GzipHandler.handle(GzipHandler.java:493) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:52) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]

at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:134) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.Server.handle(Server.java:534) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:320) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:251) [jetty-server-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:283) [jetty-io-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:108) [jetty-io-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.io.SelectChannelEndPoint$2.run(SelectChannelEndPoint.java:93) [jetty-io-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.executeProduceConsume(ExecuteProduceConsume.java:303) [jetty-util-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.produceConsume(ExecuteProduceConsume.java:148) [jetty-util-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.util.thread.strategy.ExecuteProduceConsume.run(ExecuteProduceConsume.java:136) [jetty-util-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:671) [jetty-util-9.3.19.v20170502.jar:9.3.19.v20170502]
at org.eclipse.jetty.util.thread.QueuedThreadPool$2.run(QueuedThreadPool.java:589) [jetty-util-9.3.19.v20170502.jar:9.3.19.v20170502]
at java.lang.Thread.run(Thread.java:745) [?:1.8.0_101]
2019-03-28T11:19:48,893 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.Server - handled=true async=false committed=false on HttpChannelOverHttp@398631e6{r=1,c=false,a=DISPATCHED,uri=//127.0.0.1:8080/druid/v2/sql/}
2019-03-28T11:19:48,893 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.HttpChannelState - HttpChannelState@44d6368f{s=DISPATCHED a=NOT_ASYNC i=true r=NONE/false w=false} unhandle DISPATCHED
2019-03-28T11:19:48,893 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.HttpChannel - HttpChannelOverHttp@398631e6{r=1,c=false,a=COMPLETING,uri=//127.0.0.1:8080/druid/v2/sql/} action COMPLETE
2019-03-28T11:19:48,893 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.handler.gzip.GzipHttpOutputInterceptor - org.eclipse.jetty.server.handler.gzip.GzipHttpOutputInterceptor@5e35999c exclude by status 405
2019-03-28T11:19:48,893 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.HttpChannel - sendResponse info=null content=HeapByteBuffer@35621d0[p=0,l=0,c=0,r=0]={<<<>>>} complete=true committing=true callback=Blocker@74dd8145{null}
2019-03-28T11:19:48,894 DEBUG [qtp1510162775-158] org.eclipse.jetty.server.HttpChannel - COMMIT for /druid/v2/sql/ on HttpChannelOverHttp@398631e6{r=1,c=true,a=COMPLETING,uri=//127.0.0.1:8080/druid/v2/sql/}
405 Method Not Allowed HTTP/1.1
Allow: DELETE,OPTIONS

Thanks,

Tommaso

Divya_Gangwar · March 28, 2019, 11:54am

Hi Tommaso,

The configuration done by you seems correct. Problem here is you are trying to connect to incorrect port. Broker runs on 8082 port(default). This is true only in case you haven’t set specific port for broker in broker runtime properties.

Please try post request with changing the port to 8082. If it doesn’t work, please share your broker runtime properties & common properties file.

Thanks!

Tommaso_Parisi · March 28, 2019, 1:41pm

Hi Divya,

thank for you help. I set a specific port for the broker, which is 8080

Here is the runtime.properties of the broker

druid.port=8080
druid.service=druid/broker

druid.broker.cache.useCache=true
druid.broker.cache.populateCache=true

druid.sql.enable=true
druid.sql.http.enable=true

druid.broker.http.numConnections=5
druid.server.http.numThreads=25

druid.processing.buffer.sizeBytes=536870912
druid.processing.numThreads=7

druid.broker.cache.useCache=true
druid.broker.cache.populateCache=true
druid.cache.type=local
druid.cache.sizeInBytes=2000000000

druid.query.groupBy.defaultStrategy=v2

druid.processing.numMergeBuffers: 10

And this is the common.runtime.properties

druid.extensions.directory=dist/druid/extensions
druid.extensions.hadoopDependenciesDir=hadoop-dependencies
druid.extensions.loadList=[“druid-kafka-eight”, “druid-lookups-cached-global”, “mysql-metadata-storage”, “druid-kafka-indexing-service”, “druid-histogram”]

druid.sql.enable=true
druid.sql.http.enable=true

Justin_Borromeo · March 28, 2019, 5:29pm

Hi Tommaso,

If that latest change didn’t work, you might want to try changing that config from druid.port=8080 to druid.plaintextPort=8080 (see Broker section of http://druid.io/docs/latest/configuration/index.html). Then, once the broker’s listening on 8080, sending the SQL query should work.

Justin

Tommaso_Parisi · March 29, 2019, 7:45am

Thanks Justin,

I have changed the config as you suggested (from druid.port=8080 to druid.plaintextPort=8080), but I continue to receive a 405 error.