Elasticsearch 6.3 X-PACK SQL Overview

Elasticsearch 6.3 X-PACK SQL Overview

Elasticsearch SQL aims to provide a powerful yet lightweight SQL interface to Elasticsearch

写在前面

However the backing engine itself is Elasticsearch for which Elasticsearch SQL was purposely created hence why features or concepts that are not available, or cannot be mapped correctly, in SQL appear in Elasticsearch SQL. Last but not least, Elasticsearch SQL tries to obey the principle of least suprise, though as all things in the world, everything is relative.

Elasticsearch终究是Elasticsearch，不能够完全兼容SQL。开发者们尽量准守principle of least suprise(最小惊吓原则)来开发Elasticsearch SQL。

Overview

X-PACK SQL支持以REST API、SQL CLI客户端以及JDBC的形式连接Elasticsearch。

Mapping concepts across SQL and Elasticsearch

SQL	Elasticsearch
column	field
row	document
table	index
schema	implicit
database	cluster

SQL REST API

支持txt,json,yaml,smile,cbor,csv

1. Format is txt

   POST /_xpack/sql?format=txt
   {
     "query": "SELECT date, domain FROM sinadpool_nginx* LIMIT 5"
   }

             date          |           domain            
   ------------------------+-----------------------------
   2018-08-02T00:00:00.000Z|wgw.city.sina.com.cn
   2018-08-02T00:00:00.000Z|php.weather.sina.com.cn
   2018-08-02T00:00:00.000Z|vip.stock.finance.sina.com.cn
   2018-08-02T00:00:00.000Z|guba.sina.com.cn
   2018-08-02T00:00:00.000Z|i.search.sina.com.cn

2. Format is json

   POST /_xpack/sql
   {
     "query": "SELECT * FROM sinadpool_nginx* LIMIT 1"
   }

   {
     "columns": [
       {
         "name": "date",
         "type": "date"
       },
       {
         "name": "domain",
         "type": "keyword"
       }
     ],
     "rows": [
       [
         "2018-08-02T00:00:00.000Z",
         "stock2.finance.sina.com.cn"
       ]
     ]
   }

SQL Translate API

将SQL语句转换为Elasticsearch查询语法

POST /_xpack/sql/translate
{
    "query": "SELECT * FROM library ORDER BY page_count DESC",
    "fetch_size": 10
}

SQL CLI

$ ./bin/elasticsearch-sql-cli https://some.server:9200

SQL JDBC

这部分未进一步探索，因为这个功能是收费的

<dependency>
  <groupId>org.elasticsearch.plugin</groupId>
  <artifactId>jdbc</artifactId>
  <version>6.3.2</version>
</dependency>

存在的问题

1. SQL不支持’-‘字符，需要转义

   POST /_xpack/sql?format=txt
   {
   "query": "SELECT date, domain FROM \"sinadpool_nginx-2018.08.02\" LIMIT 5"
   }

2. SQL Translate API 不支持索引不存在的查询条件

   

3. 暂不支持JOIN

4. 对较为复杂的SQL解析有问题
   以下结果一致，且与预期查询结果不一致

   POST /_xpack/sql/translate
   {
     "query": "select * from (select domain, count(1) as C from sinadpool_nginx* where date='2018-08-01' group by domain) a order by C LIMIT 10"
   }

   POST /_xpack/sql/translate
   {
     "query": "select domain, count(1) as C from sinadpool_nginx* where date='2018-08-01' group by domain LIMIT 10"
   }

Reference

SQL Access