平时应用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！