初探elasticsearch SQL

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

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

一、基本查询

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

curl -XPUT"www.iteblog.com:9200/library/book/_bulkrefresh" -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”

]

]

}

另外，elasticsearchsql目前只支持以下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了）

三、SQLCLI

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

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

四：JDBC

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

String address = “jdbc🇪🇸//” + elasticsearchAddress;
Properties connectionProperties = connectionProperties();
Connection connection = DriverManager.getConnection(address, connectionProperties);
try (Statement statement = connection.createStatement();
ResultSet results = statement.executeQuery(
“SELECTname, page_count FROMlibraryORDERBYpage_count DESCLIMIT1”)) {
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 toconvertcolumn1toan 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 查看