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.

  1. now-5h, 即5小时之前, h为时间单位,
  2. now-1d/d, 即取昨天0点开始的时间,
  3. now+1d, 当前时间加1天
  4. now-2w, 当前时间减去2周
  5. now-1M+1d, 当前时间减去1月再加上1天
  6. @’2017-03-15T10:00:00’-5h
  7. @’2017-03-01’-1s

    最小匹配度查询

    min_match(field_name, ‘text’, ‘match’, ‘analyze’)

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

  1. index=es_sql_test* | table state, age
  2. 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 数值, 分桶最小数量,小于该数量的值将不显示

    1. index=bankdata* | stats max(@timestamp[filter LogType=Res]) end, min(@timestamp[filter LogType=Req]) start by TranSeqNo
    2. | eval duration=end-start
    3. | 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.

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

注意:

  1. 使用乘号*时, 请在前后加空格

    eval-after-stats

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

    1. index=bankdata*
    2. | stats max(@timestamp[filter LogType=Res]) end, min(@timestamp[filter LogType=Req]) start by TranSeqNo
    3. | eval duration=end-start
  • 当在stats命令后使用过eval, 再次使用stats时, 此时eval命令有一定的限制, 如下:

    1. index=bankdata*
    2. | stats max(@timestamp[filter LogType=Res]) end, min(@timestamp[filter LogType=Req]) start by TranSeqNo
    3. | eval duration=end-start
    4. | 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 有效