1. 简介：
es-sql 以插件的方式运行在es中，拦截_sql开头的请求，将请求中的sql语句解释成es的DSL查询语句，在es内部调用执行后，将结果返回给用户。
部署后的效果：
rest API 调用 http://xxxx:9200/_sql?sql=select * from ds_alarm
2. 安装
下载插件
https://github.com/NLPchina/elasticsearch-sql
版本要与es版本对应
将插件压缩包里的文件解压到/opt/elasticsearch-5.6.16/plugins/sql目录下
将sql文件夹的权限赋权给es用户
chown -R es:elasticsearch /opt/elasticsearch-5.6.16/plugins/sql
然后重启es 即可。
3. 访问
此时即可通过sql 查询es:
http://10.168.4.60:9200/_sql?sql=SELECT x,y,sfdz,sfsj,zjhm from ds_alarm ORDER BY sfsj desc
4. es-sq 可视化页面（方便测试sql）
es-sql-site-standalone.zip
下载地址
（需要 先安装node.js 和npm 环境）
解压es-sql-site-standalone.zip后
> cd site-server
> npm install express –save
# /opt/site/site-server/site_configuration.json中修改端口号为9000
> node node-server.js
5. 支持的sql
大部分简单的sql都能支持
用法见官方文档
https://github.com/NLPchina/elasticsearch-sql/wiki
http://10.168.4.60:9200/_sql?sql=SELECT x,y,sfdz,sfsj,zjhm from ds_alarm ORDER BY sfsj desc
支持多表关联查询
不支持
SELECT a.command_id,a.bjrxm,c.alarm_id FROM ds_alarm a,ds_command c where a.command_id = c.command_id
支持
SELECT a.command_id,a.bjrxm,c.alarm_id FROM ds_alarm a Join ds_command c on a.command_id=c.command_id
支持 inner join 和 left join
join 只支持两张表
join中， on 后面的条件只支持and;
join 查询结果不支持 order by 或 group by , 且 limit 不支持加 offset
支持between and，
支持给字段取别名，
支持在join 之后 添加条件
支持对单表查询结果order by
不支持 '常量 field' 来在输出结果中增加列 如'1 jllx'
iner join 示例：
SELECT x,y,sfdz,sfsj,zjhm from ds_alarm ORDER BY sfsj desc
SELECT a.x,a.y,a.sfdz,a.sfsj,a.zjhm,a.bjr_lxdh as bjrLxdh,a.command_id as commandId,a.aymc,a.swzk,a.ssxqmc
from ds_alarm a JOIN ds_command b on a.command_id = b.command_id
where a.sfsj BETWEEN '2019/11/15 18:11:10' and '2019/11/16 11:11:10'
left join 示例
支持
SELECT a.aymc,a.ssxqmc,a.sfsj,a.sfdz,COALESCE(b.handle_status,'1') handleStatus,a.command_id commandId,a.x,a.y
FROM ds_alarm a LEFT JOIN handle_cases b
ON a.command_id = b.command_id
WHERE a.sfsj BETWEEN '2019/11/15 18:11:10' and '2019/11/16 11:11:10'
支持的语法：
SQL Statements
SQL Select
SQL Delete
SQL Where
SQL Order By
SQL Group By
SQL Limit (default is 200)
Conditions:
SQL Like
SQL AND & OR
SQL COUNT distinct
SQL In
SQL Between
SQL Aliases
SQL(ES) Date
SQL now()
SQL NOT
Basic aggregations:
SQL avg()
SQL count()
SQL last()
SQL max()
SQL min()
SQL sum()
SQL Fields
Fields can be listed out by exact field name or used with the include/exclude syntax for use with wildcards.
include('d*') - include all fields starting with "d"
exclude('age') - include all fields except "age"
include('*Name'), exclude('lastName') - include all fields that end with "Name" except "lastName"
支持es的一些特性
Some more features using ElasticSearch capabilities
ES TopHits
ES MISSING
ES STATS
ES EXTENDED_STATS
ES PERCENTILES
ES TERMS/TERM
ES IDS syntax: IDS_QUERY(type, ids..)
ES SCRIPTED_METRIC (read about it on Aggregations page)
ES QUERY_STRING
支持地理信息的计算查询,详见
https://github.com/NLPchina/elasticsearch-sql/wiki/Geographic-Queries
SELECT * FROM 表名 WHERE GEO_INTERSECTS(location,'POLYGON ((102 2, 103 2, 103 3, 102 3, 102 2))
SELECT * FROM 表名 WHERE GEO_DISTANCE(location,'1km',100.5,0.5)
SELECT * FROM ds_alarm WHERE GEO_DISTANCE(center,'1m','1km',100.5,0.50001)