hello, from http://kylin.apache.org/docs16/tutorial/create_cube.html
"You can drag & drop a dimension column to adjust its position in rowkey; Put the mandantory dimension at the begining, then followed the dimensions that heavily involved in filters (where condition). Put high cardinality dimensions ahead of low cardinality dimensions." Other way to improve query performance is use AGG Good Luck !! 2017-04-04 10:01 GMT+01:00 Shailesh Prajapati <shail...@infoworks.io>: > Hi, > > I am using kylin 1.6 and facing a wired issue with Cube description. > Basically, I have two Cube desc having same dimensions, measures, rowkeys, > aggregation groups. The only difference is the ordering of keys in them. > With first cube description, my queries are working and with second i am > getting following exception: > > > java.sql.SQLException: Error while executing SQL "SELECT > sum(ss_ext_sales_price) total_sales, > sum(ss_ext_discount_amt) total_discount, > s_store_id, > s_store_name > FROM > store_sales > LEFT JOIN store > ON (store_sales.ss_store_sk = store.s_store_sk) > GROUP BY > s_store_id, > s_store_name > ORDER BY > total_sales, > total_discount, > s_store_id, > s_store_name > LIMIT 50000": null > at org.apache.calcite.avatica.Helper.createException(Helper. > java:56) > at org.apache.calcite.avatica.Helper.createException(Helper. > java:41) > at org.apache.calcite.avatica.AvaticaStatement.executeInternal( > AvaticaStatement.java:147) > at org.apache.calcite.avatica.AvaticaStatement.executeQuery( > AvaticaStatement.java:208) > at org.apache.kylin.rest.service.QueryService.execute( > QueryService.java:538) > at org.apache.kylin.rest.service.QueryService.queryWithSqlMassage( > QueryService.java:452) > at org.apache.kylin.rest.service.QueryService.query( > QueryService.java:151) > at org.apache.kylin.rest.service.QueryService.doQueryWithCache( > QueryService.java:354) > at org.apache.kylin.rest.controller.QueryController. > query(QueryController.java:69) > 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.springframework.web.method.support.InvocableHandlerMethod. > doInvoke(InvocableHandlerMethod.java:221) > at org.springframework.web.method.support.InvocableHandlerMethod. > invokeForRequest(InvocableHandlerMethod.java:136) > at org.springframework.web.servlet.mvc.method.annotation. > ServletInvocableHandlerMethod.invokeAndHandle( > ServletInvocableHandlerMethod. > java:104) > at org.springframework.web.servlet.mvc.method.annotation. > RequestMappingHandlerAdapter.invokeHandleMethod( > RequestMappingHandlerAdapter.java:743) > at org.springframework.web.servlet.mvc.method.annotation. > RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter. > java:672) > at org.springframework.web.servlet.mvc.method. > AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:82) > at org.springframework.web.servlet.DispatcherServlet. > doDispatch(DispatcherServlet.java:933) > at org.springframework.web.servlet.DispatcherServlet. > doService(DispatcherServlet.java:867) > at org.springframework.web.servlet.FrameworkServlet. > processRequest( > FrameworkServlet.java:951) > at org.springframework.web.servlet.FrameworkServlet. > doPost(FrameworkServlet.java:853) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:650) > at org.springframework.web.servlet.FrameworkServlet. > service(FrameworkServlet.java:827) > at javax.servlet.http.HttpServlet.service(HttpServlet.java:731) > at org.apache.catalina.core.ApplicationFilterChain. > internalDoFilter( > ApplicationFilterChain.java:303) > at org.apache.catalina.core.ApplicationFilterChain.doFilter( > ApplicationFilterChain.java:208) > at org.apache.tomcat.websocket.server.WsFilter.doFilter( > WsFilter.java:52) > at org.apache.catalina.core.ApplicationFilterChain. > internalDoFilter( > ApplicationFilterChain.java:241) > at org.apache.catalina.core.ApplicationFilterChain.doFilter( > ApplicationFilterChain.java:208) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:330) > at org.springframework.security.web.access.intercept. > FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:118) > at org.springframework.security.web.access.intercept. > FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:84) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.access. > ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.authentication. > AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter. > java:113) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.servletapi. > SecurityContextHolderAwareRequestFilter.doFilter( > SecurityContextHolderAwareRequestFilter.java:54) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at io.infoworks.security.jwt.JwtAuthenticationTokenFilter. > successfulAuthentication(JwtAuthenticationTokenFilter.java:64) > at org.springframework.security.web.authentication. > AbstractAuthenticationProcessingFilter.doFilter( > AbstractAuthenticationProcessingFilter.java:219) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.context. > SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilt > er.java:87) > at org.springframework.security.web.FilterChainProxy$ > VirtualFilterChain.doFilter(FilterChainProxy.java:342) > at org.springframework.security.web.FilterChainProxy. > doFilterInternal(FilterChainProxy.java:192) > at org.springframework.security.web.FilterChainProxy.doFilter( > FilterChainProxy.java:160) > at org.springframework.web.filter.DelegatingFilterProxy. > invokeDelegate(DelegatingFilterProxy.java:343) > at org.springframework.web.filter.DelegatingFilterProxy.doFilter( > DelegatingFilterProxy.java:260) > at org.apache.catalina.core.ApplicationFilterChain. > internalDoFilter( > ApplicationFilterChain.java:241) > at org.apache.catalina.core.ApplicationFilterChain.doFilter( > ApplicationFilterChain.java:208) > at com.thetransactioncompany.cors.CORSFilter.doFilter( > CORSFilter.java:209) > at com.thetransactioncompany.cors.CORSFilter.doFilter( > CORSFilter.java:244) > at org.apache.catalina.core.ApplicationFilterChain. > internalDoFilter( > ApplicationFilterChain.java:241) > at org.apache.catalina.core.ApplicationFilterChain.doFilter( > ApplicationFilterChain.java:208) > at org.apache.catalina.core.StandardWrapperValve.invoke( > StandardWrapperValve.java:220) > at org.apache.catalina.core.StandardContextValve.invoke( > StandardContextValve.java:122) > at org.apache.catalina.authenticator.AuthenticatorBase.invoke( > AuthenticatorBase.java:505) > at org.apache.catalina.core.StandardHostValve.invoke( > StandardHostValve.java:169) > at org.apache.catalina.valves.ErrorReportValve.invoke( > ErrorReportValve.java:103) > at org.apache.catalina.valves.AccessLogValve.invoke( > AccessLogValve.java:956) > at org.apache.catalina.core.StandardEngineValve.invoke( > StandardEngineValve.java:116) > at org.apache.catalina.connector.CoyoteAdapter.service( > CoyoteAdapter.java:436) > at org.apache.coyote.http11.AbstractHttp11Processor.process( > AbstractHttp11Processor.java:1078) > at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler. > process(AbstractProtocol.java:625) > at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor. > run(JIoEndpoint.java:316) > at java.util.concurrent.ThreadPoolExecutor.runWorker( > ThreadPoolExecutor.java:1142) > at java.util.concurrent.ThreadPoolExecutor$Worker.run( > ThreadPoolExecutor.java:617) > at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run( > TaskThread.java:61) > at java.lang.Thread.run(Thread.java:745) > Caused by: java.lang.IllegalStateException > at org.apache.kylin.gridtable.GTInfo.validateColumnBlocks( > GTInfo.java:198) > at org.apache.kylin.gridtable.GTInfo.validate(GTInfo.java:167) > at org.apache.kylin.gridtable.GTInfo$Builder.build(GTInfo. > java:269) > at org.apache.kylin.cube.gridtable.CubeGridTable. > newGTInfo(CubeGridTable.java:53) > at org.apache.kylin.cube.gridtable.CubeGridTable. > newGTInfo(CubeGridTable.java:36) > at org.apache.kylin.storage.gtrecord.CubeScanRangePlanner. > <init>(CubeScanRangePlanner.java:88) > at org.apache.kylin.storage.gtrecord.CubeSegmentScanner.< > init>(CubeSegmentScanner.java:74) > at org.apache.kylin.storage.gtrecord. > GTCubeStorageQueryBase.search( > GTCubeStorageQueryBase.java:130) > at org.apache.kylin.query.enumerator.OLAPEnumerator. > queryStorage(OLAPEnumerator.java:114) > at org.apache.kylin.query.enumerator.OLAPEnumerator. > moveNext(OLAPEnumerator.java:65) > at Baz$1$1.moveNext(Unknown Source) > at org.apache.calcite.linq4j.EnumerableDefaults.groupBy_( > EnumerableDefaults.java:823) > at org.apache.calcite.linq4j.EnumerableDefaults.groupBy( > EnumerableDefaults.java:758) > at org.apache.calcite.linq4j.DefaultEnumerable.groupBy( > DefaultEnumerable.java:302) > at Baz.bind(Unknown Source) > at org.apache.calcite.jdbc.CalcitePrepare$ > CalciteSignature.enumerable(CalcitePrepare.java:327) > at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable( > CalciteConnectionImpl.java:282) > at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable( > CalciteMetaImpl.java:553) > at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable( > CalciteMetaImpl.java:544) > at org.apache.calcite.avatica.AvaticaResultSet.execute( > AvaticaResultSet.java:187) > at org.apache.calcite.jdbc.CalciteResultSet.execute( > CalciteResultSet.java:65) > at org.apache.calcite.jdbc.CalciteResultSet.execute( > CalciteResultSet.java:44) > at org.apache.calcite.avatica.AvaticaConnection$1.execute( > AvaticaConnection.java:605) > at org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute( > CalciteMetaImpl.java:599) > at org.apache.calcite.avatica.AvaticaConnection. > prepareAndExecuteInternal(AvaticaConnection.java:613) > at org.apache.calcite.avatica.AvaticaStatement.executeInternal( > AvaticaStatement.java:139) > ... 68 more > > > *cube desc 1: * > https://gist.github.com/shaipraj/a09991ad1599bca5f43b38c5565dbebe > *cube desc 2: * > https://gist.github.com/shaipraj/527cec8f03d55234c63d80b06353b7de > > > When i reordered the rowkeys in cube desc 2, my queries worked. My > question, are these rowkeys needs to be in order? if yes then on what > basis? and if not then what this exception is about? > > > Thanks >
