Day23-設定進階的Watcher-SQL查詢

第 12 屆 iThome 鐵人賽

DAY 23

Elastic Stack on Cloud

Elastic Stack 是一把梭，用起來再說！！！系列第 23 篇

12th鐵人賽

Ken

團隊神龍特攻隊-為了燒肉不小心成為一條龍

2020-10-01 10:40:03

943 瀏覽

如果看完前面DSL介紹還是覺得DSL很難的話，可以試試SQL
在7.X版之後內建就支援SQL

使用SQL查詢索引

SQL術語和Elasticsearch術語的對應關係

SQL	Elasticsearch
column	filed
row	document
table	index

簡單查詢

POST /_sql?format=txt
{
  "query": "SELECT * FROM \"30day-*\""
}

回傳結果

@timestamp	level	message	staus
2020-09-28T03:51:02.000Z	DEBUG	30day very good	null
2020-09-28T03:51:03.000Z	INFO	30day very good	null
2020-09-28T03:51:03.000Z	INFO	30day very good	200

WHERE查詢

POST /_sql?format=txt
{
  "query": "SELECT * FROM \"30day-*\" WHERE \"staus\" = 200"
}

WHERE MATCH查詢

POST /_sql?format=txt
{
  "query": "SELECT * FROM \"30day-*\" WHERE MATCH('level', 'INFO')"
}

SQL轉DSL

POST /_sql/translate
{
  "query": "SELECT * FROM \"30day-*\" WHERE \"staus\" = 200"
}

轉出後的DSL

{
  "size" : 1000,
  "query" : {
    "term" : {
      "staus" : {
        "value" : 200,
        "boost" : 1.0
      }
    }
  },
  "_source" : {
    "includes" : [
      "level",
      "message",
      "staus"
    ],
    "excludes" : [ ]
  },
  "docvalue_fields" : [
    {
      "field" : "@timestamp",
      "format" : "epoch_millis"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}