elasticsearch官方已支持SQL查询

背景

平时使用Elasticsearch的时候，偶尔会在Kibana中使用Query DSL来查询数据。每次要用到Query DSL时都基本忘光了，需要重新在回顾一遍，最近发现Elasticsearch已经支持SQL查询了（6.3版本以后）

 

简介

Elasticsearch SQL是一个X-Pack组件，它允许针对Elasticsearch实时执行类似SQL的查询。无论使用REST接口，命令行还是JDBC，任何客户端都可以使用SQL对Elasticsearch中的数据进行原生搜索和聚合数据。可以将Elasticsearch SQL看作是一种翻译器，它可以将SQL翻译成Query DSL。

Elasticsearch SQL具有如下特性：

• 原生支持：Elasticsearch SQL是专门为Elasticsearch打造的。

• 没有额外的零件：无需其他硬件，处理器，运行环境或依赖库即可查询Elasticsearch，Elasticsearch SQL直接在Elasticsearch内部运行。

• 轻巧高效：Elasticsearch SQL并未抽象化其搜索功能，相反的它拥抱并接受了SQL来实现全文搜索，以简洁的方式实时运行全文搜索。

学前准备

学习之前我们需要先对Elasticsearch有所了解，并安装好Elasticsearch和Kibana，这里安装的是7.5.1版本

 

第一个SQL查询

我们使用SQL来查询下前10条记录，可以通过format参数控制返回结果的格式，txt表示文本格式，看起来更直观点，默认为json格式。

在Kibana的Console中输入如下命令：

POST /_sql?format=txt
{
  "query": "SELECT id,username FROM user_alias LIMIT 10"
}

结果如下：

      id       |   username    
---------------+---------------
272079         |1811111 
531630         |雨后彩虹861        
437148         |仰_仰            
272458         |18111111
398174         |好347           
443638         |C-K831         
272595         |571111
431512         |伏羲138          
448108         |言西早655         
272602         |319511111

将SQL转化为DSL

当我们需要使用Query DSL时，也可以先使用SQL来查询，然后通过Translate API转换即可。

例如我们翻译以下查询语句：

POST /_sql/translate
{
  "query": "SELECT id,username FROM user_alias where id>10 LIMIT 10"
}

 

结果如下：

{
  "size" : 10,
  "query" : {
    "range" : {
      "id" : {
        "from" : 10,
        "to" : null,
        "include_lower" : false,
        "include_upper" : false,
        "boost" : 1.0
      }
    }
  },
  "_source" : {
    "includes" : [
      "id",
      "username"
    ],
    "excludes" : [ ]
  },
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

SQL和DSL混合使用

我们还可以将SQL和Query DSL混合使用，比如使用Query DSL来设置过滤条件。

例如查询id在30-35之间的记录，可以使用如下查询语句：

POST /_sql?format=txt
{
  "query": "SELECT id,username FROM user_alias where id>1 LIMIT 10",
   "filter": {
        "range": {
            "id": {
                "gte" : 30,
                "lte" : 35
            }
        }
    },
    "fetch_size": 10
}

结果如下：

      id       |   username    
---------------+---------------
30             |AGP_D5F74739   
34             |AGP_AE2D5D     
33             |AGP_68FA4A34   
35             |P185111111

常用SQL操作

语法

在ES中使用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查询ES中的数据，不仅可以使用一些SQL中的函数，还可以使用一些ES中特有的函数。

查询支持的函数

我们可以使用SHOW FUNCTIONS语句查看所有支持的函数，比如搜索所有带有DATE字段的函数可以使用如下语句。

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

结果如下：

     name      |     type      
---------------+---------------
CURDATE        |SCALAR         
CURRENT_DATE   |SCALAR         
DATEADD        |SCALAR         
DATEDIFF       |SCALAR         
DATEPART       |SCALAR         
DATETRUNC      |SCALAR         
DATE_ADD       |SCALAR         
DATE_DIFF      |SCALAR         
DATE_PART      |SCALAR         
DATE_TRUNC     |SCALAR

全文搜索函数

全文搜索函数是ES中特有的，当使用MATCH或QUERY函数时，会启用全文搜索功能，SCORE函数可以用来统计搜索评分。

MATCH()

使用MATCH函数查询username中包含A的记录。

POST /_sql?format=txt
{
  "query": "SELECT id,username,SCORE() FROM user_alias WHERE MATCH(username,'A') LIMIT 10"
}

结果如下：

      id       |   username    |    SCORE()    
---------------+---------------+---------------
448961         |A东营记账公司李       |2.3096764      
461619         |A百川汇机车和        |2.5565019      
464033         |A和普-激光热成       |2.5565019      
330256         |A-夏小北          |3.8531501      
448411         |A-吴凯           |4.465216       
333387         |A鑫盛网络湖南道       |2.3705633      
474269         |A清香茶居茶叶批       |2.3096764      
475386         |A海绵            |4.465216       
476178         |A能优-曾龙         |3.2514358      
334684         |A蓓贝语瑶-叶键       |2.6233978

QUERY()

使用QUERY函数查询username中包含A的记录。

POST /_sql?format=txt
{
  "query": "SELECT id,username,SCORE() FROM user_alias WHERE QUERY('username:A') LIMIT 10"
}

SQL CLI

如果你不想使用Kibana来使用ES SQL的话，也可以使用ES自带的SQL CLI来查询，该命令位于ES的bin目录下。

使用如下命令启动SQL CLI：

elasticsearch-sql-cli http://localhost:9200

然后直接输入SQL命令即可查询了，注意要加分号。

局限性

使用SQL查询ES有一定的局限性，没有原生的Query DSL那么强大，对于嵌套属性和某些函数的支持并不怎么好，但是平时用来查询下数据基本够用了。

参考资料

官方文档：https://www.elastic.co/guide/en/elasticsearch/reference/7.6/xpack-sql.html