【Elasticsearch】使用 SQL 语句实现对 Elasticsearch 的查询

---

本文内容基本都是从官网文档整理而来，工作不易，转载请声明

---

概述

Elasticsearch SQL 是为了给 Elasticsearch 提供一个强大而轻量级的 SQL 接口而存在的。
Elasticsearch SQL 是一个 X-Pack 组件 （也就意味的你的Elasticsearch版本必须安装了 X-Pack 组件），它允许根据 Elasticsearch 实时执行类似 SQL 的查询。无论使用 REST 接口、命令行还是 JDBC ，任何客户机都可以使用 SQL 在 Elasticsearch 内部本机搜索和聚合数据。我们可以把 Elasticsearch SQL 看作一个翻译器，它既能理解 SQL ，又能理解 Elasticsearch ，并通过利用 Elasticsearch 功能，使其易于实时读取和处理数据。

---

使用

使用 Elasticsearch SQL 可以现在 kibana 自己创建一个 index

PUT /library/book/_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
}

接下来就可以使用 Rest API 的方式来查询

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

在 kibana 操作如下图

也可以在 Elasticsearch 安装包的 bin 目录下的 elasticsearch-sql-cli 启动客户端使用 SQL 查询

---

Elasticsearch 和 SQL 的概念映射

虽然SQL和Elasticsearch对于数据的组织方式有不同的术语(以及不同的语义)，但本质上它们的目的是相同的。

SQL	Elasticsearch	Description（说明）
column（列）	field（字段）	Elasticsearch 中的字段对应 SQL 中的列
row（行）	document（文档）	列和字段本身不存在;它们是一行或文档的一部分。两者在语义上略有不同:行趋向于严格(并具有更多的强制)，而文档趋向于灵活或松散(同时仍然具有结构)
table（表）	index（索引）	针对 SQL 或 Elasticsearch 中的查询执行的目标。index 就是 SQL 中的表
schema	implicit	在 RDBMS（传统关系型数据库） 中，schema主要是表的名称空间，通常用作安全边界。弹性搜索并没有提供一个等价的概念。但是，当启用安全性时，Elasticsearch会自动应用安全性增强，以便角色只看到允许它查看的数据(在SQL术语中，是它的模式)
… …	… …	… …

---

SQL REST API

基本使用在前面已经有了，如果要在 kibana 控制台使用，还可以用下图的写法

返回数据格式

虽然文本格式很适合人类使用，但计算机更喜欢结构化的格式。
Elasticsearch SQL 可以返回以下格式的数据，这可以通过设置的格式属性在 URL 或通过设置接受 HTTP 头:

URL参数优先于 Accept HTTP 头。如果两者都没有指定，那么响应将以与请求相同的格式返回。

format	Accept HTTP header	Description
csv	text/csv	逗号分隔值
json	application/json	人类可读的 JSON （ JavaScript 对象符号）格式
tsv	text/tab-separated-values	制表符分隔的值
txt	text/plain	CLI-like representation
yaml	application/yaml	YAML （ YAML 不是标记语言）人类可读的格式
cbor	application/cbor	简洁的二进制对象表示
smile	application/smile	类似 CBOR 的微笑二进制数据格式

实例

csv

POST /_sql?format=csv
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 2  // 相当于 limit 2
}

 

json

POST /_sql?format=json
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 1
}

 

tsv

POST /_sql?format=tsv
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 2
}

 

txt

POST /_sql?format=txt
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 2
}

 

yaml

POST /_sql?format=yaml
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "fetch_size": 2
}

向查询传递参数

第一种写法

POST /_sql?format=txt
{
	"query": "SELECT YEAR(release_date) AS year FROM library WHERE page_count > 300 AND author = 'Frank Herbert' GROUP BY year HAVING COUNT(*) > 0"
}

第二种写法

POST /_sql?format=txt
{
	"query": "SELECT YEAR(release_date) AS year FROM library WHERE page_count > ? AND author = ? GROUP BY year HAVING COUNT(*) > ?",
	"params": [300, "Frank Herbert", 0]
}

---

SQL TRANSLATE API

SQL Translate API 接受 JSON 文档中的 SQL，并将其转换为原生的 Elasticsearch 查询。例如:

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

---

（未完待续…）