--- # System prepended metadata title: Elasticsearch (四) - 使用 SQL 語法進行查詢 tags: [NoSQL, Elasticsearch] --- # Elasticsearch (四) - 使用 SQL 語法進行查詢 ![xpack](https://i.imgur.com/CgxAHfs.png =300x300) Elasticsearch 提供了一套功能強大的擴充套件 - [X-Pack](https://www.elastic.co/subscriptions)，X-Pack 包含了安全、警告、監視、圖形、報告等等的功能集合在一起。而其中也包含了使用 SQL 語法來查詢 Elasticsearch 的功能。 ## SQL REST API SQL REST API 允許 SQL 透過 JSON 格式來執行和返回結果。 ```json= POST _xpack/sql?format= { "query": """ """ } ``` 在 Kibana Console 上執行，建議可以使用三層雙引號將 SQL 語法括起來。三層雙引號的好處是 query 可以分成多行撰寫，這對於大部分人習慣將 SQL 語法分行排版來說是很棒的功能。 另外，URL 後面可以指定回傳資料的格式，就是上面範例的 `format`。較常使用格式的如下 : * json * csv * txt **範例** ```json= POST _xpack/sql?format=json { "query": """ select * from sport where assets > 100 """ } ``` 上面的範例是指定以 json 格式回傳，結果如下 : ```json= { "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 是以表格的形式回傳，如下 : ``` assets | champion | location | team ---------------+---------------+---------------+--------------- 150 |16 |Los Angelas |Lakers 120 |6 |Chicago |Bulls ``` 更多回傳格式請參考 [Elasticsearch 官網](https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-rest-format.html)。 ### Translate 除了直接執行 SQL，X-Pack 的 SQL 功能還支援將 SQL 語法轉成 Elasticsearch 的 DSL。 ```json= POST _xpack/sql/translate { "query": """ """ } ``` **範例** ```json= POST _xpack/sql/translate { "query": """ select * from sport where assets > 100 """ } ``` 回傳的結果如下 : ```json= { "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 就可以進行查詢。如下 : ```json= 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 一樣不分大小寫。 ```sql= SELECT * FROM select * fRoM
``` 若一定要使用到關鍵保留字作為參數或者欄位名稱等等，需用 `雙引號` 括起來區分。 ```sql= SELECT "" FROM
``` ## SQL Command ### Describe Describe 用於顯示欄位的名稱和資料類型也就是 Mapping。 ```json= POST _xpack/sql?format=txt { "query": "describe
" } ``` **範例** ```json= POST _xpack/sql?format=txt { "query": "describe sport" } ``` 輸出結果如下 : ``` 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 結果都一樣。 ```json= POST _xpack/sql?format=txt { "query": "show columns
" } ``` **範例** ```json= POST _xpack/sql?format=txt { "query": "show columns in sport" } ``` 輸出結果如下，可以和上面的 Describe 比對結果完全一樣。 ``` 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 用於顯示有哪些表。 ```json= POST _xpack/sql?format=txt { "query": "show tables" } ``` 輸出結果如下 : ``` name | type ---------------+--------------- .kibana |ALIAS .kibana_1 |BASE TABLE sport |BASE TABLE ``` #### Show Functions Show Functions 用於列出所有可以使用的函式。 ```json= POST _xpack/sql?format=txt { "query": "show functions" } ``` 輸出結果如下 : ``` 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](https://www.elastic.co/guide/en/elasticsearch/reference/current/xpack-sql.html) ###### tags: `Elasticsearch` `NoSQL`