Elasticsearch SQL

es sql是一个X-pack组件 ，允许对es执行类似sql的查询，可以将Elasticsearch SQL理解为一个编译器，既能理解es，又能理解sql。可以通过利用es，实施大规模实时读取和处理数据。

sql和es的映射关系

SQL Elasticsearch
columns field
raw document
table index
catalog or database cluster实例
cluster cluster

 

 

 

 

 

 

 

先插入一些数据：

PUT /my_index/doc/_bulk
{"index":{"_id":"1"}}
{"name":"lily","birthday":"2000-01-01","gender":"female"}
{"index":{"_id":"2"}}
{"name":"kangkang","birthday":"1998-04-01","gender":"male"}
{"index":{"_id":"3"}}
{"name":"jane","birthday":"1995-02-07","gender":"female"}

 

SQL REST API

POST /_xpack/sql?format=txt
{
  "query":"select * from my_index where birthday<'1999-01-01' limit 2"
}
#  format类型有：json,yaml,smile,cbor,txt,csv,tsv

返回结果：
birthday | gender | name 
------------------------+---------------+---------------
1998-04-01T00:00:00.000Z|male |kangkang 
1995-02-07T00:00:00.000Z|female |jane

 

POST  /_xpack/sql?
{
  "query":"select * from my_index order by birthday desc",
  "fetch_size":1    # fetch_size 每页返回多少个结果
}
--------->
{
  "columns": [
    {
      "name": "birthday",
      "type": "date"
    },
    {
      "name": "gender",
      "type": "text"
    },
    {
      "name": "name",
      "type": "text"
    }
  ],
  "rows": [
    [
      "2000-01-01T00:00:00.000Z",
      "female",
      "lily"
    ]
  ],
  "cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
# 该column对象只是第一页的一部分，当cursor结果中没有返回时，说明到达最后一页。
# 可以通过发回cursor字段继续下一页。在文本格式的情况下，光标作为Cursorhttp标头返回。
POST /_xpack/sql?format=json
{
  "cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
#结果--------->
{
  "rows": [
    [
      "1998-04-01T00:00:00.000Z",
      "male",
      "kangkang"
    ]
  ],
  "cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
## -------------再次发回cursor：

POST /_xpack/sql?format=json
{
   "cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3WEZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmUyeFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHRnV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGN1pGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlMmhaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
#结果----------------》
{
  "rows": []
}
#接收到最后一页时，清空es状态，没有cursor


#要提前清理状态，可以使用 clear cursor
POST _xpack/sql/close
{
　　"cursor": "k4bwAgFz5AFEbkYxWlhKNVZHaGxia1psZEdOb0JRQUFBQUFBQUY3NkZrbGtNa3R5V2s1VVZFTnRORmd3Y21Gd2VHeERMVkVBQUFBQUFBQmVfaFpKWkRKTGNscE9WRlJEYlRSWU1ISmhjSGhzUXkxUkFBQUFBQUFBWHZzV1NXUXlTM0phVGxSVVEyMDBXREJ5WVhCNGJFTXRVUUFBQUFBQUFGNzhGa2xrTWt0eVdrNVVWRU50TkZnd2NtRndlR3hETFZFQUFBQUFBQUJlX1JaSlpESkxjbHBPVkZSRGJUUllNSEpoY0hoc1F5MVL/////DwMBZghiaXJ0aGRheQEAAWYGZ2VuZGVyAAABZgRuYW1lAAA="
}
#结果——----------------->
{  "succeeded": true }

 通过filter参数可以指定es的Query DSL来过滤

POST _xpack/sql?format=txt
{
  "query":"select * from my_index order by birthday desc",
  "filter":{
    "term": {
      "name": "kangkang"
    }
  },
  "fetch_size":1
}
# 除了query和cursor字段外 请求还可以包括fetch_size和time_zone
# fetch_size 每页返回多少个结果
# time_zone 日期函数和日期解析的时区，默认为utc

 

SQL Translate API

sql translate api接受json文档中的sql并将其转换为es查询。

POST _xpack/sql/translate
{
  "query":"select * from my_index order by birthday",
  "fetch_size":3
}
#结果----------------->
{
  "size": 3,
  "_source": {
    "includes": [
      "gender",
      "name"
    ],
    "excludes": []
  },
  "docvalue_fields": [
    "birthday"
  ],
  "sort": [
    {
      "birthday": {
        "order": "asc"
      }
    }
  ]
}

SQL CLI

可以用命令行形式，在x-pack的bin目录执行查询语句：

# ./elasticsearch-sql-cli
sql> select * from my_index where birthday<'1999-01-01';
birthday | gender | name
------------------------+---------------+---------------
1998-04-01T00:00:00.000Z|male |kangkang
1995-02-07T00:00:00.000Z|female |jane

 

SQL JDBC

将jdbc调用转化为es sql

SQL 语句

  • describe table 
    # DESC table 
# DESCRIBE table

POST _xpack/sql?format=txt
{
  "query":"describe my_index"
}
---------->
    column     |     type      
---------------+---------------
birthday       |TIMESTAMP      
gender         |VARCHAR        
gender.keyword |VARCHAR        
name           |VARCHAR        
name.keyword   |VARCHAR
  • select 
    # SELECT select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
  • show columns 
    #SHOW COLUMNS [ FROM | IN ] ? table

POST _xpack/sql?format=txt
{
  "query":"show columns in my_index"
}

    column | type
    ---------------+---------------
    birthday |TIMESTAMP
    gender |VARCHAR
    gender.keyword |VARCHAR
    name |VARCHAR
    name.keyword |VARCHAR

  • show functions 
    #SHOW FUNCTIONS [ LIKE? pattern? ]?  

 POST _xpack/sql?format=txt
 {
 　　"query":"show functions like 'sum%'"
 }

name | type 
---------------+---------------
SUM |AGGREGATE 
SUM_OF_SQUARES |AGGREGATE　

     

  • show tables 
    # SHOW TABLES [ LIKE? pattern? ]?

POST _xpack/sql?format=txt
{
  "query":"show tables like 'my_index'" 
}
#------------------------>
     name      |     type      
---------------+---------------
my_index       |BASE TABLE

functions and operators

  • 比较运算符：   = , < , <= , > , >=,  不等于 <>  !=  <=> ， between，is null/is not null
  • 逻辑运算符： AND ,OR ,NOT
  • 数字运算符：  +  -  *  / % 
    POST _xpack/sql?format=txt
{
  "query":"select 1+1 as x"
}
---------->
       x       
---------------
2
  • 数学函数：  abs(绝对值), crbt(立方根)，round(四舍五入).... 
    POST _xpack/sql?format=txt
{
  "query":"select abs(age) from test_index "
}
--------->
   ABS(age)    
---------------
27
  • 时间和日期函数： year, month, week, doy, dow, hour ,minute_of_day, minute,second，extract 
    POST _xpack/sql?format=txt
{
  "query":"select year(cast('2018-07-12' as timestamp )) as year"
}  #从日期中提取年份
------->
     year      
---------------
2018
  • 聚合：  avg , count , count(distinct) , max , min , sum  
    POST _xpack/sql/?format=txt
{
  "query":"select avg(age) as avg from test_index"
}

POST _xpack/sql?format=txt
{
  "query":"select count(distinct age) as count from test_index"
}   #不同值的个数

 

posted @ 2018-07-12 17:14  卡布爱学习  阅读(14755)  评论(0)  编辑  收藏  举报