Elasticsearch Sql 查询

一、前言

  ElasticSearch 从 6.3.0 版本也开始支持 SQL 查询了。在早期版本中，Elasticsearch 执行 SQL 的 REST 接口为_xpack/sql，但在版本 7 以后这个接口已经被废止而推荐使用 _sql 接口。

  Elasticsearch 支持传统关系型数据库 SQL 语句中的查询语句，但并不支持DML、DCL 句。换句话说，它只支持 SELECT 语句，不支持 INSERT、UPDATE、DELETE语句。SELECT 语句以外，Elaticsarch 还支持 DESCRIBE 和 SHOW 语句。

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

二、SQL 查询测试

  创建一个索引 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"
}

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，困扰多年的问题终于解决了