Elasticsearch SQL的用法是什么

Elasticsearch 5.x版本中，SQL功能還沒(méi)有集成到Elasticsearch源碼中，需要下載第三方插件后才能使用，配置過(guò)程如下： 

1.安裝ES-SQL依賴(lài)node npm 

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

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

2.下載ES-SQL并安裝 

然后切換到ES的根目錄下，執(zhí)行如下命令，下載并安ES-SQL插件： 

./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.6.30/.elasticsearch-sql-5.6.3.0.zip

離線包安裝可以執(zhí)行： 

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

3.重啟ES服務(wù) 

執(zhí)行完上述三步，你就可以使用SQL探索數(shù)據(jù)了，以kibana中的使用為例：

二、6.4 Elasticsearch SQL用法

首先我們看下Elasticsearch SQL和標(biāo)準(zhǔn)SQL中數(shù)據(jù)類(lèi)型的對(duì)應(yīng)關(guān)系：

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

2.1 REST Interface

建議先在kibana中測(cè)試(可以一次執(zhí)行多個(gè)SQL)，查詢(xún)通過(guò)之后把查詢(xún)copy到項(xiàng)目中進(jìn)行測(cè)試。

6.3+ Elasticsearch SQL有個(gè)非常實(shí)用的功能，就是可以用translate api把SQL語(yǔ)句翻譯成ES DSL語(yǔ)句，對(duì)于學(xué)習(xí)DSL感到頭痛的同學(xué)有福啦。

2.2 command-line

命令行界面的進(jìn)入方式：

./elasticsearch-sql-cli  IP:PORT(本機(jī)ip和es的端口)

進(jìn)入后的界面如下：

命令行一般作為SQL測(cè)試時(shí)使用。

2.3 JDBC

該組件為X-Pack中的收費(fèi)組件，感興趣的同學(xué)可以參考官方文檔：https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-jdbc.html

注意：查詢(xún)單個(gè)索引名一定要用""引上，否則會(huì)報(bào)錯(cuò)

*查看當(dāng)前用戶(hù)所有的索引：“SHOW TABLES;”

精準(zhǔn)查詢(xún)某個(gè)索引：“SHOW TABLES LIKE ‘indexname’;”

通配符查詢(xún)某些索引：“SHOW TABLES LIKE ‘ ’;”

*查看某個(gè)索引結(jié)構(gòu)：“DESCRIBE table;” 或者 “DESC table;”

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

*查看函數(shù)：“SHOW FUNCTIONS [ LIKE? pattern? ]?” 

精準(zhǔn)查詢(xún)某個(gè)函數(shù)：

通配符查詢(xún)某些函數(shù)：

查看所有函數(shù)：

常用的聚合函數(shù)： 

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語(yǔ)句的語(yǔ)法排序如下：

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

*限定返回?cái)?shù)據(jù)的條數(shù)：“l(fā)imit” 

SELECT * FROM "micloud_es_sink_zhouyongbo_test-2018.10.19” limit 10 ;

注意SQL中的limit比f(wàn)etch_size中的優(yōu)先級(jí)高,例如下面的例子返回的是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;

根據(jù)多個(gè)字段排序： 

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根據(jù)條件查詢(xún)：

WHERE后面跟ES復(fù)雜數(shù)據(jù)類(lèi)型： 

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

WHERE后面跟多個(gè)查詢(xún)條件： 

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

*group by分組查詢(xún)： 

根據(jù)單個(gè)字段分組查詢(xún)：

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;

根據(jù)多個(gè)字段分組查詢(xún)： 

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;

對(duì)于比較長(zhǎng)的字段，也可以對(duì)該字段聲明別名，并對(duì)別名進(jìn)行分組查詢(xún)，聲明字段別名的“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;

還可以對(duì)某字段進(jìn)行計(jì)算，然后按照計(jì)算結(jié)果分組查詢(xún)： 

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

*HAVING過(guò)濾分組結(jié)果（ES-SQL引擎同樣會(huì)在分組之后計(jì)算HAVING語(yǔ)句）：

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

*查詢(xún)嵌套類(lèi)型：

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

*用通配符查詢(xún)多個(gè)索引：

注意被查詢(xún)索引必須有相同的mapping，否則會(huì)有如下報(bào)錯(cuò)：

常用的方法和操作匯總：

*比較操作： 

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;

*數(shù)學(xué)運(yùn)算操作： 

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;

*數(shù)學(xué)函數(shù)：(分為通用函數(shù)和三角函數(shù)兩部分 )：

通用函數(shù)： 

ABS：求數(shù)字的絕對(duì)值 

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

CBRT：求數(shù)字的立方根，返回double

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

CEIL：返回大于或者等于指定表達(dá)式最小整數(shù)（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：返回自然常數(shù)e(2.718281828459045)

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

ROUND：四舍五入精確到個(gè)位

select ROUND(-3.14);

FLOOR：向下取整

select FLOOR(3.14);

LOG：計(jì)算以2為底的自然對(duì)數(shù)

select LOG(4);

LOG10：計(jì)算以10為底的自然對(duì)數(shù)

select LOG10(100);

SQRT：求一個(gè)非負(fù)實(shí)數(shù)的平方根

select  SQRT(9);

EXP：此函數(shù)返回e(自然對(duì)數(shù)的底)的X次方的值

select  EXP(3);

EXPM1：返回e x  -1

select  EXPM1(3);

三角函數(shù)：

DEGREES：返回X從弧度轉(zhuǎn)換為度值

select DEGREES(x);

RADIANS：返回X從度轉(zhuǎn)換成弧度的值

select RADIANS(x);

SIN：返回X的正弦

select SIN(x);

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

select COS(角度);

TAN：返回參數(shù)X，表示以弧度的切線值

select TAN(角度);

ASIN：返回X的反正弦，X的值必須在-1至1范圍內(nèi)，返回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);

*日期和時(shí)間處理相關(guān)方法：

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支持的主要用法了，如果在優(yōu)化SQL語(yǔ)句之后還不滿(mǎn)足查詢(xún)需求，可以拿SQL和DSL混用，ES會(huì)先根據(jù)SQL進(jìn)行查詢(xún)，然后根據(jù)DSL語(yǔ)句對(duì)SQL的執(zhí)行結(jié)果進(jìn)行二次查詢(xún)，下面是個(gè)小例子：

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
}

這個(gè)查詢(xún)就會(huì)先根據(jù)“query”后面的SQL進(jìn)行查詢(xún)，然后用執(zhí)行“filter”和“fetch_size” DSL語(yǔ)法對(duì)查詢(xún)結(jié)果進(jìn)行過(guò)濾，進(jìn)而返回最終結(jié)果。