欢迎关注Hadoop、Spark、Flink、Hive、Hbase、Flume等大数据资料分享微信公共账号:iteblog_hadoop
  1. 文章总数:960
  2. 浏览总数:11,441,264
  3. 评论:3867
  4. 分类目录:102 个
  5. 注册用户数:5823
  6. 最后更新:2018年10月13日
过往记忆博客公众号iteblog_hadoop
欢迎关注微信公众号:
iteblog_hadoop
大数据技术博客公众号bigdata_ai
大数据猿:
bigdata_ai

Elasticsearch 6.3 发布,你们要的 SQL 功能来了

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

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
Elasticsearch SQL CLI
如果想及时了解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

本博客文章除特别声明,全部都是原创!
转载本文请加上:转载自过往记忆(https://www.iteblog.com/)
本文链接: 【Elasticsearch 6.3 发布,你们要的 SQL 功能来了】(https://www.iteblog.com/archives/2378.html)
喜欢 (7)
分享 (0)
发表我的评论
取消评论

表情
本博客评论系统带有自动识别垃圾评论功能,请写一些有意义的评论,谢谢!
(3)个小伙伴在吐槽
  1. 请问 这个sql jdbc是怎么操作的,找不到es的driver
    李国冬(wintfru)2018-06-15 18:58 回复
    • 请参考 https://www.elastic.co/guide/en/elasticsearch/reference/master/sql-jdbc.html
      ChenShuai19812018-06-17 15:33 回复
  2. 厉害啊,现在什么东西不搞个SQL都不好意思呢
    恩要哦是搞咯2018-06-15 13:46 回复