kylin-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "May Zhou" <may.a.z...@foxmail.com>
Subject 回复: 回复:回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000
Date Fri, 16 Nov 2018 09:36:33 GMT
Hi Feng,


Here's the definition of mandatory dimension from Kylin document:
`Mandatory Dimensions`: Dimensions that appears always. For example, if all your queries have
"ORDER_DATE" as the group by or filtering condition, then it can be marked as mandatory. The
cuboids that doesn't have this dimension can be omitted for building.


As shown in your screenshot, when "BIZDATE" is set as a mandatory dimension, every cuboid
contains this dimension and no matter whether this dimension is included in your SQL it would
hit a cuboid with this dimension, which may cause the unexpected large query return rows.
And you can also see that after canceling "BIZDATE" as mandatory dimension, the Cube size
is much smaller.


Thus, it's recommended to have a better design on Cube to make it best in storage and query
performance.




BRs,
May










------------------ 原始邮件 ------------------
发件人: "user@kylin.apache.org 冯志山"<fengzs@teld.cn>;
发送时间: 2018年11月16日(星期五) 下午3:26
收件人: "May Zhou"<may.a.zhou@foxmail.com>;"user"<user@kylin.apache.org>;

主题:  回复:回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000



hi May, 
    Sorry for not replying to you in time. Thank you very much for your advice, I will try
to modify the configuration next.
    But I have the doubts: this  5000000 rows is metadata? or In this scenario, this pre-calculation
is not functional?
    Finally, could the mandatory dimensions will cause the error?
    The cube is below:


picture 1



picture 2



Best regards,
Zhishan  Feng 


------------------------------------------------------------------
发件人:May Zhou <may.a.zhou@foxmail.com>
发送时间:2018年11月15日(星期四) 22:21
收件人:冯志山 <fengzs@teld.cn>; user <user@kylin.apache.org>; shaofengshi
<shaofengshi@apache.org>
抄 送:潘博存 <panbc@teld.cn>
主 题:回复: 回复:回复: Error:Query returned 5078491 rows exceeds threshold
5000000


Hi Feng,


It can be inferred from your error message, "Query returned 5078491 rows exceeds threshold
5000000," that the query tried to return 5078491 rows.


As far as your concern about why this query would return more than 5000000 rows, I think you
could try the following query to set a limit to the query to see whether it throws the same
error.
select  KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*)
from  V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4  inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE
= KMS_DT.TDAY  inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID
= V_KMSSTATION_V4.ID  inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4
= V_KMS_BASECARMODEL.ID  where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805',
'201806','201807', '201808', '201809')  group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME,
V_KMS_BASECARMODEL.BIGTYPENAME limit 2000


And I have reproduced your issue in my environment as i set "kylin.query.max-return-row" to
1 in the project-level, and try to execute a simple query and it throws the similar error
"Query returned 12 rows exceeds threshold 1 while executing SQL: "select * from kylin_sales
LIMIT 50000"". 
Thus, this parameter is valid.





BRs,
May




------------------ 原始邮件 ------------------
发件人: "潘博存"<panbc@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上9:48
收件人: "May Zhou"<may.a.zhou@foxmail.com>;"user@kylin.apache.org 冯志山"<fengzs@teld.cn>;"user"<user@kylin.apache.org>;"shaofengshi"<shaofengshi@apache.org>;
抄送: "潘博存"<panbc@teld.cn>; 
主题:  回复:回复: Error:Query returned 5078491 rows exceeds threshold 5000000




the sql execute result count only 2000,why report    more thsb 5000000 rows error?




------------------------------------------------------------------
发件人:May Zhou<may.a.zhou@foxmail.com>
日 期:2018年11月15日 20:50:42
收件人:user@kylin.apache.org 冯志山<fengzs@teld.cn>; user<user@kylin.apache.org>;
shaofengshi<shaofengshi@apache.org>
抄 送:潘博存<panbc@teld.cn>
主 题:回复: Error:Query returned 5078491 rows exceeds threshold 5000000

Hi Feng,


(Sorry if interrupt you, but i am wondering whether my reply has been sent)


Have you tried to increase the value of this parameter `kylin.query.max-return-row`,which
specifies the maximum number of rows returned by the query and the default value is 5000000.


More configuration information can be referred in Kylin's Document: https://kylin.apache.org/docs/install/configuration.html






BRs,
May





------------------ 原始邮件 ------------------
发件人: "user@kylin.apache.org 冯志山"<fengzs@teld.cn>;
发送时间: 2018年11月15日(星期四) 晚上8:37
收件人: "user"<user@kylin.apache.org>;"shaofengshi"<shaofengshi@apache.org>;
抄送: "潘博存"<panbc@teld.cn>; 
主题:  Error:Query returned 5078491 rows exceeds threshold 5000000



hi,all:


    when I executing sql,I get this error: 
org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds
threshold 5000000
    My kylin version is apache-kylin-2.3.1-bin.The detail log is below:
    2018-11-15 11:43:56,163 ERROR [http-bio-7070-exec-29] controller.BasicController:61 :

org.apache.kylin.rest.exception.InternalErrorException: Query returned 5078491 rows exceeds
threshold 5000000
while executing SQL: "select  KMS_DT.YMONTH,V_KMSSTATION_V4.STAPROVINCENAME, V_KMS_BASECARMODEL.BIGTYPENAME,count(*)
from  V_KMS_DAY_ALLBILLSV4 as V_KMS_DAY_ALLBILLSV4  inner join KMS_DT as KMS_DT on V_KMS_DAY_ALLBILLSV4.BIZDATE
= KMS_DT.TDAY  inner join V_KMSSTATION_V4 as V_KMSSTATION_V4 on V_KMS_DAY_ALLBILLSV4.STATIONID
= V_KMSSTATION_V4.ID  inner join V_KMS_BASECARMODEL as V_KMS_BASECARMODEL on V_KMS_DAY_ALLBILLSV4.EXT4
= V_KMS_BASECARMODEL.ID  where KMS_DT.YMONTH in ('201801', '201802', '201803', '201804', '201805',
'201806','201807', '201808', '201809')  group by KMS_DT.YMONTH, V_KMSSTATION_V4.STAPROVINCENAME,
V_KMS_BASECARMODEL.BIGTYPENAME"
        at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:474)
        at org.apache.kylin.rest.service.QueryService.doQueryWithCache(QueryService.java:390)
        at org.apache.kylin.rest.controller.QueryController.query(QueryController.java:86)
        at sun.reflect.GeneratedMethodAccessor695.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:205)
        at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:133)
        at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:97)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:827)
        at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:738)
        at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:85)
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:967)
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901)
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970)
        at org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:872)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:650)
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:731)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:303)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127)
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilterInternal(BasicAuthenticationFilter.java:158)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:64)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56)
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105)
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331)
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214)
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177)
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:262)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:209)
        at com.thetransactioncompany.cors.CORSFilter.doFilter(CORSFilter.java:244)
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:241)
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:208)
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:219)
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:110)
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:169)
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
        at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:962)
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:116)
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:445)
        at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1115)
        at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:637)
        at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:316)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
        at java.lang.Thread.run(Thread.java:745)
     At first I thought it was about configuration:kylin.dictionary.max.cardinality=5000000,but
this configuration disappeared after version 2.0. In additon KMS_DT.YMONTH in ('201801', '201802',
'201803', '201804', '201805', '201806','201807', '201808') just return 2000 records,  So I
want to know why this error occurred and how to handle it.
Thanks.
Mime
View raw message