Elasticsearch SQL

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 允许两种注释

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