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问题

1. 这里的order by 既对查询生效了，又对聚合结果生效了。
2. 聚合结果数无法选择，这里默认只有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"
				}
			}
		}
	}
}