Elasticsearch SQL 是一个X-Pack组件,允许对Elasticsearch实时执行类似SQL的查询,为Elasticsearch提供一个强大而轻量的SQL接口。

无论使用REST接口,命令行还是JDBC,任何客户机都可以使用SQL对EsasticSearch进行查询。Elasticsearch SQL是一个将SQL转换为DQL的转换器。主要针对于查询操作。

约定

很多人习惯地把ElasticSerach和数据库进行类比,如 一个ES索引等同于一张数据库表,一条Document记录等同于一行数据库表记录,一个字段等同于表的一个属性....

在Elasticsearch SQL这里,这些类比的合适的,更方便理解SQL和DQL的映射。

SQL和ElasticSerach的映射关系

SQL

ElasticSerach

说明

column

field

一个字段或属性

row

document

一条完整的数据

table

index

schema

implicit

SQL REST API

SQL REST API 接收json格式的SQL,执行并返回结果,和传统DSL语法详细,形式相同

如:

POST /_sql?format=txt
{
"query": """
show tables
"""
}

查询

POST /_sql?format=txt
{
"query": """
SELECT key,price,title FROM "dang-dang" order by price desc limit 6
"""
}

结果:

key      |     price     |                            title                            
---------------+---------------+-------------------------------------------------------------
java1 |¥99.80 |Java从入门到项目实战(全程视频版)
C2 |¥99.80 |C语言从入门到精通(彩印版)案例视频版 编程入门/IT计算机书籍
C3 |¥99.80 |C语言从入门到项目实战(全程视频版)
浏览器5 |¥99.78 |腾讯Android自动化测试实战-汇集QQ浏览器应用宝等亿级APP自动化测试精髓【正版图书,达额立减】
C5 |¥99.70 |Unity与C++网络游戏开发实战:基于VR、AI与分布式架构
架构设计4 |¥99.44 |[现货] 微服务架构设计模式 [美] 克里斯・理查森(Chris Richardson) 9787111624127 机

返回的数据格式format

format

Axxept HTTP Header

说明

csv

text/csv

逗号分隔

json

application/json

JSON格式

tsv

text/tab-separated-values

tab分隔

txt

text/pain

文本格式

yaml

application/yaml

yaml格式

vbor

application/vbor

简洁的二进制

smile

application/smile

类似cbor的二进制

支持的参数

参数名称

默认值

说明

query

Mandatory

传递SQL语句

filter

none

可选的查询DSL,用于过滤query的执行结果

fetch_size

1000

获取结果的最大数量

request_timeout

90s

请求超时时间

page_timeout

45s

分页请求超时时间

time_zone

UTC

时区

filed_multi_value_leniency

false

当遇到一个字段多个值时抛出异常,或者从列表中返回第一个值

filter-过滤结果

POST /_sql?format=txt
{
"query": """
SELECT key,price,title FROM "dang-dang" limit 20
""",
"filter":{
"range": {
"price": {
"gte": 100
}
}
}
}

SQL Translate API

translate 接受JSON格式的SQL,并将其转换为ES查询DSL,一帮用来了解SQL背后转换原理。

POST /_sql/translate
{
"query": """
SELECT key,price,title FROM "dang-dang" limit 20
"""
}

响应:

{
"size" : 20,
"_source" : false,
"fields" : [
{
"field" : "key"
},
{
"field" : "price"
},
{
"field" : "title"
}
],
"sort" : [
{
"_doc" : {
"order" : "asc"
}
}
]
}

SQL

ES SQL 支持一般的SQL查询语法和关键字,如AND,IN,IS,JOIN,LIKE...

标识符

标识符可以有两种类型:带引号和不带引号的

1 不带引号

查询的目标字段和条件均不是关键字,不需要带引号

SELECT title FROM "dang-*"

使用场景:某一类型的索引是 dang-202108,dang-202107,dang-202106,由于索引数据太大,选择按月创建索引,此时需要从多个索引查询同一属性。

2 带引号

由于要查询的字段 from是关键字,需要用引号包起来,否则会和FROM冲突;

第二个因为包含了数学日期,会使解析器混淆,也需要包起来。

SELECT "from" FROM "<logstash-{now/d}>"

单引号和双引号

在SQL中,单引号和双引号具有不同的含义,不能相互转换;

  • 单引号:声明字符串
  • 双引号:声明标识符

示例:

SELECT "first_name"
FROM "musicians"
WHERE "last_name" = 'Carroll'
  • first_name是列名
  • musicians是表名(索引)
  • last_name是列名
  • Carroll是字符串

特殊字符

  • | 在某些上下文中用于表示占位符,也可用于某些聚合函数的参数
    , | 用于枚举列表的元素
    . | 用户数字常量或分隔标识符限定符(目录,表,列名等)
    ()| 用于特定的SQL命令,函数声明或控制优先级

注释

ElasticSearch SQL 允许两种注释

  • 单行注释 --
  • 多行注释 /**/