绊脚石乃是进身之阶。

玩转 Elasticsearch 的 SQL 功能

Elasticsearch | 作者 medcl | 发布于2018年06月27日 | | 阅读数：75196
分享到：QQ空间新浪微博微信QQ好友印象笔记有道云笔记

最近发布的 Elasticsearch 6.3 包含了大家期待已久的 SQL 特性，今天给大家介绍一下具体的使用方法。

首先看看接口的支持情况

目前支持的 SQL 只能进行数据的查询只读操作，不能进行数据的修改，所以我们的数据插入还是要走之前的常规索引接口。

目前 Elasticsearch 的支持 SQL 命令只有以下几个：

命令 说明
DESC table 用来描述索引的字段属性
SHOW COLUMNS 功能同上，只是别名
SHOW FUNCTIONS 列出支持的函数列表，支持通配符过滤
SHOW TABLES 返回索引列表
SELECT .. FROM table_name WHERE .. GROUP BY .. HAVING .. ORDER BY .. LIMIT .. 用来执行查询的命令

我们分别来看一下各自怎么用，以及有什么效果吧，自己也可以动手试一下，看看。

首先，我们创建一条数据：

POST twitter/doc/
{
  "name":"medcl",
  "twitter":"sql is awesome",
  "date":"2018-07-27",
  "id":123
}

RESTful下调用SQL

在 ES 里面执行 SQL 语句，有三种方式，第一种是 RESTful 方式，第二种是 SQL-CLI 命令行工具，第三种是通过 JDBC 来连接 ES，执行的 SQL 语句其实都一样，我们先以 RESTful 方式来说明用法。

RESTful 的语法如下：

POST /_xpack/sql?format=txt
{
    "query": "SELECT * FROM twitter"
}

因为 SQL 特性是 xpack 的免费功能，所以是在 _xpack 这个路径下面，我们只需要把 SQL 语句传给 query 字段就行了，注意最后面不要加上 ; 结尾，注意是不要！

我们执行上面的语句，查询返回的结果如下：

          date          |      id       |     name      |    twitter    
------------------------+---------------+---------------+---------------
2018-07-27T00:00:00.000Z|123            |medcl          |sql is awesome

ES 俨然已经变成 SQL 数据库了，我们再看看如何获取所有的索引列表：

POST /_xpack/sql?format=txt
{
    "query": "SHOW tables"
}

返回如下：

              name               |     type      
---------------------------------+---------------
.kibana                          |BASE TABLE     
.monitoring-alerts-6             |BASE TABLE     
.monitoring-es-6-2018.06.21      |BASE TABLE     
.monitoring-es-6-2018.06.26      |BASE TABLE     
.monitoring-es-6-2018.06.27      |BASE TABLE     
.monitoring-kibana-6-2018.06.21  |BASE TABLE     
.monitoring-kibana-6-2018.06.26  |BASE TABLE     
.monitoring-kibana-6-2018.06.27  |BASE TABLE     
.monitoring-logstash-6-2018.06.20|BASE TABLE     
.reporting-2018.06.24            |BASE TABLE     
.triggered_watches               |BASE TABLE     
.watcher-history-7-2018.06.20    |BASE TABLE     
.watcher-history-7-2018.06.21    |BASE TABLE     
.watcher-history-7-2018.06.26    |BASE TABLE     
.watcher-history-7-2018.06.27    |BASE TABLE     
.watches                         |BASE TABLE     
apache_elastic_example           |BASE TABLE     
forum-mysql                      |BASE TABLE     
twitter

有点多，我们可以按名称过滤，如 twitt 开头的索引，注意通配符只支持 %和 _，分别表示多个和单个字符（什么，不记得了，回去翻数据库的书去！）：

POST /_xpack/sql?format=txt
{
    "query": "SHOW TABLES 'twit%'"
}

POST /_xpack/sql?format=txt
{
    "query": "SHOW TABLES 'twitte_'"
}

上面返回的结果都是：

     name      |     type      
---------------+---------------
twitter        |BASE TABLE

如果要查看该索引的字段和元数据，如下：

POST /_xpack/sql?format=txt
{
    "query": "DESC twitter"
}

返回：

    column     |     type      
---------------+---------------
date           |TIMESTAMP      
id             |BIGINT         
name           |VARCHAR        
name.keyword   |VARCHAR        
twitter        |VARCHAR        
twitter.keyword|VARCHAR

都是动态生成的字段，包含了 .keyword 字段。 还能使用下面的命令来查看，主要是兼容 SQL 语法。

POST /_xpack/sql?format=txt
{
    "query": "SHOW COLUMNS IN twitter"
}

另外，如果不记得 ES 支持哪些函数，只需要执行下面的命令，即可得到完整列表：

SHOW FUNCTIONS

返回结果如下，也就是当前6.3版本支持的所有函数，如下：

      name      |     type      
----------------+---------------
AVG             |AGGREGATE      
COUNT           |AGGREGATE      
MAX             |AGGREGATE      
MIN             |AGGREGATE      
SUM             |AGGREGATE      
STDDEV_POP      |AGGREGATE      
VAR_POP         |AGGREGATE      
PERCENTILE      |AGGREGATE      
PERCENTILE_RANK |AGGREGATE      
SUM_OF_SQUARES  |AGGREGATE      
SKEWNESS        |AGGREGATE      
KURTOSIS        |AGGREGATE      
DAY_OF_MONTH    |SCALAR         
DAY             |SCALAR         
DOM             |SCALAR         
DAY_OF_WEEK     |SCALAR         
DOW             |SCALAR         
DAY_OF_YEAR     |SCALAR         
DOY             |SCALAR         
HOUR_OF_DAY     |SCALAR         
HOUR            |SCALAR         
MINUTE_OF_DAY   |SCALAR         
MINUTE_OF_HOUR  |SCALAR         
MINUTE          |SCALAR         
SECOND_OF_MINUTE|SCALAR         
SECOND          |SCALAR         
MONTH_OF_YEAR   |SCALAR         
MONTH           |SCALAR         
YEAR            |SCALAR         
WEEK_OF_YEAR    |SCALAR         
WEEK            |SCALAR         
ABS             |SCALAR         
ACOS            |SCALAR         
ASIN            |SCALAR         
ATAN            |SCALAR         
ATAN2           |SCALAR         
CBRT            |SCALAR         
CEIL            |SCALAR         
CEILING         |SCALAR         
COS             |SCALAR         
COSH            |SCALAR         
COT             |SCALAR         
DEGREES         |SCALAR         
E               |SCALAR         
EXP             |SCALAR         
EXPM1           |SCALAR         
FLOOR           |SCALAR         
LOG             |SCALAR         
LOG10           |SCALAR         
MOD             |SCALAR         
PI              |SCALAR         
POWER           |SCALAR         
RADIANS         |SCALAR         
RANDOM          |SCALAR         
RAND            |SCALAR         
ROUND           |SCALAR         
SIGN            |SCALAR         
SIGNUM          |SCALAR         
SIN             |SCALAR         
SINH            |SCALAR         
SQRT            |SCALAR         
TAN             |SCALAR         
SCORE           |SCORE

同样支持通配符进行过滤：

POST /_xpack/sql?format=txt
{
    "query": "SHOW FUNCTIONS 'S__'"
}

结果：

     name      |     type      
---------------+---------------
SUM            |AGGREGATE      
SIN            |SCALAR

那如果要进行模糊搜索呢，Elasticsearch 的搜索能力大家都知道，强！在 SQL 里面，可以用 match 关键字来写，如下：

POST /_xpack/sql?format=txt
{
    "query": "SELECT SCORE(), * FROM twitter WHERE match(twitter, 'sql is') ORDER BY id DESC"
}

最后，还能试试 SELECT 里面的一些其他操作，如过滤，别名，如下：

POST /_xpack/sql?format=txt
{
    "query": "SELECT SCORE() as score,name as myname FROM twitter as mytable where name = 'medcl' OR name ='elastic' limit 5"
}

结果如下：

     score     |    myname     
---------------+---------------
0.2876821      |medcl

或是分组和函数计算：

POST /_xpack/sql?format=txt
{
    "query": "SELECT name,max(id) as max_id FROM twitter as mytable group by name limit 5"
}

结果如下：

     name      |    max_id     
---------------+---------------
medcl          |123.0

SQL-CLI下的使用

上面的例子基本上把 SQL 的基本命令都介绍了一遍，很多情况下，用 RESTful 可能不是很方便，那么可以试试用 CLI 命令行工具来执行 SQL 语句，妥妥的 SQL 操作体验。

切换到命令行下，启动 cli 程序即可进入命令行交互提示界面，如下：

➜  elasticsearch-6.3.0 ./bin/elasticsearch-sql-cli

     .sssssss.`                     .sssssss.
  .:sXXXXXXXXXXo`                `ohXXXXXXXXXho.
 .yXXXXXXXXXXXXXXo`            `oXXXXXXXXXXXXXXX-
.XXXXXXXXXXXXXXXXXXo`        `oXXXXXXXXXXXXXXXXXX.
.XXXXXXXXXXXXXXXXXXXXo.    .oXXXXXXXXXXXXXXXXXXXXh
.XXXXXXXXXXXXXXXXXXXXXXo``oXXXXXXXXXXXXXXXXXXXXXXy
`yXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.
 `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
   `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
     `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
       `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
         `oXXXXXXXXXXXXXXXXXXXXXXXXXXXXo`
           .XXXXXXXXXXXXXXXXXXXXXXXXXo`
         .oXXXXXXXXXXXXXXXXXXXXXXXXo`
       `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `odo`
     `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXo`
   `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXXXXXo`
 `oXXXXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXXXXXXXXXo`
`yXXXXXXXXXXXXXXXXXXXXXXXo`    oXXXXXXXXXXXXXXXXX.
.XXXXXXXXXXXXXXXXXXXXXXo`   `oXXXXXXXXXXXXXXXXXXXy
.XXXXXXXXXXXXXXXXXXXXo`     /XXXXXXXXXXXXXXXXXXXXX
.XXXXXXXXXXXXXXXXXXo`        `oXXXXXXXXXXXXXXXXXX-
 -XXXXXXXXXXXXXXXo`            `oXXXXXXXXXXXXXXXo`
  .oXXXXXXXXXXXo`                `oXXXXXXXXXXXo.
    `.sshXXyso`        SQL         `.sshXhss.`

sql>

当你看到一个硕大的创口贴，表示 SQL 命令行已经准备就绪了，查看一下索引列表，不，数据表的列表：

15301043943573.jpg

各种操作妥妥的，上面已经测试过的命令就不在这里重复了，只是体验不一样罢了。

如果要连接远程的 ES 服务器，只需要启动命令行工具的时候，指定服务器地址，如果有加密，指定 keystone 文件，完整的帮助如下:

➜  elasticsearch-6.3.0 ./bin/elasticsearch-sql-cli --help
Elasticsearch SQL CLI

Non-option arguments:
uri                  

Option                   Description                                           
------                   -----------                                           
-c, --check <Boolean>    Enable initial connection check on startup (default:  
                           true)                                               
-d, --debug              Enable debug logging                                  
-h, --help               show help                                             
-k, --keystore_location  Location of a keystore to use when setting up SSL. If 
                           specified then the CLI will prompt for a keystore   
                           password. If specified when the uri isn't https then
                           an error is thrown.                                 
-s, --silent             show minimal output                                   
-v, --verbose            show verbose output

JDBC 对接

JDBC 对接的能力，让我们可以与各个 SQL 生态系统打通，利用众多现成的基于 SQL 之上的工具来使用 Elasticsearch，我们以一个工具来举例。

和其他数据库一样，要使用 JDBC，要下载该数据库的 JDBC 的驱动，我们打开： https://www.elastic.co/downloads/jdbc-client

15301048139518.jpg

只有一个 zip 包下载链接，下载即可。

然后，我们这里使用 DbVisualizer 来连接 ES 进行操作，这是一个数据库的操作和分析工具，DbVisualizer 下载地址是：https://www.dbvis.com/。

下载安装启动之后的程序主界面如下图：

15301049453527.jpg

我们如果要使用 ES 作为数据源，我们第一件事需要把 ES 的 JDBC 驱动添加到 DbVisualizer 的已知驱动里面。我们打开 DbVisualizer 的菜单【Tools】-> 【Driver Manager】，打开如下设置窗口：

15301054144234.jpg

点击绿色的加号按钮，新增一个名为 Elasticsearch-SQL 的驱动，url format 设置成 jdbc:es:，如下图：

15301054340439.jpg

然后点击上图黄色的文件夹按钮，添加我们刚刚下载好且解压之后的所有 jar 文件，如下：

15301055143574.jpg

添加完成之后，如下图：

15301055446598.jpg

就可以关闭这个 JDBC 驱动的管理窗口了。下面我们来连接到 ES 数据库。

选择主程序左侧的新建连接图标，打开向导，如下:

15301057385898.jpg

选择刚刚加入的 Elasticsearch-SQL 驱动:

15301057824336.jpg

设置连接字符串，此处没有登录信息，如果有可以对应的填上：

15301064989466.jpg

点击 Connect，即可连接到 ES，左侧导航可以展开看到对应的 ES 索引信息：

15301065711818.jpg

同样可以查看相应的库表结果和具体的数据：

15301066251658.jpg

用他自带的工具执行 SQL 也是不在话下：

15301068015599.jpg

同理，各种 ETL 工具和基于 SQL 的 BI 和可视化分析工具都能把 Elasticsearch 当做 SQL 数据库来连接获取数据了。

最后一个小贴士，如果你的索引名称包含横线，如 logstash-201811，只需要做一个用双引号包含，对双引号进行转义即可，如下：

POST /_xpack/sql?format=txt
{
"query":"SELECT COUNT(*) FROM \"logstash-*\""
}

关于 SQL 操作的文档在这里：

https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html

Enjoy!

[尊重社区原创，转载请保留或注明出处]
本文地址：http://elasticsearch.cn/article/687
10
laoyang360 rockybean famoss chenfh5 jianjianhe rochy esasdhy sony_zhang duzhaowei wuan90

9 个评论

laoyang360

laoyang360

赞，沙发！
2018-06-27 22:42
Ricky_Lau

Ricky_Lau

支持JDBC协议需要拥有ES铂金会员哦。
2018-06-29 08:55
jiaofuyou

jiaofuyou

select * from index1,index2 -- 多个索引不支持
select c1,c2,count(*) from index1 group by c1,c2 -- 多个字段 group by 还不支持
基于这两点，这个版本的官方的 ESSQL 相比 NLPchina/elasticsearch-sql 插件来说，实用方面还有差距
2018-06-29 11:29
lzslov

lzslov

大佬，sql方式分页怎么做呀。。。测试jdbc也是需要ES铂金会员
2018-06-29 15:38
jianjianhe

jianjianhe 回复 Ricky_Lau

对啊 要用这个jdbc协议要收费的
2018-06-30 13:38
medcl

medcl 回复 lzslov

分页还不支持：https://github.com/elastic/elasticsearch/issues/31549
2018-07-02 10:36
medcl

medcl

恩，JDBC 不包含在免费的部分。
2018-07-02 10:36
qianShaw

qianShaw 回复 lzslov

可以通过fetch_size 进行分页，返回的报文里面有cursor这个字段 ，然后通过这个字段查找下一页数据
2018-07-18 09:07
neo0820

neo0820 回复 jiaofuyou

是哦，实际测试确实多个group by支持不了，直接报错。不过有一点比NLPchina/elasticsearch-sql 好的就是支持嵌套查询。还是尽快完善起来吧！
2018-07-27 14:03

要回复文章请先登录或注册

Copyright © 2024 · CC BY-NC-SA 3.0

本站服务器及带宽由 提供赞助