ElasticsearchSQL的使用
针对ES中的结构化数据，使用SQL实现聚合统计会很方便，可以减少很多工作量。ES SQL支持常见的SQL语法，包括分组、排序、函数等，但是目前不支持JOIN。
在使用的时候可以使用SQL命令行、RestAPI、JDBC、ODBC等方式操作。本地测试的时候使用SQL命令行更加方便。想要实现跨语言调用使用RestAPI更加方便。Java程序员使用JDBC方式更方便。
1).ES SQL命令行下的使用：
[root@master ~]# cd $ES_HOME
[root@master elasticsearch]# bin/elasticsearch-sql-cli http://master:9200
WARNING: sun.reflect.Reflection.getCallerClass is not supported. This will impact performance.
asticElasticE
ElasticE sticEla
sticEl ticEl Elast
lasti Elasti tic
cEl ast icE
icE as cEl
icE as cEl
icEla las El
sticElasticElast icElas
las last ticElast
El asti asti stic
El asticEla Elas icE
El Elas cElasticE ticEl cE
Ela ticEl ticElasti cE
las astic last icE
sticElas asti stic
icEl sticElasticElast
icE sticE ticEla
icE sti cEla
icEl sti Ela
cEl sti cEl
Ela astic ticE
asti ElasticElasti
ticElasti lasticElas
ElasticElast
SQL
7.13.4
sql> select * from score;
name | score | subject
---------------+---------------+---------------
张三 |59 |chinese
王五 |68 |math
李四 |78 |chinese
张三 |89 |math
李四 |85 |math
王五 |97 |chinese
赵六 |77 |chinese
赵六 |84 |math
张飞 |89 |chinese
张飞 |90 |math
刘备 |87 |chinese
刘备 |84 |math
关羽 |77 |chinese
关羽 |94 |math
赵云 |97 |chinese
赵云 |95 |math
曹操 |77 |chinese
曹操 |74 |math
诸葛亮 |99 |chinese
诸葛亮 |100 |math
周瑜 |78 |chinese
周瑜 |72 |math
#查询数学成绩高于90分的学生。
sql> select * from score where score>90 and subject='math';
name | score | subject
---------------+---------------+---------------
关羽 |94 |math
赵云 |95 |math
诸葛亮 |100 |math
#如果想要实现模糊查询，使用sql中的like是否可行？
sql> select * from score where name like '张%';
name | score | subject
---------------+---------------+---------------
张三 |59 |chinese
张三 |89 |math
张飞 |89 |chinese
张飞 |90 |math
#like这种方式其实就是普通的查询了，无法实现分词查询。
#想要实现分词查询，需要使用match。
sql> select * from score where match(name,'张三丰');
name | score | subject
---------------+---------------+---------------
张三 |59 |chinese
张三 |89 |math
张飞 |89 |chinese
张飞 |90 |math
退出ES SQL命令行，需要输入exit；
sql> exit;
Bye!
2).RestAPI下ES SQL的使用。
查询score索引库中的数据的数学成绩，根据score倒序排序，获取前5条数据。
我们可以在es-client下执行ES SQL。
POST /_xpack/sql?format=txt
{
"query": "SELECT * FROM score where subject='math' order by score desc limit 5"
}
运行结果：
name | score | subject
---------------+---------------+---------------
诸葛亮 |100 |math
赵云 |95 |math
关羽 |94 |math
张飞 |90 |math
张三 |89 |math
3).JDBC操作ES SQL。
首先添加ES sql-jdbc的依赖。
<properties>
...
<elasticsearch.version>7.13.4</elasticsearch.version>
</properties>
<dependencies>
...
<dependency>
<groupId>org.elasticsearch.plugin</groupId>
<artifactId>x-pack-sql-jdbc</artifactId>
<version>${elasticsearch.version}</version>
</dependency>
</dependencies>
java代码实现：
查询score索引库中的数据的数学成绩，根据score倒序排序，获取前5条数据。
package com.simoniu.db_elasticsearch.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
/**
* JDBC操作ES SQL
* Created by simoniu
*/
public class EsJdbcDemo {
public static void main(String[] args) throws Exception{
//指定jdbcUrl
String jdbcUrl = "jdbc:es://http://master:9200/?timezone=UTC+8";
Properties properties = new Properties();
//获取JDBC连接
Connection conn = DriverManager.getConnection(jdbcUrl, properties);
Statement stmt = conn.createStatement();
ResultSet results = stmt.executeQuery("select name,score,subject from score where subject='math' order by score desc limit 5");
while (results.next()){
String name = results.getString(1);
int score = results.getInt(2);
String subject = results.getString(3);
System.out.println(name+","+score+","+subject);
}
//关闭连接
stmt.close();
conn.close();
}
}
运行结果：
Exception in thread "main" java.sql.SQLInvalidAuthorizationSpecException: current license is non-compliant for [jdbc]
注意：jdbc这种方式目前无法免费使用，需要购买授权。所以在工作中常用的是RestAPI这种方式。