Elasticsearch 提供了一套功能強大的擴充套件 - X-Pack,X-Pack 包含了安全、警告、監視、圖形、報告等等的功能集合在一起。而其中也包含了使用 SQL 語法來查詢 Elasticsearch 的功能。
SQL REST API
SQL REST API 允許 SQL 透過 JSON 格式來執行和返回結果。
1 2 3 4 5 6
| POST _xpack/sql?format=<format> { "query": """ <SQL_Query_Here> """ }
|
在 Kibana Console 上執行,建議可以使用三層雙引號將 SQL 語法括起來。三層雙引號的好處是 query 可以分成多行撰寫,這對於大部分人習慣將 SQL 語法分行排版來說是很棒的功能。
另外,URL 後面可以指定回傳資料的格式,就是上面範例的 format
。較常使用格式的如下 :
範例
1 2 3 4 5 6 7 8
| POST _xpack/sql?format=json { "query": """ select * from sport where assets > 100 """ }
|
上面的範例是指定以 json 格式回傳,結果如下 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34
| { "columns": [ { "name": "assets", "type": "long" }, { "name": "champion", "type": "long" }, { "name": "location", "type": "text" }, { "name": "team", "type": "text" } ], "rows": [ [ 150, 16, "Los Angelas", "Lakers" ], [ 120, 6, "Chicago", "Bulls" ] ] }
|
比較特別的是 txt 是以表格的形式回傳,如下 :
1 2 3 4
| assets | champion | location | team ---------------+---------------+---------------+--------------- 150 |16 |Los Angelas |Lakers 120 |6 |Chicago |Bulls
|
更多回傳格式請參考 Elasticsearch 官網。
Translate
除了直接執行 SQL,X-Pack 的 SQL 功能還支援將 SQL 語法轉成 Elasticsearch 的 DSL。
1 2 3 4 5 6
| POST _xpack/sql/translate { "query": """ <SQL_Query_Here> """ }
|
範例
1 2 3 4 5 6 7 8
| POST _xpack/sql/translate { "query": """ select * from sport where assets > 100 """ }
|
回傳的結果如下 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38
| { "size" : 1000, "query" : { "range" : { "assets" : { "from" : 100, "to" : null, "include_lower" : false, "include_upper" : false, "boost" : 1.0 } } }, "_source" : { "includes" : [ "location", "team" ], "excludes" : [ ] }, "docvalue_fields" : [ { "field" : "assets", "format" : "use_field_mapping" }, { "field" : "champion", "format" : "use_field_mapping" } ], "sort" : [ { "_doc" : { "order" : "asc" } } ] }
|
上面的輸出就是 Elasticsearch 將 SQL 轉換成 DSL 的結果。
所以就可以將這段 DSL 再加上 URL 就可以進行查詢。如下 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| GET computer/_search { "size" : 1000, "query" : { "range" : { "assets" : { "from" : 100, "to" : null, "include_lower" : false, "include_upper" : false, "boost" : 1.0 } } }, "_source" : { "includes" : [ "location", "team" ], "excludes" : [ ] }, "docvalue_fields" : [ { "field" : "assets", "format" : "use_field_mapping" }, { "field" : "champion", "format" : "use_field_mapping" } ], "sort" : [ { "_doc" : { "order" : "asc" } } ] }
|
SQL Syntax
關鍵保留字
關鍵保留字和 SQL 一樣不分大小寫。
1 2
| SELECT * FROM <table> select * fRoM <table>
|
若一定要使用到關鍵保留字作為參數或者欄位名稱等等,需用 雙引號
括起來區分。
1
| SELECT "<Field>" FROM <table>
|
SQL Command
Describe
Describe 用於顯示欄位的名稱和資料類型也就是 Mapping。
1 2 3 4
| POST _xpack/sql?format=txt { "query": "describe <table>" }
|
範例
1 2 3 4
| POST _xpack/sql?format=txt { "query": "describe sport" }
|
輸出結果如下 :
1 2 3 4 5 6 7 8
| column | type | mapping ----------------+---------------+--------------- assets |BIGINT |LONG champion |BIGINT |LONG location |VARCHAR |TEXT location.keyword|VARCHAR |KEYWORD team |VARCHAR |TEXT team.keyword |VARCHAR |KEYWORD
|
Show
Show 可以指定想要顯示的欄位 (Fields)、表(Index)和函式。
Show Columns
Show Columns 等同於 Describe 的功能,後面接 in 或 from table 結果都一樣。
1 2 3 4
| POST _xpack/sql?format=txt { "query": "show columns <in|from> <table>" }
|
範例
1 2 3 4
| POST _xpack/sql?format=txt { "query": "show columns in sport" }
|
輸出結果如下,可以和上面的 Describe 比對結果完全一樣。
1 2 3 4 5 6 7 8
| column | type | mapping ----------------+---------------+--------------- assets |BIGINT |LONG champion |BIGINT |LONG location |VARCHAR |TEXT location.keyword|VARCHAR |KEYWORD team |VARCHAR |TEXT team.keyword |VARCHAR |KEYWORD
|
Show Tables
Show Tables 用於顯示有哪些表。
1 2 3 4
| POST _xpack/sql?format=txt { "query": "show tables" }
|
輸出結果如下 :
1 2 3 4 5
| name | type ---------------+--------------- .kibana |ALIAS .kibana_1 |BASE TABLE sport |BASE TABLE
|
Show Functions
Show Functions 用於列出所有可以使用的函式。
1 2 3 4
| POST _xpack/sql?format=txt { "query": "show functions" }
|
輸出結果如下 :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
| name | type ----------------+--------------- AVG |AGGREGATE COUNT |AGGREGATE MAX |AGGREGATE MIN |AGGREGATE SUM |AGGREGATE KURTOSIS |AGGREGATE PERCENTILE |AGGREGATE PERCENTILE_RANK |AGGREGATE SKEWNESS |AGGREGATE STDDEV_POP |AGGREGATE SUM_OF_SQUARES |AGGREGATE VAR_POP |AGGREGATE DAY |SCALAR DAYNAME |SCALAR DAYOFMONTH |SCALAR DAYOFWEEK |SCALAR DAYOFYEAR |SCALAR DAY_NAME |SCALAR DAY_OF_MONTH |SCALAR DAY_OF_WEEK |SCALAR DAY_OF_YEAR |SCALAR DOM |SCALAR DOW |SCALAR DOY |SCALAR HOUR |SCALAR HOUR_OF_DAY |SCALAR MINUTE |SCALAR MINUTE_OF_DAY |SCALAR MINUTE_OF_HOUR |SCALAR MONTH |SCALAR MONTHNAME |SCALAR MONTH_NAME |SCALAR MONTH_OF_YEAR |SCALAR QUARTER |SCALAR SECOND |SCALAR SECOND_OF_MINUTE|SCALAR WEEK |SCALAR WEEK_OF_YEAR |SCALAR YEAR |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 RAND |SCALAR RANDOM |SCALAR ROUND |SCALAR SIGN |SCALAR SIGNUM |SCALAR SIN |SCALAR SINH |SCALAR SQRT |SCALAR TAN |SCALAR TRUNCATE |SCALAR ASCII |SCALAR BIT_LENGTH |SCALAR CHAR |SCALAR CHARACTER_LENGTH|SCALAR CHAR_LENGTH |SCALAR CONCAT |SCALAR INSERT |SCALAR LCASE |SCALAR LEFT |SCALAR LENGTH |SCALAR LOCATE |SCALAR LTRIM |SCALAR OCTET_LENGTH |SCALAR POSITION |SCALAR REPEAT |SCALAR REPLACE |SCALAR RIGHT |SCALAR RTRIM |SCALAR SPACE |SCALAR SUBSTRING |SCALAR UCASE |SCALAR CAST |SCALAR CONVERT |SCALAR SCORE |SCORE
|
SQL 語法限制
Elasticsearch 的 SQL Translate 目前只提供查詢的功能,也就是只能使用 SELECT
,而巢狀的 SQL 查詢也是不支援的。
此外,轉換出來的語法有些並不需要用到或是可以更精簡,所以它還是無法取代 Elasticsearch 原生的 Query DSL。
Summary
本篇介紹了如何使用 SQL 來查詢 Elasticserch,雖然 Elasticsearch 有提供這個功能,但是現階段的支援度仍然是不足的,所以還是需要學習 Elasticsearch 原生的 Query DSL 才會比較好用。
參考
[1] Elasticsearch SQL Access