ES Catalog 无法使用

DanielDuan · 2023年10月23日 03:34

【详述】在Starrocks中创建es catalog

   CREATE EXTERNAL CATALOG es
   PROPERTIES (
   "type" = "es",
   "hosts" = "http://10.0.0.0:9200",
   "es.nodes.wan.only" = "true"
   )

使用mysql client 连接集群，并尝试进行查询

set catalog es;
use database default_db;
show tables;

无法查询到任何表。
【背景】

尝试使用一个新建集群，并创建es catalog，执行 show tables 命令也没有结果。

【业务影响】
现在只是在做功能测试，暂无影响。

【StarRocks版本】：3.1.2
【Elasticsearch版本】7.9.1
【集群规模】：3fe（3 follower）+3be（fe与be混部）
【机器信息】48C/128G/万兆
【联系方式】社区群-16-段方伟，邮箱 duanfangwei2012@sina.com

fe master 错误日志：

2023-10-23 11:02:20,638 ERROR (starrocks-mysql-nio-pool-3749|39505) [ElasticsearchMetadata.toEsTable():89] transform to EsTable Error
com.starrocks.common.AnalysisException: index[metricbeat-7.13.2-2023.08.08-000021] 's properties not found for the ES
        at com.starrocks.connector.elasticsearch.EsUtil.parseProperties(EsUtil.java:207) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.EsUtil.convertColumnSchema(EsUtil.java:122) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.toEsTable(ElasticsearchMetadata.java:78) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.getTable(ElasticsearchMetadata.java:71) ~[starrocks-fe.jar:?]
        at com.starrocks.server.MetadataMgr.lambda$getTable$3(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at java.util.Optional.map(Optional.java:215) ~[?:1.8.0_261]
        at com.starrocks.server.MetadataMgr.getTable(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.handleShowTable(ShowExecutor.java:901) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.execute(ShowExecutor.java:271) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.handleShow(StmtExecutor.java:1335) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:613) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:362) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:476) ~[starrocks-fe.jar:?]

Liang · 2023年10月23日 04:14

试试在PROPERTIES里指定下es.type的值呢

DanielDuan · 2023年10月23日 04:37

MySQL [default_db]> show create catalog es;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Catalog | Create Catalog                                                                                                                                                                                                                                                           |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| hz_es   | CREATE EXTERNAL CATALOG `es`
PROPERTIES ("enable_docvalue_scan"  =  "true",
"es.nodes.wan.only"  =  "true",
"hosts"  =  "http://10.0.0.0:9200",
"es.net.ssl"  =  "false",
"es.type"  =  "_doc",
"type"  =  "es",
"user"  =  "",
"enable_keyword_sniff"  =  "true"
) |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

MySQL [default_db]>
MySQL [default_db]> show tables;
Empty set (1.95 sec)

MySQL [default_db]>

错误日志

2023-10-23 12:36:00,207 ERROR (starrocks-mysql-nio-pool-2|469047) [ElasticsearchMetadata.toEsTable():89] transform to EsTable Error
java.lang.ClassCastException: java.lang.Boolean cannot be cast to org.json.JSONObject
        at com.starrocks.connector.elasticsearch.EsUtil.parsePropertiesRoot(EsUtil.java:237) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.EsUtil.parseProperties(EsUtil.java:202) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.EsUtil.convertColumnSchema(EsUtil.java:122) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.toEsTable(ElasticsearchMetadata.java:78) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.getTable(ElasticsearchMetadata.java:71) ~[starrocks-fe.jar:?]
        at com.starrocks.server.MetadataMgr.lambda$getTable$3(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at java.util.Optional.map(Optional.java:215) ~[?:1.8.0_261]
        at com.starrocks.server.MetadataMgr.getTable(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.handleShowTable(ShowExecutor.java:901) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.execute(ShowExecutor.java:271) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.handleShow(StmtExecutor.java:1335) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:613) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:362) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:476) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:742) ~[starrocks-fe.jar:?]
        at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_261]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_261]
        at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_261]

索引有动态mapping，会不会是这个原因？

DanielDuan · 2023年10月23日 05:29

又做了如下测试

创建带有几种属性的索引

curl -X DELETE '10.0.0.0:9200/test123'
curl -X PUT '10.0.0.0:9200/test123' -H 'Content-Type: application/json' -d'
{
    "mappings": {
      "_source": {
        "includes": [],
        "excludes": []
      },
      "dynamic_templates": [
        {
          "string_template": {
            "match": "*",
            "match_mapping_type": "string",
            "mapping": {
              "type": "keyword"
            }
          }
        }
      ],
      "properties": {
        "id": {
          "type": "keyword"
        }
      }
    }
}'

索引无法被starrock查询，报错信息

2023-10-23 13:23:34,098 WARN (starrocks-mysql-nio-pool-12|473012) [ShowExecutor.handleShowTable():903] table es_test.default_db.test345 does not exist
2023-10-23 13:23:34,114 ERROR (starrocks-mysql-nio-pool-12|473012) [ElasticsearchMetadata.toEsTable():89] transform to EsTable Error
com.starrocks.common.AnalysisException: index[test123] 's properties not found for the ES
        at com.starrocks.connector.elasticsearch.EsUtil.parseProperties(EsUtil.java:207) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.EsUtil.convertColumnSchema(EsUtil.java:122) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.toEsTable(ElasticsearchMetadata.java:78) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.getTable(ElasticsearchMetadata.java:71) ~[starrocks-fe.jar:?]
        at com.starrocks.server.MetadataMgr.lambda$getTable$3(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at java.util.Optional.map(Optional.java:215) ~[?:1.8.0_261]
        at com.starrocks.server.MetadataMgr.getTable(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.handleShowTable(ShowExecutor.java:901) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.execute(ShowExecutor.java:271) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.handleShow(StmtExecutor.java:1335) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:613) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:362) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:476) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:742) ~[starrocks-fe.jar:?]
        at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_261]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_261]
        at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_261]

减少部分数据，并进行测试

curl -X DELETE '10.0.0.0:9200/test345'
curl -X PUT '10.0.0.0:9200/test345' -H 'Content-Type: application/json' -d'
{
    "mappings": {
      "dynamic_templates": [
        {
          "string_template": {
            "match": "*",
            "match_mapping_type": "string",
            "mapping": {
              "type": "keyword"
            }
          }
        }
      ],
      "properties": {
        "id": {
          "type": "keyword"
        }
      }
    }
}'

索引无法被starrock查询，报错信息

2023-10-23 13:23:34,098 WARN (starrocks-mysql-nio-pool-12|473012) [ShowExecutor.handleShowTable():903] table es_test.default_db.test345 does not exist
2023-10-23 13:23:34,114 ERROR (starrocks-mysql-nio-pool-12|473012) [ElasticsearchMetadata.toEsTable():89] transform to EsTable Error
com.starrocks.common.AnalysisException: index[test123] 's properties not found for the ES
        at com.starrocks.connector.elasticsearch.EsUtil.parseProperties(EsUtil.java:207) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.EsUtil.convertColumnSchema(EsUtil.java:122) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.toEsTable(ElasticsearchMetadata.java:78) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.getTable(ElasticsearchMetadata.java:71) ~[starrocks-fe.jar:?]
        at com.starrocks.server.MetadataMgr.lambda$getTable$3(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at java.util.Optional.map(Optional.java:215) ~[?:1.8.0_261]
        at com.starrocks.server.MetadataMgr.getTable(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.handleShowTable(ShowExecutor.java:901) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.execute(ShowExecutor.java:271) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.handleShow(StmtExecutor.java:1335) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:613) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:362) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:476) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:742) ~[starrocks-fe.jar:?]
        at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_261]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_261]
        at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_261]

删除所有其他额外属性

curl -X DELETE '10.0.0.0:9200/test456'
curl -X PUT '10.0.0.0:9200/test456' -H 'Content-Type: application/json' -d'
{
    "mappings": {
      "properties": {
        "id": {
          "type": "keyword"
        }
      }
    }
}'

能够查询成功

MySQL [default_db]> show tables;
+----------------------+
| Tables_in_default_db |
+----------------------+
| test456              |
+----------------------+
4 rows in set (0.07 sec)

MySQL [default_db]>

加上动态索引属性

curl -X DELETE '10.35.3.58:9200/test567'
curl -X PUT '10.35.3.58:9200/test567' -H 'Content-Type: application/json' -d'
{
    "mappings": {
	"dynamic": "true",
      "_source": {
        "includes": [],
        "excludes": []
      },
      "dynamic_templates": [
        {
          "string_template": {
            "match": "*",
            "match_mapping_type": "string",
            "mapping": {
              "type": "keyword"
            }
          }
        }
      ],
      "properties": {
        "id": {
          "type": "keyword"
        }
      }
    }
}'

索引无法被starrock查询，报错信息

2023-10-23 13:23:34,070 WARN (starrocks-mysql-nio-pool-12|473012) [ShowExecutor.handleShowTable():903] table es_test.default_db.test567 does not exist
2023-10-23 13:23:34,097 ERROR (starrocks-mysql-nio-pool-12|473012) [ElasticsearchMetadata.toEsTable():89] transform to EsTable Error
java.lang.ClassCastException: org.json.JSONArray cannot be cast to org.json.JSONObject
        at com.starrocks.connector.elasticsearch.EsUtil.parsePropertiesRoot(EsUtil.java:237) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.EsUtil.parseProperties(EsUtil.java:202) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.EsUtil.convertColumnSchema(EsUtil.java:122) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.toEsTable(ElasticsearchMetadata.java:78) ~[starrocks-fe.jar:?]
        at com.starrocks.connector.elasticsearch.ElasticsearchMetadata.getTable(ElasticsearchMetadata.java:71) ~[starrocks-fe.jar:?]
        at com.starrocks.server.MetadataMgr.lambda$getTable$3(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at java.util.Optional.map(Optional.java:215) ~[?:1.8.0_261]
        at com.starrocks.server.MetadataMgr.getTable(MetadataMgr.java:226) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.handleShowTable(ShowExecutor.java:901) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ShowExecutor.execute(ShowExecutor.java:271) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.handleShow(StmtExecutor.java:1335) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.StmtExecutor.execute(StmtExecutor.java:613) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.handleQuery(ConnectProcessor.java:362) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.dispatch(ConnectProcessor.java:476) ~[starrocks-fe.jar:?]
        at com.starrocks.qe.ConnectProcessor.processOnce(ConnectProcessor.java:742) ~[starrocks-fe.jar:?]
        at com.starrocks.mysql.nio.ReadListener.lambda$handleEvent$0(ReadListener.java:69) ~[starrocks-fe.jar:?]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[?:1.8.0_261]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[?:1.8.0_261]
        at java.lang.Thread.run(Thread.java:748) ~[?:1.8.0_261]

丹尼尔007 · 2023年10月23日 13:00

大概率是不支持的，不过这里我们尝试复现一下吧

matthew · 2024年03月14日 07:37

对接星环的scope（也是类似es的产品）时候也遇到了类似的问题，curl索引建立成功，用curl也能查到，但建立elastic search的catalog后show tables就显示一条也没有，后台看fe.log也是显示transform to EsTable Error

matthew · 2024年03月14日 07:38

请问专家，有什么好办法能解决不

丹尼尔007 · 2024年06月27日 02:56

抱歉回复晚了，目前 sr 不支持动态索引