像mysql一样查询ES,一看就会，爽歪歪

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"
}

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

分页limit

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

和mysql 一样没啥，很简单。

order by 排序

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

group by 分组

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

SUM 求和

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

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": "销售"
        }