乐趣区

关于java:Elasticsearch官方已支持SQL查询用起来贼方便

jiezi

平时应用 Elasticsearch 的时候，偶然会在 Kibana 中应用 Query DSL 来查问数据。每次要用到 Query DSL 时都根本忘光了，须要从新在回顾一遍，最近发现 Elasticsearch 曾经反对 SQL 查问了（6.3 版本当前），整顿了下其用法，心愿对大家有所帮忙！

SpringBoot 实战电商我的项目 mall（40k+star）地址：https://github.com/macrozheng/mall

简介

Elasticsearch SQL 是一个 X -Pack 组件，它容许针对 Elasticsearch 实时执行相似 SQL 的查问。无论应用 REST 接口，命令行还是 JDBC，任何客户端都能够应用 SQL 对 Elasticsearch 中的数据进行原生搜寻和聚合数据。能够将 Elasticsearch SQL 看作是一种翻译器，它能够将 SQL 翻译成 Query DSL。

Elasticsearch SQL 具备如下个性：

  • 原生反对：Elasticsearch SQL 是专门为 Elasticsearch 打造的。
  • 没有额定的整机：无需其余硬件，处理器，运行环境或依赖库即可查问 Elasticsearch，Elasticsearch SQL 间接在 Elasticsearch 外部运行。
  • 笨重高效：Elasticsearch SQL 并未抽象化其搜寻性能，相同的它拥抱并承受了 SQL 来实现全文搜寻，以简洁的形式实时运行全文搜寻。

学前筹备

学习之前咱们须要先对 Elasticsearch 有所理解，并装置好 Elasticsearch 和 Kibana，这里装置的是 7.6.2 版本，具体能够参考《Elasticsearch 疾速入门，把握这些刚刚好！》。

装置实现后在 Kibana 中导入测试数据，数据地址：https://github.com/macrozheng…

间接在 Kibana 的 Dev Tools 中运行如下命令即可：

第一个 SQL 查问

咱们应用 SQL 来查问下前 10 条记录，能够通过 format 参数管制返回后果的格局，txt 示意文本格式，看起来更直观点，默认为 json 格局。

在 Kibana 的 Console 中输出如下命令：

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance FROM account LIMIT 10"}

查问结果显示如下。

将 SQL 转化为 DSL

当咱们须要应用 Query DSL 时，也能够先应用 SQL 来查问，而后通过 Translate API 转换即可。

例如咱们翻译以下查问语句：

POST /_sql/translate
{"query": "SELECT account_number,address,age,balance FROM account WHERE age>32 LIMIT 10"}

最终获取到 Query DSL 后果如下。

SQL 和 DSL 混合应用

咱们还能够将 SQL 和 Query DSL 混合应用，比方应用 Query DSL 来设置过滤条件。

例如查问 age 在 30-35 之间的记录，能够应用如下查问语句：

POST /_sql?format=txt
{
  "query": "SELECT account_number,address,age,balance FROM account",
      "filter": {
        "range": {
            "age": {
                "gte" : 30,
                "lte" : 35
            }
        }
    },
    "fetch_size": 10
}

查问后果展现如下：

SQL 和 ES 对应关系

SQL ES 形容
column field 数据库中表的字段与 ES 中的属性对应
row document 数据库表中的行记录与 ES 中的文档对应
table index 数据库中的表与 ES 中的索引对应

罕用 SQL 操作

语法

在 ES 中应用 SQL 查问的语法与在数据库中应用基本一致，具体格局如下：

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 ] [, ...] ) ) ]

WHERE

能够应用 WHERE 语句设置查问条件，比方查问 state 字段为 VA 的记录，查问语句如下。

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,state FROM account WHERE state='VA'LIMIT 10"}

查问后果展现如下：

GROUP BY

咱们能够应用 GROUP BY 语句对数据进行分组，统计出分组记录数量，最大 age 和均匀 balance 等信息，查问语句如下。

POST /_sql?format=txt
{"query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state LIMIT 10"
}

HAVING

咱们能够应用 HAVING 语句对分组数据进行二次筛选，比方筛选分组记录数量大于 15 的信息，查问语句如下。

POST /_sql?format=txt
{"query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state HAVING COUNT(*)>15 LIMIT 10"
}

ORDER BY

咱们能够应用 ORDER BY 语句对数据进行排序，比方依照 balance 字段从高到低排序，查问语句如下。

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,state FROM account ORDER BY balance DESC LIMIT 10"}

DESCRIBE

咱们能够应用 DESCRIBE 语句查看表（ES 中为索引）中有哪些字段，比方查看 account 表的字段，查问语句如下。

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

SHOW TABLES

咱们能够应用 SHOW TABLES 查看所有的表（ES 中为索引）。

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

反对的函数

应用 SQL 查问 ES 中的数据，不仅能够应用一些 SQL 中的函数，还能够应用一些 ES 中特有的函数。

查问反对的函数

咱们能够应用 SHOW FUNCTIONS 语句查看所有反对的函数，比方搜寻所有带有 DATE 字段的函数能够应用如下语句。

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

全文搜寻函数

全文搜寻函数是 ES 中特有的，当应用 MATCH 或QUERY函数时，会启用全文搜寻性能，SCORE函数能够用来统计搜寻评分。

MATCH()

应用 MATCH 函数查问 address 中蕴含 Street 的记录。

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE MATCH(address,'Street') LIMIT 10"
}

QUERY()

应用 QUERY 函数查问 address 中蕴含 Street 的记录。

POST /_sql?format=txt
{"query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE QUERY('address:Street') LIMIT 10"
}

SQL CLI

如果你不想应用 Kibana 来应用 ES SQL 的话，也能够应用 ES 自带的 SQL CLI 来查问，该命令位于 ES 的 bin 目录下。

应用如下命令启动 SQL CLI：

elasticsearch-sql-cli http://localhost:9200

而后间接输出 SQL 命令即可查问了，留神要加分号。

SELECT account_number,address,age,balance FROM account LIMIT 10;

局限性

应用 SQL 查问 ES 有肯定的局限性，没有原生的 Query DSL 那么弱小，对于嵌套属性和某些函数的反对并不怎么好，然而平时用来查问下数据根本够用了。

参考资料

官网文档：https://www.elastic.co/guide/…

本文 GitHub https://github.com/macrozheng/mall-learning 曾经收录，欢送大家 Star！

退出移动版