查看“ElasticSearch SQL：简介”的源代码

[[category:ElasticSearch教程]]

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


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

== SQL 和 ES ==
SQL 和 ES 的概念映射关系：
{| class="wikitable"
! SQL !! Elasticsearch !! 描述 
|-
| column || field || SQL 中的列等价于 ES 中的字段
|-
| row || document || SQL 中的一行数据等价于 ES 的一条文档
|-
| table || index || SQL 的表跟 ES 中的索引名对应
|-
| schema || 无 || SQL 中的 schema 通常指的就是数据库，在 ES 中没有这个概念，可以简单的认为 ES 中一个 index 就是一个数据库
|}


SQL 语法：
<syntaxhighlight lang="SQL" highlight="">
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 ] [, ...] ) ) ]
</syntaxhighlight>
* SQL 语法结构跟 MySQL 类似都是 SQL 标准语法结构，区别就是 SQL 标准的支持程度。


== ES支持的SQL命令 ==

=== SHOW COLUMNS ===
'''查询 ES 索引的字段和类型'''

语法：
<syntaxhighlight lang="JSON" highlight="">
SHOW COLUMNS [ FROM | IN ]?
    [table identifier | 
    [LIKE pattern] ]    
</syntaxhighlight>


示例：
<syntaxhighlight lang="JSON" highlight="">
POST /_sql?format=txt
{
    "query": "SHOW COLUMNS FROM library"
}
</syntaxhighlight>
输出：
<syntaxhighlight lang="JSON" highlight="">
    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 
</syntaxhighlight>

=== SHOW TABLES ===
'''列出 ES 中所有索引'''


示例：
<syntaxhighlight lang="JSON" highlight="">
POST /_sql?format=txt
{
    "query": "SHOW TABLES"
}
</syntaxhighlight>
输出：
<syntaxhighlight lang="JSON" highlight="">
          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   
</syntaxhighlight>

=== SHOW FUNCTIONS ===
'''展示 ES 支持的 SQL 函数'''


示例：
<syntaxhighlight lang="JSON" highlight="">
POST /_sql?format=txt
{
    "query": "SHOW FUNCTIONS"
}
</syntaxhighlight>
输出：
<syntaxhighlight lang="JSON" highlight="">
      name       |     type      
-----------------+---------------
AVG              |AGGREGATE      
COUNT            |AGGREGATE      
FIRST            |AGGREGATE      
FIRST_VALUE      |AGGREGATE      
LAST             |AGGREGATE      
LAST_VALUE       |AGGREGATE      
MAX              |AGGREGATE      
MIN              |AGGREGATE      
SUM              |AGGREGATE      
KURTOSIS         |AGGREGATE     
.....忽略.....
</syntaxhighlight>

== ES执行SQL语句 ==
可以直接'''通过 REST API 执行 SQL 语句'''，语法格式如下：
'''<syntaxhighlight lang="JSON" highlight="">
POST /_sql?format=txt
{
    "query": "这里书写SQL语句"
}
</syntaxhighlight>'''
其中，'''format''' 属性可以指定数据返回格式：
* '''txt''' 格式：返回表格形式，便于阅读；
* '''json''' 格式：代表返回 Json 格式，便于数据处理。

语句说明：
<syntaxhighlight lang="JSON" highlight="">
SELECT {查询字段} FROM {索引名} WHERE {查询条件}
</syntaxhighlight>


示例：
# 准备测试数据：
#: <syntaxhighlight lang="JSON" highlight="">
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}
</syntaxhighlight>
# 执行SQL语句：
#: <syntaxhighlight lang="JSON" highlight="">
POST /_sql?format=txt
{
    "query": "SELECT * FROM library WHERE release_date < '2000-01-01'"
}
</syntaxhighlight>
# 执行结果：
#: <syntaxhighlight lang="JSON" highlight="">
    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
</syntaxhighlight>

== 全文搜索 ==
ES SQL 语句中主要通过 '''MATCH 函数'''实现全文搜索。【SQL 中的“MATCH”函数！！！不是 ES 的“MATCH”关键字】
* ES SQL 语法虽然支持 like 语句，但是 like 并不是使用全文搜索算法。

=== MATCH 函数 ===
语法：
<syntaxhighlight lang="JSON" highlight="">
MATCH(
    field_exp,   
    constant_exp 
    [, options]) 
</syntaxhighlight>
说明：
# '''field_exp'''：需要搜索的字段
# '''constant_exp'''：需要搜索的关键词
# '''options'''：可选参数


示例：查询 library 索引中 author 字段，匹配 frank 关键词的文档
<syntaxhighlight lang="JSON" highlight="">
POST /_sql?format=txt
{
    "query": "SELECT author, name FROM library WHERE MATCH(author, 'frank')"
}
</syntaxhighlight>
返回结果：
<syntaxhighlight lang="JSON" highlight="">
    author     |     name      
---------------+---------------
Frank Herbert  |Dune  
</syntaxhighlight>

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


示例：
<syntaxhighlight lang="JSON" highlight="">
POST /_sql?format=txt
{
    "query": "SELECT author, name FROM library WHERE MATCH(author, 'frank') ORDER BY SCORE()"
}
</syntaxhighlight>


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

示例：
<syntaxhighlight lang="SQL" highlight="">
SELECT SCORE(), * FROM library WHERE MATCH(name, 'dune') ORDER BY SCORE() DESC;
</syntaxhighlight>