玩转 Elasticsearch 的 SQL 功能
< 返回列表时间: 2020-06-16来源:OSCHINA
最近发布的 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 命令行已经准备就绪了,查看一下索引列表,不,数据表的列表:
[attach]2546[/attach]
各种操作妥妥的,上面已经测试过的命令就不在这里重复了,只是体验不一样罢了。
如果要连接远程的 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

只有一个 zip 包下载链接,下载即可。
然后,我们这里使用 DbVisualizer 来连接 ES 进行操作,这是一个数据库的操作和分析工具,DbVisualizer 下载地址是: https://www.dbvis.com/ 。
下载安装启动之后的程序主界面如下图:

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

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

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

添加完成之后,如下图:

就可以关闭这个 JDBC 驱动的管理窗口了。下面我们来连接到 ES 数据库。
选择主程序左侧的新建连接图标,打开向导,如下:

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

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

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

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

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

同理,各种 ETL 工具和基于 SQL 的 BI 和可视化分析工具都能把 Elasticsearch 当做 SQL 数据库来连接获取数据了。
最后一个小贴士,如果你的索引名称包含横线,如 logstash-201811,只需要做一个用双引号包含,对双引号进行转义即可,如下:
关于 SQL 操作的文档在这里:
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html
热门排行