ElasticSearch SQL：简介

关于

除了使用 RESTful API 查询 Elasticsearch 索引数据，也可以使用 SQL 语句查询索引数据。

需要安装 X-Pack 扩展组件，才可以让 ES 支持 SQL 查询。（新版本的 ES 默认已经安装了 X-Pack）

SQL 和 ES

SQL 和 ES 的概念映射关系：

SQL	Elasticsearch	描述
column	field	SQL 中的列等价于 ES 中的字段
row	document	SQL 中的一行数据等价于 ES 的一条文档
table	index	SQL 的表跟 ES 中的索引名对应
schema	无	SQL 中的 schema 通常指的就是数据库，在 ES 中没有这个概念，可以简单的认为 ES 中一个 index 就是一个数据库

SQL 语法：

SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

SQL 语法结构跟 MySQL 类似都是 SQL 标准语法结构，区别就是 SQL 标准的支持程度。

ES支持的SQL命令

SHOW COLUMNS

查询 ES 索引的字段和类型

语法：

SHOW COLUMNS [ FROM | IN ]?
    [table identifier | 
    [LIKE pattern] ]

示例：

POST /_sql?format=txt
{
    "query": "SHOW COLUMNS FROM library"
}

输出：

    column     |     type      |    mapping    
---------------+---------------+---------------
author         |VARCHAR        |text           
author.keyword |VARCHAR        |keyword        
name           |VARCHAR        |text           
name.keyword   |VARCHAR        |keyword        
page_count     |BIGINT         |long           
release_date   |TIMESTAMP      |datetime

SHOW TABLES

列出 ES 中所有索引

示例：

POST /_sql?format=txt
{
    "query": "SHOW TABLES"
}

输出：

          name          |     type      |     kind      
------------------------+---------------+---------------
.apm-agent-configuration|BASE TABLE     |INDEX          
.kibana                 |VIEW           |ALIAS          
.kibana_1               |BASE TABLE     |INDEX          
.kibana_task_manager    |VIEW           |ALIAS          
.kibana_task_manager_1  |BASE TABLE     |INDEX          
library                 |BASE TABLE     |INDEX          
order                   |BASE TABLE     |INDEX                 
shops                   |BASE TABLE     |INDEX

SHOW FUNCTIONS

展示 ES 支持的 SQL 函数

示例：

POST /_sql?format=txt
{
    "query": "SHOW FUNCTIONS"
}

输出：

      name       |     type      
-----------------+---------------
AVG              |AGGREGATE      
COUNT            |AGGREGATE      
FIRST            |AGGREGATE      
FIRST_VALUE      |AGGREGATE      
LAST             |AGGREGATE      
LAST_VALUE       |AGGREGATE      
MAX              |AGGREGATE      
MIN              |AGGREGATE      
SUM              |AGGREGATE      
KURTOSIS         |AGGREGATE     
.....忽略.....

ES执行SQL语句

可以直接通过 REST API 执行 SQL 语句，语法格式如下：

POST /_sql?format=txt { "query": "这里书写SQL语句" }

其中，format 属性可以指定数据返回格式：

txt 格式：返回表格形式，便于阅读；
json 格式：代表返回 Json 格式，便于数据处理。

语句说明：

SELECT {查询字段} FROM {索引名} WHERE {查询条件}

示例：

准备测试数据：

PUT /library/_bulk?refresh
{"index":{"_id": "Leviathan Wakes"}}
{"name": "Leviathan Wakes", "author": "James S.A. Corey", "release_date": "2011-06-02", "page_count": 561}
{"index":{"_id": "Hyperion"}}
{"name": "Hyperion", "author": "Dan Simmons", "release_date": "1989-05-26", "page_count": 482}
{"index":{"_id": "Dune"}}
{"name": "Dune", "author": "Frank Herbert", "release_date": "1965-06-01", "page_count": 604}

执行SQL语句：

POST /_sql?format=txt
{
    "query": "SELECT * FROM library WHERE release_date < '2000-01-01'"
}

执行结果：

    author     |     name      |  page_count   |      release_date      
---------------+---------------+---------------+------------------------
Dan Simmons    |Hyperion       |482            |1989-05-26T00:00:00.000Z
Frank Herbert  |Dune           |604            |1965-06-01T00:00:00.000Z

全文搜索

ES SQL 语句中主要通过 MATCH 函数实现全文搜索。【SQL 中的“MATCH”函数！！！不是 ES 的“MATCH”关键字】

ES SQL 语法虽然支持 like 语句，但是 like 并不是使用全文搜索算法。

MATCH 函数

语法：

MATCH(
    field_exp,   
    constant_exp 
    [, options])

说明：

field_exp：需要搜索的字段
constant_exp：需要搜索的关键词
options：可选参数

示例：查询 library 索引中 author 字段，匹配 frank 关键词的文档

POST /_sql?format=txt
{
    "query": "SELECT author, name FROM library WHERE MATCH(author, 'frank')"
}

返回结果：

    author     |     name      
---------------+---------------
Frank Herbert  |Dune

SQL相关度排序

通过 SCORE() 函数实现相关度排序。

示例：

POST /_sql?format=txt
{
    "query": "SELECT author, name FROM library WHERE MATCH(author, 'frank') ORDER BY SCORE()"
}

也可以将相关度分数返回。

示例：

SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;