本文原文：https://mp.weixin.qq.com/s/FFitBpi73PZoDWCq2D3PfA
ElasticSearch 6.3 已正式发布，其中带来了很多新特性，详情请参见：https://www.elastic.co/blog/elasticsearch-6-3-0-released。这个版本最大的亮点莫过于内置支持 SQL 模块！我在早些时间就说过 Elasticsearch 将会内置 SQL，参见：：ElasticSearch内置也将支持SQL特性。我们可以像操作 MySQL 一样使用 Elasticsearch，这样我们就可以减少 DSL 的学习成本，这个 SQL 模块是属于 X-Pack 的一部分。Elasticsearch SQL 主要有以下几个特点：
- 允许我们在 Elasticsearch 使用 SQL 查询其中的数据；
- 支持 REST、JDBC 以及命令行来下数据，任何客户端都可以使用 SQL 在 Elasticsearch 中本地搜索和聚合数据；
- 内部应该是将 SQL 翻译成 DSL 来查询数据的；
本文将简单介绍如何在 Elasticsearch 中使用 SQL。
安装
在使用之前，我们需要先安装 Elasticsearch 6.3 ，因为我这只是测试，所以安装过程非常简单。步骤如下：
iteblog$ wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.0.zip
iteblog$ wget https://artifacts.elastic.co/downloads/elasticsearch/elasticsearch-6.3.0.zip.sha512
iteblog$ shasum -a 512 -c elasticsearch-6.3.0.zip.sha512
iteblog$ unzip elasticsearch-6.3.0.zip
iteblog$ cd elasticsearch-6.3.0/
iteblog$ ./bin/elasticsearch
经过上面几步，我们就在服务器上简单地部署好了 Elasticsearch 6.3 。我们可以访问 ip:9200 页面来确定我们的 Elasticsearch 6.3 是否是正常运行：
es 正常运行
- 传入数据
在使用 Elasticsearch 之前，我们先通过下面命令往 Elasticsearch 导入一些数据：
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}
> '
返回结果：
{"took":719,"errors":false,"items":[{"index":{"_index":"library","_type":"book","_id":"Leviathan Wakes","_version":1,"result":"created","forced_refresh":true,"_shards":{"total":2,"successful":1,"failed":0},"_seq_no":0,"_primary_term":1,"status":201}},{"index":{"_index":"library","_type":"book","_id":"Hyperion","_version":1,"result":"created","forced_refresh":true,"_shards":{"total":2,"successful":1,"failed":0},"_seq_no":0,"_primary_term":1,"status":201}},{"index":{"_index":"library","_type":"book","_id":"Dune","_version":1,"result":"created","forced_refresh":true,"_shards":{"total":2,"successful":1,"failed":0},"_seq_no":1,"_primary_term":1,"status":201}}]}
- SQL REST API：
curl -X POST "www.iteblog.com:9200/_xpack/sql?format=txt" -H 'Content-Type: application/json' -d'
{
"query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}
'
返回结果：
author | name | page_count | release_date
----------------+---------------+---------------+------------------------
Frank Herbert |Dune |604 |1965-06-01T00:00:00.000Z
James S.A. Corey|Leviathan Wakes|561 |2011-06-02T00:00:00.000Z
Dan Simmons |Hyperion |482 |1989-05-26T00:00:00.000Z
上面通过 format=txt 指定以文本的形式返回结果，这种形式对我们人来说看起来很舒服，但是对机器来说很不友好，所以我们可以指定返回数据的格式：
curl -X POST "l-qdws2.tc.cn8:9200/_xpack/sql?format=json" -H 'Content-Type: application/json' -d'
{
"query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}
'
返回结果：
{
"columns": [
{
"name": "author",
"type": "text"
},
{
"name": "name",
"type": "text"
},
{
"name": "page_count",
"type": "long"
},
{
"name": "release_date",
"type": "date"
}
],
"rows": [
[
"Frank Herbert",
"Dune",
604,
"1965-06-01T00:00:00.000Z"
],
[
"James S.A. Corey",
"Leviathan Wakes",
561,
"2011-06-02T00:00:00.000Z"
],
[
"Dan Simmons",
"Hyperion",
482,
"1989-05-26T00:00:00.000Z"
]
]
}
其他的格式支持包括：yaml、smile、cbor、txt、csv、tsv 等等，我们可以通过 format 参数指定。
- SQL Translate API
Elasticsearch 提供了 SQL Translate API 接口，我们可以通过这个接口查看 Elasticsearch 如何将我们的 SQL 翻译成 DSL：
curl -X POST "l-qdws2.tc.cn8:9200/_xpack/sql/translate" -H 'Content-Type: application/json' -d'
{
"query": "SELECT * FROM library ORDER BY page_count DESC",
"fetch_size": 10
}
'
返回结果：
{
"size": 10,
"_source": {
"includes": [
"author",
"name"
],
"excludes": [ ]
},
"docvalue_fields": [
"page_count",
"release_date"
],
"sort": [
{
"page_count": {
"order": "desc"
}
}
]
}
- SQL CLI
Elasticsearch 还专门为我们提供了一个 CLI，我们可以通过下面的命令启动并查询数据：
./bin/elasticsearch-sql-cli l-qdws2.tc.cn8:9200
运行效果
- SQL JDBC
当然，我们还可以在程序里面通过 JDBC 连接 Elasticsearch 来查询里面的数据：
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());
}
关于 ElasticSearch SQL 的更多信息，请参见官方文档：https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html
