# Elasticsearch (四) - 使用 SQL 語法進行查詢
Elasticsearch 提供了一套功能強大的擴充套件 - [X-Pack](https://www.elastic.co/subscriptions)，X-Pack 包含了安全、警告、監視、圖形、報告等等的功能集合在一起。而其中也包含了使用 SQL 語法來查詢 Elasticsearch 的功能。
<!-- more -->
## SQL REST API
SQL REST API 允許 SQL 透過 JSON 格式來執行和返回結果。
```json=
POST _xpack/sql?format=<format>
{
"query": """
<SQL_Query_Here>
"""
}
```
在 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": """
<SQL_Query_Here>
"""
}
```
**範例**
```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 <table>
select * fRoM <table>
```
若一定要使用到關鍵保留字作為參數或者欄位名稱等等，需用 `雙引號` 括起來區分。
```sql=
SELECT "<Field>" FROM <table>
```
## SQL Command
### Describe
Describe 用於顯示欄位的名稱和資料類型也就是 Mapping。
```json=
POST _xpack/sql?format=txt
{
"query": "describe <table>"
}
```
**範例**
```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 <in|from> <table>"
}
```
**範例**
```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`
Google Drive
Gist
Clipboard
Sign in via Facebook
Sign in via X(Twitter)
Sign in via GitHub
Sign in via Dropbox
Sign in with Wallet
