初探elasticsearch SQL

前几节我们主要是在windows 10环境下搭建了docker，并在docker里创建了es的容器，这节我们就要开始应用容器去做一些探索。

elasticsearch在6.3版本中发布了一个es开发者万众期待的功能：elasticsearch sql，该功能可以让对于es dsl不熟悉的开发者，可以更加方便的去使用es，今天主要带大家一起看看这个功能~

 

一、基本查询

查询之前，我们先添加一些数据进到es：

curl -X PUT "www.iteblog.com:9200/library/book/_bulk?refresh" -H 'Content-Type: application/json' -d'

{"index":{"_id": "Leviathan Wakes"}}
{"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561}
{"index":{"_id": "Hyperion"}}
{"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482}
{"index":{"_id": "Dune"}}
{"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}'

 

Es中已经可以看到这些数据：

由于我的电脑里没有装curl工具，我选择的是postman来传输数据，切记，传输格式选择JSON(application/json).

我们先来试试最基本的查询：

Post http://192.168.99.100:9200/_xpack/sql?format=txt

{

    "query": "SELECT * FROM library WHERE release_date < '2000-01-01'"

}

如图：

返回数据：

上边的例子中我们的返回值是txt，同样，我们也可以指定返回数据为json格式：

Post   http://192.168.99.100:9200/_xpack/sql?format=json

{

    "query": "SELECT * FROM library"

}

返回值：

{

    "columns": [

        {

            "name": "author",

            "type": "text"

        },

        {

            "name": "name",

            "type": "text"

        },

        {

            "name": "page_count",

            "type": "long"

        },

        {

            "name": "release_date",

            "type": "date"

        }

    ],

    "rows": [

        [

            "Dan Simmons",

            "Hyperion",

            482,

            "1989-05-26T00:00:00.000Z"

        ],

        [

            "James S.A. Corey",

            "Leviathan Wakes",

            561,

            "2011-06-02T00:00:00.000Z"

        ],

        [

            "Frank Herbert",

            "Dune",

            604,

            "1965-06-01T00:00:00.000Z"

        ]

    ]

}

另外，elasticsearch sql目前只支持以下sql命令：

另外，常见的聚合函数，如：AVG, Count, min, max 等等也都支持。

更多的sql 函数，sql功能可以访问官方doc查询：https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html

 

二、 Translate API

如果你想要达到一个查询效果但是不会写DSL，你可以使用es的SQL Translate Api，将sql转换成DSL。

Post  http://192.168.99.100:9200/_xpack/sql/translate

{

    "query": "SELECT * FROM library where match('author', 'dan')",

    "fetch_size": 10

}

返回：

这样DSL就可以直接拿来用了（这个功能还是挺有用的，不用专门去学习DSL了）

 

三、SQL CLI

ElasticSearch 还为我们提供了一个 CLI，我们可以通过下面命令启动并查询数据：

./bin/elasticsearch-sql-cli ip:9200

 

四：JDBC

es也提供了jdbc的驱动用于java中的es sql：

String address = "jdbc:es://" + elasticsearchAddress;    
Properties connectionProperties = connectionProperties();
Connection connection = DriverManager.getConnection(address, connectionProperties);
try (Statement statement = connection.createStatement();
       ResultSet results = statement.executeQuery(
           "SELECT name, page_count FROM library ORDER BY page_count DESC LIMIT 1")) {
   assertTrue(results.next());
   assertEquals("Don Quixote", results.getString(1));
   assertEquals(1072, results.getInt(2));
   SQLException e = expectThrows(SQLException.class, () -> results.getInt(1));
   assertTrue(e.getMessage(), e.getMessage().contains("unable to convert column 1 to an int"));
   assertFalse(results.next());
}

（收费的，所以这段是从网上抄的，这里建议，可以用SQL Translate Api把sql转换成DSL《这个方法只是弯道超车，不用刻意去了解es的dsl语句和各种复杂的语句，也可以不用花钱就用上es sql。。。》）

转换过的dsl，存为字符串，利用如下api，进行搜索。

同时，我们也可以搭配上es常用函数：如：match  如果是term，fieldName=“XXX”就可以了

 

至此，es sql已经基本上介绍完了，更加详细的api和文档请访问：

https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html  查看