每日一博丨玩轉 Elasticsearch 的 SQL 功能

知識 06-29

歡迎下載開源中國APP獲取更多優質文章

最近發布的 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 欄位就行了，注意最後面不要加上 ; 結尾，注意是不要！

我們執行上面的語句，查詢返回的結果如下：

ES 儼然已經變成 SQL 資料庫了，我們再看看如何獲取所有的索引列表：

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

返回如下：

有點多，我們可以按名稱過濾，如 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"
}

都是動態生成的欄位，包含了 .keyword 欄位。還能使用下面的命令來查看，主要是兼容 SQL 語法。

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

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

SHOW FUNCTIONS

返回結果如下，也就是當前6.3版本支持的所有函數，如下：

同樣支持通配符進行過濾：

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

每日一博丨玩轉 Elasticsearch 的 SQL 功能