ElasticSearch SQL学习笔记

基础信息

ElasticSearch SQL是一个X-Pack组件，允许ElasticSearch实时执行类似SQL的查询，由ElasticSearch原生支持，无需安装其他插件。

基本语法

Elastic SQL语法和标准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 ] [, ...] ) ) ]

使用_sql接口来执行SQL语句：

## 注意表名和列名可以使用双引号但不能使用单引号
## 单引号表示字符串
POST _sql?format=txt
{
  "query": """
  SELECT * FROM mysql_host_info WHERE check_time>'2022-10-25 15:30:00' ORDER BY check_time ASC LIMIT 10
  """
}

将SQL转换伪DSL

可以通过_sql/translate 来将SQL语句转换伪DSL语句：

POST _sql/translate
{
  "query": """
  SELECT mysql_host,mysql_instances 
  FROM mysql_host_info 
  WHERE check_time>'2022-10-25 15:30:00' 
  ORDER BY check_time ASC LIMIT 10
  """
}

转换后的DSL为：

{
  "size": 10,
  "query": {
    "range": {
      "check_time": {
        "gt": "2022-10-25 15:30:00",
        "boost": 1
      }
    }
  },
  "_source": false,
  "fields": [
    {
      "field": "mysql_host"
    },
    {
      "field": "mysql_instances"
    }
  ],
  "sort": [
    {
      "check_time.keyword": {
        "order": "asc",
        "missing": "_last",
        "unmapped_type": "keyword"
      }
    }
  ]
}

在转换后的DSL上加上索引信息GET /mysql_host_info/_search便可直接运行。

混合SQL和DSL使用

可以将DSL和SQL混合使用，如：

POST _sql
{
  "query": """
  SELECT mysql_host,mysql_instances 
  FROM mysql_host_info 
  ORDER BY check_time ASC
  """,
  "filter": {
    "range": {
      "check_time": {
        "gt": "2022-10-25 15:30:00",
        "boost": 1
      }
    }
  },
  "fetch_size": 10
}

全文索引查询

当使用MATCH和QUERY函数时，会启动全文搜索功能，并可通过SCORE函数来获取搜索评分。如：

## 使用MATCH查询mysql_instances中包含3310的记录
POST _sql
{
  "query": """
  SELECT mysql_host,mysql_instances 
  FROM mysql_host_info 
  WHERE MATCH(mysql_instances,'3310')
  ORDER BY check_time ASC
  """
}

## 使用QUERY查询mysql_instances中包含3310的记录
## 并按照评分排序取TOP 10
POST _sql
{
  "query": """
  SELECT mysql_host,mysql_instances,SCORE() 
  FROM mysql_host_info 
  WHERE QUERY('mysql_instances:3310')
  ORDER BY SCORE() DESC
  LIMIT 10
  """
}

其他操作

• 使用SHOW TABLES来查看所有表信息：

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

• 使用DESCRIBE TABLE_NAME来查看表中字段信息：

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

• 使用SHOW FUNCTIONS来查看函数信息：

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

参考资料

• https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-getting-started.html
• https://zhuanlan.zhihu.com/p/341906989