文档章节

玩转 Elasticsearch 的 SQL 功能

Medcl
 Medcl
发布于 06/28 09:25
字数 2045
阅读 3821
收藏 101

最近发布的 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

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

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

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

Enjoy!

© 著作权归作者所有

共有 人打赏支持
Medcl

Medcl

粉丝 57
博文 8
码字总数 13023
作品 0
长沙
加载中

评论(4)

你美你说的对
你美你说的对

引用来自“你说的我就不懂”的评论

多谢楼主科普,正在用这个来提高查询性能

引用来自“裸奔的皮皮虾”的评论

最近也在用这个,sql的查询性能比API快吗?
这个我倒是没对比,主要是为了用sql方便,不用移植
裸奔的皮皮虾
裸奔的皮皮虾

引用来自“你说的我就不懂”的评论

多谢楼主科普,正在用这个来提高查询性能
最近也在用这个,sql的查询性能比API快吗?
bboss
bboss
追加一个:
Elasticsearch 6.3.0 SQL功能使用案例分享
https://my.oschina.net/bboss/blog/1834375
你美你说的对
你美你说的对
多谢楼主科普,正在用这个来提高查询性能
bboss v5.0.6.8 发布,持久支持Elasticsearch SQL

bboss v5.0.6.8发布,持久层支持Elasticsearch SQL和Elasticsearch JDBC. v5.0.6.8功能改进 持久层支持支持Elasticsearch SQL,使用参考文档:玩转Elasticsearch SQL功能 解决持久层/elasti...

bboss
07/02
0
0
bboss升级至 v5.0.6.8版本,改善对Elasticsearch SQL 的支持

v5.0.6.8功能改进如下: (1)持久层支持支持Elasticsearch SQL,使用参考文档:玩转Elasticsearch SQL功能 (2)解决持久层/elasticsearch模板变量解析多层级不起作用问题 (3)完善国际化功能 (4...

linux-tao
07/18
0
0
ELK 完整部署和使用 - 每天5分钟玩转 Docker 容器技术(90)

上一节已经部署了容器化的 ELK,本节讨论如何将日志导入 ELK 并进行图形化展示。 几乎所有的软件和应用都有自己的日志文件,容器也不例外。前面我们已经知道 Docker 会将容器日志记录到 ,那...

CloudMAN
2017/11/06
0
1
初探 ELK - 每天5分钟玩转 Docker 容器技术(89)

在开源的日志管理方案中,最出名的莫过于 ELK 了。ELK 是三个软件的合称:Elasticsearch、Logstash、Kibana。 Elasticsearch 一个近乎实时查询的全文搜索引擎。Elasticsearch 的设计目标就是...

CloudMAN
2017/11/03
0
0
Elasticsearch如何实现 SQL语句中 Group By 和 Limit 的功能

有 SQL 背景的同学在学习 Elasticsearch 时,面对一个查询需求,不由自主地会先思考如何用 SQL 来实现,然后再去想 Elasticsearch 的 Query DSL 如何实现。那么本篇就给大家讲一条常见的 SQ...

rockybean
05/21
0
0

没有更多内容

加载失败,请刷新页面

加载更多

5、前后端分离跨域问题

在以往的开发中,前后端分离也不是像现在这么热门,所谓的前端工程师也只不过是写好静态页面由Java工程师或者php工程师嵌入到页面中进行开发,这或许加重了这些工程师的工作量,而且在样式调...

永远的Chester
7分钟前
0
0
全志T3 Linux显示驱动分析

1、总体架构 全志T3处理器的显示框架是基于标准Linux的帧缓冲架构,其结构如图 1.1所示。显示控制器DE的驱动架构如图 1.2所示,包括屏蔽差异的显示管理抽象层,以及显示图层驱动、显示设备驱...

pnsam301
14分钟前
0
0
【HAVENT原创】VUE2 经验问题汇总

新建一个 Vue 实例可以有下列两种方式: 1. new 一个实例 var app= new Vue({ el:'#todo-app', // 挂载元素 data:{ // 在.vue组件中data是一个函数,要写成data () {}这种方式 i...

HAVENT
17分钟前
0
0
IO基础知识

读文件,可以使用内置的open(file,mode); with 语句自动close; 查看当前目录的绝对路径:os.path.abspath('.'); 创建目录:os.mkdir(path); 删除目录:os.rmdir(path); 拼接路径推荐使用:o...

年轻的中年大叔
20分钟前
0
0
BATJ等大厂最全经典面试题分享

金九银十,又到了面试求职高峰期,最近有很多网友都在求大厂面试题。正好我之前电脑里面有这方面的整理,于是就发上来分享给大家。 这些题目是网友去百度、蚂蚁金服、小米、乐视、美团、58、...

老道士
22分钟前
0
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部