ElasticSearch是现在最流行的搜索引擎了，查询快，性能好。可能唯一的缺点就是查询的语法Query DSL（Domain Specific Language）比较难记，今天分享一个直接用sql查询ES的方法。
1.简介
先简单介绍一下这个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
2.准备环境
大家在ES官网下载一下ES 启动就可以了，注意的是ES 需要JDK环境，然后就是需要在6.3.0以上的版本。
https://www.elastic.co/cn/downloads/past-releases#elasticsearch
建议也下载一下kibana
我这边下载的是7.15.2版本
3.搞起
创建一个索引 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": "销售"
}
4.总结
ES SQL查询的优点还是很多的，值得学习。使用场景也很多
- 简单易学： ES SQL查询使用SQL语法，对于那些熟悉SQL语法的开发人员来说，学习ES SQL查询非常容易。
- 易于使用： ES SQL查询的语法简单，易于使用，尤其是对于那些不熟悉Query DSL语法的开发人员来说。
- 可读性强： ES SQL查询的语法结构清晰，易于阅读和理解。
5.最后附上相关链接
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
