狂甩酷拽吊炸天
分类： LINUX
2019-09-05 20:56:33
从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
}
在 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 的基本命令都介绍了一遍，很多情况下，用 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 命令行已经准备就绪了，查看一下索引列表，不，数据表的列表：
各种操作妥妥的，上面已经测试过的命令就不在这里重复了，只是体验不一样罢了。
如果要连接远程的 ES 服务器，只需要启动命令行工具的时候，指定服务器地址，如果有加密，指定 keystone 文件，完整的帮助如下:
? elasticsearch-6.3.0 ./bin/elasticsearch-sql-cli --help Elasticsearch SQL CLI Non-option arguments: uri Option Description ------ ----------- -c, --checkEnable 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 对接的能力，让我们可以与各个 SQL 生态系统打通，利用众多现成的基于 SQL 之上的工具来使用 Elasticsearch，我们以一个工具来举例。
和其他数据库一样，要使用 JDBC，要下载该数据库的 JDBC 的驱动，我们打开：
只有一个 zip 包下载链接，下载即可。
然后，我们这里使用 DbVisualizer 来连接 ES 进行操作，这是一个数据库的操作和分析工具，DbVisualizer 下载地址是：。
下载安装启动之后的程序主界面如下图：
我们如果要使用 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，只需要做一个用双引号包含，对双引号进行转义即可，如下：
POST /_xpack/sql?format=txt
{
"query":"SELECT COUNT(*) FROM \"logstash-*\""
}
关于 SQL 操作的文档在这里：
https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html