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 是否正常运行：

如果想及时了解Spark、Hadoop或者Hbase相关的文章，欢迎关注微信公共帐号：iteblog_hadoop

在使用 Elasticsearch SQL 之前，我们先通过下面命令往 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 "www.iteblog.com: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 "www.iteblog.com: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 www.iteblog.com:9200

如果想及时了解Spark、Hadoop或者Hbase相关的文章，欢迎关注微信公共帐号：iteblog_hadoop

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 的更多信息，请参见官方文档：SQL Access