starrocks on es 使用外部表查询报错

💬 StarRocks 用户问答 功能使用相关
#1

1、查询前10条没有问题
mysql> select * from test_host_perf_hour_ext limit 10;
±-------------±-------------±--------------±-------------±---------------------------------±-------------±--------------±-------------±-------------±--------------±-------------±-------------±--------------±-------------±-------------±--------------±-------------±----------±-----------±-------------±-------------±--------------±---------±----------±-----------±-----------±----------+
| serverIp | avg_cpuUsage | avg_diskUsage | avg_memUsage | cmpId | dev_cpuUsage | dev_diskUsage | dev_memUsage | max_cpuUsage | max_diskUsage | max_memUsage | min_cpuUsage | min_diskUsage | min_memUsage | per_cpuUsage | per_diskUsage | per_memUsage | projectId | resourceId | resourceName | var_cpuUsage | var_diskUsage | writeDay | writeHour | writeMonth | writeTime | writeYear |
±-------------±-------------±--------------±-------------±---------------------------------±-------------±--------------±-------------±-------------±--------------±-------------±-------------±--------------±-------------±-------------±--------------±-------------±----------±-----------±-------------±-------------±--------------±---------±----------±-----------±-----------±----------+
| 172.16.15.120 | 0.4 | 0.1 | 10.19 | a25485354851a12c3e2f84636fcc9c8d | 0.11 | 0 | 0 | 0.58 | 0.1 | 10.19 | 0.2 | 0.1 | 10.18 | 0.44 | 0.1 | 10.19 | null | 15 | cvknode61533 | 0.01 | 0 | 6 | 18 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 0.39 | 0.72 | 12.57 | 6959e073271be849fb4c29c1e82641d | 0.12 | 0 | 0.01 | 0.55 | 0.72 | 12.58 | 0.2 | 0.72 | 12.56 | 0.41 | 0.72 | 12.57 | null | 16 | cvknode61534 | 0.01 | 0 | 6 | 18 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 4.72 | 0.18 | 18.58 | 862bf8a5b7cb9baae9fdc6644bfd6a76 | 0.23 | 0 | 0 | 5.09 | 0.18 | 18.58 | 4.33 | 0.18 | 18.57 | 4.74 | 0.18 | 18.58 | null | 14 | cvknode61532 | 0.06 | 0 | 6 | 21 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 4.44 | 0.18 | 18.6 | 862bf8a5b7cb9baae9fdc6644bfd6a76 | 0.16 | 0 | 0.01 | 4.76 | 0.18 | 18.61 | 4.16 | 0.18 | 18.59 | 4.42 | 0.18 | 18.6 | null | 14 | cvknode61532 | 0.03 | 0 | 7 | 0 | 9 | 2022-09-07 | 2022 |
| 172.16.15.120 | 0.47 | 0.36 | 15.98 | 975166020e78f8757bdc1c7b9f695dce | 0.16 | 0 | 0.01 | 0.92 | 0.36 | 16 | 0.18 | 0.36 | 15.98 | 0.42 | 0.36 | 15.98 | null | 13 | cvknode61531 | 0.03 | 0 | 6 | 13 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 0.49 | 0.1 | 10.2 | b25485654851a12c3e2f84636fcc9c8d | 0.11 | 0 | 0.01 | 0.71 | 0.1 | 10.22 | 0.33 | 0.1 | 10.18 | 0.45 | 0.1 | 10.2 | null | 15 | cvknode61533 | 0.01 | 0 | 6 | 19 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 0.72 | 0.72 | 12.53 | c69596073271be849fb4c29c1e82641d | 0.17 | 0 | 0 | 0.97 | 0.72 | 12.54 | 0.45 | 0.72 | 12.52 | 0.76 | 0.72 | 12.53 | null | 16 | cvknode61534 | 0.03 | 0 | 6 | 14 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 0.33 | 0.36 | 15.94 | 97516e060e78f8757bdc1c7b9f695dce | 0.1 | 0 | 0 | 0.47 | 0.36 | 15.95 | 0.16 | 0.36 | 15.93 | 0.33 | 0.36 | 15.94 | null | 13 | cvknode61531 | 0.01 | 0 | 6 | 23 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 0.45 | 0.1 | 10.22 | b25485354861a12c3e2f84636fcc9c8d | 0.11 | 0 | 0 | 0.69 | 0.1 | 10.23 | 0.3 | 0.1 | 10.21 | 0.45 | 0.1 | 10.22 | null | 15 | cvknode61533 | 0.01 | 0 | 6 | 22 | 9 | 2022-09-06 | 2022 |
| 172.16.15.120 | 1.48 | 21.43 | 33.79 | 1b36da6ba36856657a3bfb001740a78f | 0.33 | 0 | 0.02 | 2.15 | 21.43 | 33.83 | 0.99 | 21.43 | 33.76 | 1.54 | 21.43 | 33.79 | null | 2 | cvknode | 0.11 | 0 | 6 | 15 | 9 | 2022-09-06 | 2022 |
±-------------±-------------±--------------±-------------±---------------------------------±-------------±--------------±-------------±-------------±--------------±-------------±-------------±--------------±-------------±-------------±--------------±-------------±----------±-----------±-------------±-------------±--------------±---------±----------±-----------±-----------±----------+
10 rows in set (0.39 sec)

2、查询前700条数据报错
mysql> select * from test_host_perf_hour_ext limit 700;
ERROR 1064 (HY000): Failed to connect to ES server, errmsg is: Failed to connect to 172.161.71.135 port 9200 after 2394 ms: No route to

1赞
#2

同样的问题，请问您这边解决了吗

#3

发一下版本？以及be.out ?

#4

没有解决，在考虑用其他方式

#5

您这边解决了吗 ，我的环境是用docker 搭建的

#6

建立ES外部车查询报错：
insert into test.ods_es_cas_host_nic_perf_10 select * from test.external_es_cas_host_nic_perf_10;

022-10-27 06:21:24,351 WARN (thrift-server-pool-15|319) [Coordinator.updateFragmentExecStatus():1603] one instance report fail errorCode INTERNAL_ERROR Failed to connect to ES server, errmsg is: Failed to connect to 172.16.70.63 port 9200 after 3004 ms: No route to host, query_id=9318e531-55bf-11ed-b7ab-de8862f4ff33 instance_id=9318e531-55bf-11ed-b7ab-de8862f4ff35
2022-10-27 06:21:24,351 WARN (thrift-server-pool-15|319) [Coordinator.updateStatus():828] one instance report fail throw updateStatus(), need cancel. job id: -1, query id: 9318e531-55bf-11ed-b7ab-de8862f4ff33, instance id: 9318e531-55bf-11ed-b7ab-de8862f4ff35
2022-10-27 06:21:24,351 WARN (thrift-server-pool-11|194) [Coordinator.updateFragmentExecStatus():1603] one instance report fail errorCode INTERNAL_ERROR Failed to connect to ES server, errmsg is: Failed to connect to 172.16.70.63 port 9200 after 3005 ms: No route to host, query_id=9318e531-55bf-11ed-b7ab-de8862f4ff33 instance_id=9318e531-55bf-11ed-b7ab-de8862f4ff34
2022-10-27 06:21:24,351 INFO (thrift-server-pool-15|319) [Coordinator.cancelInternal():923] unfinished instance: 9318e531-55bf-11ed-b7ab-de8862f4ff35
2022-10-27 06:21:24,352 WARN (starrocks-mysql-nio-pool-10|335) [StmtExecutor.handleInsertStmtWithNewPlanner():1114] insert failed: Failed to connect to ES server, errmsg is: Failed to connect to 172.16.70.63 port 9200 after 3004 ms: No route to host
2022-10-27 06:21:24,352 WARN (starrocks-mysql-nio-pool-10|335) [StmtExecutor.handleInsertStmtWithNewPlanner():1189] handle insert stmt fail: insert_9318e531-55bf-11ed-b7ab-de8862f4ff33
com.starrocks.common.DdlException: Failed to connect to ES server, errmsg is: Failed to connect to 172.16.70.63 port 9200 after 3004 ms: No route to host
at com.starrocks.common.ErrorReport.reportDdlException(ErrorReport.java:80) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.handleInsertStmtWithNewPlanner(StmtExecutor.java:1115) ~[starrocks-fe.jar:?]
at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:433) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:285) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:432) ~[starrocks-fe.jar:?]
at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:668) ~[starrocks-fe.jar:?]
at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:55) ~[starrocks-fe.jar:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [?:1.8.0_301]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [?:1.8.0_301]
at java.lang.Thread.run(Thread.java:748) [?:1.8.0_301]
2022-10-27 06:21:24,466 INFO (starrocks-mysql-nio-pool-10|335) [QeProcessorImpl.unregisterQuery():79] deregister query id 9318e531-55bf-11ed-b7ab-de8862f4ff33
2022-10-27 06:21:24,470 INFO (starrocks-mysql-nio-pool-10|335) [StmtExecutor.analyze():581] begin to analyze stmt: 310, forwarded stmt id: 0
2022-10-27 06:21:24,480 INFO (starrocks-mysql-nio-pool-10|335) [DatabaseTransactionMgr.beginTransaction():300] begin transaction: txn_id: 70 with label insert_94f80573-55bf-11ed-b7ab-de8862f4ff33 from coordinator FE: 172.16.62.201, listner id: -1
2022-10-27 06:21:24,481 INFO (starrocks-mysql-nio-pool-10|335) [QeProcessorImpl.registerQuery():69] register query id = 94f80573-55bf-11ed-b7ab-de8862f4ff33, job: -1
2022-10-27 06:21:24,482 INFO (starrocks-mysql-nio-pool-10|335) [Coordinator.exec():457] dispatch load job: 94f80573-55bf-11ed-b7ab-de8862f4ff33 to [TNetworkAddress(hostname:172.16.62.203, port:9060), TNetworkAddress(hostname:172.16.62.202, port:9060), TNetworkAddress(hostname:172.16.62.201, port:9060)]
2022-10-27 06:21:25,502 INFO (thrift-server-pool-0|151) [ReportHandler.handleReport():224] receive report from be 10002. type: WORKGROUP_REPORT, current queue size: 1
2022-10-27 06:21:25,503 INFO (Routine load scheduler|46) [RoutineLoadScheduler.process():76] there are 0 job need schedule

#7

版本是 2.2.1-147f178 docker搭建

#8

这个升到2.2.x最新的版本吧，是一个已经修过的问题

#9

我从2.2.1版本升级到2.4.0版本了，还是有这个问题，查询ES索引索引数据，
错误提示：
mysql> select * from escloudos_host_perf_hour_ext ;
ERROR 1064 (HY000): Failed to connect to ES server, errmsg is: Failed to connect to 172.16.71.35 port 9200 after 3007 ms: No route to

#10

这个报错是不是SR集群访问不了ES集群。

#11

小于1500条数据能查询到，加where 条件就查询不到，大于1500条数据也查询不到

#12

可能是访问的数据量过多超时导致的吧，我找个研发同学进来看看吧。

#13

麻烦看一下be日志，把相应时间的错误贴一下

#14

上边就是fe.log
日志也是这样

#16

starrocks有两个主要模块，fe和be，可以看一下be目录下面的日志，有没有什么明显错误，be.INFO.log和be.out

京ICP备2022026421号-1