Elasticsearch - 使用 SQL 語法進行查詢

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。