Elasticsearch SQL用法詳解

Elasticsearch 5.x版本中，SQL功能還沒有整合到Elasticsearch原始碼中，需要下載第三方外掛後才能使用，配置過程如下： 

1.安裝ES-SQL依賴node npm 

ES-SQL 5.x版本以後，安裝需要依賴node和npm，先安裝node和npm，安裝後在檢查node及npm的安裝，命令如下： 

yum -y install nodejs npm 
node -v　 
npm -v

2.下載ES-SQL並安裝 

然後切換到ES的根目錄下，執行如下命令，下載並安ES-SQL外掛： 

./bin/elasticsearch-plugin install 

離線包安裝可以執行： 

./bin/elasticsearch-plugin install file:/elasticsearch-sql-5.6.3.0.zip

3.重啟ES服務 

執行完上述三步，你就可以使用SQL探索資料了，以kibana中的使用為例：

二、6.4 Elasticsearch SQL用法

首先我們看下Elasticsearch SQL和標準SQL中資料型別的對應關係：

Elasticsearch SQL支援三種client： REST Interface， command-line，JDBC

2.1 REST Interface

建議先在kibana中測試(可以一次執行多個SQL)，查詢透過之後把查詢copy到專案中進行測試。

6.3+ Elasticsearch SQL有個非常實用的功能，就是可以用translate api把SQL語句翻譯成ES DSL語句，對於學習DSL感到頭痛的同學有福啦。

2.2 command-line

命令列介面的進入方式：

./elasticsearch-sql-cli  IP:PORT(本機ip和es的埠)

進入後的介面如下：

命令列一般作為SQL測試時使用。

2.3 JDBC

該元件為X-Pack中的收費元件，感興趣的同學可以參考官方文件：https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html

注意：查詢單個索引名一定要用""引上，否則會報錯

*檢視當前使用者所有的索引：“SHOW TABLES;”

精準查詢某個索引：“SHOW TABLES LIKE ‘indexname’;”

萬用字元查詢某些索引：“SHOW TABLES LIKE ‘ ’;”

*檢視某個索引結構：“DESCRIBE table;” 或者 “DESC table;”

上面兩個命令都是“SHOW COLUMNS [ FROM | IN ] ? table”命令的別名

*檢視函式：“SHOW FUNCTIONS [ LIKE? pattern? ]?” 

精準查詢某個函式：

萬用字元查詢某些函式：

檢視所有函式：

常用的聚合函式： 

SELECT MIN(value_1) min, MAX(value_1) max, AVG(value_1) avg,SUM(value_1) sum,COUNT(*) count,COUNT(DISTINCT value_1) dictinct_count FROM "micloud_es_sink_zhouyongbo_test-2018.10.19”;

SELECT語句的語法排序如下：

SELECT select_expr [, ...]
[ FROM table_name ]  
[ WHERE condition ] 
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]

*限定返回資料的條數：“limit” 

SELECT * FROM "micloud_es_sink_zhouyongbo_test-2018.10.19” limit 10 ;

注意SQL中的limit比fetch_size中的優先順序高,例如下面的例子返回的是5條 :

{ 
  "query": "SELECT * FROM "micloud_es_sink_zhouyongbo_test-2018.10.19” limit 5", 
  "fetch_size":10 
}

*排序：“order by + 欄位名字 + asc/desc”

SELECT * FROM "micloud_es_sink_zhouyongbo_test-2018.10.19” ORDER BY value_1 asc/desc;

根據多個欄位排序： 

select city c,value_1 + 1 vp from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by c,vp order by c desc,vp asc;

*WHERE根據條件查詢：

WHERE後面跟ES複雜資料型別： 

SELECT first_name FROM index WHERE first_name.raw = ‘John’  ;

WHERE後面跟多個查詢條件： 

SELECT * FROM micloud_es_sink_zhouyongbo_test* where city=‘北京' and value_1=8 ORDER BY value_1 desc ;

*group by分組查詢： 

根據單個欄位分組查詢：

select city,count(city) as count_city,sum(value_1) as count_value_1 from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by city;

根據多個欄位分組查詢： 

select city,count(city) count_city,sum(value_1) count_value_1 from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by city,value_1;

對於比較長的欄位，也可以對該欄位宣告別名，並對別名進行分組查詢，宣告欄位別名的“as”可省略： 

select city c,count(city) count_city,sum(value_1) count_value_1 from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by c,value_1;

還可以對某欄位進行計算，然後按照計算結果分組查詢： 

select city c,value_1 + 1 vp from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by c,vp;

*HAVING過濾分組結果（ES-SQL引擎同樣會在分組之後計算HAVING語句）：

Select city c,count(*) count from "micloud_es_sink_zhouyongbo_test-2018.10.19" group by c having count > 53834;

*查詢巢狀型別：

select * from zhouyongbo_test04 where love.kaishu=‘魯公’;

*用萬用字元查詢多個索引：

注意被查詢索引必須有相同的mapping，否則會有如下報錯：

常用的方法和操作彙總：

*比較操作： 

Equality (=) 

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 = 6 limit 5;

Inequality (<> or != or 

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 <> 6 limit 5;

Comparison (<, 

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 >= 6 limit 5;

BETWEEN

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 between 6 and 8 limit 5;

IS NULL/IS NOT NULL

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 is not NULL limit 5;

*邏輯操作：

AND

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 > 5 and value_1 < 7 limit 5;

OR

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where value_1 = 5 or value_1 = 7 limit 5;

NOT

select * from "micloud_es_sink_zhouyongbo_test-2018.10.19" where not value_1 > 5 limit 5;

*數學運算操作： 

Add (+)

select  1 + 1 as x;

Subtract (infix -) 

select  1 - 1 as x;

Negate (unary -) 

select  - 1 as x;

Multiply (*) 

select  6 * 6 as x;

Divide (/) 

select  30 / 5 as x;

Modulo or Reminder(%) 

select  30 % 7 as x;

*數學函式：(分為通用函式和三角函式兩部分 )：

通用函式： 

ABS：求數字的絕對值 

select ABS(value_1) from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5;

CBRT：求數字的立方根，返回double

select value_1 v,CBRT(value_1) cbrt from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5;

CEIL：返回大於或者等於指定表示式最小整數（double）

select value_1 v,CEIL(value_1) from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5;

CEILING：等同於CEIL

select value_1 v,CEILING(value_1) from "micloud_es_sink_zhouyongbo_test-2018.10.19" limit 5;

E：返回自然常數e(2.718281828459045)

select  value_1,E(value_1)  from  "micloud_es_sink_zhouyongbo_test-2018.10.19"  limit  5;

ROUND：四捨五入精確到個位

select ROUND(-3.14);

FLOOR：向下取整

select FLOOR(3.14);

LOG：計算以2為底的自然對數

select LOG(4);

LOG10：計算以10為底的自然對數

select LOG10(100);

SQRT：求一個非負實數的平方根

select  SQRT(9);

EXP：此函式返回e(自然對數的底)的X次方的值

select  EXP(3);

EXPM1：返回e x  -1

select  EXPM1(3);

三角函式：

DEGREES：返回X從弧度轉換為度值

select DEGREES(x);

RADIANS：返回X從度轉換成弧度的值

select RADIANS(x);

SIN：返回X的正弦

select SIN(x);

COS：返回X，X值是以弧度給出的餘弦值

select COS(角度);

TAN：返回引數X，表示以弧度的切線值

select TAN(角度);

ASIN：返回X的反正弦，X的值必須在-1至1範圍內，返回NULL

select ASIN(x);

ACOS：返回X的反正弦，X值必須-1到1之間範圍否則將返回NULL

select ACOS(x);

ATAN：返回X的反正切

select ATAN(x);

SINH：返回X的雙曲正弦值

select SINH(x);

COSH：返回X的雙曲餘弦值

select COSH(x);

*日期和時間處理相關方法：

YEAR：

SELECT YEAR(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS year;

MONTH_OF_YEAR() or MONTH()：

SELECT MONTH(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS month;

WEEK_OF_YEAR() or WEEK()：

SELECT WEEK(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS week;

DAY_OF_YEAR() or DOY()，效果等同於EXTRACT(

SELECT DOY(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS day;

DAY_OF_MONTH(), DOM(), or DAY()：

SELECT DAY(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS day;

DAY_OF_WEEK() or DOW()：

SELECT DOW(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS day;

HOUR_OF_DAY() or HOUR()：

SELECT HOUR(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS hour;

MINUTE_OF_DAY()：

SELECT MINUTE_OF_DAY(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS minute;

MINUTE_OF_HOUR() or MINUTE()：

SELECT MINUTE(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS minute;

SECOND_OF_MINUTE() or SECOND()：

SELECT SECOND(CAST('2018-10-23T16:59:27Z' AS TIMESTAMP)) AS second;

如上就是6.4 Elasticsearch SQL支援的主要用法了，如果在最佳化SQL語句之後還不滿足查詢需求，可以拿SQL和DSL混用，ES會先根據SQL進行查詢，然後根據DSL語句對SQL的執行結果進行二次查詢，下面是個小例子：

POST /_xpack/sql?format=txt 
{ 
    "query": "SELECT * FROM library ORDER BY page_count DESC", 
    "filter": { 
        "range": { 
            "page_count": { 
                "gte" : 100, 
                "lte" : 200 
            } 
        } 
    }, 
    "fetch_size": 5
}

這個查詢就會先根據“query”後面的SQL進行查詢，然後用執行“filter”和“fetch_size” DSL語法對查詢結果進行過濾，進而返回最終結果。

參考文件: 

6.4.0 Elasticsearch SQL新特性簡介：

 

6.4.0 Elasticsearch SQL使用文件：

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