沙师弟,师父的充电器掉了

玩转 Elasticsearch 的 SQL 功能

Elasticsearchmedcl 发表了文章 • 9 个评论 • 75017 次浏览 • 2018-06-27 21:54 • 来自相关话题

最近发布的 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 .. | 用来执行查询的命令 |

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

首先,我们创建一条数据:

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

RESTful下调用SQL


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

RESTful 的语法如下:

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

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

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

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

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

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

返回如下:

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

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

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

上面返回的结果都是:

<br /> name | type <br /> ---------------+---------------<br /> twitter |BASE TABLE <br /> <br />

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

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

返回:

<br /> column | type <br /> ---------------+---------------<br /> date |TIMESTAMP <br /> id |BIGINT <br /> name |VARCHAR <br /> name.keyword |VARCHAR <br /> twitter |VARCHAR <br /> twitter.keyword|VARCHAR <br />

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

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

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

<br /> SHOW FUNCTIONS<br />

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

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

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

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

结果:

<br /> name | type <br /> ---------------+---------------<br /> SUM |AGGREGATE <br /> SIN |SCALAR <br />

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

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

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

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

<br /> score | myname <br /> ---------------+---------------<br /> 0.2876821 |medcl <br />

或是分组和函数计算:

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

结果如下:

<br /> name | max_id <br /> ---------------+---------------<br /> medcl |123.0 <br />

SQL-CLI下的使用


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

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

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

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

15301043943573.jpg



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

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

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

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,只需要做一个用双引号包含,对双引号进行转义即可,如下:

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

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

[https://www.elastic.co/guide/e ... .html](https://www.elastic.co/guide/e ... c.html)

Enjoy!

mutate进行add_field加入中文字段名称时乱码

Logstashrockybean 回复了问题 • 3 人关注 • 2 个回复 • 4693 次浏览 • 2018-06-27 20:05 • 来自相关话题

es数据节点运行崩溃

Elasticsearchcwn1986 回复了问题 • 2 人关注 • 2 个回复 • 4620 次浏览 • 2018-06-27 19:18 • 来自相关话题

es数据节点运行崩溃

回复

Elasticsearchcwn1986 发起了问题 • 1 人关注 • 0 个回复 • 1424 次浏览 • 2018-06-27 17:51 • 来自相关话题

logstash收取的kafka消息如何去提取

LogstashEsun 回复了问题 • 4 人关注 • 3 个回复 • 5070 次浏览 • 2018-06-27 16:45 • 来自相关话题

社区日报 第315期 (2018-06-27)

社区日报千夜 发表了文章 • 0 个评论 • 1600 次浏览 • 2018-06-27 11:32 • 来自相关话题

1. 浅谈ES技术与实践
http://t.cn/RBaaEP1 
2.日志采集中的关键技术分析
http://t.cn/RroGKAZ 
3.基于微服务的日志中心设计、实现与关键配置
http://t.cn/RBSE3Mx 
 
活动预告
1. 6月30日南京meetup参会报名中
https://elasticsearch.cn/m/article/647 
2. 7月21日上海meetup演讲申请中
https://elasticsearch.cn/m/article/655 
 
编辑:江水
归档:https://elasticsearch.cn/article/686
订阅:https://tinyletter.com/elastic-daily
 

fuzzy查询放在filter里和放在must里命中结果数量相差悬殊

Elasticsearchkennywu76 回复了问题 • 3 人关注 • 4 个回复 • 2490 次浏览 • 2018-06-27 11:27 • 来自相关话题

filebeat 无法传递数据给logstash

Logstashhlsa816215 回复了问题 • 4 人关注 • 4 个回复 • 6881 次浏览 • 2018-06-27 11:07 • 来自相关话题

elk 5.0 安装完成之后使用x-pack管理用户问题

KibanaMrliushao 回复了问题 • 8 人关注 • 10 个回复 • 11350 次浏览 • 2018-06-27 11:01 • 来自相关话题

es如果是测试环境的linux配置最低是什么

回复

Elasticsearchlxwan08 发起了问题 • 1 人关注 • 0 个回复 • 2379 次浏览 • 2018-06-27 10:36 • 来自相关话题

logstash6.x: undefined method '*' for nil:NilClass

回复

Logstasherha_erha 回复了问题 • 1 人关注 • 2 个回复 • 3283 次浏览 • 2018-06-27 10:15 • 来自相关话题

java中连接transport client需要启动本机的elasticsearch么?在连接过程中遇到了一些问题

回复

Elasticsearchckelastic 发起了问题 • 1 人关注 • 0 个回复 • 1919 次浏览 • 2018-06-26 23:48 • 来自相关话题

问一个查询类的问题。

Elasticsearchlaoyang360 回复了问题 • 2 人关注 • 1 个回复 • 1511 次浏览 • 2018-06-26 23:09 • 来自相关话题

能否对 nested 类型 分页取出?

Elasticsearchlaoyang360 回复了问题 • 3 人关注 • 2 个回复 • 3504 次浏览 • 2018-06-26 22:34 • 来自相关话题

query_string多值OR查询

Elasticsearchlaoyang360 回复了问题 • 3 人关注 • 1 个回复 • 4767 次浏览 • 2018-06-26 22:32 • 来自相关话题