elasticsearch-sql插件安装与使用

待我称王封你为后i 2022-06-13 05:10 931阅读 0赞

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

[.NET]: http://lib.csdn.net/base/dotnet