最近项目中出了点问题，原因是将ES的查询SQL通过ES Service的_sql/translate 接口转换为DSL时，出现了严重嵌套的问题.
1. 现象
1.1. 查询SQL
SELECT *
FROM table
WHERE is_delete IS NULL
AND (
(
codes LIKE '%40100101%'
OR codes LIKE '%40100102%'
OR codes LIKE '%40100103%'
OR codes LIKE '%40100104%'
OR codes LIKE '%40100105%'
OR codes LIKE '%40100106%'
OR codes LIKE '%40100116%'
OR codes LIKE '%40100111%'
)
AND (
codes LIKE '%50300101%'
OR codes LIKE '%50300102%'
OR codes LIKE '%50300103%'
OR codes LIKE '%50300104%'
OR codes LIKE '%50300107%'
OR codes LIKE '%50300108%'
OR codes LIKE '%50300110%'
OR codes LIKE '%50300128%'
OR codes LIKE '%50300115%'
OR codes LIKE '%50300116%'
OR codes LIKE '%50300119%'
OR codes LIKE '%50300120%'
OR codes LIKE '%50300125%'
OR codes LIKE '%50300126%'
)
)1.2. _sql/translate转换结果
{
"query": {
"bool": {
"must": [
{
"bool": {
"must_not": [
{
"exists": {
"field": "is_delete",
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"bool": {
"must": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"wildcard": {
"codes": {
"wildcard": "*40100101*",
"boost": 1
}
}
},
{
"wildcard": {
"codes": {
"wildcard": "*40100102*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*40100103*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*40100104*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*40100105*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*40100106*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*40100116*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*40100111*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"bool": {
"should": [
{
"wildcard": {
"codes": {
"wildcard": "*50300101*",
"boost": 1
}
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300102*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300103*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300104*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300107*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300108*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300110*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300128*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300115*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300116*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300119*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300120*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300125*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
},
{
"wildcard": {
"codes": {
"wildcard": "*50300126*",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}
}2. 解决
2.1. 方案一：重新解析SQL，手动拼接DSL
这种方法需要自己手动对SQL中的表名、字段、WHERE条件等进行解析，按照不同的条件拼接不同的查询语句
例如 【field = value 】条件转换为：
{
"term": {
"field": {
"value": "value",
"boost": 1
}
}
}【field != value 】条件转换为：
{
"bool": {
"must_not": [
{
"term": {
"field": {
"value": "value",
"boost": 1
}
}
}
],
"adjust_pure_negative": true,
"boost": 1
}
}这种方法除了转换准确性比较高，还需要按照关系依次拼接最终的DSL，但是工作量比较大。有兴趣的可以试试。
2.2. 方案二：elasticsql
之前操作ES，除了同家的kibana，还用到了另外一款开源项目：ElasticView
GitHub - 1340691923/ElasticView: 这是一个简单好用的ElasticSearch可视化客户端，支持连接6，7，8版本的ES，不妨一试
其中也提供了SQL转DSL的功能，并且在使用后发现转换结果比_sql/translate要好不少，以上面SQL为例，将并列的条件都放到了同一个should下，避免了过多的嵌套，并且模糊匹配时使用的是match_phrase ，在当前场景下比 wildcard 性能还要好。
转换结果如下：
{
"query": {
"bool": {
"must": [
{
"match_phrase": {
"is_delete": {
"query": ""
}
}
},
{
"bool": {
"should": [
{
"match_phrase": {
"codes": {
"query": "40100101"
}
}
},
{
"match_phrase": {
"codes": {
"query": "40100102"
}
}
},
{
"match_phrase": {
"codes": {
"query": "40100103"
}
}
},
{
"match_phrase": {
"codes": {
"query": "40100104"
}
}
},
{
"match_phrase": {
"codes": {
"query": "40100105"
}
}
},
{
"match_phrase": {
"codes": {
"query": "40100106"
}
}
},
{
"match_phrase": {
"codes": {
"query": "40100116"
}
}
},
{
"match_phrase": {
"codes": {
"query": "40100111"
}
}
}
]
}
},
{
"bool": {
"should": [
{
"match_phrase": {
"codes": {
"query": "50300101"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300102"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300103"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300104"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300107"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300108"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300110"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300128"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300115"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300116"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300119"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300120"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300125"
}
}
},
{
"match_phrase": {
"codes": {
"query": "50300126"
}
}
}
]
}
}
]
}
},
"from": 0,
"size": 1
}所以也看了一下它底层是怎么去转换的。
可以看到组件是GO语言开发，调用了 github.com/cch123/elasticsql 中的抓换方法。由于我的项目是Java + Scala 的，本打算按照处理逻辑用Java写一遍，不过决定先处理生产上的问题，后面有时间再做。所以是写了个简单的GO方法，编译成可执行文件后由Java程序去调用执行。
3. 代码
sqltodslaction.go：
package main
import (
"encoding/json"
"fmt"
"github.com/cch123/elasticsql"
"os"
)
// ConvertResponse 返回的响应
type ConvertResponse struct {
DSL string `json:"dsl"`
Table string `json:"table"`
Error string `json:"error,omitempty"`
}
// ConvertSQLToDSL 调用 elasticsql.ConvertPretty 的方法
// 接受一个 SQL 字符串参数，返回 ConvertResponse 结构体
func ConvertSQLToDSL(sql string) ConvertResponse {
dsl, table, err := elasticsql.ConvertPretty(sql)
if err != nil {
return ConvertResponse{
Error: err.Error(),
}
}
return ConvertResponse{
DSL: dsl,
Table: table,
}
}
func main() {
if len(os.Args) != 2 {
fmt.Println("请传入正确参数：<SQL>")
return
}
// 示例调用
//sql := "SELECT * FROM my_table WHERE id = 1"
sql := os.Args[1]
response := ConvertSQLToDSL(sql)
// 打印结果
responseJSON, _ := json.Marshal(response)
fmt.Println(string(responseJSON))
}
编译：
go build -o greet main.goJava使用：
import java.io.BufferedReader;
import java.io.InputStreamReader;
public class Sql2DSLAction {
public static void main(String[] args) {
String sql = "SELECT\n" +
" *\n" +
"FROM\n" +
" table1\n" +
"WHERE\n" +
" is_delete = ''\n" +
" AND (\n" +
" (\n" +
" (\n" +
" (\n" +
" codes LIKE '%BQ40100101%'\n" +
" OR codes LIKE '%BQ40100102%'\n" +
" OR codes LIKE '%BQ40100103%'\n" +
" OR codes LIKE '%BQ40100104%'\n" +
" OR codes LIKE '%BQ40100105%'\n" +
" OR codes LIKE '%BQ40100106%'\n" +
" OR codes LIKE '%BQ40100116%'\n" +
" OR codes LIKE '%BQ40100111%'\n" +
" )\n" +
" AND (\n" +
" codes LIKE '%BQ50300101%'\n" +
" OR codes LIKE '%BQ50300102%'\n" +
" OR codes LIKE '%BQ50300103%'\n" +
" OR codes LIKE '%BQ50300104%'\n" +
" OR codes LIKE '%BQ50300107%'\n" +
" OR codes LIKE '%BQ50300108%'\n" +
" OR codes LIKE '%BQ50300110%'\n" +
" OR codes LIKE '%BQ50300128%'\n" +
" OR codes LIKE '%BQ50300115%'\n" +
" OR codes LIKE '%BQ50300116%'\n" +
" OR codes LIKE '%BQ50300119%'\n" +
" OR codes LIKE '%BQ50300120%'\n" +
" OR codes LIKE '%BQ50300125%'\n" +
" OR codes LIKE '%BQ50300126%'\n" +
" )\n" +
" )\n" +
" )\n" +
" )";
String path = "E:\\GolandProjects\\SqlToDslAction\\greet";
String result = "";
try {
ProcessBuilder pb = new ProcessBuilder(path, sql);
pb.redirectErrorStream(true);
Process process = pb.start();
BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()));
String line;
if ((line = reader.readLine()) != null) {
result = line;
}
int exitCode = process.waitFor();
System.out.println("Exited with code: " + exitCode);
} catch (Exception e) {
e.printStackTrace();
}
System.out.println("Conversion result："+result);
}
}
Scala使用：
import cn.hutool.json.JSONUtil
import scala.io.Source
import scala.sys.process._
object Sql2DSLActionScala {
def main(args: Array[String]): Unit = {
val sql =
"""SELECT
| *
|FROM
| table1
|WHERE
| is_delete = ''
| AND (
| (
| (
| (
| codes LIKE '%BQ40100101%'
| OR codes LIKE '%BQ40100102%'
| OR codes LIKE '%BQ40100103%'
| OR codes LIKE '%BQ40100104%'
| OR codes LIKE '%BQ40100105%'
| OR codes LIKE '%BQ40100106%'
| OR codes LIKE '%BQ40100116%'
| OR codes LIKE '%BQ40100111%'
| )
| AND (
| codes LIKE '%BQ50300101%'
| OR codes LIKE '%BQ50300102%'
| OR codes LIKE '%BQ50300103%'
| OR codes LIKE '%BQ50300104%'
| OR codes LIKE '%BQ50300107%'
| OR codes LIKE '%BQ50300108%'
| OR codes LIKE '%BQ50300110%'
| OR codes LIKE '%BQ50300128%'
| OR codes LIKE '%BQ50300115%'
| OR codes LIKE '%BQ50300116%'
| OR codes LIKE '%BQ50300119%'
| OR codes LIKE '%BQ50300120%'
| OR codes LIKE '%BQ50300125%'
| OR codes LIKE '%BQ50300126%'
| )
| )
| )
| )""".stripMargin
val unit = convertSqlToDsl(sql)
println("DSL>" + unit._1)
println("TABLE>" + unit._2)
}
def convertSqlToDsl(sql: String) = {
val path = "E:\\GolandProjects\\SqlToDslAction\\greet"
var result = ""
try {
val processBuilder = Process(Seq(path, sql))
val process = processBuilder.run(new ProcessIO(
_ => (),
stdout => {
val source = Source.fromInputStream(stdout)
result = source.getLines().mkString
source.close()
},
stderr => {
val errorSource = Source.fromInputStream(stderr)
errorSource.getLines().foreach(println)
errorSource.close()
}
))
val exitCode = process.exitValue()
println(s"Exited with code: $exitCode")
} catch {
case e: Exception => e.printStackTrace()
}
println(s"Conversion result：$result")
val jSONObject = JSONUtil.parseObj(result)
val dsl = jSONObject.getStr("dsl")
val table = jSONObject.getStr("table")
(dsl, table)
}
}
我这里没用环境，只好先用这种方法调用；有Go环境的或者Docker的可以直接部署一个服务进行接口调用更好；
es_controller.go：
package main
import (
"encoding/json"
"fmt"
"github.com/cch123/elasticsql"
"net/http"
)
type ConvertRequest struct {
SQL string `json:"sql"`
}
type ConvertResponse struct {
DSL string `json:"dsl"`
Table string `json:"table"`
Error string `json:"error,omitempty"`
}
func convertHandler(w http.ResponseWriter, r *http.Request) {
var req ConvertRequest
// 解析请求体
err := json.NewDecoder(r.Body).Decode(&req)
if err != nil {
http.Error(w, err.Error(), http.StatusBadRequest)
return
}
// 调用 elasticsql.ConvertPretty
dsl, table, err := elasticsql.ConvertPretty(req.SQL)
if err != nil {
response := ConvertResponse{
Error: err.Error(),
}
w.Header().Set("Content-Type", "application/json")
json.NewEncoder(w).Encode(response)
return
}
// 构建响应
response := ConvertResponse{
DSL: dsl,
Table: table,
}
w.Header().Set("Content-Type", "application/json")
json.NewEncoder(w).Encode(response)
}
func main() {
http.HandleFunc("/convert", convertHandler)
fmt.Println("Server is running on port 8080...")
http.ListenAndServe(":8080", nil)
}
调用接口：
curl -X POST -H "Content-Type: application/json" -d '{"sql":"SELECT * FROM table1 WHERE is_delete = '' AND ( ( ( ( codes LIKE '%BQ40100101%' OR codes LIKE '%BQ40100102%' OR codes LIKE '%BQ40100103%' OR codes LIKE '%BQ40100104%' OR codes LIKE '%BQ40100105%' OR codes LIKE '%BQ40100106%' OR codes LIKE '%BQ40100116%' OR codes LIKE '%BQ40100111%' ) AND ( codes LIKE '%BQ50300101%' OR codes LIKE '%BQ50300102%' OR codes LIKE '%BQ50300103%' OR codes LIKE '%BQ50300104%' OR codes LIKE '%BQ50300107%' OR codes LIKE '%BQ50300108%' OR codes LIKE '%BQ50300110%' OR codes LIKE '%BQ50300128%' OR codes LIKE '%BQ50300115%' OR codes LIKE '%BQ50300116%' OR codes LIKE '%BQ50300119%' OR codes LIKE '%BQ50300120%' OR codes LIKE '%BQ50300125%' OR codes LIKE '%BQ50300126%' ) ) ) )"}' http://localhost:8080/convert不知道有没有Java去转换DSL的项目，如果了解的欢迎分享~
扫一扫
4943
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
