You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@kylin.apache.org by lancelot chen <cj...@gmail.com> on 2016/05/13 07:06:36 UTC
Usage of TOP_N measure
I'm confusing about usage of TOP_N measure.
I have a cube with TOP_N measure built in, measure definition is:
{
"name": "PV_1D_001_TOP100",
"function": {
"expression": "TOP_N",
"parameter": {
"type": "column",
"value": "PV_1D_001",
"next_parameter": {
"type": "column",
"value": "INDS",
"next_parameter": null
}
},
"returntype": "topn(100)"
},
"dependent_measure_ref": null
}
But I can't get this measure by using either "SUM | GROUP BY | ORDER BY"
nor "SELECT TOP_N | GROUP BY". Error message is:
When using SUM | GROUP BY | ORDER BY:
Error while executing SQL "select sum(pv_1d_001) pv from test group
by INDS order by pv desc LIMIT 50000": Can't find any realization. Please
confirm with providers. SQL digest: fact table LBS.LBS_AD_PV_1D,group by
[LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
[expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
nextParam=null], returnType=null]]
When using SELECT TOP_N | GROUP BY:
Error while executing SQL "select top_n(pv_1d_001) pv from test
group by INDS LIMIT 50000": From line 2, column 5 to line 2, column 20: No
match found for function signature TOP_N(<NUMERIC>)
Re: Usage of TOP_N measure
Posted by ShaoFeng Shi <sh...@apache.org>.
Cool, thanks for the feedback;
2016-05-14 14:54 GMT+08:00 lancelot chen <cj...@gmail.com>:
> I have successfully get the top100 measure by add all dimensions to
> "includes" field, thanks a lot!
>
--
Best regards,
Shaofeng Shi
Re: Usage of TOP_N measure
Posted by lancelot chen <cj...@gmail.com>.
I have successfully get the top100 measure by add all dimensions to
"includes" field, thanks a lot!
Re: Usage of TOP_N measure
Posted by ShaoFeng Shi <sh...@apache.org>.
To by pass it, you can add all dimension to the aggregation group
"includes" field.
2016-05-13 18:19 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
> This is a bug, open a jira to track it:
> https://issues.apache.org/jira/browse/KYLIN-1687
>
>
> 2016-05-13 17:45 GMT+08:00 lancelot chen <cj...@gmail.com>:
>
>> After disable this cube and remove "INDS" from dimenssions, I got another
>> error message:
>>
>> 2016-05-13 09:41:04,674 ERROR [http-bio-8887-exec-1]
>> controller.QueryController:209 : Exception when execute sql
>> java.sql.SQLException: Error while executing SQL "select
>> sum(pv_1d_001) pv
>> from lbs_ad_pv_1d
>> group by INDS
>> order by sum(pv_1d_001) desc
>> limit 100": 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:143)
>> at
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
>> at
>> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
>> at
>> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
>> at org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
>> at
>> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
>> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>> at
>> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
>> at
>> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
>> at
>> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
>> at
>> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>> at java.lang.reflect.Method.invoke(Method.java:601)
>> at
>> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>> at
>> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>> at
>> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>> at
>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>> at
>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>> at
>> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>> at
>> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>> at
>> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>> at
>> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>> at
>> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>> 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
>> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>> at
>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>> at
>> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>> at
>> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>> at
>> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>> at
>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>> at
>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>> 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:195)
>> at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>> 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:504)
>> at
>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>> at
>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>> at
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>> at
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>> at
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>> at
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>> at
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>> at
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>> at
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>> at
>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>> at java.lang.Thread.run(Thread.java:722)
>> Caused by: java.util.NoSuchElementException
>> at java.util.ArrayList$Itr.next(ArrayList.java:794)
>> at java.util.Collections.min(Collections.java:665)
>> at
>> org.apache.kylin.cube.cuboid.Cuboid.translateToValidCuboid(Cuboid.java:201)
>> at
>> org.apache.kylin.cube.cuboid.Cuboid.translateToValidCuboid(Cuboid.java:125)
>> at org.apache.kylin.cube.cuboid.Cuboid.findById(Cuboid.java:67)
>> at
>> org.apache.kylin.storage.hbase.cube.v2.CubeStorageQuery.identifyCuboid(CubeStorageQuery.java:183)
>> at
>> org.apache.kylin.storage.hbase.cube.v2.CubeStorageQuery.search(CubeStorageQuery.java:96)
>> at
>> org.apache.kylin.query.enumerator.OLAPEnumerator.queryStorage(OLAPEnumerator.java:125)
>> at
>> org.apache.kylin.query.enumerator.OLAPEnumerator.moveNext(OLAPEnumerator.java:71)
>> at Baz$1$1.moveNext(Unknown Source)
>> at
>> org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:819)
>> at
>> org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:754)
>> 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:326)
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:281)
>> at
>> org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:545)
>> at
>> org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:536)
>> 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:566)
>> at
>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:578)
>> at
>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
>> at
>> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
>> ... 80 more
>>
>> And definition json is below:
>>
>> {
>>
>> "uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
>>
>> "version": "1.5.1",
>>
>> "name": "lbs_ad_pv_top100_1d_cube",
>>
>> "description": "",
>>
>> "dimensions": [
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.QUERY",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "QUERY",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.CITY_NAME",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "CITY_NAME",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.AD_TYPE",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "AD_TYPE",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.MAP_NAME",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "MAP_NAME",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.DS",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "DS",
>>
>> "derived": null
>>
>> }
>>
>> ],
>>
>> "measures": [
>>
>> {
>>
>> "name": "PV_1D_001_TOP100",
>>
>> "function": {
>>
>> "expression": "TOP_N",
>>
>> "parameter": {
>>
>> "type": "column",
>>
>> "value": "PV_1D_001",
>>
>> "next_parameter": {
>>
>> "type": "column",
>>
>> "value": "INDS",
>>
>> "next_parameter": null
>>
>> }
>>
>> },
>>
>> "returntype": "topn(100)"
>>
>> },
>>
>> "dependent_measure_ref": null
>>
>> },
>>
>> {
>>
>> "name": "CNT",
>>
>> "function": {
>>
>> "expression": "COUNT",
>>
>> "parameter": {
>>
>> "type": "constant",
>>
>> "value": "1",
>>
>> "next_parameter": null
>>
>> },
>>
>> "returntype": "bigint"
>>
>> },
>>
>> "dependent_measure_ref": null
>>
>> }
>>
>> ],
>>
>> "rowkey": {
>>
>> "rowkey_columns": [
>>
>> {
>>
>> "column": "QUERY",
>>
>> "encoding": "fixed_length:255",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "CITY_NAME",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "AD_TYPE",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "MAP_NAME",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "DS",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> }
>>
>> ]
>>
>> },
>>
>> "signature": "E9yIk19S/zfk/Lv1OlnExQ==",
>>
>> "last_modified": 1463131465268,
>>
>> "model_name": "lbs_ad_pv_1d_model",
>>
>> "null_string": null,
>>
>> "hbase_mapping": {
>>
>> "column_family": [
>>
>> {
>>
>> "name": "F1",
>>
>> "columns": [
>>
>> {
>>
>> "qualifier": "M",
>>
>> "measure_refs": [
>>
>> "PV_1D_001_TOP100",
>>
>> "CNT"
>>
>> ]
>>
>> }
>>
>> ]
>>
>> }
>>
>> ]
>>
>> },
>>
>> "aggregation_groups": [
>>
>> {
>>
>> "includes": [
>>
>> "DS"
>>
>> ],
>>
>> "select_rule": {
>>
>> "hierarchy_dims": [],
>>
>> "mandatory_dims": [
>>
>> "DS"
>>
>> ],
>>
>> "joint_dims": []
>>
>> }
>>
>> }
>>
>> ],
>>
>> "notify_list": [],
>>
>> "status_need_notify": [],
>>
>> "partition_date_start": 1462320000000,
>>
>> "partition_date_end": 3153600000000,
>>
>> "auto_merge_time_ranges": [],
>>
>> "retention_range": 0,
>>
>> "engine_type": 2,
>>
>> "storage_type": 2,
>>
>> "override_kylin_properties": {}
>>
>> }
>>
>> 2016-05-13 17:18 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
>>
>>> ok I see the problem: "INDS" has already been defined as a dimension,
>>> then it should not be used in TopN. Please disable this cube, remove "INDS"
>>> from dimension, save it, and then rebuild.
>>>
>>> In v1.5.2 Kylin will check and report error for such case before saving
>>> the cube: https://issues.apache.org/jira/browse/KYLIN-1631
>>>
>>> 2016-05-13 17:07 GMT+08:00 lancelot chen <cj...@gmail.com>:
>>>
>>>> Thanks for quick reply. Below is my cube definition json:
>>>> {
>>>>
>>>> "uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
>>>>
>>>> "version": "1.5.1",
>>>>
>>>> "name": "lbs_ad_pv_top100_1d_cube",
>>>>
>>>> "description": "",
>>>>
>>>> "dimensions": [
>>>>
>>>> {
>>>>
>>>> "name": "LBS.LBS_AD_PV_1D.QUERY",
>>>>
>>>> "table": "LBS.LBS_AD_PV_1D",
>>>>
>>>> "column": "QUERY",
>>>>
>>>> "derived": null
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "name": "LBS.LBS_AD_PV_1D.CITY_NAME",
>>>>
>>>> "table": "LBS.LBS_AD_PV_1D",
>>>>
>>>> "column": "CITY_NAME",
>>>>
>>>> "derived": null
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "name": "LBS.LBS_AD_PV_1D.AD_TYPE",
>>>>
>>>> "table": "LBS.LBS_AD_PV_1D",
>>>>
>>>> "column": "AD_TYPE",
>>>>
>>>> "derived": null
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "name": "LBS.LBS_AD_PV_1D.INDS",
>>>>
>>>> "table": "LBS.LBS_AD_PV_1D",
>>>>
>>>> "column": "INDS",
>>>>
>>>> "derived": null
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "name": "LBS.LBS_AD_PV_1D.MAP_NAME",
>>>>
>>>> "table": "LBS.LBS_AD_PV_1D",
>>>>
>>>> "column": "MAP_NAME",
>>>>
>>>> "derived": null
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "name": "LBS.LBS_AD_PV_1D.DS",
>>>>
>>>> "table": "LBS.LBS_AD_PV_1D",
>>>>
>>>> "column": "DS",
>>>>
>>>> "derived": null
>>>>
>>>> }
>>>>
>>>> ],
>>>>
>>>> "measures": [
>>>>
>>>> {
>>>>
>>>> "name": "PV_1D_001_TOP100",
>>>>
>>>> "function": {
>>>>
>>>> "expression": "TOP_N",
>>>>
>>>> "parameter": {
>>>>
>>>> "type": "column",
>>>>
>>>> "value": "PV_1D_001",
>>>>
>>>> "next_parameter": {
>>>>
>>>> "type": "column",
>>>>
>>>> "value": "INDS",
>>>>
>>>> "next_parameter": null
>>>>
>>>> }
>>>>
>>>> },
>>>>
>>>> "returntype": "topn(100)"
>>>>
>>>> },
>>>>
>>>> "dependent_measure_ref": null
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "name": "CNT",
>>>>
>>>> "function": {
>>>>
>>>> "expression": "COUNT",
>>>>
>>>> "parameter": {
>>>>
>>>> "type": "constant",
>>>>
>>>> "value": "1",
>>>>
>>>> "next_parameter": null
>>>>
>>>> },
>>>>
>>>> "returntype": "bigint"
>>>>
>>>> },
>>>>
>>>> "dependent_measure_ref": null
>>>>
>>>> }
>>>>
>>>> ],
>>>>
>>>> "rowkey": {
>>>>
>>>> "rowkey_columns": [
>>>>
>>>> {
>>>>
>>>> "column": "INDS",
>>>>
>>>> "encoding": "dict",
>>>>
>>>> "isShardBy": false
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "column": "QUERY",
>>>>
>>>> "encoding": "fixed_length:255",
>>>>
>>>> "isShardBy": false
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "column": "CITY_NAME",
>>>>
>>>> "encoding": "dict",
>>>>
>>>> "isShardBy": false
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "column": "AD_TYPE",
>>>>
>>>> "encoding": "dict",
>>>>
>>>> "isShardBy": false
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "column": "MAP_NAME",
>>>>
>>>> "encoding": "dict",
>>>>
>>>> "isShardBy": false
>>>>
>>>> },
>>>>
>>>> {
>>>>
>>>> "column": "DS",
>>>>
>>>> "encoding": "dict",
>>>>
>>>> "isShardBy": false
>>>>
>>>> }
>>>>
>>>> ]
>>>>
>>>> },
>>>>
>>>> "signature": "eqmmsW5dmIjABLUOoeDQGg==",
>>>>
>>>> "last_modified": 1463048780867,
>>>>
>>>> "model_name": "lbs_ad_pv_1d_model",
>>>>
>>>> "null_string": null,
>>>>
>>>> "hbase_mapping": {
>>>>
>>>> "column_family": [
>>>>
>>>> {
>>>>
>>>> "name": "F1",
>>>>
>>>> "columns": [
>>>>
>>>> {
>>>>
>>>> "qualifier": "M",
>>>>
>>>> "measure_refs": [
>>>>
>>>> "PV_1D_001_TOP100",
>>>>
>>>> "CNT"
>>>>
>>>> ]
>>>>
>>>> }
>>>>
>>>> ]
>>>>
>>>> }
>>>>
>>>> ]
>>>>
>>>> },
>>>>
>>>> "aggregation_groups": [
>>>>
>>>> {
>>>>
>>>> "includes": [
>>>>
>>>> "INDS"
>>>>
>>>> ],
>>>>
>>>> "select_rule": {
>>>>
>>>> "hierarchy_dims": [],
>>>>
>>>> "mandatory_dims": [],
>>>>
>>>> "joint_dims": []
>>>>
>>>> }
>>>>
>>>> }
>>>>
>>>> ],
>>>>
>>>> "notify_list": [],
>>>>
>>>> "status_need_notify": [],
>>>>
>>>> "partition_date_start": 1462320000000,
>>>>
>>>> "partition_date_end": 3153600000000,
>>>>
>>>> "auto_merge_time_ranges": [],
>>>>
>>>> "retention_range": 0,
>>>>
>>>> "engine_type": 2,
>>>>
>>>> "storage_type": 2,
>>>>
>>>> "override_kylin_properties": {}
>>>>
>>>> }
>>>>
>>>>
>>>> I restarted server and rerun query using SUM | GROUP BY | ORDER BY ,
>>>> and I got the following error message:
>>>>
>>>>
>>>> 2016-05-13 09:04:44,557 INFO [http-bio-8887-exec-4]
>>>> controller.QueryController:174 : Using project: lbs
>>>>
>>>> 2016-05-13 09:04:44,564 INFO [http-bio-8887-exec-4]
>>>> controller.QueryController:175 : The original query: select
>>>>
>>>> sum(pv_1d_001) pv
>>>>
>>>> from lbs_ad_pv_1d
>>>>
>>>> group by INDS
>>>>
>>>> order by pv desc
>>>>
>>>>
>>>> 2016-05-13 09:04:44,592 INFO [http-bio-8887-exec-4]
>>>> service.QueryService:264 : The corrected query: select
>>>>
>>>> sum(pv_1d_001) pv
>>>>
>>>> from lbs_ad_pv_1d
>>>>
>>>> group by INDS
>>>>
>>>> order by pv desc
>>>>
>>>> LIMIT 50000
>>>>
>>>> 2016-05-13 09:04:44,595 DEBUG [http-bio-8887-exec-4]
>>>> service.CacheService:144 : Creating a new data source, OLAP data source
>>>> pointing to kylin_metadata@hbase
>>>>
>>>> 2016-05-13 09:04:44,600 INFO [http-bio-8887-exec-4]
>>>> project.ProjectL2Cache:172 : Loading L2 project cache for LBS
>>>>
>>>> 2016-05-13 09:04:44,619 INFO [http-bio-8887-exec-4]
>>>> schema.OLAPSchemaFactory:135 : Schema json:{
>>>>
>>>> "version": "1.0",
>>>>
>>>> "defaultSchema": "LBS",
>>>>
>>>> "schemas": [
>>>>
>>>> {
>>>>
>>>> "type": "custom",
>>>>
>>>> "name": "LBS",
>>>>
>>>> "factory":
>>>> "org.apache.kylin.query.schema.OLAPSchemaFactory",
>>>>
>>>> "operand": {
>>>>
>>>> "project": "LBS"
>>>>
>>>> },
>>>>
>>>> "functions": [
>>>>
>>>> {
>>>>
>>>> name: 'MASSIN',
>>>>
>>>> className: 'org.apache.kylin.query.udf.MassInUDF'
>>>>
>>>> }
>>>>
>>>> ]
>>>>
>>>> }
>>>>
>>>> ]
>>>>
>>>> }
>>>>
>>>> 2016-05-13 09:04:44,620 DEBUG [http-bio-8887-exec-4]
>>>> service.CacheService:149 : The new temp olap json is :{
>>>>
>>>> "version": "1.0",
>>>>
>>>> "defaultSchema": "LBS",
>>>>
>>>> "schemas": [
>>>>
>>>> {
>>>>
>>>> "type": "custom",
>>>>
>>>> "name": "LBS",
>>>>
>>>> "factory":
>>>> "org.apache.kylin.query.schema.OLAPSchemaFactory",
>>>>
>>>> "operand": {
>>>>
>>>> "project": "LBS"
>>>>
>>>> },
>>>>
>>>> "functions": [
>>>>
>>>> {
>>>>
>>>> name: 'MASSIN',
>>>>
>>>> className: 'org.apache.kylin.query.udf.MassInUDF'
>>>>
>>>> }
>>>>
>>>> ]
>>>>
>>>> }
>>>>
>>>> ]
>>>>
>>>> }
>>>>
>>>>
>>>> 2016-05-13 09:04:46,129 INFO [http-bio-8887-exec-4]
>>>> routing.QueryRouter:48 : The project manager's reference is
>>>> org.apache.kylin.metadata.project.ProjectManager@ece372
>>>>
>>>> 2016-05-13 09:04:46,131 INFO [http-bio-8887-exec-4]
>>>> routing.QueryRouter:60 : Find candidates by table LBS.LBS_AD_PV_1D and
>>>> project=LBS : org.apache.kylin.query.routing.Candidate@1f580d76
>>>>
>>>> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
>>>> cube.CubeCapabilityChecker:85 : Exclude cube lbs_ad_pv_top100_1d_cube
>>>> because unmatched aggregations
>>>>
>>>> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
>>>> routing.QueryRouter:49 : Applying rule: class
>>>> org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule,
>>>> realizations before: [lbs_ad_pv_top100_1d_cube(CUBE)], realizations after:
>>>> []
>>>>
>>>> 2016-05-13 09:04:46,137 INFO [http-bio-8887-exec-4]
>>>> routing.QueryRouter:49 : Applying rule: class
>>>> org.apache.kylin.query.routing.rules.RealizationSortRule, realizations
>>>> before: [], realizations after: []
>>>>
>>>> 2016-05-13 09:04:46,139 ERROR [http-bio-8887-exec-4]
>>>> controller.QueryController:209 : Exception when execute sql
>>>>
>>>> java.sql.SQLException: Error while executing SQL "select
>>>>
>>>> sum(pv_1d_001) pv
>>>>
>>>> from lbs_ad_pv_1d
>>>>
>>>> group by INDS
>>>>
>>>> order by pv desc
>>>>
>>>> LIMIT 50000": Can't find any realization. Please confirm with
>>>> providers. SQL digest: fact table LBS.LBS_AD_PV_1D,group by
>>>> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
>>>> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
>>>> nextParam=null], returnType=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:143)
>>>>
>>>> at
>>>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
>>>>
>>>> at
>>>> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
>>>>
>>>> at
>>>> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
>>>>
>>>> at
>>>> org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
>>>>
>>>> at
>>>> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
>>>>
>>>> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>>>>
>>>> at
>>>> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
>>>>
>>>> at
>>>> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
>>>>
>>>> at
>>>> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
>>>>
>>>> at
>>>> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>>>>
>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>>
>>>> at
>>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>>>
>>>> at
>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>>>
>>>> at java.lang.reflect.Method.invoke(Method.java:601)
>>>>
>>>> at
>>>> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>>>>
>>>> at
>>>> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>>>>
>>>> at
>>>> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>>>>
>>>> at
>>>> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>>>>
>>>> at
>>>> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>>>>
>>>> at
>>>> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>>>>
>>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>>>>
>>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>>>
>>>> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>>>>
>>>> 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
>>>> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>>>>
>>>> at
>>>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>>>>
>>>> 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:195)
>>>>
>>>> at
>>>> com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>>>>
>>>> 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:504)
>>>>
>>>> at
>>>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>>>>
>>>> at
>>>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>>>>
>>>> at
>>>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>>>>
>>>> at
>>>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>>>>
>>>> at
>>>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>>>>
>>>> at
>>>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>>>>
>>>> at
>>>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>>>>
>>>> at
>>>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>>>>
>>>> at
>>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>>>>
>>>> at
>>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>>>>
>>>> at
>>>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>>>>
>>>> at java.lang.Thread.run(Thread.java:722)
>>>>
>>>> Caused by: org.apache.kylin.query.routing.NoRealizationFoundException:
>>>> Can't find any realization. Please confirm with providers. SQL digest: fact
>>>> table LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>>>>
>>>> at
>>>> org.apache.kylin.query.routing.QueryRouter.selectRealization(QueryRouter.java:66)
>>>>
>>>> at
>>>> org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:80)
>>>>
>>>> at
>>>> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102)
>>>>
>>>> at
>>>> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
>>>>
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1171)
>>>>
>>>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297)
>>>>
>>>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196)
>>>>
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:721)
>>>>
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:588)
>>>>
>>>> at
>>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:558)
>>>>
>>>> at
>>>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
>>>>
>>>> at
>>>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573)
>>>>
>>>> at
>>>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
>>>>
>>>> at
>>>> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
>>>>
>>>> ... 80 more
>>>>
>>>> 2016-05-13 09:04:46,148 INFO [http-bio-8887-exec-4]
>>>> service.QueryService:248 :
>>>>
>>>> ==========================[QUERY]===============================
>>>>
>>>> SQL: select
>>>>
>>>> sum(pv_1d_001) pv
>>>>
>>>> from lbs_ad_pv_1d
>>>>
>>>> group by INDS
>>>>
>>>> order by pv desc
>>>>
>>>>
>>>> User: ADMIN
>>>>
>>>> Success: false
>>>>
>>>> Duration: 0.0
>>>>
>>>> Project: lbs
>>>>
>>>> Realization Names: []
>>>>
>>>> Cuboid Ids: []
>>>>
>>>> Total scan count: 0
>>>>
>>>> Result row count: 0
>>>>
>>>> Accept Partial: true
>>>>
>>>> Is Partial Result: false
>>>>
>>>> Hit Exception Cache: false
>>>>
>>>> Storage cache used: false
>>>>
>>>> Message: Error while executing SQL "select sum(pv_1d_001) pv from
>>>> lbs_ad_pv_1d group by INDS order by pv desc LIMIT 50000": Can't find any
>>>> realization. Please confirm with providers. SQL digest: fact table
>>>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>>>>
>>>> ==========================[QUERY]===============================
>>>>
>>>>
>>>> 2016-05-13 09:04:46,153 ERROR [http-bio-8887-exec-4]
>>>> controller.BasicController:44 :
>>>>
>>>> org.apache.kylin.rest.exception.InternalErrorException: Error while
>>>> executing SQL "select sum(pv_1d_001) pv from lbs_ad_pv_1d group by INDS
>>>> order by pv desc LIMIT 50000": Can't find any realization. Please confirm
>>>> with providers. SQL digest: fact table LBS.LBS_AD_PV_1D,group by
>>>> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
>>>> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
>>>> nextParam=null], returnType=null]].
>>>>
>>>> at
>>>> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:224)
>>>>
>>>> at
>>>> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>>>>
>>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>>
>>>> at
>>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>>>
>>>> at
>>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>>>
>>>> at java.lang.reflect.Method.invoke(Method.java:601)
>>>>
>>>> at
>>>> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>>>>
>>>> at
>>>> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>>>>
>>>> at
>>>> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>>>>
>>>> at
>>>> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>>>>
>>>> at
>>>> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>>>>
>>>> at
>>>> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>>>>
>>>> at
>>>> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>>>>
>>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>>>>
>>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>>>
>>>> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>>>>
>>>> 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
>>>> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>>>>
>>>> at
>>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>>
>>>> at
>>>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>>>>
>>>> at
>>>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>>>>
>>>> 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:195)
>>>>
>>>> at
>>>> com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>>>>
>>>> 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:504)
>>>>
>>>> at
>>>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>>>>
>>>> at
>>>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>>>>
>>>> at
>>>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>>>>
>>>> at
>>>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>>>>
>>>> at
>>>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>>>>
>>>> at
>>>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>>>>
>>>> at
>>>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>>>>
>>>> at
>>>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>>>>
>>>> at
>>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>>>>
>>>> at
>>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>>>>
>>>> at
>>>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>>>>
>>>> at java.lang.Thread.run(Thread.java:722)
>>>>
>>>> 2016-05-13 16:53 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
>>>>
>>>>> The "top_n" is not a SQL function so the second statement is wrong.
>>>>>
>>>>> The first statment looks good; to investigate the issue, please
>>>>> provide:
>>>>> 1) the cube definition json;
>>>>> 2) the log message in logs/kylin.log when you run this SQL first time
>>>>> (no cache be hit)
>>>>>
>>>>> 2016-05-13 15:06 GMT+08:00 lancelot chen <cj...@gmail.com>:
>>>>>
>>>>>> I'm confusing about usage of TOP_N measure.
>>>>>> I have a cube with TOP_N measure built in, measure definition is:
>>>>>>
>>>>>> {
>>>>>>
>>>>>> "name": "PV_1D_001_TOP100",
>>>>>>
>>>>>> "function": {
>>>>>>
>>>>>> "expression": "TOP_N",
>>>>>>
>>>>>> "parameter": {
>>>>>>
>>>>>> "type": "column",
>>>>>>
>>>>>> "value": "PV_1D_001",
>>>>>>
>>>>>> "next_parameter": {
>>>>>>
>>>>>> "type": "column",
>>>>>>
>>>>>> "value": "INDS",
>>>>>>
>>>>>> "next_parameter": null
>>>>>>
>>>>>> }
>>>>>>
>>>>>> },
>>>>>>
>>>>>> "returntype": "topn(100)"
>>>>>>
>>>>>> },
>>>>>>
>>>>>> "dependent_measure_ref": null
>>>>>>
>>>>>> }
>>>>>>
>>>>>> But I can't get this measure by using either "SUM | GROUP BY | ORDER
>>>>>> BY" nor "SELECT TOP_N | GROUP BY". Error message is:
>>>>>>
>>>>>> When using SUM | GROUP BY | ORDER BY:
>>>>>>
>>>>>> Error while executing SQL "select sum(pv_1d_001) pv from
>>>>>> test group by INDS order by pv desc LIMIT 50000": Can't find any
>>>>>> realization. Please confirm with providers. SQL digest: fact table
>>>>>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>>>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>>>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]]
>>>>>>
>>>>>> When using SELECT TOP_N | GROUP BY:
>>>>>>
>>>>>> Error while executing SQL "select top_n(pv_1d_001) pv from
>>>>>> test group by INDS LIMIT 50000": From line 2, column 5 to line 2, column
>>>>>> 20: No match found for function signature TOP_N(<NUMERIC>)
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Best regards,
>>>>>
>>>>> Shaofeng Shi
>>>>>
>>>>>
>>>>
>>>
>>>
>>> --
>>> Best regards,
>>>
>>> Shaofeng Shi
>>>
>>>
>>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
>
--
Best regards,
Shaofeng Shi
Re: Usage of TOP_N measure
Posted by ShaoFeng Shi <sh...@apache.org>.
This is a bug, open a jira to track it:
https://issues.apache.org/jira/browse/KYLIN-1687
2016-05-13 17:45 GMT+08:00 lancelot chen <cj...@gmail.com>:
> After disable this cube and remove "INDS" from dimenssions, I got another
> error message:
>
> 2016-05-13 09:41:04,674 ERROR [http-bio-8887-exec-1]
> controller.QueryController:209 : Exception when execute sql
> java.sql.SQLException: Error while executing SQL "select
> sum(pv_1d_001) pv
> from lbs_ad_pv_1d
> group by INDS
> order by sum(pv_1d_001) desc
> limit 100": 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:143)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
> at
> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
> at
> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
> at org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
> at
> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
> at
> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
> at
> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
> at
> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
> at
> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
> at java.lang.reflect.Method.invoke(Method.java:601)
> at
> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
> at
> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
> at
> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
> at
> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
> at
> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
> at
> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
> at
> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
> at
> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
> at
> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
> at
> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
> 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
> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> at
> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> at
> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> at
> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> at
> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
> at
> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
> at
> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
> 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:195)
> at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
> 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:504)
> at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
> at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
> at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
> at
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
> at
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
> at
> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
> at
> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
> at java.lang.Thread.run(Thread.java:722)
> Caused by: java.util.NoSuchElementException
> at java.util.ArrayList$Itr.next(ArrayList.java:794)
> at java.util.Collections.min(Collections.java:665)
> at
> org.apache.kylin.cube.cuboid.Cuboid.translateToValidCuboid(Cuboid.java:201)
> at
> org.apache.kylin.cube.cuboid.Cuboid.translateToValidCuboid(Cuboid.java:125)
> at org.apache.kylin.cube.cuboid.Cuboid.findById(Cuboid.java:67)
> at
> org.apache.kylin.storage.hbase.cube.v2.CubeStorageQuery.identifyCuboid(CubeStorageQuery.java:183)
> at
> org.apache.kylin.storage.hbase.cube.v2.CubeStorageQuery.search(CubeStorageQuery.java:96)
> at
> org.apache.kylin.query.enumerator.OLAPEnumerator.queryStorage(OLAPEnumerator.java:125)
> at
> org.apache.kylin.query.enumerator.OLAPEnumerator.moveNext(OLAPEnumerator.java:71)
> at Baz$1$1.moveNext(Unknown Source)
> at
> org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:819)
> at
> org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:754)
> 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:326)
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:281)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:545)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:536)
> 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:566)
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:578)
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
> ... 80 more
>
> And definition json is below:
>
> {
>
> "uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
>
> "version": "1.5.1",
>
> "name": "lbs_ad_pv_top100_1d_cube",
>
> "description": "",
>
> "dimensions": [
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.QUERY",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "QUERY",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.CITY_NAME",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "CITY_NAME",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.AD_TYPE",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "AD_TYPE",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.MAP_NAME",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "MAP_NAME",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.DS",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "DS",
>
> "derived": null
>
> }
>
> ],
>
> "measures": [
>
> {
>
> "name": "PV_1D_001_TOP100",
>
> "function": {
>
> "expression": "TOP_N",
>
> "parameter": {
>
> "type": "column",
>
> "value": "PV_1D_001",
>
> "next_parameter": {
>
> "type": "column",
>
> "value": "INDS",
>
> "next_parameter": null
>
> }
>
> },
>
> "returntype": "topn(100)"
>
> },
>
> "dependent_measure_ref": null
>
> },
>
> {
>
> "name": "CNT",
>
> "function": {
>
> "expression": "COUNT",
>
> "parameter": {
>
> "type": "constant",
>
> "value": "1",
>
> "next_parameter": null
>
> },
>
> "returntype": "bigint"
>
> },
>
> "dependent_measure_ref": null
>
> }
>
> ],
>
> "rowkey": {
>
> "rowkey_columns": [
>
> {
>
> "column": "QUERY",
>
> "encoding": "fixed_length:255",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "CITY_NAME",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "AD_TYPE",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "MAP_NAME",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "DS",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> }
>
> ]
>
> },
>
> "signature": "E9yIk19S/zfk/Lv1OlnExQ==",
>
> "last_modified": 1463131465268,
>
> "model_name": "lbs_ad_pv_1d_model",
>
> "null_string": null,
>
> "hbase_mapping": {
>
> "column_family": [
>
> {
>
> "name": "F1",
>
> "columns": [
>
> {
>
> "qualifier": "M",
>
> "measure_refs": [
>
> "PV_1D_001_TOP100",
>
> "CNT"
>
> ]
>
> }
>
> ]
>
> }
>
> ]
>
> },
>
> "aggregation_groups": [
>
> {
>
> "includes": [
>
> "DS"
>
> ],
>
> "select_rule": {
>
> "hierarchy_dims": [],
>
> "mandatory_dims": [
>
> "DS"
>
> ],
>
> "joint_dims": []
>
> }
>
> }
>
> ],
>
> "notify_list": [],
>
> "status_need_notify": [],
>
> "partition_date_start": 1462320000000,
>
> "partition_date_end": 3153600000000,
>
> "auto_merge_time_ranges": [],
>
> "retention_range": 0,
>
> "engine_type": 2,
>
> "storage_type": 2,
>
> "override_kylin_properties": {}
>
> }
>
> 2016-05-13 17:18 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
>
>> ok I see the problem: "INDS" has already been defined as a dimension,
>> then it should not be used in TopN. Please disable this cube, remove "INDS"
>> from dimension, save it, and then rebuild.
>>
>> In v1.5.2 Kylin will check and report error for such case before saving
>> the cube: https://issues.apache.org/jira/browse/KYLIN-1631
>>
>> 2016-05-13 17:07 GMT+08:00 lancelot chen <cj...@gmail.com>:
>>
>>> Thanks for quick reply. Below is my cube definition json:
>>> {
>>>
>>> "uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
>>>
>>> "version": "1.5.1",
>>>
>>> "name": "lbs_ad_pv_top100_1d_cube",
>>>
>>> "description": "",
>>>
>>> "dimensions": [
>>>
>>> {
>>>
>>> "name": "LBS.LBS_AD_PV_1D.QUERY",
>>>
>>> "table": "LBS.LBS_AD_PV_1D",
>>>
>>> "column": "QUERY",
>>>
>>> "derived": null
>>>
>>> },
>>>
>>> {
>>>
>>> "name": "LBS.LBS_AD_PV_1D.CITY_NAME",
>>>
>>> "table": "LBS.LBS_AD_PV_1D",
>>>
>>> "column": "CITY_NAME",
>>>
>>> "derived": null
>>>
>>> },
>>>
>>> {
>>>
>>> "name": "LBS.LBS_AD_PV_1D.AD_TYPE",
>>>
>>> "table": "LBS.LBS_AD_PV_1D",
>>>
>>> "column": "AD_TYPE",
>>>
>>> "derived": null
>>>
>>> },
>>>
>>> {
>>>
>>> "name": "LBS.LBS_AD_PV_1D.INDS",
>>>
>>> "table": "LBS.LBS_AD_PV_1D",
>>>
>>> "column": "INDS",
>>>
>>> "derived": null
>>>
>>> },
>>>
>>> {
>>>
>>> "name": "LBS.LBS_AD_PV_1D.MAP_NAME",
>>>
>>> "table": "LBS.LBS_AD_PV_1D",
>>>
>>> "column": "MAP_NAME",
>>>
>>> "derived": null
>>>
>>> },
>>>
>>> {
>>>
>>> "name": "LBS.LBS_AD_PV_1D.DS",
>>>
>>> "table": "LBS.LBS_AD_PV_1D",
>>>
>>> "column": "DS",
>>>
>>> "derived": null
>>>
>>> }
>>>
>>> ],
>>>
>>> "measures": [
>>>
>>> {
>>>
>>> "name": "PV_1D_001_TOP100",
>>>
>>> "function": {
>>>
>>> "expression": "TOP_N",
>>>
>>> "parameter": {
>>>
>>> "type": "column",
>>>
>>> "value": "PV_1D_001",
>>>
>>> "next_parameter": {
>>>
>>> "type": "column",
>>>
>>> "value": "INDS",
>>>
>>> "next_parameter": null
>>>
>>> }
>>>
>>> },
>>>
>>> "returntype": "topn(100)"
>>>
>>> },
>>>
>>> "dependent_measure_ref": null
>>>
>>> },
>>>
>>> {
>>>
>>> "name": "CNT",
>>>
>>> "function": {
>>>
>>> "expression": "COUNT",
>>>
>>> "parameter": {
>>>
>>> "type": "constant",
>>>
>>> "value": "1",
>>>
>>> "next_parameter": null
>>>
>>> },
>>>
>>> "returntype": "bigint"
>>>
>>> },
>>>
>>> "dependent_measure_ref": null
>>>
>>> }
>>>
>>> ],
>>>
>>> "rowkey": {
>>>
>>> "rowkey_columns": [
>>>
>>> {
>>>
>>> "column": "INDS",
>>>
>>> "encoding": "dict",
>>>
>>> "isShardBy": false
>>>
>>> },
>>>
>>> {
>>>
>>> "column": "QUERY",
>>>
>>> "encoding": "fixed_length:255",
>>>
>>> "isShardBy": false
>>>
>>> },
>>>
>>> {
>>>
>>> "column": "CITY_NAME",
>>>
>>> "encoding": "dict",
>>>
>>> "isShardBy": false
>>>
>>> },
>>>
>>> {
>>>
>>> "column": "AD_TYPE",
>>>
>>> "encoding": "dict",
>>>
>>> "isShardBy": false
>>>
>>> },
>>>
>>> {
>>>
>>> "column": "MAP_NAME",
>>>
>>> "encoding": "dict",
>>>
>>> "isShardBy": false
>>>
>>> },
>>>
>>> {
>>>
>>> "column": "DS",
>>>
>>> "encoding": "dict",
>>>
>>> "isShardBy": false
>>>
>>> }
>>>
>>> ]
>>>
>>> },
>>>
>>> "signature": "eqmmsW5dmIjABLUOoeDQGg==",
>>>
>>> "last_modified": 1463048780867,
>>>
>>> "model_name": "lbs_ad_pv_1d_model",
>>>
>>> "null_string": null,
>>>
>>> "hbase_mapping": {
>>>
>>> "column_family": [
>>>
>>> {
>>>
>>> "name": "F1",
>>>
>>> "columns": [
>>>
>>> {
>>>
>>> "qualifier": "M",
>>>
>>> "measure_refs": [
>>>
>>> "PV_1D_001_TOP100",
>>>
>>> "CNT"
>>>
>>> ]
>>>
>>> }
>>>
>>> ]
>>>
>>> }
>>>
>>> ]
>>>
>>> },
>>>
>>> "aggregation_groups": [
>>>
>>> {
>>>
>>> "includes": [
>>>
>>> "INDS"
>>>
>>> ],
>>>
>>> "select_rule": {
>>>
>>> "hierarchy_dims": [],
>>>
>>> "mandatory_dims": [],
>>>
>>> "joint_dims": []
>>>
>>> }
>>>
>>> }
>>>
>>> ],
>>>
>>> "notify_list": [],
>>>
>>> "status_need_notify": [],
>>>
>>> "partition_date_start": 1462320000000,
>>>
>>> "partition_date_end": 3153600000000,
>>>
>>> "auto_merge_time_ranges": [],
>>>
>>> "retention_range": 0,
>>>
>>> "engine_type": 2,
>>>
>>> "storage_type": 2,
>>>
>>> "override_kylin_properties": {}
>>>
>>> }
>>>
>>>
>>> I restarted server and rerun query using SUM | GROUP BY | ORDER BY , and
>>> I got the following error message:
>>>
>>>
>>> 2016-05-13 09:04:44,557 INFO [http-bio-8887-exec-4]
>>> controller.QueryController:174 : Using project: lbs
>>>
>>> 2016-05-13 09:04:44,564 INFO [http-bio-8887-exec-4]
>>> controller.QueryController:175 : The original query: select
>>>
>>> sum(pv_1d_001) pv
>>>
>>> from lbs_ad_pv_1d
>>>
>>> group by INDS
>>>
>>> order by pv desc
>>>
>>>
>>> 2016-05-13 09:04:44,592 INFO [http-bio-8887-exec-4]
>>> service.QueryService:264 : The corrected query: select
>>>
>>> sum(pv_1d_001) pv
>>>
>>> from lbs_ad_pv_1d
>>>
>>> group by INDS
>>>
>>> order by pv desc
>>>
>>> LIMIT 50000
>>>
>>> 2016-05-13 09:04:44,595 DEBUG [http-bio-8887-exec-4]
>>> service.CacheService:144 : Creating a new data source, OLAP data source
>>> pointing to kylin_metadata@hbase
>>>
>>> 2016-05-13 09:04:44,600 INFO [http-bio-8887-exec-4]
>>> project.ProjectL2Cache:172 : Loading L2 project cache for LBS
>>>
>>> 2016-05-13 09:04:44,619 INFO [http-bio-8887-exec-4]
>>> schema.OLAPSchemaFactory:135 : Schema json:{
>>>
>>> "version": "1.0",
>>>
>>> "defaultSchema": "LBS",
>>>
>>> "schemas": [
>>>
>>> {
>>>
>>> "type": "custom",
>>>
>>> "name": "LBS",
>>>
>>> "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
>>>
>>> "operand": {
>>>
>>> "project": "LBS"
>>>
>>> },
>>>
>>> "functions": [
>>>
>>> {
>>>
>>> name: 'MASSIN',
>>>
>>> className: 'org.apache.kylin.query.udf.MassInUDF'
>>>
>>> }
>>>
>>> ]
>>>
>>> }
>>>
>>> ]
>>>
>>> }
>>>
>>> 2016-05-13 09:04:44,620 DEBUG [http-bio-8887-exec-4]
>>> service.CacheService:149 : The new temp olap json is :{
>>>
>>> "version": "1.0",
>>>
>>> "defaultSchema": "LBS",
>>>
>>> "schemas": [
>>>
>>> {
>>>
>>> "type": "custom",
>>>
>>> "name": "LBS",
>>>
>>> "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
>>>
>>> "operand": {
>>>
>>> "project": "LBS"
>>>
>>> },
>>>
>>> "functions": [
>>>
>>> {
>>>
>>> name: 'MASSIN',
>>>
>>> className: 'org.apache.kylin.query.udf.MassInUDF'
>>>
>>> }
>>>
>>> ]
>>>
>>> }
>>>
>>> ]
>>>
>>> }
>>>
>>>
>>> 2016-05-13 09:04:46,129 INFO [http-bio-8887-exec-4]
>>> routing.QueryRouter:48 : The project manager's reference is
>>> org.apache.kylin.metadata.project.ProjectManager@ece372
>>>
>>> 2016-05-13 09:04:46,131 INFO [http-bio-8887-exec-4]
>>> routing.QueryRouter:60 : Find candidates by table LBS.LBS_AD_PV_1D and
>>> project=LBS : org.apache.kylin.query.routing.Candidate@1f580d76
>>>
>>> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
>>> cube.CubeCapabilityChecker:85 : Exclude cube lbs_ad_pv_top100_1d_cube
>>> because unmatched aggregations
>>>
>>> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
>>> routing.QueryRouter:49 : Applying rule: class
>>> org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule,
>>> realizations before: [lbs_ad_pv_top100_1d_cube(CUBE)], realizations after:
>>> []
>>>
>>> 2016-05-13 09:04:46,137 INFO [http-bio-8887-exec-4]
>>> routing.QueryRouter:49 : Applying rule: class
>>> org.apache.kylin.query.routing.rules.RealizationSortRule, realizations
>>> before: [], realizations after: []
>>>
>>> 2016-05-13 09:04:46,139 ERROR [http-bio-8887-exec-4]
>>> controller.QueryController:209 : Exception when execute sql
>>>
>>> java.sql.SQLException: Error while executing SQL "select
>>>
>>> sum(pv_1d_001) pv
>>>
>>> from lbs_ad_pv_1d
>>>
>>> group by INDS
>>>
>>> order by pv desc
>>>
>>> LIMIT 50000": Can't find any realization. Please confirm with providers.
>>> SQL digest: fact table LBS.LBS_AD_PV_1D,group by
>>> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
>>> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
>>> nextParam=null], returnType=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:143)
>>>
>>> at
>>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
>>>
>>> at
>>> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
>>>
>>> at
>>> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
>>>
>>> at
>>> org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
>>>
>>> at
>>> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
>>>
>>> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>>>
>>> at
>>> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
>>>
>>> at
>>> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
>>>
>>> at
>>> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
>>>
>>> at
>>> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>>>
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>
>>> at
>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>>
>>> at
>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>>
>>> at java.lang.reflect.Method.invoke(Method.java:601)
>>>
>>> at
>>> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>>>
>>> at
>>> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>>>
>>> at
>>> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>>>
>>> at
>>> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>>>
>>> at
>>> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>>>
>>> at
>>> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>>>
>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>>>
>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>>
>>> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>>>
>>> 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
>>> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>>>
>>> at
>>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>>>
>>> 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:195)
>>>
>>> at
>>> com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>>>
>>> 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:504)
>>>
>>> at
>>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>>>
>>> at
>>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>>>
>>> at
>>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>>>
>>> at
>>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>>>
>>> at
>>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>>>
>>> at
>>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>>>
>>> at
>>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>>>
>>> at
>>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>>>
>>> at
>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>>>
>>> at
>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>>>
>>> at
>>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>>>
>>> at java.lang.Thread.run(Thread.java:722)
>>>
>>> Caused by: org.apache.kylin.query.routing.NoRealizationFoundException:
>>> Can't find any realization. Please confirm with providers. SQL digest: fact
>>> table LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>>>
>>> at
>>> org.apache.kylin.query.routing.QueryRouter.selectRealization(QueryRouter.java:66)
>>>
>>> at
>>> org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:80)
>>>
>>> at
>>> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102)
>>>
>>> at
>>> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
>>>
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1171)
>>>
>>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297)
>>>
>>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196)
>>>
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:721)
>>>
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:588)
>>>
>>> at
>>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:558)
>>>
>>> at
>>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
>>>
>>> at
>>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573)
>>>
>>> at
>>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
>>>
>>> at
>>> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
>>>
>>> ... 80 more
>>>
>>> 2016-05-13 09:04:46,148 INFO [http-bio-8887-exec-4]
>>> service.QueryService:248 :
>>>
>>> ==========================[QUERY]===============================
>>>
>>> SQL: select
>>>
>>> sum(pv_1d_001) pv
>>>
>>> from lbs_ad_pv_1d
>>>
>>> group by INDS
>>>
>>> order by pv desc
>>>
>>>
>>> User: ADMIN
>>>
>>> Success: false
>>>
>>> Duration: 0.0
>>>
>>> Project: lbs
>>>
>>> Realization Names: []
>>>
>>> Cuboid Ids: []
>>>
>>> Total scan count: 0
>>>
>>> Result row count: 0
>>>
>>> Accept Partial: true
>>>
>>> Is Partial Result: false
>>>
>>> Hit Exception Cache: false
>>>
>>> Storage cache used: false
>>>
>>> Message: Error while executing SQL "select sum(pv_1d_001) pv from
>>> lbs_ad_pv_1d group by INDS order by pv desc LIMIT 50000": Can't find any
>>> realization. Please confirm with providers. SQL digest: fact table
>>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>>>
>>> ==========================[QUERY]===============================
>>>
>>>
>>> 2016-05-13 09:04:46,153 ERROR [http-bio-8887-exec-4]
>>> controller.BasicController:44 :
>>>
>>> org.apache.kylin.rest.exception.InternalErrorException: Error while
>>> executing SQL "select sum(pv_1d_001) pv from lbs_ad_pv_1d group by INDS
>>> order by pv desc LIMIT 50000": Can't find any realization. Please confirm
>>> with providers. SQL digest: fact table LBS.LBS_AD_PV_1D,group by
>>> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
>>> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
>>> nextParam=null], returnType=null]].
>>>
>>> at
>>> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:224)
>>>
>>> at
>>> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>>>
>>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>>
>>> at
>>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>>
>>> at
>>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>>
>>> at java.lang.reflect.Method.invoke(Method.java:601)
>>>
>>> at
>>> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>>>
>>> at
>>> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>>>
>>> at
>>> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>>>
>>> at
>>> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>>>
>>> at
>>> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>>>
>>> at
>>> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>>>
>>> at
>>> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>>>
>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>>>
>>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>>
>>> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>>>
>>> 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
>>> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>>>
>>> at
>>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>>
>>> at
>>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>>>
>>> at
>>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>>>
>>> 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:195)
>>>
>>> at
>>> com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>>>
>>> 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:504)
>>>
>>> at
>>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>>>
>>> at
>>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>>>
>>> at
>>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>>>
>>> at
>>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>>>
>>> at
>>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>>>
>>> at
>>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>>>
>>> at
>>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>>>
>>> at
>>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>>>
>>> at
>>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>>>
>>> at
>>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>>>
>>> at
>>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>>>
>>> at java.lang.Thread.run(Thread.java:722)
>>>
>>> 2016-05-13 16:53 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
>>>
>>>> The "top_n" is not a SQL function so the second statement is wrong.
>>>>
>>>> The first statment looks good; to investigate the issue, please provide:
>>>> 1) the cube definition json;
>>>> 2) the log message in logs/kylin.log when you run this SQL first time
>>>> (no cache be hit)
>>>>
>>>> 2016-05-13 15:06 GMT+08:00 lancelot chen <cj...@gmail.com>:
>>>>
>>>>> I'm confusing about usage of TOP_N measure.
>>>>> I have a cube with TOP_N measure built in, measure definition is:
>>>>>
>>>>> {
>>>>>
>>>>> "name": "PV_1D_001_TOP100",
>>>>>
>>>>> "function": {
>>>>>
>>>>> "expression": "TOP_N",
>>>>>
>>>>> "parameter": {
>>>>>
>>>>> "type": "column",
>>>>>
>>>>> "value": "PV_1D_001",
>>>>>
>>>>> "next_parameter": {
>>>>>
>>>>> "type": "column",
>>>>>
>>>>> "value": "INDS",
>>>>>
>>>>> "next_parameter": null
>>>>>
>>>>> }
>>>>>
>>>>> },
>>>>>
>>>>> "returntype": "topn(100)"
>>>>>
>>>>> },
>>>>>
>>>>> "dependent_measure_ref": null
>>>>>
>>>>> }
>>>>>
>>>>> But I can't get this measure by using either "SUM | GROUP BY | ORDER
>>>>> BY" nor "SELECT TOP_N | GROUP BY". Error message is:
>>>>>
>>>>> When using SUM | GROUP BY | ORDER BY:
>>>>>
>>>>> Error while executing SQL "select sum(pv_1d_001) pv from test
>>>>> group by INDS order by pv desc LIMIT 50000": Can't find any realization.
>>>>> Please confirm with providers. SQL digest: fact table
>>>>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]]
>>>>>
>>>>> When using SELECT TOP_N | GROUP BY:
>>>>>
>>>>> Error while executing SQL "select top_n(pv_1d_001) pv from
>>>>> test group by INDS LIMIT 50000": From line 2, column 5 to line 2, column
>>>>> 20: No match found for function signature TOP_N(<NUMERIC>)
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Best regards,
>>>>
>>>> Shaofeng Shi
>>>>
>>>>
>>>
>>
>>
>> --
>> Best regards,
>>
>> Shaofeng Shi
>>
>>
>
--
Best regards,
Shaofeng Shi
Re: Usage of TOP_N measure
Posted by lancelot chen <cj...@gmail.com>.
After disable this cube and remove "INDS" from dimenssions, I got another
error message:
2016-05-13 09:41:04,674 ERROR [http-bio-8887-exec-1]
controller.QueryController:209 : Exception when execute sql
java.sql.SQLException: Error while executing SQL "select
sum(pv_1d_001) pv
from lbs_ad_pv_1d
group by INDS
order by sum(pv_1d_001) desc
limit 100": 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:143)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
at org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
at
org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
at org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
at
org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at
org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
at
org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
at
org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
at
org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
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
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
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:195)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
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:504)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:722)
Caused by: java.util.NoSuchElementException
at java.util.ArrayList$Itr.next(ArrayList.java:794)
at java.util.Collections.min(Collections.java:665)
at
org.apache.kylin.cube.cuboid.Cuboid.translateToValidCuboid(Cuboid.java:201)
at
org.apache.kylin.cube.cuboid.Cuboid.translateToValidCuboid(Cuboid.java:125)
at org.apache.kylin.cube.cuboid.Cuboid.findById(Cuboid.java:67)
at
org.apache.kylin.storage.hbase.cube.v2.CubeStorageQuery.identifyCuboid(CubeStorageQuery.java:183)
at
org.apache.kylin.storage.hbase.cube.v2.CubeStorageQuery.search(CubeStorageQuery.java:96)
at
org.apache.kylin.query.enumerator.OLAPEnumerator.queryStorage(OLAPEnumerator.java:125)
at
org.apache.kylin.query.enumerator.OLAPEnumerator.moveNext(OLAPEnumerator.java:71)
at Baz$1$1.moveNext(Unknown Source)
at
org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:819)
at
org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:754)
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:326)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:281)
at
org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:545)
at
org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:536)
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:566)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:578)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
... 80 more
And definition json is below:
{
"uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
"version": "1.5.1",
"name": "lbs_ad_pv_top100_1d_cube",
"description": "",
"dimensions": [
{
"name": "LBS.LBS_AD_PV_1D.QUERY",
"table": "LBS.LBS_AD_PV_1D",
"column": "QUERY",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.CITY_NAME",
"table": "LBS.LBS_AD_PV_1D",
"column": "CITY_NAME",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.AD_TYPE",
"table": "LBS.LBS_AD_PV_1D",
"column": "AD_TYPE",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.MAP_NAME",
"table": "LBS.LBS_AD_PV_1D",
"column": "MAP_NAME",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.DS",
"table": "LBS.LBS_AD_PV_1D",
"column": "DS",
"derived": null
}
],
"measures": [
{
"name": "PV_1D_001_TOP100",
"function": {
"expression": "TOP_N",
"parameter": {
"type": "column",
"value": "PV_1D_001",
"next_parameter": {
"type": "column",
"value": "INDS",
"next_parameter": null
}
},
"returntype": "topn(100)"
},
"dependent_measure_ref": null
},
{
"name": "CNT",
"function": {
"expression": "COUNT",
"parameter": {
"type": "constant",
"value": "1",
"next_parameter": null
},
"returntype": "bigint"
},
"dependent_measure_ref": null
}
],
"rowkey": {
"rowkey_columns": [
{
"column": "QUERY",
"encoding": "fixed_length:255",
"isShardBy": false
},
{
"column": "CITY_NAME",
"encoding": "dict",
"isShardBy": false
},
{
"column": "AD_TYPE",
"encoding": "dict",
"isShardBy": false
},
{
"column": "MAP_NAME",
"encoding": "dict",
"isShardBy": false
},
{
"column": "DS",
"encoding": "dict",
"isShardBy": false
}
]
},
"signature": "E9yIk19S/zfk/Lv1OlnExQ==",
"last_modified": 1463131465268,
"model_name": "lbs_ad_pv_1d_model",
"null_string": null,
"hbase_mapping": {
"column_family": [
{
"name": "F1",
"columns": [
{
"qualifier": "M",
"measure_refs": [
"PV_1D_001_TOP100",
"CNT"
]
}
]
}
]
},
"aggregation_groups": [
{
"includes": [
"DS"
],
"select_rule": {
"hierarchy_dims": [],
"mandatory_dims": [
"DS"
],
"joint_dims": []
}
}
],
"notify_list": [],
"status_need_notify": [],
"partition_date_start": 1462320000000,
"partition_date_end": 3153600000000,
"auto_merge_time_ranges": [],
"retention_range": 0,
"engine_type": 2,
"storage_type": 2,
"override_kylin_properties": {}
}
2016-05-13 17:18 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
> ok I see the problem: "INDS" has already been defined as a dimension,
> then it should not be used in TopN. Please disable this cube, remove "INDS"
> from dimension, save it, and then rebuild.
>
> In v1.5.2 Kylin will check and report error for such case before saving
> the cube: https://issues.apache.org/jira/browse/KYLIN-1631
>
> 2016-05-13 17:07 GMT+08:00 lancelot chen <cj...@gmail.com>:
>
>> Thanks for quick reply. Below is my cube definition json:
>> {
>>
>> "uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
>>
>> "version": "1.5.1",
>>
>> "name": "lbs_ad_pv_top100_1d_cube",
>>
>> "description": "",
>>
>> "dimensions": [
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.QUERY",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "QUERY",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.CITY_NAME",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "CITY_NAME",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.AD_TYPE",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "AD_TYPE",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.INDS",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "INDS",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.MAP_NAME",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "MAP_NAME",
>>
>> "derived": null
>>
>> },
>>
>> {
>>
>> "name": "LBS.LBS_AD_PV_1D.DS",
>>
>> "table": "LBS.LBS_AD_PV_1D",
>>
>> "column": "DS",
>>
>> "derived": null
>>
>> }
>>
>> ],
>>
>> "measures": [
>>
>> {
>>
>> "name": "PV_1D_001_TOP100",
>>
>> "function": {
>>
>> "expression": "TOP_N",
>>
>> "parameter": {
>>
>> "type": "column",
>>
>> "value": "PV_1D_001",
>>
>> "next_parameter": {
>>
>> "type": "column",
>>
>> "value": "INDS",
>>
>> "next_parameter": null
>>
>> }
>>
>> },
>>
>> "returntype": "topn(100)"
>>
>> },
>>
>> "dependent_measure_ref": null
>>
>> },
>>
>> {
>>
>> "name": "CNT",
>>
>> "function": {
>>
>> "expression": "COUNT",
>>
>> "parameter": {
>>
>> "type": "constant",
>>
>> "value": "1",
>>
>> "next_parameter": null
>>
>> },
>>
>> "returntype": "bigint"
>>
>> },
>>
>> "dependent_measure_ref": null
>>
>> }
>>
>> ],
>>
>> "rowkey": {
>>
>> "rowkey_columns": [
>>
>> {
>>
>> "column": "INDS",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "QUERY",
>>
>> "encoding": "fixed_length:255",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "CITY_NAME",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "AD_TYPE",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "MAP_NAME",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> },
>>
>> {
>>
>> "column": "DS",
>>
>> "encoding": "dict",
>>
>> "isShardBy": false
>>
>> }
>>
>> ]
>>
>> },
>>
>> "signature": "eqmmsW5dmIjABLUOoeDQGg==",
>>
>> "last_modified": 1463048780867,
>>
>> "model_name": "lbs_ad_pv_1d_model",
>>
>> "null_string": null,
>>
>> "hbase_mapping": {
>>
>> "column_family": [
>>
>> {
>>
>> "name": "F1",
>>
>> "columns": [
>>
>> {
>>
>> "qualifier": "M",
>>
>> "measure_refs": [
>>
>> "PV_1D_001_TOP100",
>>
>> "CNT"
>>
>> ]
>>
>> }
>>
>> ]
>>
>> }
>>
>> ]
>>
>> },
>>
>> "aggregation_groups": [
>>
>> {
>>
>> "includes": [
>>
>> "INDS"
>>
>> ],
>>
>> "select_rule": {
>>
>> "hierarchy_dims": [],
>>
>> "mandatory_dims": [],
>>
>> "joint_dims": []
>>
>> }
>>
>> }
>>
>> ],
>>
>> "notify_list": [],
>>
>> "status_need_notify": [],
>>
>> "partition_date_start": 1462320000000,
>>
>> "partition_date_end": 3153600000000,
>>
>> "auto_merge_time_ranges": [],
>>
>> "retention_range": 0,
>>
>> "engine_type": 2,
>>
>> "storage_type": 2,
>>
>> "override_kylin_properties": {}
>>
>> }
>>
>>
>> I restarted server and rerun query using SUM | GROUP BY | ORDER BY , and
>> I got the following error message:
>>
>>
>> 2016-05-13 09:04:44,557 INFO [http-bio-8887-exec-4]
>> controller.QueryController:174 : Using project: lbs
>>
>> 2016-05-13 09:04:44,564 INFO [http-bio-8887-exec-4]
>> controller.QueryController:175 : The original query: select
>>
>> sum(pv_1d_001) pv
>>
>> from lbs_ad_pv_1d
>>
>> group by INDS
>>
>> order by pv desc
>>
>>
>> 2016-05-13 09:04:44,592 INFO [http-bio-8887-exec-4]
>> service.QueryService:264 : The corrected query: select
>>
>> sum(pv_1d_001) pv
>>
>> from lbs_ad_pv_1d
>>
>> group by INDS
>>
>> order by pv desc
>>
>> LIMIT 50000
>>
>> 2016-05-13 09:04:44,595 DEBUG [http-bio-8887-exec-4]
>> service.CacheService:144 : Creating a new data source, OLAP data source
>> pointing to kylin_metadata@hbase
>>
>> 2016-05-13 09:04:44,600 INFO [http-bio-8887-exec-4]
>> project.ProjectL2Cache:172 : Loading L2 project cache for LBS
>>
>> 2016-05-13 09:04:44,619 INFO [http-bio-8887-exec-4]
>> schema.OLAPSchemaFactory:135 : Schema json:{
>>
>> "version": "1.0",
>>
>> "defaultSchema": "LBS",
>>
>> "schemas": [
>>
>> {
>>
>> "type": "custom",
>>
>> "name": "LBS",
>>
>> "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
>>
>> "operand": {
>>
>> "project": "LBS"
>>
>> },
>>
>> "functions": [
>>
>> {
>>
>> name: 'MASSIN',
>>
>> className: 'org.apache.kylin.query.udf.MassInUDF'
>>
>> }
>>
>> ]
>>
>> }
>>
>> ]
>>
>> }
>>
>> 2016-05-13 09:04:44,620 DEBUG [http-bio-8887-exec-4]
>> service.CacheService:149 : The new temp olap json is :{
>>
>> "version": "1.0",
>>
>> "defaultSchema": "LBS",
>>
>> "schemas": [
>>
>> {
>>
>> "type": "custom",
>>
>> "name": "LBS",
>>
>> "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
>>
>> "operand": {
>>
>> "project": "LBS"
>>
>> },
>>
>> "functions": [
>>
>> {
>>
>> name: 'MASSIN',
>>
>> className: 'org.apache.kylin.query.udf.MassInUDF'
>>
>> }
>>
>> ]
>>
>> }
>>
>> ]
>>
>> }
>>
>>
>> 2016-05-13 09:04:46,129 INFO [http-bio-8887-exec-4]
>> routing.QueryRouter:48 : The project manager's reference is
>> org.apache.kylin.metadata.project.ProjectManager@ece372
>>
>> 2016-05-13 09:04:46,131 INFO [http-bio-8887-exec-4]
>> routing.QueryRouter:60 : Find candidates by table LBS.LBS_AD_PV_1D and
>> project=LBS : org.apache.kylin.query.routing.Candidate@1f580d76
>>
>> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
>> cube.CubeCapabilityChecker:85 : Exclude cube lbs_ad_pv_top100_1d_cube
>> because unmatched aggregations
>>
>> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
>> routing.QueryRouter:49 : Applying rule: class
>> org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule,
>> realizations before: [lbs_ad_pv_top100_1d_cube(CUBE)], realizations after:
>> []
>>
>> 2016-05-13 09:04:46,137 INFO [http-bio-8887-exec-4]
>> routing.QueryRouter:49 : Applying rule: class
>> org.apache.kylin.query.routing.rules.RealizationSortRule, realizations
>> before: [], realizations after: []
>>
>> 2016-05-13 09:04:46,139 ERROR [http-bio-8887-exec-4]
>> controller.QueryController:209 : Exception when execute sql
>>
>> java.sql.SQLException: Error while executing SQL "select
>>
>> sum(pv_1d_001) pv
>>
>> from lbs_ad_pv_1d
>>
>> group by INDS
>>
>> order by pv desc
>>
>> LIMIT 50000": Can't find any realization. Please confirm with providers.
>> SQL digest: fact table LBS.LBS_AD_PV_1D,group by
>> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
>> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
>> nextParam=null], returnType=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:143)
>>
>> at
>> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
>>
>> at
>> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
>>
>> at
>> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
>>
>> at org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
>>
>> at
>> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
>>
>> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>>
>> at
>> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
>>
>> at
>> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
>>
>> at
>> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
>>
>> at
>> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>>
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>
>> at java.lang.reflect.Method.invoke(Method.java:601)
>>
>> at
>> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>>
>> at
>> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>>
>> at
>> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>>
>> at
>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>>
>> at
>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>>
>> at
>> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>>
>> at
>> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>>
>> at
>> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>>
>> at
>> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>>
>> at
>> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>>
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>>
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>
>> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>>
>> 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
>> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>>
>> at
>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>>
>> 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:195)
>>
>> at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>>
>> 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:504)
>>
>> at
>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>>
>> at
>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>>
>> at
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>>
>> at
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>>
>> at
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>>
>> at
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>>
>> at
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>>
>> at
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>>
>> at
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>>
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>>
>> at
>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>>
>> at java.lang.Thread.run(Thread.java:722)
>>
>> Caused by: org.apache.kylin.query.routing.NoRealizationFoundException:
>> Can't find any realization. Please confirm with providers. SQL digest: fact
>> table LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>>
>> at
>> org.apache.kylin.query.routing.QueryRouter.selectRealization(QueryRouter.java:66)
>>
>> at
>> org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:80)
>>
>> at
>> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102)
>>
>> at
>> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
>>
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1171)
>>
>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297)
>>
>> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196)
>>
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:721)
>>
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:588)
>>
>> at
>> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:558)
>>
>> at
>> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
>>
>> at
>> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573)
>>
>> at
>> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
>>
>> at
>> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
>>
>> ... 80 more
>>
>> 2016-05-13 09:04:46,148 INFO [http-bio-8887-exec-4]
>> service.QueryService:248 :
>>
>> ==========================[QUERY]===============================
>>
>> SQL: select
>>
>> sum(pv_1d_001) pv
>>
>> from lbs_ad_pv_1d
>>
>> group by INDS
>>
>> order by pv desc
>>
>>
>> User: ADMIN
>>
>> Success: false
>>
>> Duration: 0.0
>>
>> Project: lbs
>>
>> Realization Names: []
>>
>> Cuboid Ids: []
>>
>> Total scan count: 0
>>
>> Result row count: 0
>>
>> Accept Partial: true
>>
>> Is Partial Result: false
>>
>> Hit Exception Cache: false
>>
>> Storage cache used: false
>>
>> Message: Error while executing SQL "select sum(pv_1d_001) pv from
>> lbs_ad_pv_1d group by INDS order by pv desc LIMIT 50000": Can't find any
>> realization. Please confirm with providers. SQL digest: fact table
>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>>
>> ==========================[QUERY]===============================
>>
>>
>> 2016-05-13 09:04:46,153 ERROR [http-bio-8887-exec-4]
>> controller.BasicController:44 :
>>
>> org.apache.kylin.rest.exception.InternalErrorException: Error while
>> executing SQL "select sum(pv_1d_001) pv from lbs_ad_pv_1d group by INDS
>> order by pv desc LIMIT 50000": Can't find any realization. Please confirm
>> with providers. SQL digest: fact table LBS.LBS_AD_PV_1D,group by
>> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
>> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
>> nextParam=null], returnType=null]].
>>
>> at
>> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:224)
>>
>> at
>> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>>
>> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>>
>> at
>> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>>
>> at
>> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>>
>> at java.lang.reflect.Method.invoke(Method.java:601)
>>
>> at
>> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>>
>> at
>> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>>
>> at
>> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>>
>> at
>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>>
>> at
>> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>>
>> at
>> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>>
>> at
>> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>>
>> at
>> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>>
>> at
>> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>>
>> at
>> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>>
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>>
>> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>>
>> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>>
>> 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
>> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>>
>> at
>> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>>
>> at
>> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>>
>> at
>> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>>
>> 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:195)
>>
>> at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>>
>> 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:504)
>>
>> at
>> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>>
>> at
>> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>>
>> at
>> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>>
>> at
>> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>>
>> at
>> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>>
>> at
>> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>>
>> at
>> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>>
>> at
>> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>>
>> at
>> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>>
>> at
>> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>>
>> at
>> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>>
>> at java.lang.Thread.run(Thread.java:722)
>>
>> 2016-05-13 16:53 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
>>
>>> The "top_n" is not a SQL function so the second statement is wrong.
>>>
>>> The first statment looks good; to investigate the issue, please provide:
>>> 1) the cube definition json;
>>> 2) the log message in logs/kylin.log when you run this SQL first time
>>> (no cache be hit)
>>>
>>> 2016-05-13 15:06 GMT+08:00 lancelot chen <cj...@gmail.com>:
>>>
>>>> I'm confusing about usage of TOP_N measure.
>>>> I have a cube with TOP_N measure built in, measure definition is:
>>>>
>>>> {
>>>>
>>>> "name": "PV_1D_001_TOP100",
>>>>
>>>> "function": {
>>>>
>>>> "expression": "TOP_N",
>>>>
>>>> "parameter": {
>>>>
>>>> "type": "column",
>>>>
>>>> "value": "PV_1D_001",
>>>>
>>>> "next_parameter": {
>>>>
>>>> "type": "column",
>>>>
>>>> "value": "INDS",
>>>>
>>>> "next_parameter": null
>>>>
>>>> }
>>>>
>>>> },
>>>>
>>>> "returntype": "topn(100)"
>>>>
>>>> },
>>>>
>>>> "dependent_measure_ref": null
>>>>
>>>> }
>>>>
>>>> But I can't get this measure by using either "SUM | GROUP BY | ORDER
>>>> BY" nor "SELECT TOP_N | GROUP BY". Error message is:
>>>>
>>>> When using SUM | GROUP BY | ORDER BY:
>>>>
>>>> Error while executing SQL "select sum(pv_1d_001) pv from test
>>>> group by INDS order by pv desc LIMIT 50000": Can't find any realization.
>>>> Please confirm with providers. SQL digest: fact table
>>>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]]
>>>>
>>>> When using SELECT TOP_N | GROUP BY:
>>>>
>>>> Error while executing SQL "select top_n(pv_1d_001) pv from
>>>> test group by INDS LIMIT 50000": From line 2, column 5 to line 2, column
>>>> 20: No match found for function signature TOP_N(<NUMERIC>)
>>>>
>>>
>>>
>>>
>>> --
>>> Best regards,
>>>
>>> Shaofeng Shi
>>>
>>>
>>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
>
Re: Usage of TOP_N measure
Posted by ShaoFeng Shi <sh...@apache.org>.
ok I see the problem: "INDS" has already been defined as a dimension, then
it should not be used in TopN. Please disable this cube, remove "INDS" from
dimension, save it, and then rebuild.
In v1.5.2 Kylin will check and report error for such case before saving the
cube: https://issues.apache.org/jira/browse/KYLIN-1631
2016-05-13 17:07 GMT+08:00 lancelot chen <cj...@gmail.com>:
> Thanks for quick reply. Below is my cube definition json:
> {
>
> "uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
>
> "version": "1.5.1",
>
> "name": "lbs_ad_pv_top100_1d_cube",
>
> "description": "",
>
> "dimensions": [
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.QUERY",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "QUERY",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.CITY_NAME",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "CITY_NAME",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.AD_TYPE",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "AD_TYPE",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.INDS",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "INDS",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.MAP_NAME",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "MAP_NAME",
>
> "derived": null
>
> },
>
> {
>
> "name": "LBS.LBS_AD_PV_1D.DS",
>
> "table": "LBS.LBS_AD_PV_1D",
>
> "column": "DS",
>
> "derived": null
>
> }
>
> ],
>
> "measures": [
>
> {
>
> "name": "PV_1D_001_TOP100",
>
> "function": {
>
> "expression": "TOP_N",
>
> "parameter": {
>
> "type": "column",
>
> "value": "PV_1D_001",
>
> "next_parameter": {
>
> "type": "column",
>
> "value": "INDS",
>
> "next_parameter": null
>
> }
>
> },
>
> "returntype": "topn(100)"
>
> },
>
> "dependent_measure_ref": null
>
> },
>
> {
>
> "name": "CNT",
>
> "function": {
>
> "expression": "COUNT",
>
> "parameter": {
>
> "type": "constant",
>
> "value": "1",
>
> "next_parameter": null
>
> },
>
> "returntype": "bigint"
>
> },
>
> "dependent_measure_ref": null
>
> }
>
> ],
>
> "rowkey": {
>
> "rowkey_columns": [
>
> {
>
> "column": "INDS",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "QUERY",
>
> "encoding": "fixed_length:255",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "CITY_NAME",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "AD_TYPE",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "MAP_NAME",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> },
>
> {
>
> "column": "DS",
>
> "encoding": "dict",
>
> "isShardBy": false
>
> }
>
> ]
>
> },
>
> "signature": "eqmmsW5dmIjABLUOoeDQGg==",
>
> "last_modified": 1463048780867,
>
> "model_name": "lbs_ad_pv_1d_model",
>
> "null_string": null,
>
> "hbase_mapping": {
>
> "column_family": [
>
> {
>
> "name": "F1",
>
> "columns": [
>
> {
>
> "qualifier": "M",
>
> "measure_refs": [
>
> "PV_1D_001_TOP100",
>
> "CNT"
>
> ]
>
> }
>
> ]
>
> }
>
> ]
>
> },
>
> "aggregation_groups": [
>
> {
>
> "includes": [
>
> "INDS"
>
> ],
>
> "select_rule": {
>
> "hierarchy_dims": [],
>
> "mandatory_dims": [],
>
> "joint_dims": []
>
> }
>
> }
>
> ],
>
> "notify_list": [],
>
> "status_need_notify": [],
>
> "partition_date_start": 1462320000000,
>
> "partition_date_end": 3153600000000,
>
> "auto_merge_time_ranges": [],
>
> "retention_range": 0,
>
> "engine_type": 2,
>
> "storage_type": 2,
>
> "override_kylin_properties": {}
>
> }
>
>
> I restarted server and rerun query using SUM | GROUP BY | ORDER BY , and I
> got the following error message:
>
>
> 2016-05-13 09:04:44,557 INFO [http-bio-8887-exec-4]
> controller.QueryController:174 : Using project: lbs
>
> 2016-05-13 09:04:44,564 INFO [http-bio-8887-exec-4]
> controller.QueryController:175 : The original query: select
>
> sum(pv_1d_001) pv
>
> from lbs_ad_pv_1d
>
> group by INDS
>
> order by pv desc
>
>
> 2016-05-13 09:04:44,592 INFO [http-bio-8887-exec-4]
> service.QueryService:264 : The corrected query: select
>
> sum(pv_1d_001) pv
>
> from lbs_ad_pv_1d
>
> group by INDS
>
> order by pv desc
>
> LIMIT 50000
>
> 2016-05-13 09:04:44,595 DEBUG [http-bio-8887-exec-4]
> service.CacheService:144 : Creating a new data source, OLAP data source
> pointing to kylin_metadata@hbase
>
> 2016-05-13 09:04:44,600 INFO [http-bio-8887-exec-4]
> project.ProjectL2Cache:172 : Loading L2 project cache for LBS
>
> 2016-05-13 09:04:44,619 INFO [http-bio-8887-exec-4]
> schema.OLAPSchemaFactory:135 : Schema json:{
>
> "version": "1.0",
>
> "defaultSchema": "LBS",
>
> "schemas": [
>
> {
>
> "type": "custom",
>
> "name": "LBS",
>
> "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
>
> "operand": {
>
> "project": "LBS"
>
> },
>
> "functions": [
>
> {
>
> name: 'MASSIN',
>
> className: 'org.apache.kylin.query.udf.MassInUDF'
>
> }
>
> ]
>
> }
>
> ]
>
> }
>
> 2016-05-13 09:04:44,620 DEBUG [http-bio-8887-exec-4]
> service.CacheService:149 : The new temp olap json is :{
>
> "version": "1.0",
>
> "defaultSchema": "LBS",
>
> "schemas": [
>
> {
>
> "type": "custom",
>
> "name": "LBS",
>
> "factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
>
> "operand": {
>
> "project": "LBS"
>
> },
>
> "functions": [
>
> {
>
> name: 'MASSIN',
>
> className: 'org.apache.kylin.query.udf.MassInUDF'
>
> }
>
> ]
>
> }
>
> ]
>
> }
>
>
> 2016-05-13 09:04:46,129 INFO [http-bio-8887-exec-4]
> routing.QueryRouter:48 : The project manager's reference is
> org.apache.kylin.metadata.project.ProjectManager@ece372
>
> 2016-05-13 09:04:46,131 INFO [http-bio-8887-exec-4]
> routing.QueryRouter:60 : Find candidates by table LBS.LBS_AD_PV_1D and
> project=LBS : org.apache.kylin.query.routing.Candidate@1f580d76
>
> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
> cube.CubeCapabilityChecker:85 : Exclude cube lbs_ad_pv_top100_1d_cube
> because unmatched aggregations
>
> 2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
> routing.QueryRouter:49 : Applying rule: class
> org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule,
> realizations before: [lbs_ad_pv_top100_1d_cube(CUBE)], realizations after:
> []
>
> 2016-05-13 09:04:46,137 INFO [http-bio-8887-exec-4]
> routing.QueryRouter:49 : Applying rule: class
> org.apache.kylin.query.routing.rules.RealizationSortRule, realizations
> before: [], realizations after: []
>
> 2016-05-13 09:04:46,139 ERROR [http-bio-8887-exec-4]
> controller.QueryController:209 : Exception when execute sql
>
> java.sql.SQLException: Error while executing SQL "select
>
> sum(pv_1d_001) pv
>
> from lbs_ad_pv_1d
>
> group by INDS
>
> order by pv desc
>
> LIMIT 50000": Can't find any realization. Please confirm with providers.
> SQL digest: fact table LBS.LBS_AD_PV_1D,group by
> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
> nextParam=null], returnType=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:143)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
>
> at
> org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
>
> at
> org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
>
> at org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
>
> at
> org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
>
> at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
>
> at
> org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
>
> at
> org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
>
> at
> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
>
> at
> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>
> at java.lang.reflect.Method.invoke(Method.java:601)
>
> at
> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>
> at
> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>
> at
> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>
> at
> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>
> at
> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>
> at
> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>
> at
> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>
> at
> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>
> at
> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>
> at
> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>
> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>
> 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
> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>
> at
> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>
> 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:195)
>
> at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>
> 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:504)
>
> at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>
> at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>
> at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>
> at
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>
> at
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>
> at
> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>
> at
> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>
> at java.lang.Thread.run(Thread.java:722)
>
> Caused by: org.apache.kylin.query.routing.NoRealizationFoundException:
> Can't find any realization. Please confirm with providers. SQL digest: fact
> table LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>
> at
> org.apache.kylin.query.routing.QueryRouter.selectRealization(QueryRouter.java:66)
>
> at
> org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:80)
>
> at
> org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102)
>
> at
> org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
>
> at
> org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1171)
>
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297)
>
> at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196)
>
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:721)
>
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:588)
>
> at
> org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:558)
>
> at
> org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
>
> at
> org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573)
>
> at
> org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
>
> at
> org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
>
> ... 80 more
>
> 2016-05-13 09:04:46,148 INFO [http-bio-8887-exec-4]
> service.QueryService:248 :
>
> ==========================[QUERY]===============================
>
> SQL: select
>
> sum(pv_1d_001) pv
>
> from lbs_ad_pv_1d
>
> group by INDS
>
> order by pv desc
>
>
> User: ADMIN
>
> Success: false
>
> Duration: 0.0
>
> Project: lbs
>
> Realization Names: []
>
> Cuboid Ids: []
>
> Total scan count: 0
>
> Result row count: 0
>
> Accept Partial: true
>
> Is Partial Result: false
>
> Hit Exception Cache: false
>
> Storage cache used: false
>
> Message: Error while executing SQL "select sum(pv_1d_001) pv from
> lbs_ad_pv_1d group by INDS order by pv desc LIMIT 50000": Can't find any
> realization. Please confirm with providers. SQL digest: fact table
> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
> [type=column, value=PV_1D_001, nextParam=null], returnType=null]].
>
> ==========================[QUERY]===============================
>
>
> 2016-05-13 09:04:46,153 ERROR [http-bio-8887-exec-4]
> controller.BasicController:44 :
>
> org.apache.kylin.rest.exception.InternalErrorException: Error while
> executing SQL "select sum(pv_1d_001) pv from lbs_ad_pv_1d group by INDS
> order by pv desc LIMIT 50000": Can't find any realization. Please confirm
> with providers. SQL digest: fact table LBS.LBS_AD_PV_1D,group by
> [LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
> [expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
> nextParam=null], returnType=null]].
>
> at
> org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:224)
>
> at
> org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
>
> at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
>
> at
> sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
>
> at
> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
>
> at java.lang.reflect.Method.invoke(Method.java:601)
>
> at
> org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
>
> at
> org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
>
> at
> org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
>
> at
> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
>
> at
> org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
>
> at
> org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
>
> at
> org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
>
> at
> org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
>
> at
> org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
>
> at
> org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
>
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
>
> at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
>
> 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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
>
> 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
> org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
>
> at
> org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
>
> at
> org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
>
> at
> org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
>
> 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:195)
>
> at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
>
> 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:504)
>
> at
> org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
>
> at
> org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
>
> at
> org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
>
> at
> org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
>
> at
> org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
>
> at
> org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
>
> at
> org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
>
> at
> org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
>
> at
> java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
>
> at
> java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
>
> at
> org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
>
> at java.lang.Thread.run(Thread.java:722)
>
> 2016-05-13 16:53 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
>
>> The "top_n" is not a SQL function so the second statement is wrong.
>>
>> The first statment looks good; to investigate the issue, please provide:
>> 1) the cube definition json;
>> 2) the log message in logs/kylin.log when you run this SQL first time (no
>> cache be hit)
>>
>> 2016-05-13 15:06 GMT+08:00 lancelot chen <cj...@gmail.com>:
>>
>>> I'm confusing about usage of TOP_N measure.
>>> I have a cube with TOP_N measure built in, measure definition is:
>>>
>>> {
>>>
>>> "name": "PV_1D_001_TOP100",
>>>
>>> "function": {
>>>
>>> "expression": "TOP_N",
>>>
>>> "parameter": {
>>>
>>> "type": "column",
>>>
>>> "value": "PV_1D_001",
>>>
>>> "next_parameter": {
>>>
>>> "type": "column",
>>>
>>> "value": "INDS",
>>>
>>> "next_parameter": null
>>>
>>> }
>>>
>>> },
>>>
>>> "returntype": "topn(100)"
>>>
>>> },
>>>
>>> "dependent_measure_ref": null
>>>
>>> }
>>>
>>> But I can't get this measure by using either "SUM | GROUP BY | ORDER BY"
>>> nor "SELECT TOP_N | GROUP BY". Error message is:
>>>
>>> When using SUM | GROUP BY | ORDER BY:
>>>
>>> Error while executing SQL "select sum(pv_1d_001) pv from test
>>> group by INDS order by pv desc LIMIT 50000": Can't find any realization.
>>> Please confirm with providers. SQL digest: fact table
>>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]]
>>>
>>> When using SELECT TOP_N | GROUP BY:
>>>
>>> Error while executing SQL "select top_n(pv_1d_001) pv from test
>>> group by INDS LIMIT 50000": From line 2, column 5 to line 2, column 20: No
>>> match found for function signature TOP_N(<NUMERIC>)
>>>
>>
>>
>>
>> --
>> Best regards,
>>
>> Shaofeng Shi
>>
>>
>
--
Best regards,
Shaofeng Shi
Re: Usage of TOP_N measure
Posted by lancelot chen <cj...@gmail.com>.
Thanks for quick reply. Below is my cube definition json:
{
"uuid": "eab2b606-89aa-44fa-8f40-4bc5a1bca997",
"version": "1.5.1",
"name": "lbs_ad_pv_top100_1d_cube",
"description": "",
"dimensions": [
{
"name": "LBS.LBS_AD_PV_1D.QUERY",
"table": "LBS.LBS_AD_PV_1D",
"column": "QUERY",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.CITY_NAME",
"table": "LBS.LBS_AD_PV_1D",
"column": "CITY_NAME",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.AD_TYPE",
"table": "LBS.LBS_AD_PV_1D",
"column": "AD_TYPE",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.INDS",
"table": "LBS.LBS_AD_PV_1D",
"column": "INDS",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.MAP_NAME",
"table": "LBS.LBS_AD_PV_1D",
"column": "MAP_NAME",
"derived": null
},
{
"name": "LBS.LBS_AD_PV_1D.DS",
"table": "LBS.LBS_AD_PV_1D",
"column": "DS",
"derived": null
}
],
"measures": [
{
"name": "PV_1D_001_TOP100",
"function": {
"expression": "TOP_N",
"parameter": {
"type": "column",
"value": "PV_1D_001",
"next_parameter": {
"type": "column",
"value": "INDS",
"next_parameter": null
}
},
"returntype": "topn(100)"
},
"dependent_measure_ref": null
},
{
"name": "CNT",
"function": {
"expression": "COUNT",
"parameter": {
"type": "constant",
"value": "1",
"next_parameter": null
},
"returntype": "bigint"
},
"dependent_measure_ref": null
}
],
"rowkey": {
"rowkey_columns": [
{
"column": "INDS",
"encoding": "dict",
"isShardBy": false
},
{
"column": "QUERY",
"encoding": "fixed_length:255",
"isShardBy": false
},
{
"column": "CITY_NAME",
"encoding": "dict",
"isShardBy": false
},
{
"column": "AD_TYPE",
"encoding": "dict",
"isShardBy": false
},
{
"column": "MAP_NAME",
"encoding": "dict",
"isShardBy": false
},
{
"column": "DS",
"encoding": "dict",
"isShardBy": false
}
]
},
"signature": "eqmmsW5dmIjABLUOoeDQGg==",
"last_modified": 1463048780867,
"model_name": "lbs_ad_pv_1d_model",
"null_string": null,
"hbase_mapping": {
"column_family": [
{
"name": "F1",
"columns": [
{
"qualifier": "M",
"measure_refs": [
"PV_1D_001_TOP100",
"CNT"
]
}
]
}
]
},
"aggregation_groups": [
{
"includes": [
"INDS"
],
"select_rule": {
"hierarchy_dims": [],
"mandatory_dims": [],
"joint_dims": []
}
}
],
"notify_list": [],
"status_need_notify": [],
"partition_date_start": 1462320000000,
"partition_date_end": 3153600000000,
"auto_merge_time_ranges": [],
"retention_range": 0,
"engine_type": 2,
"storage_type": 2,
"override_kylin_properties": {}
}
I restarted server and rerun query using SUM | GROUP BY | ORDER BY , and I
got the following error message:
2016-05-13 09:04:44,557 INFO [http-bio-8887-exec-4]
controller.QueryController:174 : Using project: lbs
2016-05-13 09:04:44,564 INFO [http-bio-8887-exec-4]
controller.QueryController:175 : The original query: select
sum(pv_1d_001) pv
from lbs_ad_pv_1d
group by INDS
order by pv desc
2016-05-13 09:04:44,592 INFO [http-bio-8887-exec-4]
service.QueryService:264 : The corrected query: select
sum(pv_1d_001) pv
from lbs_ad_pv_1d
group by INDS
order by pv desc
LIMIT 50000
2016-05-13 09:04:44,595 DEBUG [http-bio-8887-exec-4]
service.CacheService:144 : Creating a new data source, OLAP data source
pointing to kylin_metadata@hbase
2016-05-13 09:04:44,600 INFO [http-bio-8887-exec-4]
project.ProjectL2Cache:172 : Loading L2 project cache for LBS
2016-05-13 09:04:44,619 INFO [http-bio-8887-exec-4]
schema.OLAPSchemaFactory:135 : Schema json:{
"version": "1.0",
"defaultSchema": "LBS",
"schemas": [
{
"type": "custom",
"name": "LBS",
"factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
"operand": {
"project": "LBS"
},
"functions": [
{
name: 'MASSIN',
className: 'org.apache.kylin.query.udf.MassInUDF'
}
]
}
]
}
2016-05-13 09:04:44,620 DEBUG [http-bio-8887-exec-4]
service.CacheService:149 : The new temp olap json is :{
"version": "1.0",
"defaultSchema": "LBS",
"schemas": [
{
"type": "custom",
"name": "LBS",
"factory": "org.apache.kylin.query.schema.OLAPSchemaFactory",
"operand": {
"project": "LBS"
},
"functions": [
{
name: 'MASSIN',
className: 'org.apache.kylin.query.udf.MassInUDF'
}
]
}
]
}
2016-05-13 09:04:46,129 INFO [http-bio-8887-exec-4] routing.QueryRouter:48
: The project manager's reference is
org.apache.kylin.metadata.project.ProjectManager@ece372
2016-05-13 09:04:46,131 INFO [http-bio-8887-exec-4] routing.QueryRouter:60
: Find candidates by table LBS.LBS_AD_PV_1D and project=LBS :
org.apache.kylin.query.routing.Candidate@1f580d76
2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4]
cube.CubeCapabilityChecker:85 : Exclude cube lbs_ad_pv_top100_1d_cube
because unmatched aggregations
2016-05-13 09:04:46,136 INFO [http-bio-8887-exec-4] routing.QueryRouter:49
: Applying rule: class
org.apache.kylin.query.routing.rules.RemoveUncapableRealizationsRule,
realizations before: [lbs_ad_pv_top100_1d_cube(CUBE)], realizations after:
[]
2016-05-13 09:04:46,137 INFO [http-bio-8887-exec-4] routing.QueryRouter:49
: Applying rule: class
org.apache.kylin.query.routing.rules.RealizationSortRule, realizations
before: [], realizations after: []
2016-05-13 09:04:46,139 ERROR [http-bio-8887-exec-4]
controller.QueryController:209 : Exception when execute sql
java.sql.SQLException: Error while executing SQL "select
sum(pv_1d_001) pv
from lbs_ad_pv_1d
group by INDS
order by pv desc
LIMIT 50000": Can't find any realization. Please confirm with providers.
SQL digest: fact table LBS.LBS_AD_PV_1D,group by
[LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
[expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
nextParam=null], returnType=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:143)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
at org.apache.kylin.rest.service.QueryService.execute(QueryService.java:355)
at
org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.java:271)
at org.apache.kylin.rest.service.QueryService.query(QueryService.java:119)
at
org.apache.kylin.rest.service.QueryService$$FastClassByCGLIB$$4957273f.invoke(<generated>)
at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
at
org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
at
org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$de61e8d6.query(<generated>)
at
org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:192)
at
org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
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
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
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:195)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
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:504)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:722)
Caused by: org.apache.kylin.query.routing.NoRealizationFoundException:
Can't find any realization. Please confirm with providers. SQL digest: fact
table LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
[type=column, value=PV_1D_001, nextParam=null], returnType=null]].
at
org.apache.kylin.query.routing.QueryRouter.selectRealization(QueryRouter.java:66)
at
org.apache.kylin.query.relnode.OLAPToEnumerableConverter.implement(OLAPToEnumerableConverter.java:80)
at
org.apache.calcite.adapter.enumerable.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:102)
at
org.apache.calcite.adapter.enumerable.EnumerableInterpretable.toBindable(EnumerableInterpretable.java:92)
at
org.apache.calcite.prepare.CalcitePrepareImpl$CalcitePreparingStmt.implement(CalcitePrepareImpl.java:1171)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:297)
at org.apache.calcite.prepare.Prepare.prepareSql(Prepare.java:196)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare2_(CalcitePrepareImpl.java:721)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepare_(CalcitePrepareImpl.java:588)
at
org.apache.calcite.prepare.CalcitePrepareImpl.prepareSql(CalcitePrepareImpl.java:558)
at
org.apache.calcite.jdbc.CalciteConnectionImpl.parseQuery(CalciteConnectionImpl.java:214)
at
org.apache.calcite.jdbc.CalciteMetaImpl.prepareAndExecute(CalciteMetaImpl.java:573)
at
org.apache.calcite.avatica.AvaticaConnection.prepareAndExecuteInternal(AvaticaConnection.java:571)
at
org.apache.calcite.avatica.AvaticaStatement.executeInternal(AvaticaStatement.java:135)
... 80 more
2016-05-13 09:04:46,148 INFO [http-bio-8887-exec-4]
service.QueryService:248 :
==========================[QUERY]===============================
SQL: select
sum(pv_1d_001) pv
from lbs_ad_pv_1d
group by INDS
order by pv desc
User: ADMIN
Success: false
Duration: 0.0
Project: lbs
Realization Names: []
Cuboid Ids: []
Total scan count: 0
Result row count: 0
Accept Partial: true
Is Partial Result: false
Hit Exception Cache: false
Storage cache used: false
Message: Error while executing SQL "select sum(pv_1d_001) pv from
lbs_ad_pv_1d group by INDS order by pv desc LIMIT 50000": Can't find any
realization. Please confirm with providers. SQL digest: fact table
LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
[type=column, value=PV_1D_001, nextParam=null], returnType=null]].
==========================[QUERY]===============================
2016-05-13 09:04:46,153 ERROR [http-bio-8887-exec-4]
controller.BasicController:44 :
org.apache.kylin.rest.exception.InternalErrorException: Error while
executing SQL "select sum(pv_1d_001) pv from lbs_ad_pv_1d group by INDS
order by pv desc LIMIT 50000": Can't find any realization. Please confirm
with providers. SQL digest: fact table LBS.LBS_AD_PV_1D,group by
[LBS.LBS_AD_PV_1D.INDS],filter on [],with aggregates[FunctionDesc
[expression=SUM, parameter=ParameterDesc [type=column, value=PV_1D_001,
nextParam=null], returnType=null]].
at
org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.java:224)
at
org.apache.kylin.rest.controller.QueryController.query(QueryController.java:94)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:601)
at
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
at
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
at
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
at
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
at
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
at
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
at
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
at
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
at
org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:789)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:646)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:727)
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.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
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
org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:201)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.ui.DefaultLoginPageGeneratingFilter.doFilter(DefaultLoginPageGeneratingFilter.java:91)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:183)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
at
org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:342)
at
org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.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:346)
at
org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
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:195)
at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:266)
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:504)
at
org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:170)
at
org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:950)
at
org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
at
org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:421)
at
org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1074)
at
org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:611)
at
org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:314)
at
java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1110)
at
java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:603)
at
org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.lang.Thread.run(Thread.java:722)
2016-05-13 16:53 GMT+08:00 ShaoFeng Shi <sh...@apache.org>:
> The "top_n" is not a SQL function so the second statement is wrong.
>
> The first statment looks good; to investigate the issue, please provide:
> 1) the cube definition json;
> 2) the log message in logs/kylin.log when you run this SQL first time (no
> cache be hit)
>
> 2016-05-13 15:06 GMT+08:00 lancelot chen <cj...@gmail.com>:
>
>> I'm confusing about usage of TOP_N measure.
>> I have a cube with TOP_N measure built in, measure definition is:
>>
>> {
>>
>> "name": "PV_1D_001_TOP100",
>>
>> "function": {
>>
>> "expression": "TOP_N",
>>
>> "parameter": {
>>
>> "type": "column",
>>
>> "value": "PV_1D_001",
>>
>> "next_parameter": {
>>
>> "type": "column",
>>
>> "value": "INDS",
>>
>> "next_parameter": null
>>
>> }
>>
>> },
>>
>> "returntype": "topn(100)"
>>
>> },
>>
>> "dependent_measure_ref": null
>>
>> }
>>
>> But I can't get this measure by using either "SUM | GROUP BY | ORDER BY"
>> nor "SELECT TOP_N | GROUP BY". Error message is:
>>
>> When using SUM | GROUP BY | ORDER BY:
>>
>> Error while executing SQL "select sum(pv_1d_001) pv from test
>> group by INDS order by pv desc LIMIT 50000": Can't find any realization.
>> Please confirm with providers. SQL digest: fact table
>> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
>> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
>> [type=column, value=PV_1D_001, nextParam=null], returnType=null]]
>>
>> When using SELECT TOP_N | GROUP BY:
>>
>> Error while executing SQL "select top_n(pv_1d_001) pv from test
>> group by INDS LIMIT 50000": From line 2, column 5 to line 2, column 20: No
>> match found for function signature TOP_N(<NUMERIC>)
>>
>
>
>
> --
> Best regards,
>
> Shaofeng Shi
>
>
Re: Usage of TOP_N measure
Posted by ShaoFeng Shi <sh...@apache.org>.
The "top_n" is not a SQL function so the second statement is wrong.
The first statment looks good; to investigate the issue, please provide:
1) the cube definition json;
2) the log message in logs/kylin.log when you run this SQL first time (no
cache be hit)
2016-05-13 15:06 GMT+08:00 lancelot chen <cj...@gmail.com>:
> I'm confusing about usage of TOP_N measure.
> I have a cube with TOP_N measure built in, measure definition is:
>
> {
>
> "name": "PV_1D_001_TOP100",
>
> "function": {
>
> "expression": "TOP_N",
>
> "parameter": {
>
> "type": "column",
>
> "value": "PV_1D_001",
>
> "next_parameter": {
>
> "type": "column",
>
> "value": "INDS",
>
> "next_parameter": null
>
> }
>
> },
>
> "returntype": "topn(100)"
>
> },
>
> "dependent_measure_ref": null
>
> }
>
> But I can't get this measure by using either "SUM | GROUP BY | ORDER BY"
> nor "SELECT TOP_N | GROUP BY". Error message is:
>
> When using SUM | GROUP BY | ORDER BY:
>
> Error while executing SQL "select sum(pv_1d_001) pv from test
> group by INDS order by pv desc LIMIT 50000": Can't find any realization.
> Please confirm with providers. SQL digest: fact table
> LBS.LBS_AD_PV_1D,group by [LBS.LBS_AD_PV_1D.INDS],filter on [],with
> aggregates[FunctionDesc [expression=SUM, parameter=ParameterDesc
> [type=column, value=PV_1D_001, nextParam=null], returnType=null]]
>
> When using SELECT TOP_N | GROUP BY:
>
> Error while executing SQL "select top_n(pv_1d_001) pv from test
> group by INDS LIMIT 50000": From line 2, column 5 to line 2, column 20: No
> match found for function signature TOP_N(<NUMERIC>)
>
--
Best regards,
Shaofeng Shi