Elasticsearch SQL插件调研与问题整理
在最新的es8.11版本中，开始有了es|ql语言。非常接近sql，但是还是不太一样。而在之前的版本中，sql能力很弱，并且属于白金版本的内容。也就是说需要氪金才能体验，才能使用。
我是es研发工程师。负责公司内部的es集群的日常维护（万亿级规模），升级改造与优化。最近在做一件事情，需要能够降低es的学习使用成本，给公司更多的人带来方便，即使不懂es也能用起来。所以需要一个好用的API，不再去写es的哪些语法。
本来考虑使用query_string语法来支持。业务人员来自己自由组装逻辑表达式即可。但是给大家体验后，普遍觉得比较麻烦，比较难。
想一下，既然难，那什么才是容易的呢。对于开发人员来说，应该sql能力是必备的能力，属于基本不用再学的东西。于是就调研了es里边能用的sql插件。
通过大量调研，普遍的方案，都是open Distro，但是版本停留在了7.10版本。另外一个插件，nlpChina，已经更新到了8.10版本，更新非常及时。于是就选用它。但是我发现插件普遍存在性能问题。主要是sql的原语和es的原语对照不齐。且插件为了普遍性，损失了很大的性能。通过这篇文章，就整理出来插件中存在的问题。我会先写sql语句，以及找到其转化后的DSL语句。并指出转换后存在的问题。
ES-sql插件（搜索语法）
参考文档
Basic Queries - Open Distro Documentation
sql插件地址
https://github.com/NLPchina/elasticsearch-sql
语法支持
SELECT [DISTINCT] (* | expression) [[AS] alias] [, ...]
FROM index_name
[WHERE predicates]
[GROUP BY expression [, ...]
[HAVING predicates]]
[ORDER BY expression [IS [NOT] NULL] [ASC | DESC] [, ...]]
[LIMIT [offset, ] size]
NLPchina/elasticsearch-sql存在的问题
问题汇总
在sql中where后边的逻辑符号测试。存在以下问题。
| 运算符 | 描述 | 是否支持 | 是否存在问题 |
| = | 等于 | √ | 原语问题 |
| <> | 不等于。注释：在 SQL 的一些版本中，该操作符可被写成 != | √ | 原语问题 |
| > | 大于 | √ | |
| < | 小于 | √ | |
| >= | 大于等于 | √ | |
| <= | 小于等于 | √ | |
| BETWEEN | 在某个范围内 | √ | |
| LIKE | 搜索某种模式 | √ | |
| IN | 指定针对某个列的多个可能值 | √ | 原语问题 |
| IS NULL | 为空 | √ | |
| IS NOT NULL | 不为空 | √ | 解析语句臃肿 |
问题1- =语法问题
此问题，存在严重的性能问题。虽然最终的检索结果，可能是一样的。但是底层逻辑执行绕了一大圈。等号被解析成了match_phrase，始终是要分词的，在分词之后，又走的检索逻辑，且要计算距离。严重损耗性能！
select * from product-index where price=2 limit 10
sql2dsl
{
"from": 0,
"size": 10,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"match_phrase": {
"price": {
"query": 2
}
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}
}解决方式1：需要修改语法，指定为精准匹配
select * from product-index where price=term("10") limit 10
sql2dsl
{
"from": 0,
"size": 10,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"term": {
"price": {
"value": "10"
}
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}
}解决方式2：TODO 修改插件源码中的解析逻辑。需要考虑等号的逻辑。在sql语句中，等号的原语，应该是精准匹配。
问题2- in 语法问题
此问题和问题1类似，同样存在被解析成match_phrase的问题。同时，更严重的问题是，in原语它解析成了should。这里实际上应该使用terms语法。因为should实际上被用来做加分操作。并不符合sql中in的原语。
select * from product-index where price in(1,2) limit 10
sql2dsl
{
"from": 0,
"size": 10,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"bool": {
"should": [{
"match_phrase": {
"price": {
"query": 1
}
}
}, {
"match_phrase": {
"price": {
"query": 2
}
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}
}问题解决方式1：使用以下形式。
select * from product-index where price=terms(1,2) limit 10
问题解决方式2：修改源码逻辑。
问题3- <> 语法问题
此问题和上述问题相似，被解析成为了 match_phrase
select * from product-index where price<>2 limit 10
sql2dsl
{
"from": 0,
"size": 10,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"bool": {
"must_not": [{
"match_phrase": {
"price": {
"query": 2
}
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}
}select * from product-index where price<>term(2) limit 10
问题解决方式:改源码
问题4 IS NOT NULL 语法问题
语句非常臃肿。用了两层must not，来表示must
select * from product-index where price IS NOT NULL limit 10
sql2dsl
{
"from": 0,
"size": 10,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"bool": {
"must_not": [{
"bool": {
"must_not": [{
"exists": {
"field": "price",
"boost": 1.0
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}
}问题5-解析后的条件，都带着 filter
问题：会滥用缓存
{
"from": 0,
"size": 10,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"match_phrase": {
"price": {
"query": 2
}
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
}
}问题解决方法1：考虑在查询的时候，指定不缓存。
问题解决方法2：修改逻辑。
问题6- group by 语法
原语没有问题，解析对应的是es中的 terms桶聚合。shard_size": 5000 设置的过大。
select * from product-index where price=2 GROUP BY price limit 10
sql2dsl
{
"from": 0,
"size": 0,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"match_phrase": {
"price": {
"query": 2
}
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
},
"aggregations": {
"price": {
"terms": {
"field": "price",
"size": 10,
"shard_size": 5000,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": [{
"_count": "desc"
}, {
"_key": "asc"
}]
}
}
}
}问题7 聚合场景下的 order by问题
- 这里的order by 既对查询生效了，又对聚合结果生效了。
- 聚合结果数无法选择，这里默认只有10条。
select * from product-index where price=2 GROUP BY price ORDER BY price asc limit 0
sql2dsl
{
"from": 0,
"size": 0,
"query": {
"bool": {
"filter": [{
"bool": {
"must": [{
"match_phrase": {
"price": {
"query": 2
}
}
}],
"boost": 1.0
}
}],
"boost": 1.0
}
},
"sort": [{
"price": {
"order": "asc"
}
}],
"aggregations": {
"price": {
"terms": {
"field": "price",
"size": 10,
"shard_size": 5000,
"min_doc_count": 1,
"shard_min_doc_count": 0,
"show_term_doc_count_error": false,
"order": {
"_key": "asc"
}
}
}
}
}