关于elasticsearch:Elasticsearch-SQL-详解

Elasticsearch SQL 是一个 X-Pack 组件，容许用户应用相似 SQL 的语法在 ES 中进行查问。用户能够在 REST、JDBC、命令行中应用 SQL 在 ES 执行数据检索和数据聚合操作。ES SQL 有以下几个特点：

• 本地集成，SQL 模块是 ES 本人构建的，间接集成到公布的版本中。
• 不须要内部的组件，应用 SQL 模块不须要额定的依赖，如硬件、运行时库等。
• 轻量高效，SQL 模块不形象 ES 和其搜寻能力，而是裸露 SQL 接口，容许以雷同的申明性、简洁的形式进行适当的全文搜寻。

上面的内容咱们基于 ES 7.13 来学习一下 Elasticsearch SQL 模块提供的性能。

如果你对 ES 感兴趣，欢送订阅我的 Elasticsearch 从入门到实际小册，咱们一起学习提高！

一、Elasticsearch SQL 应用

在开始应用 SQL 模块提供的性能前，在 kibana 执行以下指令来创立数据：

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

导入数据实现后，能够执行上面的 SQL 进行数据搜寻了：

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

如上实例，应用 _sql 指明应用 SQL 模块，在 query 字段中指定要执行的 SQL 语句。应用 format 指定返回数据的格局，数据格式可选项有以下几个，它们都是见名识意的：

上述 SQL 执行的后果如下：

更多的返回格局，你能够本人尝试。

除了间接执行 SQL 外，还能够对后果进行过滤，应用 filter 字段在参数中指定过滤条件，能够应用规范的 ES DSL 查问语句过滤 SQL 运行的后果，其实例如下：

POST /_sql?format=txt
{
  "query": "SELECT * FROM library ORDER BY page_count DESC",
  "filter": {
    "range": {
      "page_count": {
        "gte" : 500,
        "lte" : 600
      }
    }
  },
  "fetch_size": 5
}

如上实例，其后果为：

     author     |     name      |  page_count   |      release_date      
----------------+---------------+---------------+------------------------
James S.A. Corey|Leviathan Wakes|561            |2011-06-02T00:00:00.000Z

另外能够应用 ‘?’ 占位符来传递参数，而后将参数和语句组装成残缺的 SQL 语句：

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 和 Elasticsearch SQL 概念映射关系

尽管 SQL 和 Elasticsearch 对于数据的组织形式 (以及不同的语义) 有不同的术语，但实质上它们的用处是雷同的。上面是它们的映射关系表：

尽管这些概念之间的映射在语义上有些不同，但它们间更多的是有共同点，而不是不同点。

三、SQL Translate API

SQL Translate API 接管 JSON 格局的 SQL 语句，而后将其转换为 ES 的 DSL 查问语句，然而这个语句不会被执行，咱们能够能够用这个 API 来将 SQL 翻译到 DSL 语句，其实例如下：

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

如上实例，翻译进去的 DSL 如下：

{
  "size": 10,
  "_source": false,
  "fields": [{ "field": "author"},
    {"field": "name"},
    {"field": "page_count"},
    {
      "field": "release_date",
      "format": "strict_date_optional_time_nanos"
    }
  ],
  "sort": [
    {
      "page_count": {
        "order": "desc",
        "missing": "_first",
        "unmapped_type": "short"
      }
    }
  ]
}

四、SQL 语法介绍

上面来学习一下 ES 提供的 SQL 语法和语义。

1、词法构造

ES SQL 的词法构造很大水平上相似于 ANSI SQL 自身。ES SQL 以后一次只能承受一个命令，这里的命令是由输出流结尾完结的 token 序列。这些 token 能够是关键字、标识符（带引号或者不带引号）、文本（或者常量）、特殊字符符号（通常是分隔符）。

1. 关键字
   关键词这个其实跟咱们写 SQL 语句那种关键字的定义是一样的，例如 SELECT、FROM 等都是关键字，须要留神的是，关键字不辨别大小写。

SELECT * FROM my_table

如上示例，共有 4 个 token：SELECT、、FROM、my_table，其中 SELECT、、FROM 是关键词，示意在 SQL 具备固定含意的词。而 my_table 是一个标识符，其示意了 SQL 中实体，如表、列等。

能够看到，关键词与标识符都有雷同的词汇构造，在 SQL 中长的差不多，有时候难以分辨。ES SQL 反对的关键字有很多这里就不一一列出了，你能够参考官网文档。

2. 标识符

标识符有两种类型：带引号的和不带引号的，示例如下：

SELECT ip_address FROM "hosts-*"

如上示例，查问中有两个标识符别离为不带引号的 ip_address 和带引号的 hosts-*（通配符模式）。因为 ip_address 不与任何关键字抵触，所以能够不带引号。而 hosts-* 与 –（减号操作）和 * 抵触，所以要加引号。

对于标识符来说，应该尽量避免应用简单的命名和与关键字抵触的命名，并且在输出的时候应用引号作为标识符，这样能够打消歧义。

3. 间接常量

ES SQL 反对两种隐式的类型常量：字符串和数字。

• 字符串，字符串能够用单引号进行限定，例如：’mysql’。如果在字符串中蕴含了单引号，则须要应用另一个单引号进行本义，例如：’Captain EO”s Voyage’。
• 数值常量，数值常量能够应用十进制和迷信计数法进行示意，其示例如下：

1969    -- integer notation
3.14    -- decimal notation
.1234   -- decimal notation starting with decimal point
4E5     -- scientific notation (with exponent marker)
1.2e-3  -- scientific notation with decimal point

一个蕴含小数点的数值常量会被解析为 Double 类型。如果适宜解析为整型，则解析为 Integer，否则解析为长整型（Long）。

4. 单引号、双引号

在 SQL 中，单引号和双引号具备不同的含意，不能调换应用。单引号用于申明字符串，而双引号用于示意标识符。示例如下：

SELECT "first_name" FROM "musicians"  WHERE "last_name"  = 'Carroll'    

如上示例，first_name、musicians、last_name 都是标识符，用双引号。而 Carroll 是字符串，用单引号。

5. 特殊字符

一些非数字和字母的字符具备不同于运算符的专用含意，特殊字符有：

6. 运算符

ES SQL 中大多数的运算符它们的优先级都是雷同的，并且是左关联。如果须要批改优先级，则要用括号来强制扭转其优先级。下表是 ES SQL 反对的运算符和其优先级：

7. 正文

ES SQL 反对两种正文：单行和多行正文，其示例如下：

-- single line comment，单行正文

/* multi
   line
   comment
   that supports /* nested comments */
   多行正文
   */

2、SQL 命令

上面来介绍 SQL 的命令。

1. DESCRIBE TABLE

应用此命令用来查看索引的构造，其语法如下：

DESCRIBE
    [table identifier | 
    [LIKE pattern]]     

第 1 行，能够对关键字 DESCRIBE 进行缩写为 DESC。
第 2 行，单表标识符或者双引号 ES 多索引模式。
第 3 行，SQL Like 匹配模式。

DESCRIBE 命令应用示例如下：

DESCRIBE table;

2. SELECT

这个其实咱们很相熟了，应用 SELECT 返回须要显示的列，其语法如下：

SELECT [TOP [ count] ] select_expr [, ...]
[FROM table_name]
[WHERE condition]
[GROUP BY grouping_element [, ...] ]
[HAVING condition]
[ORDER BY expression [ ASC | DESC] [, ...] ]
[LIMIT [ count] ]
[PIVOT ( aggregation_expr FOR column IN ( value [ [ AS] alias ] [, ...] ) ) ]

在 ES 中应用 SELECT 查问的语法与在数据库中应用基本一致，这里就不做形容了。

3. SHOW COLUMNS

应用 SHOW COLUMNS 命令能够列出表的所有列和其类型、其余属性，其语法如下：

SHOW COLUMNS [FROM | IN]?
    [table identifier | 
    [LIKE pattern] ] 

其应用示例如下：

SHOW COLUMNS IN emp;
SHOW COLUMNS IN emp LIKE 'birth_da%'; // 匹配 birth_da 结尾的列

4. SHOW FUNCTIONS

应用 SHOW FUNCTIONS 能够列出所有 SQL 反对的函数和其类型，LIKE 子句匹配对应的后果，其使语法如下：

SHOW FUNCTIONS [LIKE pattern?]? 

示例如下：

SHOW FUNCTIONS;
SHOW FUNCTIONS LIKE 'ABS'; // 准确匹配
SHOW FUNCTIONS LIKE 'A__'; // 一个 '_' 示意一个字符，所以准确匹配 A + 两个字符，如 AVG、ABS。SHOW FUNCTIONS LIKE '%DAY%'; // 匹配有 DAY 的函数

5. SHOW TABLES

咱们能够应用 SHOW TABLES 查看所有的表（ES 中为索引），其语法如下：

SHOW TABLES
    [INCLUDE FROZEN]?   
    [table identifier | 
    [LIKE pattern]]?   

简略的实例如下：

SHOW TABLES;
SHOW TABLES "*,-l*"; // 应用 ES multi-target syntax 进行匹配
SHOW TABLES LIKE 'emp'; // 准确匹配
SHOW TABLES LIKE 'emp%'; // 匹配 emp + 多个字符的表
SHOW TABLES LIKE 'em_'; // 匹配 em + 单个字符的表
SHOW TABLES LIKE '%em_'; // 匹配 多个字符 + em + 单个字符的表

6. 索引模式
   ES SQL 反对两种类型的模式匹配形式类匹配多个索引或者表：多索引模式和 LIKE 模式。

• 多索引模式
  反对应用通配符 * 或者排他匹配，示例如下：

  SHOW TABLES "*,-l*";

  多索引模式是通过 ES multi-target syntax 来进行反对的。

• LIKE 模式
  这个咱们就比拟相熟了，下面也很多例子，这里就不在赘述了。

五、ES SQL 应用实际

在应用 SQL 前，咱们先筹备数据，此处咱们将应用 Kibana 提供的航班数据：

如上图，在 Kibana 中点击右边栏的 Analytics 下的 Overview，弹出的页面中抉择 Sample data 的 Tab，而后点击 add data 按钮即可退出航班的数据。

能够应用以下语句查看航班数据的数据结构：

POST /kibana_sample_data_flights/_search
{"query": { "match_all": {} }
}

ok，上面来看看罕用的 SQL 如何编写。

1. WHERE

咱们过滤出目的地为 US 的数据：

POST /_sql?format=txt
{"query": "SELECT FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry ='US'"}

如上示例，对于用过 SQL 进行数据查问的你来说，必定不会生疏了，最初其后果为：

2. GROUP BY

能够应用 GROUP BY 语句对数据进行分组聚合统计操作，例如查问航班分组的均匀航行间隔等。其示例如下：

POST /_sql?format=txt
{"query": "SELECT count(*),max(DistanceMiles), avg(DistanceMiles) FROM kibana_sample_data_flights GROUP BY DestCountry"
}

如上示例，咱们以目的地国家进行分组，而后统计每个分组的数量、最大的航行间隔，均匀航行间隔。其后果如下：

3. HAVING

能够应用 HAVING 对分组的数据进行二次筛选，比方筛选分组中记录数大于 100 的数据，其示例如下：

POST /_sql?format=txt
{"query": "SELECT count(*),max(DistanceMiles), avg(DistanceMiles) FROM kibana_sample_data_flights GROUP BY DestCountry HAVING COUNT(*) > 100"
}

如上示例，咱们过滤出了分组中记录数大于 100 的数据，其后果如下：

4. ORDER BY

咱们能够应用 ORDER BY 进行排序，例如将均匀航行间隔降序排序，其示例如下：

POST /_sql?format=txt
{"query": "SELECT count(*),max(DistanceMiles), avg(DistanceMiles) as avgDistance FROM kibana_sample_data_flights GROUP BY DestCountry HAVING COUNT(*) > 100 ORDER BY avgDistance desc"
}

如上示例，咱们将数据用均匀间隔排序，其后果为：

5. 分页

分页有多种实现形式，能够应用 limit、top、fetch_size 来进行分页。

1、limit 分页操作

POST /_sql?format=txt
{"query": "SELECT FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry ='US'limit 10"}

2、应用 top 进行分页

POST /_sql?format=txt
{"query": "SELECT top 10 FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry ='US'"}

3、应用 fetch_size 进行分页

POST /_sql?format=txt
{"query": "SELECT FlightNum, OriginWeather, OriginCountry, Carrier FROM kibana_sample_data_flights WHERE DestCountry ='US'","fetch_size": 10}

6. 子查问

ES SQL 是能够反对相似于 SELECT X FROM (SELECT * FROM Y) 这样简略的子查问的，其示例如下：

POST /_sql?format=txt
{"query": "SELECT avg(data.DistanceMiles) from (SELECT FlightNum, OriginWeather, OriginCountry, Carrier, DistanceMiles FROM kibana_sample_data_flights WHERE DestCountry ='US') as data"
}

须要留神的是，可能简单一点的子查问会不被反对。更多的限度能够参考官网文档。

六、总结

本文具体介绍 ES SQL 的相干常识，整的来说，大部分都是基于官网文档进行直译过去的。更多对于 ES SQL 的应用形式，能够参考官网文档。