Elasticsearch SQL查询指南

好的，Elasticsearch 提供了强大的 SQL 查询功能，让你能够使用熟悉的 SQL 语法来查询和分析数据，而无需编写复杂的 Elasticsearch JSON DSL 查询。

以下是使用 Elasticsearch SQL 的详细指南，包括多种方法、示例和最佳实践。

核心概念

1. 索引 (Index) ↔ 表 (Table)： 在 SQL 术语中，一个 Elasticsearch 索引相当于一张数据库表。
2. 文档 (Document) ↔ 行 (Row)： 索引中的一个文档相当于表中的一行数据。
3. 字段 (Field) ↔ 列 (Column)： 文档中的字段相当于表中的列。
4. 映射 (Mapping) ↔ 模式 (Schema)： 字段的数据类型（如 text, keyword, long, date）由映射定义，相当于数据库的模式。

---

方法一：使用 SQL REST API (最常见)

这是最简单直接的方式，通过发送 HTTP 请求到 _sql 端点。

基本格式：

POST /_sql?format=<format>
{
  "query": "SELECT * FROM library ORDER BY page_count DESC LIMIT 5"
}

参数说明：

• format： 指定返回数据的格式。常用值有：
  • json (默认)
  • csv
  • txt (以文本表格形式展示，人类可读性最强)
  • tsv
  • yaml
• query： 要执行的 SQL 语句字符串。

示例

假设我们有一个名为 my_index 的索引，其中包含书籍信息。

1. 基础查询

# 查询所有字段（前10条记录）
POST /_sql?format=txt
{
  "query": "SELECT * FROM my_index"
}

# 查询特定字段
POST /_sql?format=txt
{
  "query": "SELECT title, author, price FROM my_index"
}

2. WHERE 条件过滤

# 查找作者是 "George R. R. Martin" 的书
POST /_sql?format=txt
{
  "query": "SELECT title, price FROM my_index WHERE author = 'George R. R. Martin'"
}

# 查找价格大于 20 的书
POST /_sql?format=txt
{
  "query": "SELECT title, price FROM my_index WHERE price > 20"
}

# 使用 AND/OR
POST /_sql?format=txt
{
  "query": "SELECT title, price FROM my_index WHERE price > 20 AND author LIKE '%Martin%'"
}

3. 聚合查询 (GROUP BY)

# 计算每位作者出的书的总数
POST /_sql?format=txt
{
  "query": "SELECT author, COUNT(*) as book_count FROM my_index GROUP BY author"
}

# 计算每位作者所出书籍的平均价格
POST /_sql?format=txt
{
  "query": "SELECT author, AVG(price) as avg_price FROM my_index GROUP BY author"
}

4. 排序和分页 (ORDER BY & LIMIT)

# 按价格降序排列，查看最贵的5本书
POST /_sql?format=txt
{
  "query": "SELECT title, author, price FROM my_index ORDER BY price DESC LIMIT 5"
}

# 分页 (使用 LIMIT <size> OFFSET <from>)
POST /_sql?format=txt
{
  "query": "SELECT title, price FROM my_index ORDER BY price DESC LIMIT 10 OFFSET 10"
}

5. 日期范围查询
假设有一个 publish_date 字段，类型为 date。

# 查询 2020 年以后出版的书
POST /_sql?format=txt
{
  "query": "SELECT title, publish_date FROM my_index WHERE publish_date > '2020-01-01'"
}

---

方法二：使用 Translate API

如果你想知道你的 SQL 语句被转换成了什么样的原生 Elasticsearch 查询 DSL，可以使用 Translate API。

POST /_sql/translate
{
  "query": "SELECT author, COUNT(*) FROM my_index GROUP BY author"
}

Elasticsearch 会返回一个 JSON，其中包含转换后的 ES DSL 查询。这对于学习和调试非常有帮助。

---

方法三：在 Kibana 中使用

Kibana 提供了集成的界面来执行 Elasticsearch SQL 查询。

1. 打开 Kibana。
2. 左侧导航栏，进入 Management > Dev Tools。
3. 在 Console 标签页中，你可以直接输入 REST API 请求。

你还可以使用 Kibana 的 Discover 功能，通过 “Use SQL” 选项切换到 SQL 模式进行查询和可视化。

---

重要注意事项和限制

1. 字段类型与 SQL 类型： Elasticsearch 的 text 字段默认会被分词（用于全文搜索），而 keyword 字段用于精确匹配和聚合。在 SQL 查询中，对 text 字段进行 GROUP BY 或 = 过滤可能会失败，通常需要使用 keyword 子字段（通常是 fieldname.keyword）。

   • 错误示例： SELECT * FROM my_index WHERE title = 'Quick Brown Fox' (如果 title 是 text 类型，可能匹配不到)
   • 正确示例： SELECT * FROM my_index WHERE title.keyword = 'The Quick Brown Fox'

2. 索引模式： FROM 子句支持通配符来匹配多个索引，类似于 SQL 中的表名模式匹配。

   • FROM logstash-2023.10.*
   • FROM my_index,my_other_index

3. 不支持的功能：

   • DELETE / UPDATE： Elasticsearch SQL 主要用于查询，不支持 DELETE、UPDATE、INSERT 等数据操作语句 (DML)。数据变更仍需使用 Elasticsearch 的索引/更新 API。
   • JOINs： 不支持跨索引的 JOIN（关联查询）。所有数据必须来自单个索引（或匹配相同映射的多个索引）。
   • 存储过程/事务： 不支持数据库事务或存储过程。

4. 性能： 对于非常复杂的聚合和查询，原生 DSL 可能提供更多优化选项。但对于大多数常见场景，SQL 的性能已经足够好，并且其可读性优势巨大。

总结

特性	描述
优点	学习成本低，对于熟悉 SQL 的用户非常友好；查询语句简洁明了。
适用场景	即席查询 (Ad-hoc)、数据探索、生成报表、以及希望用 SQL 快速原型开发的场景。
不适用场景	需要更新/删除数据、进行跨索引关联查询、使用非常底层的 Elasticsearch 特有功能。
核心方法	使用 POST /_sql?format=txt REST API。
关键工具	cURL, Kibana Console, 任何 HTTP 客户端。

通过 Elasticsearch SQL，你可以极大地简化对 Elasticsearch 中数据的查询过程，让你能更专注于数据本身而非复杂的查询语法。