Elastic Stack：es7 sql新特性

一.入门

查询所有

?

1 2 3 4

POST /_sql?format=txt {     "query": "SELECT * FROM tvs " }

 按颜色分组，查询价格的最大值、最小值、总和、平均值

?

1 2 3 4

POST /_sql?format=txt {   "query": "select color,sum(price),min(price),max(price),avg(price) from tvs group by color" }

sql翻译：

?

1 2 3 4

POST /_sql/translate {     "query": "SELECT * FROM tvs " }

 返回：

与DSL混用：

?

1 2 3 4 5 6 7 8 9 10 11 12

POST /_sql?format=txt {     "query": "SELECT * FROM tvs",     "filter": {         "range": {             "price": {                 "gte" : 1200,                 "lte" : 2000             }         }     } }

 二.elasticsearch-sql-cli

进入elasticsearch的安装目录

?

1 2	cd bin ./elasticsearch-sql-cli

 然后再sql命令行可以执行sql语句

三.java代码实现sql功能

在kibana中开启白金版试用

 

加入相关功能的依赖：

?

1 2 3 4 5 6 7 8 9 10 11 12

<dependency>     <groupId>org.elasticsearch.plugin</groupId>     <artifactId>x-pack-sql-jdbc</artifactId>     <version>7.3.0</version> </dependency>   <repositories><!-- 如果jar包下载不下来，就需要在pom文件中配置一下仓库 -->     <repository>         <id>elastic.co</id>         <url>https://artifacts.elastic.co/maven</url>     </repository> </repositories>

  测试：

?

1 2 3 4 5 6 7 8 9 10 11 12 13

//创建连接 Connection connection = DriverManager.getConnection("jdbc:es://http://192.168.10.138:9200"); //创建statement Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("select * from tvs"); //获取结果 while (resultSet.next()){     System.out.println(resultSet.getString(1));     System.out.println(resultSet.getString(2));     System.out.println(resultSet.getString(3));     System.out.println(resultSet.getString(4));     System.out.println("--------"); }