Elastic Stack：es7 sql新特性

一.入门

查询所有

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

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

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

sql翻译：

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

 返回：

与DSL混用：

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

 二.elasticsearch-sql-cli

进入elasticsearch的安装目录

cd bin
./elasticsearch-sql-cli

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

三.java代码实现sql功能

在kibana中开启白金版试用

 

加入相关功能的依赖：

    <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> 　

  测试：

        //创建连接
        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("--------");
        }