大家好：
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(AvaticaStatement.java:143)
at
org.apache.calcite.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:186)
at
org.apache.kylin.rest.service.QueryService.execute(QueryService.java:361)
at
org.apache.kylin.rest.service.QueryService.queryWithSqlMassage(QueryService.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$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:618)
at
org.apache.kylin.rest.service.QueryService$$EnhancerByCGLIB$$5d1a2567.query(<generated>)
at
org.apache.kylin.rest.controller.QueryController.doQueryWithCache(QueryController.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(CubeHBaseEndpointRPC.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.java: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.b987ac977cedbc87773275794711120d.,
hostname=jxq-23-197-78.h.chinabank.com.c
n,16020,1464677435379, seqNum=2
at
org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.java:169)
at
org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel.callExecService(RegionCoprocessorRpcChannel.java:107)
at
org.apache.hadoop.hbase.ipc.CoprocessorRpcChannel.callMethod(CoprocessorRpcChannel.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(CubeHBaseEndpointRPC.java:393)
at
org.apache.kylin.storage.hbase.cube.v2.CubeHBaseEndpointRPC$2.call(CubeHBaseEndpointRPC.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.java: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(RSRpcServices.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(NativeConstructorAccessorImpl.java:62)
at
sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:408)
at
org.apache.hadoop.ipc.RemoteException.instantiateException(RemoteException.java:106)
at
org.apache.hadoop.ipc.RemoteException.unwrapRemoteException(RemoteException.java:95)
at
org.apache.hadoop.hbase.protobuf.ProtobufUtil.getRemoteException(ProtobufUtil.java:326)
at
org.apache.hadoop.hbase.protobuf.ProtobufUtil.execService(ProtobufUtil.java:1622)
at
org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionCoprocessorRpcChannel.java:104)
at
org.apache.hadoop.hbase.ipc.RegionCoprocessorRpcChannel$1.call(RegionCoprocessorRpcChannel.java:94)
at
org.apache.hadoop.hbase.client.RpcRetryingCaller.callWithRetries(RpcRetryingCaller.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.java: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(RSRpcServices.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(AbstractRpcClient.java:222
t
org.apache.hadoop.hbase.ipc.AbstractRpcClient$BlockingRpcChannelImplementation.callBlockingMethod(AbstractRpc
Client.java:323)
at
org.apache.hadoop.hbase.protobuf.generated.ClientProtos$ClientService$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
还有问题就是都有哪些点可以优化查询？？？
谢谢！
