DSL的诞生 | 复杂sql转成Elasticsearch DSL深入详解

源自死磕ElasticsearchQQ群（626036393）中的一个问题：
问题如下：

where (position=ES or work=ES or content=ES) and academic=本科 and （city=北京 or city=深圳）

怎么构建ES的查询条件？

我的问题拆解与实现如下：

1、sql语句转成DSL有哪些方法？

方案一：借助工具 NLP团体开发的Elasticsearch-sql;
2.X安装过，5.X没有再安装。
方案二：借助工具ElasticHQ的自动转换模块：

方案一、方案二和Github上其他语言开发的sql转DSL工具对简单的sql生成的DSL相对准确，但对于复杂的sql生成的不一定精确。（如上所示）

方案三：徒手生成。

2、如何根据复杂的sql语句生成ES的DSL查询语句呢？

步骤1：拆解

where (position=ES or work=ES or content=ES) and academic=本科 and （city=北京 or city=深圳）

这个sql语句由几部分组成呢？
以and作为拆分，共分为3部分：
三个部分sql用and衔接，转换为DSL对应最外层must；

第一部分： position=ES or work=ES or content=ES
三个子条件sql用or衔接，转换DSL对应should；

第二部分： academic=本科
单一条件转换为DSL对应term精确匹配；

第三部分： city=北京 or city=深圳
city的两个or语句转换为DSL对应terms多词精确匹配。

上面的sql都用的=号，假定不需要分词，我们统一采用termquery和termsquery实现。
引申：
如果需要分词，更换为matchquery或者match_parsequery即可。
如果需要模糊匹配，更换为wildcardquery接口。

步骤2：套bool多条件检索DSL模板

复杂bool多条件检索DSL模板：
包含了：查询/检索、聚合、排序、指定字段输出、输出起点、输出多少等信息。

{  "query": {  "bool": {  "must": [],  "must_not": [],  "should": []  }  },  "aggs": {  "my_agg": {  "terms": {  "field": "user",  "size": 10  }  }  },  "highlight": {  "pre_tags": [  "<em>"  ],  "post_tags": [  "</em>"  ],  "fields": {  "body": {  "number_of_fragments": 1,  "fragment_size": 20  },  " ": {}  }  },  "size": 20,  "from": 100,  "_source": [  " ",  "id"  ],  "sort": [  {  "_id": {  "order": "desc"  }  }  ]}

简单bool多条件查询DSL模板：
只包含查询。

{  "query": {  "bool": {  "must": [],  "must_not": [],  "should": []  }  }}

以上根据我们的sql特点，简单模板即能满足要求。

步骤3：构造生成DSL

根据，步骤1、步骤2，可以构思出根据sql转换后的DSL应该：
1）最外层bool
2）第二层：must 三个并行条件
3）第三层：各自的匹配条件。（存在bool嵌套bool的情况）

3、动动手，验证下。

3.1 创建索引（自动生成mapping）

put test_index_01

3.2 提交数据

post test_index_01/test_type_01/1{  "no":"1",  "city":"北京",  "academic":"专科",  "content":"ES",  "position":"ES",  "work":"ES"}post test_index_01/test_type_01/2{  "no":"2",  "city":"天津",  "academic":"本科",  "content":"ES",  "position":"ES",  "work":"ES"}post test_index_01/test_type_01/3{  "no":"3",  "city":"深圳",  "academic":"本科",  "content":"ES",  "position":"ES2",  "work":"ES3"}post test_index_01/test_type_01/4{  "no":"4",  "city":"北京",  "academic":"本科",  "content":"ES1",  "position":"ES2",  "work":"ES"}

插入后ES-head插件控制台查询结果：

3.3 完成检索

post test_index_01/_search{  "query": {  "bool": {  "must": [  {  "terms": {  "city.keyword": [  "北京",  "深圳"  ]  }  },  {  "term": {  "academic.keyword": "本科"  }  },  {  "bool": {  "should": [  {  "term": {  "content.keyword": "ES"  }  },  {  "term": {  "position.keyword": "ES"  }  },  {  "term": {  "work.keyword": "ES"  }  }  ]  }  }  ]  }  },  "size": 10,  "from": 0}

注意：
没有做分词，做的精确匹配，所以加了”.keyword”。

3.4 返回结果

{  "took": 1,  "timed_out": false,  "_shards": {  "total": 5,  "successful": 5,  "failed": 0  },  "hits": {  "total": 2,  "max_score": 1.0577903,  "hits": [  {  "_index": "test_index_01",  "_type": "test_type_01",  "_id": "4",  "_score": 1.0577903,  "_source": {  "no": "4",  "city": "北京",  "academic": "本科",  "content": "ES1",  "position": "ES2",  "work": "ES"  }  },  {  "_index": "test_index_01",  "_type": "test_type_01",  "_id": "3",  "_score": 0.8630463,  "_source": {  "no": "3",  "city": "深圳",  "academic": "本科",  "content": "ES",  "position": "ES2",  "work": "ES3"  }  }  ]  }}

4、小结

实践是检验真理的唯一标准！
如有不同意见，欢迎拍砖探讨！

——————————————————————————————————

作者：铭毅天下
转载请标明出处，原文地址：
http://blog.csdn.net/laoyang360/article/details/78556221