有老铁测试了es6.3.0的sql功能吗?

Elasticsearch | 作者 feloxx | 发布于2018年06月19日 | | 阅读数：6128

我单机装了个6.3.0
执行的时候老出现如下错误,有老铁遇到过吗?

我是这么来启动的
./elasticsearch-sql-cli http://127.0.0.1:9200

sql> show tables;

      name      |     type

----------------+---------------

hello           |BASE TABLE



sql> select * from hello;

Server error [Server encountered an error [Cannot extract value [deliveraddress.address] from source]. [SqlIllegalArgumentException[Cannot extract value [deliveraddress.address] from source]

	at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extractFromSource(FieldHitExtractor.java:139)

	at org.elasticsearch.xpack.sql.execution.search.extractor.FieldHitExtractor.extract(FieldHitExtractor.java:95)

	at org.elasticsearch.xpack.sql.execution.search.SearchHitRowSet.getColumn(SearchHitRowSet.java:114)

	at org.elasticsearch.xpack.sql.session.AbstractRowSet.column(AbstractRowSet.java:18)

这是测试数据的mapping

{

  "test2": {

    "properties": {

      "deliveraddress": {

        "properties": {

          "phone_no": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "default": {

            "type": "boolean"

          },

          "address": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "province": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "city": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "mapping_id": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "name": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "full_address": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "zip_code": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          }

        }

      },

      "alipaywealth": {

        "properties": {

          "balance": {

            "type": "long"

          },

          "total_quotient": {

            "type": "long"

          },

          "huabei_creditamount": {

            "type": "long"

          },

          "mapping_id": {

            "type": "text",

            "fields": {

              "keyword": {

                "ignore_above": 256,

                "type": "keyword"

              }

            }

          },

          "huabei_totalcreditamount": {

            "type": "long"

          },

          "total_profit": {

            "type": "long"

          }

        }

      },

      "id": {

        "type": "text",

        "fields": {

          "keyword": {

            "ignore_above": 256,

            "type": "keyword"

          }

        }

      }

    }

  }

}

这是测试数据

{

  "_id": "5b1cbc7935eb6e0007a154bb",

  "deliveraddress": [

    {

      "phone_no": "13*******98",

      "default": true,

      "address": "江苏省无asdads市徐***镇",

      "province": "江苏",

      "city": "无锡",

      "mapping_id": "3561511087asdasd341",

      "name": "b***",

      "full_address": "湖asd***上7号",

      "zip_code": "214400"

    },

    {

      "phone_no": "15*******70",

      "default": false,

      "address": "江苏省苏州asdasdasd张家港经济技术开发区",

      "province": "江苏",

      "city": "苏州",

      "mapping_id": "3561511asdasd505341",

      "name": "a**",

      "full_address": "新asd路***德***",

      "zip_code": "215600"

    }

  ],

  "alipaywealth": {

    "balance": 0,

    "total_quotient": 0,

    "huabei_creditamount": 500,

    "mapping_id": "3561511asdsa63505341",

    "huabei_totalcreditamount": 500,

    "total_profit": 0

  }

}

---

初步怀疑是不是不支持嵌套,数组啥的呀

然后我就翻了翻源码,发现了这个

我的错误就是在最后一个else里出现的

仔细一看,发现这个地方循环只要走了两次,或者前面的条件不成立就肯定会抛这个异常,这怎么看上去像是有点问题呢

    @SuppressWarnings("unchecked")

    Object extractFromSource(Map<String, Object> map) {

        Object value = map;

        boolean first = true;

        // each node is a key inside the map

        for (String node : path) {

            if (value == null) {

                return null;

            } else if (first || value instanceof Map) {

                first = false;

                value = ((Map<String, Object>) value).get(node);

            } else {

                throw new SqlIllegalArgumentException("Cannot extract value [{}] from source", fieldName);

            }

        }

        return unwrapMultiValue(value);

    }

[尊重社区原创，转载请保留或注明出处]
本文地址：http://elasticsearch.cn/article/676

sql

16 个评论

laoyang360

有的，参照官网就可以实现。
https://mp.weixin.qq.com/s/LgNtTU6VAR4RcwbybqtTtA

里面有截图

feloxx 回复 laoyang360

老哥,得用自己的数据测试呀
用官方的能跑通不能代表自己的数据就没问题呀

就比如我现在导入的hello数据,里面有好几层的嵌套

feloxx 回复 laoyang360

老哥,官方的例子跑通了也不行呀

得用自己的数据跑通呀,我现在就是用自己的数据不行.

laoyang360 回复 feloxx

导入的mapping和样例数据发一下吧。

feloxx 回复 laoyang360

官方的测试数据,没有嵌套,数组之类

我的测试数据,就有个这样,一个字段的值是个数组

medcl

SQL操作的结果你要保证是一张“宽表”，如果是数组当然不行的。

jiaofuyou

我也测试了6.3的SQL，尝试从规范的索引名，象：test,article等查询就没有问题，我的数据比较简单，就是两个最简的字段，name,age，select 是可以的，但是当尝试查询 logstash-2018.06.19 这种索引时，返回：
{
"type": "parsing_exception",
"reason": "line 1:23: mismatched input '-' expecting {<EOF>, ',', 'FULL', 'GROUP', 'HAVING', 'INNER', 'JOIN', 'LEFT', 'LIMIT', 'NATURAL', 'ORDER', 'RIGHT', 'WHERE'}"
}
看意思是说含有 "-" 这样的特殊字符

感觉这个第一个版本还有些不太完善

jiaofuyou 回复 medcl

M大，是否 logstash-2018.06.19 这样基于时间序列的带特殊字符的索引，查询语法有什么特殊写法呢？还是有特殊的用法？

feloxx 回复 medcl

看了看源码,估计就是这样了
sql还不支持这样的查询

但是我觉得,目前带有json数组的数据是可以存进去,那就可以查出来,可能只是目前的sql刚出,还支持的不够好把.
再研究研究

jiaofuyou

HI，feloxx，你有试过 logstash-2018.06.19 这种格式的索引吗，是否能正常查询？

feloxx 回复 jiaofuyou

不能,估计是现在的sql功能还不支持这种特殊字符

同样也是这种错误"mismatched input '-' expecting"

feloxx 回复 feloxx

但是查询的时候带上通配符是可以查出来
比如

sql> select * from test\-10\-10\-10;
Bad request [line 1:19: mismatched input '-' expecting ************

sql> select * from test*10*10*10;
这样就能正常出结果

jiaofuyou

如下的查询：
{
"query": "select callno,ccode,count(*),sum(datavol) from ocslog*2018*06*19 where imsi like '45400% ' group by callno,ccode"
}

返回错误：
{
"type": "planning_exception",
"reason": "Found 1 problem(s)\nline 1:101: Currently, only a single expression can be used with GROUP BY; please select one of [callno, ccode]"
}

group by 还只支持单一字段，那这个就有点太受限制了

feloxx 回复 jiaofuyou

刚出来的功能

看来支持的还不够全呀

medcl

转义一下就可以的：
```
POST /_xpack/sql?format=txt
{
"query":"SELECT COUNT(*) FROM \"logstash-*\""
}
```

shuoyun 回复 feloxx

我也遇到了这个问题，不知道你是如何解决的：一个字段的值为数组，计算该数组的元素个数
{"took":5,"timed_out":false,"_shards":{"total":6,"successful":6,"skipped":0,"failed":0},"hits":{"total":{"value":1,"relation":"eq"},"max_score":0.0,"hits":[{"_index":"user_portrayal","_type":"_doc","_id":"4001464_1","_score":0.0,"_source":{"none_pay_course_list":[{"course":"11_30"},{"course":"11_37"},{"course":"11_23"},{"course":"11_38"}]}}]}}

curl.exe -X GET "x.x.x.x:x/_sql" -H 'Content-Type: application/json' -d"SELECT none_pay_course_list FROM user_portrayal where uc_id=4001464" ;

要回复文章请先登录或注册

有老铁测试了es6.3.0的sql功能吗?

16 个评论

发起人

活动推荐