elasticsearch SQL：在Elasticsearch中启用和使用SQL功能

本文由简悦 SimpRead 转码，原文地址 blog.csdn.net

❃博主首页： 「码到三十五」，同名公众号 :「码到三十五」，wx 号 : 「liwu0213」
☠博主专栏： <mysql 高手> <elasticsearch 高手> <源码解读> <java 核心> <面试攻关>
♝博主的话： 搬的每块砖，皆为峰峦之基；公众号搜索「码到三十五」关注这个爱发技术干货的 coder，一起筑基

- - 一、基本概念
  - 二、主要功能和优势
  - - Elasticsearch SQL 特点
  - 三、启用和使用 SQL 功能
  - 四、Elasticsearch SQL 的使用
  - - 4.1 语法
    - 4.2 sql 查询
    - 4.3 将 SQL 转换为 DSL
    - 4.4 全文检索
    - - MATCH 函数
      - QUERY()
    - 4.5 分组统计
    - 4.6 DESCRIBE
    - 4.7 SHOW TABLES
    - 4.8 查询支持的函数
  - 五、适用场景及潜在限制

一、基本概念

Elasticsearch 是一个基于 Lucene 的开源、分布式、RESTful 搜索引擎。它提供了全文搜索、结构化搜索、分析以及分布式索引等功能。Elasticsearch SQL 是 Elasticsearch 的扩展功能，允许用户使用 SQL 语法查询 Elasticsearch 数据。通过 SQL 接口，开发者可以利用熟悉的 SQL 语言，编写更直观、更易懂的查询，并且避免对大量复杂的原生 REST 请求的编写。

[外链图片转存失败, 源站可能有防盗链机制, 建议将图片保存下来直接上传 (img-EupnvsbF-1721093861434)(https://i-blog.csdnimg.cn/direct/9e79724515584560a3ec575d33f54904.jpeg#pic_center)]

二、主要功能和优势

易用性：使用熟悉的 SQL 语法，降低了学习成本。
灵活性：支持复杂的查询和聚合操作。
性能：Elasticsearch 本身的分布式架构和高效查询引擎保证了查询性能。
集成性：通过 JDBC 驱动，可以与各种 SQL 工具和应用程序集成。

Elasticsearch SQL 特点

1. 本地集成
Elasticsearch SQL 是专门为 Elasticsearch 构建的。每个 SQL 查询都根据底层存储对相关节点有效执行。
2. 没有额外的要求
不依赖其他的硬件、进程、运行时库，Elasticsearch SQL 可以直接运行在 Elasticsearch 集群上
3. 轻量且高效
像 SQL 那样简洁、高效地完成查询

三、启用和使用 SQL 功能

要在 Elasticsearch 中启用和使用 SQL 功能，你需要安装 X-Pack 插件。X-Pack 插件包含了许多扩展功能，包括 SQL 接口。安装完成后，需要在 Elasticsearch 配置文件中启用 X-Pack 插件，并重启 Elasticsearch 服务。

# 安装X-Pack插件
./bin/elasticsearch-plugin install x-pack

# 启用X-Pack插件
# 在elasticsearch.yml配置文件中添加以下配置
xpack.sql.enabled: true

在启用 SQL 功能后，你可以通过 REST API、命令行工具或 JDBC 驱动来执行 SQL 查询。Elasticsearch SQL 的语法与标准的 SQL 语法非常相似，支持 SELECT、FROM、WHERE、GROUP BY、HAVING、ORDER BY 等常见 SQL 语句。

四、Elasticsearch SQL 的使用

4.1 语法

SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

[外链图片转存失败, 源站可能有防盗链机制, 建议将图片保存下来直接上传 (img-ZN8DC9ek-1721093861436)(https://i-blog.csdnimg.cn/direct/9dd378cca9294abcb6cecd3b49c3114a.png)]
目前 FROM 只支持单表

4.2 sql 查询

# 使用curl命令查询
curl -X GET "localhost:9200/_sql?format=txt" -H 'Content-Type: application/json' -d'
{
  "query": "SELECT * FROM indexName WHERE age > 30"
}'

format=txt 是指查询返回结果的数据格式

[外链图片转存失败, 源站可能有防盗链机制, 建议将图片保存下来直接上传 (img-i4AKhlnx-1721093861437)(https://i-blog.csdnimg.cn/direct/287895433292497ca6392411edf7ddff.png)]

4.3 将 SQL 转换为 DSL

GET /_sql/translate
{
"query":"SELECT * FROM es_order limit 1"
}

结果如下：

{
  "size" : 1,
  "_source" : {
    "includes" : [
      "id",
      "orderNo",
      "orderTime"
    ],
    "excludes" : [ ]
  },
  "docvalue_fields" : [
    {
      "field" : "id"
    },
    {
      "field" : "orderNo"
    },
    {
      "field" : "orderTime"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

4.4 全文检索

当使用 MATCH 或 QUERY 函数时，会启用全文搜索功能，SCORE 函数可以用来统计搜索评分。

MATCH 函数

MATCH(
    field_exp,   
    constant_exp 
    [, options]) 
field_exp：匹配字段
constant_exp：匹配常量表达式

用法:

GET /_sql?format=txt
{
"query":"select * from es_order where MATCH(address, '武汉') or MATCH(productType, '手机') limit 10"
}

QUERY()

使用 QUERY 函数查询 address 中包含 Street 的记录。

POST /_sql?format=txt
{
"query":"select id,orderNo,name,address,SCORE()  from es_order where QUERY('address: 武汉') limit 10"
}

4.5 分组统计

GET /_sql?format=txt
{
"query":"select city, count(*) as age_cnt from es_order group by city"
}

这种方式要更加直观、简洁。

HAVING
我们可以使用 HAVING 语句对分组数据进行二次筛选，比如筛选分组记录数量大于 1000 的信息，查询语句如下。

POST /_sql?format=txt
{
 "query":"select city, count(*) as age_cnt from es_order group by city having count(*) > 1000"
}

ORDER BY
使用 ORDER BY 语句对数据进行排序，比如按照统计字段从高到低排序，查询语句如下。

POST /_sql?format=txt
{
 "query":"select city, count(*) cc as age_cnt from es_order group by city having count(*) > 1000 order by cc "
}

注意: 目前 Elasticsearch SQL 还存在一些限制。例如：不支持 JOIN、不支持较复杂的子查询。所以，有一些相对复杂一些的功能，还得借助于 DSL 方式来实现

4.6 DESCRIBE

使用 DESCRIBE 语句查看索引中有哪些字段，比如查看 es_order 索引的字段，查询语句如下。

POST /_sql?format=txt
{
  "query": "DESCRIBE es_order"
}

4.7 SHOW TABLES

使用 SHOW TABLES 查看所有的索引

POST /_sql?format=txt
{
  "query": "SHOW TABLES"
}

4.8 查询支持的函数

使用 SQL 查询 ES 中的数据，不仅可以使用一些 SQL 中的函数，还可以使用一些 ES 中特有的函数。SHOW FUNCTIONS 语句查看所有支持的函数，比如搜索所有带有 DATE 字段的函数可以使用如下语句。

POST /_sql?format=txt
{
  "query": "SHOW FUNCTIONS LIKE '%DATE%'"
}

[外链图片转存失败, 源站可能有防盗链机制, 建议将图片保存下来直接上传 (img-eC2dU8qP-1721093861439)(https://i-blog.csdnimg.cn/direct/eed4483e4e4f4e43bdb5709b7d35b178.png)]

五、适用场景及潜在限制

Elasticsearch SQL 适用于需要对大量数据进行复杂查询的场景，如数据分析、报表生成、数据探索等。然而，由于 SQL 查询的复杂性，它可能不适用于所有场景。例如，对于需要高并发、低延迟的场景，原生 REST 查询可能更合适。

此外，虽然 Elasticsearch SQL 提供了 SQL 接口，但它并不是完全兼容 SQL。例如，它不支持所有的 SQL 函数和特性。因此，在使用 Elasticsearch SQL 时，需要了解它的限制，并根据实际情况选择使用。

总结来说，Elasticsearch SQL 提供了一种直观、易用的方式查询 Elasticsearch 数据。它允许开发者利用熟悉的 SQL 语言，编写更直观、更易懂的查询，并避免对大量复杂的原生 REST 请求的编写。然而，它的适用场景和性能特点需要在实际使用中仔细考虑。

关注公众号获取更多技术干货 !