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
如果感觉本文对您有帮助,请点击‘顶’支持一下,您的支持是我坚持写作最大的动力,谢谢!

©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页