yes, "hierarchy" is a good option for such dimensions; 2016-07-04 16:56 GMT+08:00 仇同心 <[email protected]>:
> 少峰，您好： > "derived" need post aggregation as we know; from Day to Month, it > need aggregate 30 times data in memory to result set; For Quarter it need > more; So when the measure is "memory-hungry" measure (like distinct count, > raw, top-n), it is likely to get the out of memory error; you can try to > define "month" and "quarter" as normal dimension so to reduce the post > aggregation. > > define "month" and "quarter" as normal dimension so to reduce the post > aggregation. > 您的意思是：设置 年、上半年、季度、月、周、日 为normal dimension。那么在aggregation groups > 时是否需要设置年、上半年、季度、月、周、日为Hierarchies dimensions？ > > 谢谢！ > > > -----邮件原件----- > 发件人: ShaoFeng Shi [mailto:[email protected]] > 发送时间: 2016年7月1日 13:23 > 收件人: [email protected] > 抄送: [email protected] > 主题: Re: 答复: kylin查询，报超时异常:Timeout visiting cube! > > "derived" need post aggregation as we know; from Day to Month, it need > aggregate 30 times data in memory to result set; For Quarter it need more; > So when the measure is "memory-hungry" measure (like distinct count, raw, > top-n), it is likely to get the out of memory error; you can try to define > "month" and "quarter" as normal dimension so to reduce the post aggregation. > > 2016-07-01 11:20 GMT+08:00 仇同心 <[email protected]>: > > > 少峰，您好： > > two "distinct count" measures, are HyperLogLog counter。 > > > > 一、 group by b.dim_month_name 这个是derived measure。 > > > > 测试了下，如果where 条件是月，group by 周 查询时间是66秒， where 条件是周，group by > > 日，查询时间是9秒 > > > > 如果where 条件是年，group by 月 ；where 条件是上半年，group by 季度或者月 > > 都会内存溢出错误。 > > > > Hbase的heap size大小也调到了32GB。kylin.query.cube.visit.timeout.times > > 调到了10 > > > > 二、如果group by的是nomal维度则查询很快 > > > > 三、如果增加hbase的regionderver是否可解决此问题 ？ > > > > > > 谢谢~ > > > > > > > > -----邮件原件----- > > 发件人: ShaoFeng Shi [mailto:[email protected]] > > 发送时间: 2016年7月1日 9:32 > > 收件人: dev > > 抄送: [email protected] > > 主题: Re: kylin查询，报超时异常:Timeout visiting cube! > > > > hi tongxing, > > > > The root cause is OutOfMemory: > > > > Caused by: java.lang.OutOfMemoryError > > > > at > > java.io.ByteArrayOutputStream.hugeCapacity(ByteArrayOutputStream.java: > > 123) > > > > at > > java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:117) > > > > at > > java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.jav > > a:93) > > > > at > > java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:153) > > > > at > > > > > org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.CubeVisitService.visitCube(CubeVisitService.java: > > > > > > You query uses two "distinct count" measures, are they HyperLogLog > > counter or Bitmap counter? > > > > 2016-06-30 18:09 GMT+08:00 仇同心 <[email protected]>: > > > > > 大家好： > > > Kylin查询时报超时异常,sql是： > > > select b.dim_month_name,sum(a.ordr_amt) as 订单金额, > > > sum(a.pay_amt) as 支付金额,count(*) as 订单数, > > > count(distinct a.user_pin)as 用户数,count(distinct a.is_new) as 新用户数 > > > from dmt.dmt_mem_vip_tx_ordr_det_i_d a left join dim.dim_day b on > > > a.pay_time=b.dim_day_txdate left join dim.dim_kylin_mem_type_buss c > > > on a.mem_type=c.mem_type left join dim.dim_kylin_mem_type_source d > > > on a.mem_type_source=d.mem_type_source > > > where b.dim_year_name='2016年' > > > group by b.dim_month_name > > > order by b.dim_month_name asc > > > LIMIT 50000 > > > > > > > > > 错误日志为： > > > 016-06-30 17:11:36,149 ERROR [http-bio-7070-exec-10] > > > controller.QueryController:209 : Exception when execute sql > > > java.sql.SQLException: Error while executing SQL "select > > > b.dim_month_name,sum(a.ordr_amt) as 订单金额, > > > sum(a.pay_amt) as 支付金额,count(*) as 订单数, > > > count(distinct a.user_pin)as 用户数,count(distinct a.is_new) as 新用户数 > > > from dmt.dmt_mem_vip_tx_ordr_det_i_d a left join dim.dim_day b on > > > a.pay_time=b.dim_day_txdate left join dim.dim_kylin_mem_type_buss c > > > on a.mem_type=c.mem_type left join dim.dim_kylin_mem_type_source d > > > on a.mem_type_source=d.mem_type_source > > > where b.dim_year_name='2016年' > > > group by b.dim_month_name > > > order by b.dim_month_name asc > > > LIMIT 50000": Timeout visiting cube! > > > 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(AvaticaSta > > tement.java:143) > > > at > > > > > org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatem > > ent.java:186) > > > at > > > > org.apache.kylin.rest.service.QueryService.execute(QueryService.java:361) > > > at > > > > > org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QuerySe > > rvice.java:273) > > > at > > > org.apache.kylin.rest.service.QueryService.query(QueryService.java:121) > > > 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$DynamicAdvisedInterce > > ptor.intercept(Cglib2AopProxy.java:618) > > > at > > > > > org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$5d1a2567. > > query(<generated>) > > > at > > > org.apache.kylin.rest.controller.QueryController.doQueryWithCache(Qu > > > er > > > yController.java:192) > > > > > > > > > Caught exception in thread pool-8-thr > > > ead-2: > > > java.lang.RuntimeException: <sub-thread for GTScanRequest c66d2a5> > > > Error when visiting cubes by endpoint > > > at > > > > > org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$1.run(Cube > > HBaseEndpointRPC.java:345) > > > at > > > java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511) > > > at java.util.concurrent.FutureTask.run(FutureTask.java:266) > > > at > > > > > java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.j > > ava:1142) > > > at > > > > > java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor. > > java:617) > > > at java.lang.Thread.run(Thread.java:745) > > > Caused by: java.net.SocketTimeoutException: callTimeout=60000, > > > callDuration=109489: row '^@^@' on table 'KYLIN_UVD9MY6HD P' at > > > region=KYLIN_UVD9MY6HDP,,1467269837242.b987ac977cedbc877732757947111 > > > 20 d., hostname=jxq-23-197-78.h.chinabank.com.c > > > n,16020,1464677435379, seqNum=2 > > > at > > > > > org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRe > > tryingCaller.java:169) > > > at > > > > > org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel.callExecServic > > e(RegionCoprocessorRpcChannel.java:107) > > > at > > > > > org.apache.hadoop.hbase.ipc.CoprocessorRpcChannel.callMethod(Coprocess > > orRpcChannel.java:56) > > > at > > > org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.generated. > > > CubeVisitProtos$CubeVisitService$Stub.v > > > isitCube(CubeVisitProtos.java:4225) > > > at > > > org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$2.call(C > > > ub > > > eHBaseEndpointRPC.java:393) > > > at > > > > > org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$2.call(Cub > > eHBaseEndpointRPC.java:389) > > > at > > org.apache.hadoop.hbase.client.HTable$15.call(HTable.java:1736) > > > ... 4 more > > > Caused by: java.io.IOException: java.io.IOException > > > at > > org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2169) > > > at > > org.apache.hadoop.hbase.ipc.CallRunner.run(CallRunner.java:107) > > > at > > > > > org.apache.hadoop.hbase.ipc.RpcExecutor.consumerLoop(RpcExecutor.java: > > 133) > > > at > > > org.apache.hadoop.hbase.ipc.RpcExecutor$1.run(RpcExecutor.java:108) > > > at java.lang.Thread.run(Thread.java:745) > > > Caused by: java.lang.OutOfMemoryError > > > at > > > > > java.io.ByteArrayOutputStream.hugeCapacity(ByteArrayOutputStream.java: > > 123) > > > at > > > java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:117) > > > at > > > > > java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.jav > > a:93) > > > at > > > java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:153) > > > at > > > > > > org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.CubeVisitService.visitCube(CubeVisitService.java: > > > 304) > > > at > > > org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.generated. > > > CubeVisitProtos$CubeVisitService.callMe > > > thod(CubeVisitProtos.java:4164) > > > at > > > > > org.apache.hadoop.hbase.regionserver.HRegion.execService(HRegion.java: > > 7483) > > > at > > > > > org.apache.hadoop.hbase.regionserver.RSRpcServices.execServiceOnRegion > > (RSRpcServices.java:1891) > > > at > > > > > org.apache.hadoop.hbase.regionserver.RSRpcServices.execService(RSRpcSe > > rvices.java:1873) > > > at > > > > > > org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$2.callBlockingMethod(ClientProtos.java: > > > 32389) > > > at > > org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2127) > > > ... 4 more > > > > > > at > > > sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native > > > Method) > > > at > > > > > sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructo > > rAccessorImpl.java:62) > > > at > > > > > sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingCo > > nstructorAccessorImpl.java:45) > > > at > > java.lang.reflect.Constructor.newInstance(Constructor.java:408) > > > at > > > > > org.apache.hadoop.ipc.RemoteException.instantiateException(RemoteExcep > > tion.java:106) > > > at > > > > > org.apache.hadoop.ipc.RemoteException.unwrapRemoteException(RemoteExce > > ption.java:95) > > > at > > > > > org.apache.hadoop.hbase.protobuf.ProtobufUtil.getRemoteException(Proto > > bufUtil.java:326) > > > at > > > > > org.apache.hadoop.hbase.protobuf.ProtobufUtil.execService(ProtobufUtil > > .java:1622) > > > at > > > > > org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionC > > oprocessorRpcChannel.java:104) > > > at > > > > > org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionC > > oprocessorRpcChannel.java:94) > > > at > > > > > org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRe > > tryingCaller.java:136) > > > ... 10 more > > > Caused by: > > > > > > org.apache.hadoop.hbase.ipc.RemoteWithExtrasException(java.io.IOException): > > > java.io.IOException > > > at > > > org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2169) > > > at org.apache.hadoop.hbase.ipc.CallRunner.run(CallRunner.java:107) > > > at > > > > > org.apache.hadoop.hbase.ipc.RpcExecutor.consumerLoop(RpcExecutor.java: > > 133) > > > at > > > org.apache.hadoop.hbase.ipc.RpcExecutor$1.run(RpcExecutor.java:108) > > > at java.lang.Thread.run(Thread.java:745) > > > Caused by: java.lang.OutOfMemoryError > > > at > > > > > java.io.ByteArrayOutputStream.hugeCapacity(ByteArrayOutputStream.java: > > 123) > > > at > > > java.io.ByteArrayOutputStream.grow(ByteArrayOutputStream.java:117) > > > at > > > > > java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.jav > > a:93) > > > at > > > java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:153) > > > at > > > > > > org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.CubeVisitService.visitCube(CubeVisitService.java: > > > 304) > > > at > > > org.apache.kylin.storage.hbase.cube.v2.coprocessor.endpoint.generated. > > > CubeVisitProtos$CubeVisitService.callMe > > > thod(CubeVisitProtos.java:4164) > > > at > > > > > org.apache.hadoop.hbase.regionserver.HRegion.execService(HRegion.java: > > 7483) > > > at > > > > > org.apache.hadoop.hbase.regionserver.RSRpcServices.execServiceOnRegion > > (RSRpcServices.java:1891) > > > at > > > > > org.apache.hadoop.hbase.regionserver.RSRpcServices.execService(RSRpcSe > > rvices.java:1873) > > > at > > > > > > org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$2.callBlockingMethod(ClientProtos.java: > > > 32389) > > > at > > org.apache.hadoop.hbase.ipc.RpcServer.call(RpcServer.java:2127) > > > ... 4 more > > > > > > at > > > org.apache.hadoop.hbase.ipc.RpcClientImpl.call(RpcClientImpl.java:1235) > > > at > > > org.apache.hadoop.hbase.ipc.AbstractRpcClient.callBlockingMethod(Abs > > > tr > > > actRpcClient.java:222 > > > t > > > org.apache.hadoop.hbase.ipc.AbstractRpcClient$BlockingRpcChannelImpl > > > em entation.callBlockingMethod(AbstractRpc > > > Client.java:323) > > > at > > > org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientServic > > > e$ BlockingStub.execService(ClientProtos.j > > > ava:32855) > > > at > > > > > org.apache.hadoop.hbase.protobuf.ProtobufUtil.execService(ProtobufUtil > > .java:1618) > > > ... 13 more > > > > > > 目前cube size : 1.5GB source records :8亿 > > > 版本： hadoop2.6 hive -hive-1.2.1 hbase 1.1.5 kylin 1.5.2 > > > > > > 还有问题就是都有哪些点可以优化查询？？？ > > > > > > 谢谢！ > > > > > > > > > > > > > > > > > > > > > -- > > Best regards, > > > > Shaofeng Shi > > > > > > -- > Best regards, > > Shaofeng Shi > -- Best regards, Shaofeng Shi
