HackMD
Elasticsearch - 使用 SQL 語法進行查詢
Image Not Showing
Possible Reasons
- The image file may be corrupted
- The server hosting the image is unavailable
- The image path is incorrect
- The image format is not supported
Elasticsearch 提供了一套功能強大的擴充套件 - X-Pack，X-Pack 包含了安全、警告、監視、圖形、報告等等的功能集合在一起。而其中也包含了使用 SQL 語法來查詢 Elasticsearch 的功能。
SQL REST API
SQL REST API 允許 SQL 透過 JSON 格式來執行和返回結果。
POST _xpack/sql?format=<format>
{
"query": """
<SQL_Query_Here>
"""
}
在 Kibana Console 上執行，建議可以使用三層雙引號將 SQL 語法括起來。三層雙引號的好處是 query 可以分成多行撰寫，這對於大部分人習慣將 SQL 語法分行排版來說是很棒的功能。
另外，URL 後面可以指定回傳資料的格式，就是上面範例的 format。較常使用格式的如下 :
- json
- csv
- txt
範例
POST _xpack/sql?format=json
{
"query": """
select *
from sport
where assets > 100
"""
}
上面的範例是指定以 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 官網。
Translate
除了直接執行 SQL，X-Pack 的 SQL 功能還支援將 SQL 語法轉成 Elasticsearch 的 DSL。
POST _xpack/sql/translate
{
"query": """
<SQL_Query_Here>
"""
}
範例
POST _xpack/sql/translate
{
"query": """
select *
from sport
where assets > 100
"""
}
回傳的結果如下 :
{
"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 就可以進行查詢。如下 :
GET sport/_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 一樣不分大小寫。
SELECT * FROM <table>
select * fRoM <table>
若一定要使用到關鍵保留字作為參數或者欄位名稱等等，需用 雙引號 括起來區分。
SELECT "<Field>" FROM <table>
SQL Command
Describe
Describe 用於顯示欄位的名稱和資料類型也就是 Mapping。
POST _xpack/sql?format=txt
{
"query": "describe <table>"
}
範例
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 結果都一樣。
POST _xpack/sql?format=txt
{
"query": "show columns <in|from> <table>"
}
範例
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 用於顯示有哪些表。
POST _xpack/sql?format=txt
{
"query": "show tables"
}
輸出結果如下 :
name | type
---------------+---------------
.kibana |ALIAS
.kibana_1 |BASE TABLE
sport |BASE TABLE
Show Functions
Show Functions 用於列出所有可以使用的函式。
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。
