Kibana中Elasticsearch SQL字符串转整数求和失败问题排查

我有一个ES索引，其中spotted_field字段在Kibana开发者控制台的索引映射中显示为：

"spotted_field" : {
  "type" : "text"
}

该字段存储的是仅包含整数的字符串，且无法修改其映射。我希望将该字段从字符串转换为整数，以计算总和作为仪表盘指标。

我尝试在Canvas模板中使用Elasticsearch SQL实现，先在数据控制台执行：

SELECT CONVERT("spotted_field", SQL_INTEGER) AS sum
FROM "my_index"

预览数据显示正常，但当执行求和语句：

SELECT SUM(CONVERT("spotted_field", SQL_INTEGER)) AS sum
FROM "my_index"

时，预览数据报错：

[essql] > Unexpected error from Elasticsearch: search_phase_execution_exception - all shards failed

我怀疑该错误与格式问题有关，想了解转换过程中遗漏了什么？如果字段为keyword类型，转换是否会更顺利？

注：我使用的是Kibana 7.17.12版本。
补充：我确认CONVERT函数可正常返回预期格式。

报错原因 #

text类型字段默认会被分词，即使存储的是整数字符串，在执行SUM(CONVERT(...))这类聚合操作时，Elasticsearch需要在分片层面做分布式聚合计算。text字段的分词特性会导致转换后的数值在分片计算时出现异常，最终引发全分片失败的错误——哪怕单独执行CONVERT能正常返回样本数据，也不代表所有分片的转换结果都能被聚合逻辑正确处理。

可行解决办法 #

1. 使用Runtime Fields临时转换 #

在Kibana的索引模式中添加一个整数类型的Runtime字段，字段值通过Painless脚本生成：

emit(Integer.parseInt(doc['spotted_field'].value));

创建完成后，即可直接用这个Runtime字段在Canvas中做求和聚合，无需修改原索引映射。

2. 改用Elasticsearch DSL聚合 #

在Canvas中直接使用DSL作为数据源，绕过Elasticsearch SQL的限制：

{
  "aggs": {
    "total_sum": {
      "sum": {
        "script": {
          "source": "Integer.parseInt(doc['spotted_field'].value)"
        }
      }
    }
  },
  "size": 0
}

执行该DSL即可得到正确的求和结果。

关于keyword类型的疑问 #

如果字段是keyword类型，转换和聚合会更顺利。keyword类型存储原始字符串且不分词，Elasticsearch SQL在处理这类字段的转换和聚合时，底层逻辑更稳定，不会因分词导致的字段值碎片化问题出现聚合异常，分片层面能正确处理转换后的整数值，避免全分片失败的情况。

内容的提问来源于stack exchange，提问作者Raphadasilva