ElasticSearch 7 SQL 详解

最新推荐文章于 2024-04-30 16:20:46 发布
最新推荐文章于 2024-04-30 16:20:46 发布
阅读量1.6k 收藏 2
点赞数 1
分类专栏： 数据库 文章标签： elasticsearch sql jenkins
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wjw465150/article/details/134583672
版权

平时使用Elasticsearch的时候,会在Kibana中使用Query DSL来查询数据.每次要用到Query DSL时都基本忘光了,需要重新在回顾一遍,最近发现Elasticsearch已经支持SQL查询了(6.3版本以后),整理了下一些用法.

简介

Elasticsearch SQL是一个X-Pack组件,它允许针对Elasticsearch实时执行类似SQL的查询.无论使用REST接口,命令行还是JDBC,任何客户端都可以使用SQL对Elasticsearch中的数据进行原生搜索和聚合数据.可以将Elasticsearch SQL看作是一种翻译器,它可以将SQL翻译成Query DSL.

Elasticsearch SQL具有如下特性:

  • 原生支持:Elasticsearch SQL是专门为Elasticsearch打造的.
  • 没有额外的零件:无需其他硬件,处理器,运行环境或依赖库即可查询Elasticsearch,Elasticsearch SQL直接在Elasticsearch内部运行.
  • 轻巧高效:Elasticsearch SQL并未抽象化其搜索功能,相反的它拥抱并接受了SQL来实现全文搜索,以简洁的方式实时运行全文搜索.

准备

先安装好Elasticsearch和Kibana,这里安装的是7.17.0版本

安装完成后在Kibana中 http://127.0.0.1:5601/app/dev_tools#/console{target=“_blank”}
导入测试数据,数据地址: https://github.com/macrozheng/mall-learning/blob/master/document/json/accounts.json{target=“_blank”}

直接在Kibana的Dev Tools中运行如下命令即可:

POST /account/_bulk
{"index":{"_id":"1"}}
{"account_number":1,"balance":39225,"firstname":"Amber","lastname":"Duke","age":32,"gender":"M","address":"880 Holmes Lane","employer":"Pyrami","email":"amberduke@pyrami.com","city":"Brogan","state":"IL"}
{"index":{"_id":"6"}}
{"account_number":6,"balance":5686,"firstname":"Hattie","lastname":"Bond","age":36,"gender":"M","address":"671 Bristol Street","employer":"Netagy","email":"hattiebond@netagy.com","city":"Dante","state":"TN"}
{"index":{"_id":"13"}}
{"account_number":13,"balance":32838,"firstname":"Nanette","lastname":"Bates","age":28,"gender":"F","address":"789 Madison Street","employer":"Quility","email":"nanettebates@quility.com","city":"Nogal","state":"VA"}
{"index":{"_id":"18"}}
{"account_number":18,"balance":4180,"firstname":"Dale","lastname":"Adams","age":33,"gender":"M","address":"467 Hutchinson Court","employer":"Boink","email":"daleadams@boink.com","city":"Orick","state":"MD"}
{"index":{"_id":"20"}}
{"account_number":20,"balance":16418,"firstname":"Elinor","lastname":"Ratliff","age":36,"gender":"M","address":"282 Kings Place","employer":"Scentric","email":"elinorratliff@scentric.com","city":"Ribera","state":"WA"}
{"index":{"_id":"25"}}
{"account_number":25,"balance":40540,"firstname":"Virginia","lastname":"Ayala","age":39,"gender":"F","address":"171 Putnam Avenue","employer":"Filodyne","email":"virginiaayala@filodyne.com","city":"Nicholson","state":"PA"}
{"index":{"_id":"32"}}
{"account_number":32,"balance":48086,"firstname":"Dillard","lastname":"Mcpherson","age":34,"gender":"F","address":"702 Quentin Street","employer":"Quailcom","email":"dillardmcpherson@quailcom.com","city":"Veguita","state":"IN"}
{"index":{"_id":"37"}}
{"account_number":37,"balance":18612,"firstname":"Mcgee","lastname":"Mooney","age":39,"gender":"M","address":"826 Fillmore Place","employer":"Reversus","email":"mcgeemooney@reversus.com","city":"Tooleville","state":"OK"}
{"index":{"_id":"44"}}
{"account_number":44,"balance":34487,"firstname":"Aurelia","lastname":"Harding","age":37,"gender":"M","address":"502 Baycliff Terrace","employer":"Orbalix","email":"aureliaharding@orbalix.com","city":"Yardville","state":"DE"}
{"index":{"_id":"49"}}
{"account_number":49,"balance":29104,"firstname":"Fulton","lastname":"Holt","age":23,"gender":"F","address":"451 Humboldt Street","employer":"Anocha","email":"fultonholt@anocha.com","city":"Sunriver","state":"RI"}

第一个SQL查询

我们使用SQL来查询下前10条记录,可以通过format参数控制返回结果的格式,txt表示文本格式,看起来更直观点,默认为json格式.

在Kibana的Dev Tools中输入如下命令:

POST /_sql?format=txt
{
  "query": "SELECT account_number,address,age,balance FROM account LIMIT 10"
}

查询结果显示如下.

account_number |      address       |      age      |    balance    
---------------+--------------------+---------------+---------------
1              |880 Holmes Lane     |32             |39225          
6              |671 Bristol Street  |36             |5686           
13             |789 Madison Street  |28             |32838          
18             |467 Hutchinson Court|33             |4180           
20             |282 Kings Place     |36             |16418          
25             |171 Putnam Avenue   |39             |40540          
32             |702 Quentin Street  |34             |48086          
37             |826 Fillmore Place  |39             |18612          
44             |502 Baycliff Terrace|37             |34487          
49             |451 Humboldt Street |23             |29104

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

formatAccept Http header说明
csvtext/csv逗号分隔
jsonapplication/jsonJson 格式
tsvtext/tab-separated-valuestab 分隔
txttext/plain文本格式
yamlapplication/yamlyaml
cborapplication/cbor简洁的二进制对象表示格式
smileapplication/smile类似于 cbor 的另一种二进制格式

将SQL转化为DSL

当我们需要使用Query DSL时,也可以先使用SQL来查询,然后通过Translate API转换即可.

例如我们翻译以下查询语句:

POST /_sql/translate
{
  "query": "SELECT account_number,address,age,balance FROM account WHERE age>32 LIMIT 10"
}

最终获取到Query DSL结果如下.

{
  "size" : 10,
  "query" : {
    "range" : {
      "age" : {
        "from" : 32,
        "to" : null,
        "include_lower" : false,
        "include_upper" : false,
        "boost" : 1.0
      }
    }
  },
  "_source" : false,
  "fields" : [
    {
      "field" : "account_number"
    },
    {
      "field" : "address"
    },
    {
      "field" : "age"
    },
    {
      "field" : "balance"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

然后可以用Query DSL 语法来查询:

GET /account/_search
{
  "size": 10,
  "query": {
    "range": {
      "age": {
        "from": 32,
        "to": null,
        "include_lower": false,
        "include_upper": false,
        "boost": 1
      }
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "account_number"
    },
    {
      "field": "address"
    },
    {
      "field": "age"
    },
    {
      "field": "balance"
    }
  ],
  "sort": [
    {
      "_doc": {
        "order": "asc"
      }
    }
  ]
}

SQL和DSL混合使用

我们还可以将SQL和Query DSL混合使用,比如使用Query DSL来设置过滤条件.

例如查询 age在30-35 之间的记录,可以使用如下查询语句:

POST /_sql?format=txt
{
  "query": "SELECT account_number,address,age,balance FROM account",
  "filter": {
    "range": {
      "age": {
        "gte": 30,
        "lte": 35
      }
    }
  },
  "fetch_size": 10
}

SQL和ES对应关系

虽然 SQL 和 Elasticsearch 对于数据的组织方式(以及不同的语义)有不同的术语,但本质上它们的用途是相同的.下面是它们的映射关系表:

SQLElasticsearch说明
columnfield在 Elasticsearch 字段时,SQL 将这样的条目调用为 column.注意,在 Elasticsearch,一个字段可以包含同一类型的多个值(本质上是一个列表) ,而在 SQL 中,一个列可以只包含一个表示类型的值.Elasticsearch SQL 将尽最大努力保留 SQL 语义,并根据查询的不同,拒绝那些返回多个值的字段.
rowdocument列和字段本身不存在; 它们是行或文档的一部分.两者的语义略有不同: 行row往往是严格的(并且有更多的强制执行),而文档往往更灵活或更松散(同时仍然具有结构).
tableindex在 SQL 还是 Elasticsearch 中查询针对的目标
schemaimplicit在关系型数据库中,schema 主要是表的名称空间,通常用作安全边界.Elasticsearch没有为它提供一个等价的概念.

虽然这些概念之间的映射在语义上有些不同,但它们间更多的是有共同点,而不是不同点.

词法结构

ES SQL 的词法结构很大程度上类似于 ANSI SQL 本身.ES SQL 当前一次只能接受一个命令,这里的命令是由输入流结尾结束的 token 序列.这些 token 可以是关键字,标识符(带引号或者不带引号),文本(或者常量),特殊字符符号(通常是分隔符).

关键字

关键词这个其实跟我们写 SQL 语句那种关键字的定义是一样的,例如 SELECT,FROM 等都是关键字,需要注意的是,关键字不区分大小写.

SELECT * FROM my_table

如上示例,共有 4 个 token:SELECT, * ,FROM ,my_table,其中 SELECT,* ,FROM 是关键词,表示在 SQL 具有固定含义的词.而 my_table 是一个标识符,其表示了 SQL 中实体,如表,列等

标识符

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

SELECT ip_address FROM "hosts-*"

如上示例,查询中有两个标识符分别为不带引号的 ip_address 和带引号的 hosts-*(通配符模式).
因为 ip_address 不与任何关键字冲突,所以可以不带引号.而 hosts-* 与 - (减号操作)和 * 冲突,所以要加引号.

📝注意: 对于标识符来说,应该尽量避免使用复杂的命名和与关键字冲突的命名,并且在输入的时候使用引号作为标识符,这样可以消除歧义.

直接常量

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).

单引号,双引号

在 SQL 中,单引号和双引号具有不同的含义,不能互换使用.单引号用于声明字符串,而双引号用于表示标识符.示例如下:

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

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

特殊字符

一些非数字和字母的字符具有不同于运算符的专用含义,特殊字符有:

字符描述
*在一些上下文中表示数据表的所有字段,也可以表示某些聚合函数的参数.
,用于列举列表的元素
.用于数字常量或者分隔标识符限定符(表,列等)
()用于特定的 SQL 命令,函数声明,或者强制优先级.

运算符

ES SQL 中大多数的运算符它们的优先级都是相同的,并且是左关联.如果需要修改优先级,则要用括号来强制改变其优先级.下表是 ES SQL 支持的运算符和其优先级:

运算符结合性说明
.左结合限定符或者分割符
::左结合PostgreSQL-style 风格的类型转换符
+ -右结合一元加减符
* / %左结合乘法,除法,取模
+ -左结合加法,减法运算
BETWEEN IN LIKE范围包含,字符匹配
< > <= >= = <=> <> !=比较运算
NOT右结合逻辑非
AND左结合逻辑与
OR 左结合逻辑或

注释

ES SQL 支持两种注释:单行和多行注释,其示例如下:

-- single line comment,单行注释

/* multi
   line
   comment
   that supports /* nested comments */
   多行注释
   */

常用SQL操作

语法介绍

在ES中使用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 ] [, ...] ) ) ]

WHERE

可以使用WHERE语句设置查询条件,比如查询state字段为VA的记录,查询语句如下.

POST /_sql?format=txt
{
  "query": "SELECT account_number,address,age,balance,state FROM account WHERE state='VA' LIMIT 10"
}

查询结果如下:

account_number |      address       |      age      |    balance    |     state     
---------------+--------------------+---------------+---------------+---------------
13             |789 Madison Street  |28             |32838          |VA             
486            |991 Applegate Court |22             |35902          |VA             
703            |489 Flatlands Avenue|29             |27443          |VA             
835            |641 Royce Street    |25             |46558          |VA             
897            |731 Poplar Street   |25             |45973          |VA             
564            |842 Congress Street |22             |43631          |VA             
588            |301 Anna Court      |31             |43531          |VA             
660            |916 Amersfort Place |33             |46427          |VA             
797            |919 Quay Street     |26             |6854           |VA             
836            |953 Dinsmore Place  |25             |20797          |VA

GROUP BY

我们可以使用 GROUP BY 语句对数据进行分组,统计出分组记录数量,最大age和平均balance等信息,查询语句如下.

POST /_sql?format=txt
{
  "query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state LIMIT 10"
}

HAVING

我们可以使用 HAVING 语句对分组数据进行二次筛选,比如筛选分组记录数量大于15的信息,查询语句如下.

POST /_sql?format=txt
{
  "query": "SELECT state,COUNT(*),MAX(age),AVG(balance) FROM account GROUP BY state HAVING COUNT(*)>15 LIMIT 10"
}

查询结果如下:

     state     |   COUNT(*)    |   MAX(age)    |   AVG(balance)   
---------------+---------------+---------------+------------------
AK             |22             |40             |26131.863636363636
AL             |25             |40             |25739.56          
AR             |18             |39             |27238.166666666668
CA             |17             |40             |22517.882352941175
CT             |16             |39             |28278.4375        
DC             |24             |40             |23180.583333333332
FL             |18             |38             |20443.444444444445

ORDER BY

我们可以使用ORDER BY语句对数据进行排序,比如按照balance字段从高到低排序,查询语句如下.

POST /_sql?format=txt
{
  "query": "SELECT account_number,address,age,balance,state FROM account ORDER BY balance DESC LIMIT 10 "
}

查询结果如下:

account_number |       address        |      age      |    balance    |     state     
---------------+----------------------+---------------+---------------+---------------
248            |717 Hendrickson Place |36             |49989          |WA             
854            |603 Cooper Street     |25             |49795          |AL             
240            |659 Highland Boulevard|35             |49741          |NH             
97             |512 Cumberland Walk   |40             |49671          |MO             
842            |833 Bushwick Court    |23             |49587          |TX             
168            |975 Flatbush Avenue   |20             |49568          |IL             
803            |963 Highland Avenue   |25             |49567          |MS             
926            |833 Quincy Street     |21             |49433          |VT             
954            |688 Hart Street       |22             |49404          |MD             
572            |994 Chester Court     |20             |49355          |UT

DESCRIBE

我们可以使用 DESCRIBE 语句查看表(ES中为索引)中有哪些字段,比如查看account表的字段,查询语句如下.

POST /_sql?format=txt
{
  "query": "DESCRIBE account"
}

查询结果如下:

     column      |     type      |    mapping    
-----------------+---------------+---------------
account_number   |BIGINT         |long           
address          |VARCHAR        |text           
address.keyword  |VARCHAR        |keyword        
age              |BIGINT         |long           
balance          |BIGINT         |long           
city             |VARCHAR        |text           
city.keyword     |VARCHAR        |keyword        
email            |VARCHAR        |text           
email.keyword    |VARCHAR        |keyword        
employer         |VARCHAR        |text           
employer.keyword |VARCHAR        |keyword        
firstname        |VARCHAR        |text           
firstname.keyword|VARCHAR        |keyword        
gender           |VARCHAR        |text           
gender.keyword   |VARCHAR        |keyword        
lastname         |VARCHAR        |text           
lastname.keyword |VARCHAR        |keyword        
state            |VARCHAR        |text           
state.keyword    |VARCHAR        |keyword

SHOW TABLES

我们可以使用 SHOW TABLES 查看所有的表(ES中为索引).

POST /_sql?format=txt
{
  "query": "SHOW TABLES"
}

查询结果如下:

#! this request accesses system indices: [.kibana_7.17.0_001, .kibana_task_manager_7.17.0_001], but in a future major version, direct access to system indices will be prevented by default
#! this request accesses system indices: [.apm-agent-configuration, .apm-custom-link, .async-search, .kibana_7.17.0_001, .kibana_task_manager_7.17.0_001, .tasks], but in a future major version, direct access to system indices will be prevented by default
    catalog    |             name              |     type      |     kind      
---------------+-------------------------------+---------------+---------------
my-application |.apm-agent-configuration       |TABLE          |INDEX          
my-application |.apm-custom-link               |TABLE          |INDEX          
my-application |.async-search                  |TABLE          |INDEX          
my-application |.kibana                        |VIEW           |ALIAS          
my-application |.kibana_7.17.0                 |VIEW           |ALIAS          
my-application |.kibana_7.17.0_001             |TABLE          |INDEX          
my-application |.kibana_task_manager           |VIEW           |ALIAS          
my-application |.kibana_task_manager_7.17.0    |VIEW           |ALIAS          
my-application |.kibana_task_manager_7.17.0_001|TABLE          |INDEX          
my-application |.tasks                         |TABLE          |INDEX          
my-application |account                        |TABLE          |INDEX          
my-application |kibana_sample_data_flights     |TABLE          |INDEX

支持的函数

使用SQL查询ES中的数据,不仅可以使用一些SQL中的函数,还可以使用一些ES中特有的函数.

查询支持的函数

我们可以使用 SHOW FUNCTIONS 语句查看所有支持的函数,比如搜索所有带有 DATE 字段的函数可以使用如下语句.

POST /_sql?format=txt
{
  "query": "SHOW FUNCTIONS LIKE '%DATE%'"
}

查询结果如下:

     name      |     type      
---------------+---------------
CURDATE        |SCALAR         
CURRENT_DATE   |SCALAR         
DATEADD        |SCALAR         
DATEDIFF       |SCALAR         
DATEPART       |SCALAR         
DATETIME_FORMAT|SCALAR         
DATETIME_PARSE |SCALAR         
DATETRUNC      |SCALAR         
DATE_ADD       |SCALAR         
DATE_DIFF      |SCALAR         
DATE_PARSE     |SCALAR         
DATE_PART      |SCALAR         
DATE_TRUNC     |SCALAR

全文搜索函数

全文搜索函数是ES中特有的,当使用 MATCH 或 QUERY 函数时,会启用全文搜索功能,SCORE 函数可以用来统计搜索评分.

MATCH()

使用MATCH函数查询address中包含Street的记录.

POST /_sql?format=txt
{
  "query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE MATCH(address,'Street') LIMIT 10"
}

查询结果如下:

account_number |        address        |      age      |    balance    |    SCORE()    
---------------+-----------------------+---------------+---------------+---------------
6              |671 Bristol Street     |36             |5686           |0.95395315     
13             |789 Madison Street     |28             |32838          |0.95395315     
32             |702 Quentin Street     |34             |48086          |0.95395315     
49             |451 Humboldt Street    |23             |29104          |0.95395315     
51             |334 River Street       |31             |14097          |0.95395315     
63             |510 Sedgwick Street    |30             |6077           |0.95395315     
87             |446 Halleck Street     |22             |1133           |0.95395315     
107            |694 Jefferson Street   |28             |48844          |0.95395315     
138            |422 Malbone Street     |39             |9006           |0.95395315     
140            |878 Schermerhorn Street|32             |26696          |0.95395315
QUERY()

使用 QUERY 函数查询address中包含Street的记录.

POST /_sql?format=txt
{
  "query": "SELECT account_number,address,age,balance,SCORE() FROM account WHERE QUERY('address:Street') LIMIT 10"
}

查询结果如下:

account_number |        address        |      age      |    balance    |    SCORE()    
---------------+-----------------------+---------------+---------------+---------------
6              |671 Bristol Street     |36             |5686           |0.95395315     
13             |789 Madison Street     |28             |32838          |0.95395315     
32             |702 Quentin Street     |34             |48086          |0.95395315     
49             |451 Humboldt Street    |23             |29104          |0.95395315     
51             |334 River Street       |31             |14097          |0.95395315     
63             |510 Sedgwick Street    |30             |6077           |0.95395315     
87             |446 Halleck Street     |22             |1133           |0.95395315     
107            |694 Jefferson Street   |28             |48844          |0.95395315     
138            |422 Malbone Street     |39             |9006           |0.95395315     
140            |878 Schermerhorn Street|32             |26696          |0.95395315

SQL CLI

如果你不想使用Kibana来使用ES SQL的话,也可以使用ES自带的SQL CLI来查询,该命令位于ES的bin目录下.

使用如下命令启动SQL CLI:

elasticsearch-sql-cli http://localhost:9200

然后直接输入SQL命令即可查询了,注意要加分号.

SELECT account_number,address,age,balance FROM account LIMIT 10;

ES SQL 的局限性

使用SQL查询ES有一定的局限性,没有原生的Query DSL那么强大,对于嵌套属性和某些函数的支持并不怎么好,但是平时用来查询下数据基本够用了.

ES SQL 使用实战

我们先准备数据,此处我们将使用 Kibana 提供的航班数据:

如下图,在 Kibana 中点击左边栏的 Analytics 下的 Overview ,右边的页面中选择 DashBoard 然后点击 Install some sample data 链接,
再点击 Sample flight data 即可加入航班的数据.
在这里插入图片描述
可以使用以下语句查看航班数据:

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

下面来看看常用的 SQL 如何编写.

1. WHERE

我们过滤出目的地为 US 的数据:

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

查询结果如下:

   FlightNum   |   OriginWeather   | OriginCountry |    Carrier     
---------------+-------------------+---------------+----------------
R43CELD        |Cloudy             |US             |JetBeats        
3YAQM9U        |Clear              |US             |JetBeats        
8SHQI41        |Cloudy             |US             |JetBeats        
HF9AP10        |Sunny              |US             |JetBeats        
ZTL6FPB        |Heavy Fog          |IT             |ES-Air          
TF9BTQL        |Clear              |JP             |Kibana Airlines 
T9QK7GX        |Clear              |IN             |Logstash Airways
4AHGESO        |Rain               |ZA             |Kibana Airlines 
J684XSR        |Sunny              |AR             |JetBeats        
T390OH4        |Cloudy             |IN             |ES-Air          
Q33SYKK        |Sunny              |KR             |JetBeats        
JBQ50Y2        |Clear              |IT             |Logstash Airways

2. GROUP BY

可以使用 GROUP BY 语句对数据进行分组聚合统计操作,例如查询航班分组的平均飞行距离等.其示例如下:

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

如上示例,我们以目的地国家进行分组,然后统计每个分组的数量,最大的飞行距离,平均飞行距离.其结果如下:

   count(*)    |max(DistanceMiles)|avg(DistanceMiles)
---------------+------------------+------------------
46             |7600.7158203125   |3233.800320625305 
305            |12140.8603515625  |6603.605808945953 
377            |9917.6455078125   |3128.910634331741 
416            |10832.3994140625  |7915.6610843951885
944            |10600.296875      |4077.664177652133 
691            |10293.208984375   |2775.8247816469493
45             |12075.3935546875  |7542.028591579861 
1096           |12353.7802734375  |5037.134736095902 
91             |10000.7255859375  |5683.497867123111 
278            |10030.87109375    |3448.2222546090325
48             |9670.9072265625   |3278.826272328695 
237            |10575.1279296875  |5419.154288118902 
15             |10346.84765625    |3214.9680114746093

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 的数据,其结果如下:

   count(*)    |max(DistanceMiles)|avg(DistanceMiles)
---------------+------------------+------------------
305            |12140.8603515625  |6603.605808945953 
377            |9917.6455078125   |3128.910634331741 
416            |10832.3994140625  |7915.6610843951885
944            |10600.296875      |4077.664177652133 
691            |10293.208984375   |2775.8247816469493
1096           |12353.7802734375  |5037.134736095902 
278            |10030.87109375    |3448.2222546090325
237            |10575.1279296875  |5419.154288118902 
449            |10282.5048828125  |3213.2889483309536
373            |10774.0           |5064.675941446831

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"
}

如上示例,我们将数据用平均距离排序,其结果为

   count(*)    |max(DistanceMiles)|   avgDistance    
---------------+------------------+------------------
416            |10832.3994140625  |7915.6610843951885
305            |12140.8603515625  |6603.605808945953 
283            |10556.7587890625  |6030.0211101842015
237            |10575.1279296875  |5419.154288118902 
214            |11447.2265625     |5323.084783429297 
774            |11407.380859375   |5280.042444507589 
116            |10553.98828125    |5118.16688169282  
373            |10774.0           |5064.675941446831

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
}

其结果如下:

   FlightNum   | OriginWeather | OriginCountry |    Carrier     
---------------+---------------+---------------+----------------
R43CELD        |Cloudy         |US             |JetBeats        
3YAQM9U        |Clear          |US             |JetBeats        
8SHQI41        |Cloudy         |US             |JetBeats        
HF9AP10        |Sunny          |US             |JetBeats        
ZTL6FPB        |Heavy Fog      |IT             |ES-Air          
TF9BTQL        |Clear          |JP             |Kibana Airlines 
T9QK7GX        |Clear          |IN             |Logstash Airways
4AHGESO        |Rain           |ZA             |Kibana Airlines 
J684XSR        |Sunny          |AR             |JetBeats        
T390OH4        |Cloudy         |IN             |ES-Air

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"
}

其结果如下:

avg(data.DistanceMiles)
-----------------------
4714.944895442431

参考资料

官方文档:xpack-sql{target=“_blank”}

爱游泳的老白
关注
  • 1
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
专栏目录
三种数据库SQL分页方法详解
09-17
三种数据库SQL分页方法详解 java sql mssql mysql oracle 分页代码 java培训，8人精品小班教学 保证质量，包就业，半年全部学完。 www.5djava.com Email:[email protected]
Elasticsearch 安装 Elasticsearch-SQL 插件使用 SQL 操作
学亮编程手记
08-20 770
Elasticsearch 的查询语言 (DSL) 语法很复杂，查询的功能 (filter/query/match/agg/geo) 各种各样，不管是通过 restful API 还是通过 java 的 API 进行操作，都非常不方便。而 Elasticsearch-SQL 插件可以使用传统 SQL 语法查询 Elasticsearch，使用起来非常不错。Elasticsearch-SQL 项目地址：https://github.com/NLPchina/elasticsearch-sql。
ElasticSearch 7.3 实战：ES7.X SQL新特性解析及使用Java api实现sql功能
qq_33240556的博客
03-28 342
请注意，实际的API用法可能会根据Elasticsearch官方发布的具体版本有所不同，请查阅Elasticsearch 7.3的相关文档以获取最新和详细的API使用方法。同时，对于SQL新特性的完整列表和具体用法，建议直接查阅Elasticsearch官方文档。在Elasticsearch 7.x版本中，对SQL的支持得到了增强，使得用户可以直接使用SQL查询语法来操作Elasticsearch中的数据。
ESAPI使用方法
11-16
ESAPI入门使用例子，防XSS攻击，防SQL注入，过滤等等。
MLSQL编译时权限控制示例详解
01-19
前言 关于mysql的权限简单的理解就是mysql允许你做你全力以内的事情，不可以越界。 权限控制，对于MLSQL而言的重要程度可以说是生命线。 MLSQL需要面对各式各样的资源访问，比如MySQL, Oracle,HDFS，Hive,Kafka,Sorl,ElasticSearch,Redis,API,Web等等，不同用户对这些数据源（以及表，列）的权限是不一样的。 传统模式是，每个用户都需要有个proxy user,然后到每个数据源里面给这个proxy user进行授权。 这看起来似乎就是麻烦点，但是在实际操作中，基本是很难执行的，不同的数据源在不同的团队里面，那么整个申请流程可能要天
ElasticSearch SQL 教程
05-03
本视频教程是介绍 ElasticSearch SQL 功能的视频。 If you (or others you know) are versed in SQL statements and newer to the Elasticsearch query syntax but want to benefit from the power Elasticsearch, this is the talk for you. After making its debut at last year's conference, Elasticsearch SQL is a project designed to bridge these two worlds. From converting SQL statements into Elasticsearch queries to a console experience for exploring data using "SELECT x FROM y WHERE z > 1000" type queries, there's a lot on the horizon.
Elasticsearch SQL 详解
dgl199203118的博客
09-27 5083
一文了解 elasticsearch SQL 的用法！
Elasticsearch SQL
Alicia
12-23 3720
Elasticsearch SQL Elasticsearch SQL是一个X-Pack组件，它允许针对Elasticsearch实时执行类似SQL的查询。无论使用REST接口，命令行还是JDBC，任何客户端都可以使用SQL对Elasticsearch中的数据进行原生搜索和聚合数据。可以将Elasticsearch SQL看作是一种翻译器，它可以将SQL翻译成Query DSL。 官方文档(ElasticSearch[7.x]) 语法 在ES中使用SQL查询的语法与在数据库中使用基本一致，具体格式如下：
Elasticsearch-05-Elasticsearch-sql组件史上最全详解
苍煜
06-30 846
这篇文章集合了市面上elasticsearch-sql和es官方出的x-pack两种形式的sql 从sql组件的安装到利用工具查询sql，以及利用java代码封装工具类访问
Elasticsearch7使用jdbc连接
m0_54853503的博客
04-18 1203
Elasticsearch7.0.1 使用jdbc连接 项目中添加maven依赖 <repositories> <repository> <id>elastic.co</id> <url>https://artifacts.elastic.co/maven</url> </repository> </repositories> <dependency> <grou
Elasticsearch语句—SQL详解
平行世界
09-09 1642
SQL语句等同Elasticsearch语句转换
PHP 使用MySQL管理Session的回调函数详解
01-20
复制代码 代码如下:<?php class MySession extends DBSQL {  /**   * __construct()   */  public function __construct() {   parent::__construct ();  }  /**   * open()   *    * @param <String> $sSavePath   * @param <String>$sSessionNames   *    * @return <Boolean> true   */  public function open($sSavePath, $sSes
x-pack-sql-jdbc-7.5.2.jar
02-10
x-pack-sql-jdbc-7.5.2是数据库客户端连接elasticsearch的驱动jdbc库
x-pack-sql-jdbc-7.9.1.jar
03-04
x-pack-sql-jdbc-7.9.1.jar
基于ESAPI的防sql注入jar包及使用示例.rar
10-17
基于ESAPI的防sql注入jar包及使用示例 esapi-2.1.0.1.jar包 两个properties文件 一个java工具类 一个说明文档
es优化实践(es升级带来的大坑)-＞问题与痛点-＞优化过程-＞原理分析-＞最佳实践
正直 的博客
04-29 264
这里分享一个ES2.X升级到ES5.X带来的天坑问题,从问题与痛点,到优化过程,到原理分析,到最佳实践
【翻译】Elasticsearch-索引模块
最新发布
qq_49444793的博客
04-30 964
索引块限制对指定的索引的可用的操作类型（就是指对该索引能进行什么操作）。这些块有不同的风格，可以阻止写、读或元数据操作。块可以通过动态索引设置来设置/移除，也可以通过专用API添加，这也可以确保写入块一旦成功返回用户，所有索引分片都能正确地计算该块，例如，在添加写块后，所有对索引进行中的写入会完成。
ESLint 和 Prettier 各自的作用及区别
qq_39454432的博客
04-29 661
ESLint 和 Prettier 各自的作用及区别
Git Commit命令撤销操作
yuanjinshenglife的专栏
04-28 345
Git Commit命令撤销操作
如何使用 es7.1.1 elasticsearch-sql
07-28
要使用 Elasticsearch SQL 插件，您需要先确保您已经安装了 Elasticsearch 7.1.1 版本。然后，按照以下步骤进行操作： 1. 安装 Elasticsearch SQL 插件： - 打开终端或命令提示符窗口。 - 运行以下命令安装 Elasticsearch SQL 插件： ``` ./bin/elasticsearch-plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/7.1.1.0/elasticsearch-sql-7.1.1.0.zip ``` - 安装完成后，重启 Elasticsearch 以使插件生效。 2. 使用 Elasticsearch SQL： - 打开终端或命令提示符窗口。 - 进入 Elasticsearch 的安装目录。 - 运行以下命令以启动 Elasticsearch SQL 查询： ``` ./bin/elasticsearch-sql-cli ``` - 您将进入 Elasticsearch SQL 的命令行界面。 - 现在，您可以开始使用 SQL 语法来查询 Elasticsearch 数据。 例如，您可以运行以下命令来执行一个简单的 SELECT 查询： ``` SELECT * FROM your_index_name WHERE your_field_name = 'your_value' ``` 注意：在上述查询中，`your_index_name` 是您要查询的索引名称，`your_field_name` 是您要查询的字段名称，`your_value` 是您要匹配的字段值。 这样，您就可以使用 Elasticsearch SQL 插件来执行 SQL 查询并检索 Elasticsearch 中的数据了。请记住，Elasticsearch SQL 插件提供了一种方便的方式来使用 SQL 语法进行查询，但它可能不适用于所有类型的查询，特别是复杂的查询。因此，在使用插件时，请确保您了解其使用限制和适用场景。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

爱游泳的老白

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值