无论才能、知识多么卓著,如果缺乏热情,则无异纸上画饼充饥,无补于事。

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

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

我单机装了个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


16 个评论

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

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

就比如我现在导入的hello数据,里面有好几层的嵌套
老哥,官方的例子跑通了也不行呀

得用自己的数据跑通呀,我现在就是用自己的数据不行.
导入的mapping和样例数据发一下吧。
官方的测试数据,没有嵌套,数组之类

我的测试数据,就有个这样,一个字段的值是个数组
SQL操作的结果你要保证是一张“宽表”,如果是数组当然不行的。
我也测试了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'}"
}
看意思是说含有 "-" 这样的特殊字符

感觉这个第一个版本还有些不太完善
M大,是否 logstash-2018.06.19 这样基于时间序列的带特殊字符的索引,查询语法有什么特殊写法呢?还是有特殊的用法?
feloxx

feloxx 回复 medcl

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

但是我觉得,目前带有json数组的数据是可以存进去,那就可以查出来,可能只是目前的sql刚出,还支持的不够好把.
再研究研究
HI,feloxx,你有试过 logstash-2018.06.19 这种格式的索引吗,是否能正常查询?
不能,估计是现在的sql功能还不支持这种特殊字符

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

feloxx 回复 feloxx

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

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

sql> select * from test*10*10*10;
这样就能正常出结果
如下的查询:
{
"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 还只支持单一字段,那这个就有点太受限制了
刚出来的功能

看来支持的还不够全呀
转义一下就可以的:
```
POST /_xpack/sql?format=txt
{
"query":"SELECT COUNT(*) FROM \"logstash-*\""
}
```
我也遇到了这个问题,不知道你是如何解决的:一个字段的值为数组,计算该数组的元素个数
{"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" ;

要回复文章请先登录注册