0%

Elasticsearch (四) - 使用 SQL 語法進行查詢

xpack

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。較常使用格式的如下 :

  • json
  • csv
  • txt

範例

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