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
  },
  "title": {}
  }
  },
  "size": 20,
  "from": 100,
  "_source": [
  "title",
  "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、小结

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

——————————————————————————————————
更多ES相关实战干货经验分享，请扫描下方【铭毅天下】微信公众号二维码关注。
（每周至少更新一篇！）

和你一起，死磕Elasticsearch！
——————————————————————————————————

2017.11.16 22:39 于家中床前

作者：铭毅天下
转载请标明出处，原文地址：
http://blog.csdn.net/laoyang360/article/details/78556221
如果感觉本文对您有帮助，请点击‘顶’支持一下，您的支持是我坚持写作最大的动力，谢谢！