ElasticSearch - Elasticsearch-SQL - 《大数据中间件》

SPL 查询语法 for elasticsearch
基本查询
聚合查询
组合查询

https://github.com/NLPchina/elasticsearch-sql
https://www.cnblogs.com/jajian/p/10053504.html
Elasticsearch SQL language
基础查询
 聚合查询
 join

SPL 查询语法 for elasticsearch

基本查询

全文检索

index=es_sql_test* 502 AND Woodard

短语查询

index=es_sql_test* “502 Baycliff Terrace”

字段值查询

index=es_sql_test* state=PA AND age<30 AND gender=M

逻辑运算符

必须大写, 支持的运算符有:

AND state=PA AND age>20
OR state=PA OR state=DE
NOT NOT state=DE
() (state=PA OR state=DE) AND age>20
IN state in (PA,DE)

null/exist 运算
null city is null
NOT-null city is NOT null
exist city is NOT null, 与NOT-null相同

数值范围
= age=20 精确匹配age的字段值
!= age!=20 不匹配age的字段值
< age<20 匹配age小于20的字段值
age>20 匹配age大于20的字段值
<= age<=20 匹配age小于等于20的字段值
= age>=20 匹配age大于等于20的字段值

通配符
匹配0个或者多个字符 e.g. firstname=ri, firstname=Rach
?匹配1个字符 e.g. firstname=Eff?e

特殊字符
非数字,大小写字母, @, _ 字符或 space出现在查询语句中时,需要添加引号
e.g. “http://website.com:9000“, “hello word”, “user@mail.com“

相对时间
时间单位可以是年(y) 月(M) 日(d) 周(w) 时(h) 分(m) 秒(s) 毫秒(ms)
now, 当前时间
‘2017-03-15T10:00:00’, UTC时间字符串, 年(y)必须4位数字, 月(M) 日(d) 周(w) 时(h) 分(m) 秒(s) 必须2位数字
-1h, 减去1小时
+1d, 加1天
/d, 舍弃日期中的小时及以下单位的数值

e.g.

now-5h, 即5小时之前, h为时间单位,
now-1d/d, 即取昨天0点开始的时间,
now+1d, 当前时间加1天
now-2w, 当前时间减去2周
now-1M+1d, 当前时间减去1月再加上1天
@’2017-03-15T10:00:00’-5h
@’2017-03-01’-1s
最小匹配度查询
min_match(field_name, ‘text’, ‘match’, ‘analyze’)

field_name: 字段名称, 必选
text: string, 搜索的长字符串, 必选
match: string, 匹配度, 必须, 详细说明(https://www.elastic.co/guide/en/elasticsearch/reference/5.4/query-dsl-minimum-should-match.html)
analyze: string, 分词器名称, 可选, 如果text中包括中文, 建议该参数使用ik_smart

e.g. index=sharplook min_match(agent,”Intel Mac OS X 10_12_5”,”90%”) index=sharplook min_match(agent,”欢迎来到搜索平台”,”80%”,”ik_smart”)

sort

命令格式: | sort sort-by-field [desc | asc] index=es_sql_test* | sort by age asc

默认排序为 @timestamp desc

head, tail

命令格式: | head/tail INT | head 3 取前三条记录 | tail 3 取后三条记录

top, rare

命令格式: | top/rare INT field | top 5 lastname | rare 3 lastname

该命令用来统计field的数量, 并取前N条记录.

fields

命令格式: | fields field [, field]* | fields state, city, firstname, age

该命令用来指定输出列名

table

命令格式: | table field [, field]* | table state, age

index=es_sql_test* | table state, age
index=bankdata* | stats count by ServiceCode, SystemName | head 1 | table "count"

transaction

命令格式: | transaction field [maxopentxn=INT maxopenevents=INT] index=es_sql_test* | transaction state maxopentxn=5 maxopenevents=1

maxopentxn 最大事务数量, 默认10
maxopenevents 最大事务日志条数, 默认10

sub search

命令格式: ( search spl_sub_query ;) 子查询必须以分号;结束 index = bankdata ServiceCode=01 AND (search index=bankdata SystemId=0000 | head 1 | fields TranSeqNo;)
子查询语法支持除 transaction, tops 外所有语法
子查询应该放在查询条件的最后
子查询建议包括 fields 关键字
聚合查询
stats

命令格式: | stats metric_function(field) [as field_alias] by field-1, field-2, … index=es_sql_test* | stats count(firstname) as people_count by state

metric_function 支持的函数有:

sum 累加和
avg 平均值
count 出现的次数
dc 出现在的次数, 去重
min 最小值
max 最大值
stats-filter-before

命令格式 | stats metric_function(field[filter expr]) [as field_alias] by field-1, field-2, … index=bankdata* | stats max(@timestamp[filter LogType=Res]), min(@timestamp[filter LogType=Req]) by TranSeqNo

stats-filter-after

命令格式: | stats metric_function(field) [as field_alias] by field [, field] | filter metric_func_expr [, metric_func_expr] index=bankdata | stats count(LogType) as cc by TranSeqNo | filter cc>30 index=bankdata | stats count as cc, avg(Duration) aa by TranSeqNo | filter cc>20, aa>12

stats-bucket-sort-limit

命令格式: | stats metric_function(field) [as field_alias] by field[ sort by metric_func_field_alias,N] ,field-2, … index=bankdata* | stats avg(Duration) as avg_dur by SystemName[sort by avg_dur,3], ServiceType

metric_func_field_alias 指标函数的别名
N 限制分桶数据输出的数量, 不限时请输入0
stats-stats

命令格式: | stats metric_function(field) [as field_alias] by field [, field] | stats metric_function(metric_func_field_alias) index=bankdata LogType=Req | stats sum(Duration) as duration_total by TranSeqNo | stats avg(duration_total) as duration_avg by ServiceCode

stats-tops

命令格式: | stats tops(N,source=[field-1,field-2,…], field = fieldName asc|desc) index=bankdata* | stats tops(2,source=[ServiceType,SystemName] ,field=TranSeqNo asc) as top_transeq by SystemId

N 返回聚合记录行数
source 非必须, 返回聚合记录行的列名
field 非必须, 排序字段, asc 正序, desc 倒序

stats-first

命令格式: | stats first(field = fieldName asc|desc, field-1,field-2,…) index=bankdata* | stats count, first(ServiceType,SystemName) by SystemId
field-1,field-2,… 返回聚合记录的列名
field 非必须, 排序字段, asc 正序, desc 倒序

stats-histogram

命令格式: histogram(field,interval,mincount=n) index=bankdata* | stats avg(Duration) by histogram(Duration,10,mincount=0)
field 数值列名
interval 数值间隔
mincount 数值, 分桶最小数量,小于该数量的值将不显示

stats-datehistogram

命令格式: date_histogram(field,interval,format=’{format}’,time_zone=’{tz}’,mincount={mincount}}) index=bankdata* | stats count(TranSeqNo) as tran_count by date_histogram(@timestamp,hour,mincount=0)
field 时间列名,
interval 时间间隔, 时间单位可以是年(year) 季(quarter) 月(month) 周(week) 日(d) 时(h) 分(m) 秒(s) 毫秒(ms), 如: 1d, 2h, (year,month,quarter,week)不支持倍数
format 时间格式, 如: yyyy-MM-dd hh:mm:ss
time_zone 时区, 默认为UTC时间, 如: +08:00
offset 时间偏移, 如: +2h
mintime string/long 最小时间
maxtime string/long 最大时间

mincount 数值, 分桶最小数量,小于该数量的值将不显示

index=bankdata* | stats max(@timestamp[filter LogType=Res]) end, min(@timestamp[filter LogType=Req]) start by TranSeqNo
| eval duration=end-start
| stats avg(duration) as avgDuration by ServiceCode, date_histogram(@timestamp,hour)

stats-range

命令格式: range(field,value-1,value-2,value-3…) index=bankdata* | stats count(TranSeqNo) as tran_count by range(Duration,0,10,50,100,500,2000)

field 数值列名
value-1,value-2,value-3… 自定义数值间隔

stats-date_range

命令格式: date_range(field,datetime-1,datetime-2,datetime-3…) index=bankdata* | stats count(TranSeqNo) as tran_count by date_range(@timestamp,”2016/10”,”2016/11”,”2016/12”,”2017/01”,”2017/02”, format=”yyyy/MM”)
field 时间列名
datetime-1,datetime-2,datetime-3… 自定义时间间隔
组合查询
eval

命令格式: | eval eval-field=eval-expression index=es_sql_test* | eval name = firstname +’.’+ lastname | fields name, age, state

支持的函数包括:

isnull(field) 判断field是否为空
abs(field) 返回field的绝对值
ceil(field) 向上取整
floor(field) 向下取整
log(field) 计算field的对数
log10(field) 计算field的对数
sqrt(field) 计算平方根
cbrt(field) 计算立方根
exp(field) 计算field的指数
round(field, [scale]) 对field做精度计算(四舍五入), scale: int 精度,可选
min(field1, field2) 取两个field中较小的值
max(field1, field2) 取两个field中较大的值
concat(separator, field1, field2, ……) 用separator(分隔符)将field1, field2, …连接
split(field,separator,n) 将field用separator分隔成数组并取数组中的第n个值
substr(field, m, n) 从field的第m个位值开始到第n个位值结束, 但不包括n处的字符
trim(field) 去除field的前后空格
date_format(date_field,format_string) 将date_field按照format_string格式化, 如: date_format(@timestam,’yyyy-MM-dd HH:mm:ss’)
coalesce(field1, field2, ……) 取第一个不为null的值
if(bool_operator, result1, result2) 如果bool_operator为真, 返回result1, 否则返回result2, bool_operator支持的符号有: ==, !=, >, >=, <, <= if(status>200, “error”, “ok”)
case(bool_operator, result1, bool_operator, result2, ……, [default, default_result]) 类似switch case, 根据判断条件生成返回多个枚举值中的一个, default 可选, 默认值为null case(status==200, “c1”, status==404, “c2”, default, “c3”)

eval嵌套 e.g.

eval name=split(firstname+'.'+substr(lastname,0,2),'.')[1]
    eval xx=round(cbrt(age),1)
    eval xx=round((age+10)/3,2)

注意:

使用乘号*时, 请在前后加空格
eval-after-stats

在使用stats命令之后使用eval,此时支持所有eval命令, 如下:

index=bankdata* 
| stats max(@timestamp[filter LogType=Res]) end, min(@timestamp[filter LogType=Req]) start by TranSeqNo
| eval duration=end-start

当在stats命令后使用过eval, 再次使用stats时, 此时eval命令有一定的限制, 如下:
```
index=bankdata* 
| stats max(@timestamp[filter LogType=Res]) end, min(@timestamp[filter LogType=Req]) start by TranSeqNo
| eval duration=end-start
| stats avg_dur = avg(duration)
```
注意:两个stats中间的eval命令仅限于基本的数学计算’+’,’-‘,’*’,’/‘,’%’

search-pipeline-after-stats

命令格式: | search field op_symbol value index = es_sql* state=DE AND age>20 | stats count cc,avg(age) by city | search avg_age>35 | table city,cc,avg_age

op_symbol 支持的操作符有:

= 等于
!= 不等于
大于
= 大于等于
< 小于
<= 小于等于

注意: 如果需要将过滤后的结果显示成图表, 请使用 table 命令

stats-pipeline-after-stats

命令格式: | stats metric_function(field) [as field_alias] by field-1, field-2, … index = es_sql* state=DE AND age>20 | stats count cc,avg(age) by city | search avg_age>35 | eval x = cc%2 | stats sum(avg_age) by x | table x,sum_avg_age

metric_function 支持的函数有:

sum 累加和
avg 平均值
count 出现的次数
min 最小值
max 最大值

注意: 如果需要将过滤后的结果展示成图表, 请使用 table 命令

moving_avg

命令格式: | eval field_alias = moving_avg(field,[,’model’=string,’window’=number,’predict’=number,’type’=string,’alpha’=float,’beta’=float,’gamma’=flat,’pad’=1/0]) index = bankdata* | stats avg(Duration) avg_dur by date_histogram(@timestamp,hour) | eval mov_avg = moving_avg(avg_dur,’model’=’holt-winters’,’window’=’6’, ‘predict’=3,’type’=’add’,’alpha’=0.8,’beta’=0.2,’gamma’=0.5,’pad’=1)

model string, 移动平均数计算模型,默认: simple, 可用模型: simple, linear, ewma, holt-linear, holt-winters,
window int, 滑动窗口跨越数量, 默认: 5
predict int, 预测数量
settings
- type string, add/mult model= holt-winters 有效
- alpha float model= ewma,holt-linear, holt-winters 有效
- beta float model= holt-linear, holt-winters 有效
- gamma float model= holt-winters 有效
- pad 0/1 model= holt-winters 有效

Elasticsearch-SQL

SPL 查询语法 for elasticsearch

基本查询

全文检索

短语查询

字段值查询

逻辑运算符

null/exist 运算

数值范围

通配符

特殊字符

相对时间

最小匹配度查询

sort

head, tail

top, rare

fields

table

transaction

sub search

聚合查询

stats

stats-filter-before

stats-filter-after

stats-bucket-sort-limit

stats-stats

stats-tops

stats-first

stats-histogram

stats-datehistogram

stats-range

stats-date_range

组合查询

eval

eval-after-stats

search-pipeline-after-stats

stats-pipeline-after-stats

moving_avg