【Elasticsearch】使用 SQL 语句实现对 Elasticsearch 的查询_传入sql语句使用elasticsearchdatafreezer的queryrowbysql函数查

---

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

---

概述

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
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

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

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

在 kibana 操作如下图

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

---

Elasticsearch 和 SQL 的概念映射

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

---

SQL REST API

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

返回数据格式

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

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

实例

csv

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

 

json

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

 

tsv

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

 

txt

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

 

yaml

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

向查询传递参数

第一种写法

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"
}
1
2
3
4

第二种写法

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]
}
1
2
3
4
5

---

SQL TRANSLATE API

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

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

---

（未完待续…）