出自 图灵学院 ElasticSearch课程
我自己学完了,然后给老师的代码和讲义自己练习了一遍,然后整理了一下,做了个笔记

概述

Elasticsearch SQL允许执行类SQL的查询，可以使用REST接口、命令行或者是JDBC，都可以使用SQL来进行数据的检索和数据的聚合。

Elasticsearch SQL特点：

  • 本地集成

    Elasticsearch SQL是专门为Elasticsearch构建的。每个SQL查询都根据底层存储对相关节点有效执行。

  • 没有额外的要求

    不依赖其他的硬件、进程、运行时库，Elasticsearch SQL可以直接运行在Elasticsearch集群上

  • 轻量且高效

    像SQL那样简洁、高效地完成查询

SQL与Elasticsearch对应关系

SQL Elasticsearch
column（列） field（字段）
row（行） document（文档）
table（表） index（索引）
schema（模式） mapping（映射）
database server（数据库服务器） Elasticsearch集群实例

Elasticsearch SQL语法

  1. SELECT select_expr [, ...]
  2. [ FROM table_name ]
  3. [ WHERE condition ]
  4. [ GROUP BY grouping_element [, ...] ]
  5. [ HAVING condition]
  6. [ ORDER BY expression [ ASC | DESC ] [, ...] ]
  7. [ LIMIT [ count ] ]
  8. [ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

目前FROM只支持单表

Elasticsearch SQL目前的一些限制

目前Elasticsearch SQL还存在一些限制。例如：不支持JOIN、不支持较复杂的子查询。所以，有一些相对复杂一些的功能，还得借助于DSL方式来实现。

另外现在ElasticSearch中 还是dsl语句是主流语句,sql语句在ElasticSearch中不是那么的成熟.

案例

查询职位索引库中的一条数据

format：表示指定返回的数据类型
//1.查询职位信息

  1. GET /_sql
  2. {
  3. "query":"SELECT * FROM es_db limit 1"
  4. }

结果

  1. {
  2.   "columns" : [
  3.     {
  4.       "name" : "address",
  5.       "type" : "text"
  6.     },
  7.     {
  8.       "name" : "age",
  9.       "type" : "long"
  10.     },
  11.     {
  12.       "name" : "city",
  13.       "type" : "text"
  14.     },
  15.     {
  16.       "name" : "name",
  17.       "type" : "text"
  18.     },
  19.     {
  20.       "name" : "provice",
  21.       "type" : "text"
  22.     },
  23.     {
  24.       "name" : "remark",
  25.       "type" : "text"
  26.     },
  27.     {
  28.       "name" : "sex",
  29.       "type" : "long"
  30.     },
  31.     {
  32.       "name" : "street",
  33.       "type" : "text"
  34.     }
  35.   ],
  36.   "rows" : [
  37.     [
  38.       "广州荔湾大厦",
  39.       28,
  40.       null,
  41.       "李四",
  42.       null,
  43.       "java assistant",
  44.       1,
  45.       null
  46.     ]
  47.   ]
  48. }

结果返回表的格式

默认是返回json格式的结果

  1. GET /_sql?format=txt
  2. {
  3. "query":"SELECT * FROM es_db limit 1"
  4. }

结果

  1.     address    |      age      |     city      |     name      |    provice    |    remark     |      sex      |    street     
  2. ---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------
  3. 广州荔湾大厦         |28             |null           |李四             |null           |java assistant |1              |null

除了txt类型，Elasticsearch SQL还支持以下类型，

格式 描述
csv 逗号分隔符
json JSON格式
tsv 制表符分隔符
txt 类cli表示
yaml YAML人类可读的格式

使用tsv格式

  1. GET /_sql?format=tsv
  2. {
  3. "query":"SELECT * FROM es_db limit 1"
  4. }

结果

  1. address    age    city    name    provice    remark    sex    street
  2. 广州荔湾大厦    28        李四        java assistant    1

将SQL转换为DSL

将sql转成dsl语句, 这种使用场景是:比如说你MySQL用的非常的6 ,但是你不太会ElasticSearch的dsl语法,那么就可以把你想查询的效果用sql语句写出来,然后再翻译成dsl语句.

输入: 

  1. GET /_sql/translate
  2. {
  3. "query":"SELECT * FROM es_db where address  = '广州荔湾大厦'"
  4. }

结果:

  1. {
  2.   "size" : 1000,
  3.   "query" : {
  4.     "term" : {
  5.       "address.keyword" : {
  6.         "value" : "广州荔湾大厦",
  7.         "boost" : 1.0
  8.       }
  9.     }
  10.   },
  11.   "_source" : {
  12.     "includes" : [
  13.       "address",
  14.       "age",
  15.       "city",
  16.       "name",
  17.       "provice",
  18.       "remark",
  19.       "sex",
  20.       "street"
  21.     ],
  22.     "excludes" : [ ]
  23.   },
  24.   "sort" : [
  25.     {
  26.       "_doc" : {
  27.         "order" : "asc"
  28.       }
  29.     }
  30.   ]
  31. }

然后你把翻译的语句跟上 dsl语句的请求地址 比如说: GET /es_db/_search ,这样就可以使用了.

  2. GET /es_db/_search
  3. {
  4.   "size" : 1000,
  5.   "query" : {
  6.     "term" : {
  7.       "address.keyword" : {
  8.         "value" : "广州荔湾大厦",
  9.         "boost" : 1.0
  10.       }
  11.     }
  12.   },
  13.   "_source" : {
  14.     "includes" : [
  15.       "address",
  16.       "age",
  17.       "city",
  18.       "name",
  19.       "provice",
  20.       "remark",
  21.       "sex",
  22.       "street"
  23.     ],
  24.     "excludes" : [ ]
  25.   },
  26.   "sort" : [
  27.     {
  28.       "_doc" : {
  29.         "order" : "asc"
  30.       }
  31.     }
  32.   ]
  33. }

职位全文检索

检索address包含广州和name中包含张三的用户。

在执行全文检索时，需要使用到MATCH函数。

  1. MATCH(
  2.     field_exp,   
  3.     constant_exp 
  4.     [, options])

field_exp：匹配字段
constant_exp：匹配常量表达式

用sql语句可以这么写:

  1. GET /_sql?format=txt
  2. {
  3. "query":"select * from es_db where MATCH(address, '广州') or MATCH(name, '张三') limit 10"
  4. }

结果:

  1.     address    |      age      |     city      |     name      |    provice    |    remark     |      sex      |    street     
  2. ---------------+---------------+---------------+---------------+---------------+---------------+---------------+---------------
  3. 广州荔湾大厦         |28             |null           |李四             |null           |java assistant |1              |null           
  4. 广州白云山公园        |26             |null           |rod            |null           |php developer  |0              |null

通过Elasticsearch SQL方式实现分组统计

输入:

  1. GET /_sql?format=txt
  2. {
  3. "query":"select age, count(*) as age_cnt from es_db group by age"
  4. }

结果:

  1.       age      |    age_cnt    
  2. ---------------+---------------
  3. 19             |1              
  4. 22             |1              
  5. 25             |1              
  6. 26             |1              
  7. 28             |1

这种方式要更加直观、简洁。