Elasticsearch SQL查询

ES SQL查询的用法跟MYSQL基本一样，熟悉MYSQL的同学，可以直接通过SQL查询ES数据。

1.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语法结构跟MYSQL类似都是SQL标准语法结构，区别就是SQL标准的支持程度。

SQL例子

// emp 就是ES索引名
SELECT * FROM emp LIMIT 1;

SELECT gender AS g, COUNT(*) AS c FROM emp GROUP BY gender;

SELECT MIN(salary) AS min, MAX(salary) AS max, AVG(salary) AS avg, COUNT(*) AS count FROM emp;

都是标准的SQL写法。

2.ES支持的SQL命令

2.1.SHOW COLUMNS

查询ES索引的字段和类型

语法：

SHOW COLUMNS [ FROM | IN ]?
    [table identifier | 
    [LIKE pattern] ]    

例子：

POST /_sql?format=txt
{
    "query": "SHOW COLUMNS FROM library"
}

输出：

    column     |     type      |    mapping    
---------------+---------------+---------------
author         |VARCHAR        |text           
author.keyword |VARCHAR        |keyword        
name           |VARCHAR        |text           
name.keyword   |VARCHAR        |keyword        
page_count     |BIGINT         |long           
release_date   |TIMESTAMP      |datetime       

2.2.SHOW TABLES

将ES中所有的索引都列出来

例子：

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

输出：

          name          |     type      |     kind      
------------------------+---------------+---------------
.apm-agent-configuration|BASE TABLE     |INDEX          
.kibana                 |VIEW           |ALIAS          
.kibana_1               |BASE TABLE     |INDEX          
.kibana_task_manager    |VIEW           |ALIAS          
.kibana_task_manager_1  |BASE TABLE     |INDEX          
library                 |BASE TABLE     |INDEX          
order                   |BASE TABLE     |INDEX                 
shops                   |BASE TABLE     |INDEX          

2.3.SHOW FUNCTIONS

展示ES支持的SQL函数有哪些

例子：

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

输出：

      name       |     type      
-----------------+---------------
AVG              |AGGREGATE      
COUNT            |AGGREGATE      
FIRST            |AGGREGATE      
FIRST_VALUE      |AGGREGATE      
LAST             |AGGREGATE      
LAST_VALUE       |AGGREGATE      
MAX              |AGGREGATE      
MIN              |AGGREGATE      
SUM              |AGGREGATE      
KURTOSIS         |AGGREGATE     
.....忽略.....