ElasticSearch的sql语法说明和简单使用

出自 图灵学院 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语法

SELECT 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 ] [, ...] ) ) ]

目前FROM只支持单表
​

Elasticsearch SQL目前的一些限制

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

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

案例

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

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

GET /_sql
{
"query":"SELECT * FROM es_db limit 1"
}

结果

{
  "columns" : [
    {
      "name" : "address",
      "type" : "text"
    },
    {
      "name" : "age",
      "type" : "long"
    },
    {
      "name" : "city",
      "type" : "text"
    },
    {
      "name" : "name",
      "type" : "text"
    },
    {
      "name" : "provice",
      "type" : "text"
    },
    {
      "name" : "remark",
      "type" : "text"
    },
    {
      "name" : "sex",
      "type" : "long"
    },
    {
      "name" : "street",
      "type" : "text"
    }
  ],
  "rows" : [
    [
      "广州荔湾大厦",
      28,
      null,
      "李四",
      null,
      "java assistant",
      1,
      null
    ]
  ]
}

结果返回表的格式

默认是返回json格式的结果
​

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

结果

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

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

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

使用tsv格式

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

结果

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

将SQL转换为DSL

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

输入:

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

结果:

{
  "size" : 1000,
  "query" : {
    "term" : {
      "address.keyword" : {
        "value" : "广州荔湾大厦",
        "boost" : 1.0
      }
    }
  },
  "_source" : {
    "includes" : [
      "address",
      "age",
      "city",
      "name",
      "provice",
      "remark",
      "sex",
      "street"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

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

GET /es_db/_search
{
  "size" : 1000,
  "query" : {
    "term" : {
      "address.keyword" : {
        "value" : "广州荔湾大厦",
        "boost" : 1.0
      }
    }
  },
  "_source" : {
    "includes" : [
      "address",
      "age",
      "city",
      "name",
      "provice",
      "remark",
      "sex",
      "street"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

职位全文检索

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

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

MATCH(
    field_exp,   
    constant_exp 
    [, options]) 

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

用sql语句可以这么写:

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

结果:
​

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

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

输入:

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

结果:

      age      |    age_cnt    
---------------+---------------
19             |1              
22             |1              
25             |1              
26             |1              
28             |1              

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