elasticsearch-sql插件安装与使用

版权声明:本文为博主原创文章,转载请注明出处。 https://blog.csdn.net/L253272670/article/details/54861703

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

没有更多推荐了,返回首页