像MySQL一样查询ES，困扰多年的问题终于解决了……

小羊架构 2024-11-13 11:10:32

 
 

ElasticSearch是现在最流行的搜索引擎了，查询快，性能好。可能唯一的缺点就是查询的语法Query DSL（Domain Specific Language）比较难记，今天分享一个直接用sql查询ES的方法。

 
 

 

一、简介

 

先简单介绍一下这个sql查询，因为社区一直反馈这个Query DSL 实在是太难用了。大家可以感受一下下面这个es的查询。

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
GET /my_index/_search{  "query": {    "bool": {      "must": [        { "match": { "title": "search" } },        {          "bool": {            "should": [              { "term": { "category": "books" } },              { "term": { "category": "music" } }            ]          }        }      ],      "filter": {        "range": {          "price": { "gte": 20, "lte": 100 }        }      }    }  },  "aggs": {    "avg_price_per_category": {      "terms": {        "field": "category",        "size": 10      },      "aggs": {        "avg_price": {          "avg": {            "field": "price"          }        }      }    }  }}

 

这个查询使用了bool查询来组合多个条件，包括must、should和filter。同时也包含了聚合(aggs)来计算不同类别的平均价格。对于业务查询来讲，这个查询很普通。但是还是很难理解，特别是对于新手来讲，更难记了，很容易出错。

 

如果是mysql的查询，就是这么写

 

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
SELECT title, category, price FROM my_index WHERE (title = 'search' AND (category = 'books' OR category = 'music')) AND price >= 20 AND price <= 100 GROUP BY category ORDER BY AVG(price) DESC LIMIT 10

 

 

mysql 的查询就很简洁明了，看起来更舒服，后续维护也更方便。

 

既然都是查询，为啥不兼容一下mysql的语法呢，像很多工具现在都是兼容mysql的语法，比如说hive,starrocks，flink等等，原因就是因为mysql的用户多，社区活跃。还有一个原因就是因为mysql的语法比较简单，容易理解。所以ElasticSearch 官方ElasticSearch 从 6.3.0 版本也开始支持 SQL 查询了，这就是一个喜大奔普的事情了，哈哈。

 

下面是官方的文档和介绍，大家可以看看 https://www.elastic.co/guide/en/elasticsearch/reference/8.9/sql-overview.html

 

 

二、准备环境

 

大家在ES官网下载一下ES 启动就可以了，注意的是ES 需要JDK环境，然后就是需要在6.3.0以上的版本。https://www.elastic.co/cn/downloads/past-releases#elasticsearch

 

 

建议也下载一下kibana

 

 

我这边下载的是7.15.2版本

 

三、搞起

 

创建一个索引 my_index

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
PUT /my_index{  "mappings": {    "properties": {      "title": { "type": "text" },      "category": { "type": "keyword" },      "price": { "type": "float" }    }  }}

插入一些数据

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
POST /my_index/_doc/1{  "title": "ES学习手册",  "category": "books",  "price": 29.99}
POST /my_index/_doc/2{  "title": "on my way",  "category": "music",  "price": 13.57}
POST /my_index/_doc/3{  "title": "Kibana中文笔记",  "category": "books",  "price": 21.54}

传统的查询所有

 

  •  
  •  
  •  
GET /my_index/_search{}

返回的是文档的格式

 

 

如果用sql 查询

 

  •  
  •  
  •  
  •  
POST /_sql?format=txt{  "query": "SELECT * FROM my_index"}

 

返回的是类似数据库的表格形式，是不是写起来更舒服呢。

 

 
1、分页limit

 

  •  
  •  
  •  
  •  
POST /_sql?format=txt{  "query": "SELECT * FROM my_index limit 1"}

 

 

和mysql 一样没啥，很简单。

 

 
2、order by 排序

 

 

  •  
  •  
  •  
  •  
POST /_sql?format=txt{  "query": "SELECT * FROM my_index order by price desc"}

 

 
3、group by 分组

 

  •  
  •  
  •  
  •  
POST /_sql?format=txt{  "query": "SELECT category,count(1) FROM my_index group by category"}

 

 

 
4、SUM 求和

 

  •  
  •  
  •  
  •  
POST /_sql?format=txt{  "query": "SELECT sum(price) FROM my_index"}

 

 
5、where

 

  •  
  •  
  •  
  •  
  •  
POST /_sql?format=txt
{  "query": "SELECT * FROM my_index where price = '13.57'"}

 

看看是不是支持时间的转换的处理，插入一些数据

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
POST /my_index/_doc/4{  "title": "JAVA编程思想",  "category": "books",  "price": 21.54,  "create_date":"2023-11-18T12:00:00.123"}
POST /my_index/_doc/5{  "title": "Mysql操作手册",  "category": "books",  "price": 21.54,  "create_date":"2023-11-17T07:00:00.123"}

时间转换为 yyyy-mm-dd 格式

 

  •  
  •  
POST /_sql?format=txt{"query": "SELECT title, DATETIME_FORMAT(create_date, 'YYYY-MM-dd') date from my_index where category= 'books'" }

 

时间加减

 

  •  
  •  
POST /_sql?format=txt{"query": "SELECT date_add('hour', 8,create_date) date from my_index where category= 'books'" }

 

字符串拆分

 

  •  
  •  
  •  
  •  
POST /_sql?format=txt{  "query": "SELECT SUBSTRING(category, 1, 3) AS SubstringValue FROM my_index"}

 

基本上mysql 能查的 es sql 也能查，以后查询ES 数据就很方便的，特别是对于做各种报表的查询。像这样。

 

 

一般对于这种报表，返回的数据都是差不多json数组的格式。而对于es sql,查询起来很方便

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
[        {            "data": "5",            "axis": "总数"        },        {            "data": "0",            "axis": "待出库"        },        {            "data": "0",            "axis": "配送中"        },        {            "data": "5",            "axis": "已签收"        },        {            "data": "0",            "axis": "交易完成"        },        {            "data": "0",            "axis": "已取消"        },        {            "data": "5",            "axis": "销售"        }

 

 

四、总结

 

ES SQL查询的优点还是很多的，值得学习，使用场景也很多。

 

  • 简单易学：ES SQL查询使用SQL语法，对于那些熟悉SQL语法的开发人员来说，学习ES SQL查询非常容易。

  • 易于使用：ES SQL查询的语法简单，易于使用，尤其是对于那些不熟悉Query DSL语法的开发人员来说。

  • 可读性强：ES SQL查询的语法结构清晰，易于阅读和理解。

 

 

>>>>

最后附上相关链接

 

  • ES 官方下载

    https://www.elastic.co/cn/downloads/past-releases#elasticsearch

  • ES sql文档

    https://www.elastic.co/guide/en/elasticsearch/reference/8.9/sql-overview.html

 

作者丨小羊架构
来源丨公众号：小羊架构（ID：yz18500553935）
dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn
热门文章
最新评论
访客 2024年04月08日

如果字段的最大可能长度超过255字节，那么长度值可能…

访客 2024年03月04日

只能说作者太用心了，优秀

访客 2024年02月23日

感谢详解

访客 2024年02月20日

一般干个7-8年（即30岁左右），能做到年入40w-50w；有…

访客 2023年08月20日

230721

活动预告
Copyright © 广州新炬网络技术有限公司 All rights reserved. 粤ICP备2024180830号-3