个人专题目录

1. es7 sql新特性

1.1 快速入门

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

1.2 启动方式

• http 请求

• 客户端：elasticsearch-sql-cli.bat

1.3 显示方式

1.4 sql 翻译

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

返回：

{
  "size" : 1000,
  "_source" : false,
  "stored_fields" : "_none_",
  "docvalue_fields" : [
    {
      "field" : "brand"
    },
    {
      "field" : "color"
    },
    {
      "field" : "price"
    },
    {
      "field" : "sold_date",
      "format" : "epoch_millis"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

1.5 与其他DSL结合

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

1.6 java 代码实现sql功能

前提 es拥有白金版功能

kibana中管理-》许可管理 开启白金版试用

导入依赖

    <dependency>
        <groupId>org.elasticsearch.plugin</groupId>
        <artifactId>x-pack-sql-jdbc</artifactId>
        <version>7.3.0</version>
    </dependency>
    
    <repositories>
        <repository>
            <id>elastic.co</id>
            <url>https://artifacts.elastic.co/maven</url>
        </repository>
    </repositories>

代码

public static void main(String[] args) {
        try  {
            Connection connection = DriverManager.getConnection("jdbc:es://http://localhost:9200");
            Statement statement = connection.createStatement();
            ResultSet results = statement.executeQuery(
                    "select * from tvs");
            while(results.next()){
                System.out.println(results.getString(1));
                System.out.println(results.getString(2));
                System.out.println(results.getString(3));
                System.out.println(results.getString(4));
                System.out.println("============================");
            }
        }catch (Exception e){
            e.printStackTrace();
        }

大型企业可以购买白金版，增加Machine Learning、高级安全性x-pack。