elasticsearch-sql插件安装与使用

elasticsearch版本为2.2.0

源码地址：https://github.com/NLPchina/elasticsearch-sql/tree/elastic2.2.0

安装方式有以下三种：

1、按照以上链接说明的方式进行安装

./bin/plugin install https://github.com/NLPchina/elasticsearch-sql/releases/download/2.2.0/elasticsearch-sql-2.2.0.zip 

2、下载es-sql插件包，https://github.com/NLPchina/elasticsearch-sql/releases/download/2.2.0/elasticsearch-sql-2.2.0.zip 

解压要$ES_HOME/plugins目录下，重命名 mv elasticsearch-sql-2.2.0 sql

重启es集群

3、下载源码，进行打包编译，mvn assembly:assembly -Dmaven.test.skip=true，target目录下生成压缩包elasticsearch-sql-2.2.0.1.zip，

（本人已编译完成，可以直接下载http://download.csdn.net/detail/l253272670/9746818）

解压要$ES_HOME/plugins目录下，重命名 mv elasticsearch-sql-2.2.0 sql

重启es集群即可。

elasticsearch-sql使用说明：

1、web页面地址，http://localhost:9200/_plugin/sql/

2、简单的sql示例

http://localhost:9200/_sql?sql=select * from indexName limit 10

3、将sql翻译为elasticsearch查询语句

http://localhost:9200/_sql/_explain?sql=select * from indexName limit 10

Basic Usage

• Visit The elasticsearch-sql web front end:

http://localhost:9200/_plugin/sql/

• Simple query

http://localhost:9200/_sql?sql=select * from indexName limit 10

• Explain SQL to elasticsearch query DSL

http://localhost:9200/_sql/_explain?sql=select * from indexName limit 10

SQL Usage

• Query

  SELECT * FROM bank WHERE age >30 AND gender = 'm'

• Aggregation

  select COUNT(*),SUM(age),MIN(age) as m, MAX(age),AVG(age)FROM bank GROUP BY gender ORDER BY SUM(age), m DESC

• Delete

  DELETE FROM bank WHERE age >30 AND gender = 'm'

Beyond sql

• Search

  SELECT address FROM bank WHERE address = matchQuery('880 Holmes Lane') ORDER BY _score DESC LIMIT 3

• Aggregations

  • range age group 20-25,25-30,30-35,35-40

    SELECT COUNT(age) FROM bank GROUP BY range(age, 20,25,30,35,40)

  • range date group by day

    SELECT online FROM online GROUP BY date_histogram(field='insert_time','interval'='1d')

  • range date group by your config

    SELECT online FROM online GROUP BY date_range(field='insert_time','format'='yyyy-MM-dd' ,'2014-08-18','2014-08-17','now-8d','now-7d','now-6d','now')

• ES Geographic

  SELECT * FROM locations WHERE GEO_BOUNDING_BOX(fieldname,100.0,1.0,101,0.0)

• Select type

  SELECT * FROM indexName/type

SQL Features

• SQL Select
• SQL Delete
• SQL Where
• SQL Order By
• SQL Group By
• SQL AND & OR
• SQL Like
• SQL COUNT distinct
• SQL In
• SQL Between
• SQL Aliases
• SQL Not Null
• SQL(ES) Date
• SQL avg()
• SQL count()
• SQL last()
• SQL max()
• SQL min()
• SQL sum()
• SQL Nulls
• SQL isnull()
• SQL now()

Beyond sql features

• ES TopHits
• ES MISSING
• ES STATS
• ES GEO_INTERSECTS
• ES GEO_BOUNDING_BOX
• ES GEO_DISTANCE
• ES GEOHASH_GRID aggregation