Elasticsearch插件:elasticsearch-sql安装和使用

使用此插件,您可以使用熟悉的SQL语法查询elasticsearch。您还可以在SQL中使用ES函数。

有两种方法可以使用此插件:

  1. 使用其余的api 
http://localhost:9200/_sql?sql=select * from indexName limit 10

2. 或者通过浏览器访问 http://localhost:9200/_plugin/sql 


Web前端概述

 

安装

安装为插件:版本(sql插件与Elasticsearch的版本对应关系)

elasticsearch versionlatest versionremarksbranch
1.x1.7.6tested against elastic 1.7.6elastic1.x
2.0.02.0.4delete commands not supportedelastic2.0
2.1.02.1.0.2delete commands not supportedelastic2.1
2.1.12.1.1.1delete commands not supportedelastic2.1.1
2.1.22.1.2.0delete commands not supportedelastic2.1.2
2.2.02.2.0.1delete commands not supportedelastic2.2.0
2.2.12.2.1.0delete commands not supportedelastic2.2.1
2.3.02.3.0.0delete commands not supportedelastic2.3.0
2.3.12.3.1.1delete commands not supportedelastic2.3.1
2.3.22.3.2.0delete commands not supportedelastic2.3.2
2.3.32.3.3.0delete commands not supportedelastic2.3.3
2.3.42.3.4.0delete commands not supportedelastic2.3.4
2.3.52.3.5.0delete commands not supportedelastic2.3.5
2.4.02.4.0.1delete commands not supportedelastic2.4.0
2.4.12.4.1.0delete commands not supportedelastic2.4.1
2.4.22.4.2.1delete commands not supportedelastic2.4.2
2.4.32.4.3.0delete commands not supportedelastic2.4.3
2.4.42.4.4.0delete commands not supportedelastic2.4.4
2.4.52.4.5.0delete commands not supportedelastic2.4.5
2.4.62.4.6.0delete commands not supportedelastic2.4.6
5.0.15.0.1.0delete commands not supportedelastic5.0.1
5.1.15.1.1.0delete commands not supportedelastic5.1.1
5.1.25.1.2.0delete commands not supportedelastic5.1.2
5.2.05.2.0.0delete commands not supportedelastic5.2.0
5.2.15.2.1.0delete commands not supportedelastic5.2.1
5.2.25.2.2.0delete commands not supportedelastic5.2.2
5.3.05.3.0.0delete commands not supportedelastic5.3.0
5.3.15.3.1.0delete commands not supportedelastic5.3.1
5.3.25.3.2.0delete commands not supportedelastic5.3.2
5.3.35.3.3.0delete commands not supportedelastic5.3.3
5.4.05.4.0.0delete commands not supportedelastic5.4.0
5.4.15.4.1.0delete commands not supportedelastic5.4.1
5.4.25.4.2.0delete commands not supportedelastic5.4.2
5.4.35.4.3.0delete commands not supportedelastic5.4.3
5.5.05.5.0.1delete commands not supportedelastic5.5.0
5.5.15.5.1.0delete commands not supportedelastic5.5.1
5.5.25.5.2.0delete commands not supportedelastic5.5.2
5.5.35.5.3.0delete commands not supportedelastic5.5.3
5.6.05.6.0.0delete commands not supportedelastic5.6.0
5.6.15.6.1.0delete commands not supportedelastic5.6.1
5.6.25.6.2.0delete commands not supportedelastic5.6.2
5.6.35.6.3.0delete commands not supportedelastic5.6.3
5.6.45.6.4.0delete commands not supportedelastic5.6.4
5.6.55.6.5.0delete commands not supportedelastic5.6.5
5.6.65.6.6.0 elastic5.6.6
5.6.75.6.7.0 elastic5.6.7
5.6.85.6.8.0 elastic5.6.8
5.6.95.6.9.0 elastic5.6.9
5.6.105.6.10.0 elastic5.6.10
6.0.06.0.0.0 elastic6.0.0
6.0.16.0.1.0 elastic6.0.1
6.1.06.1.0.0 elastic6.1.0
6.1.16.1.1.0 elastic6.1.1
6.1.26.1.2.0 elastic6.1.2
6.1.36.1.3.0 elastic6.1.3
6.1.46.1.4.0 elastic6.1.4
6.2.06.2.0.0 elastic6.2.0
6.2.16.2.1.0 elastic6.2.1
6.2.26.2.2.0 elastic6.2.2
6.2.36.2.3.0 elastic6.2.3
6.2.46.2.4.0 elastic6.2.4
6.3.06.3.0.0 elastic6.3.0
6.3.16.3.1.0 elastic6.3.1

不同Elasticsearch版本的安装命令

Elasticsearch 1.x

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

Elasticsearch 2.0.0

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

Elasticsearch 2.1.0

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

Elasticsearch 2.1.1

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

Elasticsearch 2.1.2

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

Elasticsearch 2.2.0

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

Elasticsearch 2.2.1

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

Elasticsearch 2.3.0

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

Elasticsearch 2.3.1

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

Elasticsearch 2.3.2

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

Elasticsearch 2.3.3

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

Elasticsearch 2.3.4

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

Elasticsearch 2.3.5

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

Elasticsearch 2.4.0

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

Elasticsearch 2.4.1

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

Elasticsearch 2.4.2

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

Elasticsearch 2.4.3

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

Elasticsearch 2.4.4

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

Elasticsearch 2.4.5

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

Elasticsearch 2.4.6

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

Elasticsearch 5.0.1

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

Elasticsearch 5.1.1

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

Elasticsearch 5.1.2

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

Elasticsearch 5.2.0

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

Elasticsearch 5.2.1

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

Elasticsearch 5.2.2

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

Elasticsearch 5.3.0

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

Elasticsearch 5.3.1

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

Elasticsearch 5.3.2

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

Elasticsearch 5.3.3

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

Elasticsearch 5.4.0

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

Elasticsearch 5.4.1

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

Elasticsearch 5.4.2

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

Elasticsearch 5.4.3

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

Elasticsearch 5.5.0

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

Elasticsearch 5.5.1

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

Elasticsearch 5.5.2

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

Elasticsearch 5.5.3

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

Elasticsearch 5.6.0

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

Elasticsearch 5.6.1

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

Elasticsearch 5.6.2

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

Elasticsearch 5.6.3

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

Elasticsearch 5.6.4

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

Elasticsearch 5.6.5

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

Elasticsearch 5.6.6

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

Elasticsearch 5.6.7

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

Elasticsearch 5.6.8

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

Elasticsearch 5.6.9

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

Elasticsearch 5.6.10

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

Elasticsearch 6.0.0

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

Elasticsearch 6.0.1

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

Elasticsearch 6.1.0

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

Elasticsearch 6.1.1

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

Elasticsearch 6.1.2

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

Elasticsearch 6.1.3

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

Elasticsearch 6.1.4

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

Elasticsearch 6.2.0

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

Elasticsearch 6.2.1

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

Elasticsearch 6.2.2

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

Elasticsearch 6.2.3

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

Elasticsearch 6.2.4

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

Elasticsearch 6.3.0

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

Elasticsearch 6.3.1

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

After doing this, you need to restart the Elasticsearch server. Otherwise you may get errors like Invalid index name [sql], must not start with '']; ","status":400}.

执行此操作后,您需要重新启动Elasticsearch服务器。否则你可能会收到错误

Invalid index name [sql], must not start with '']; ","status":400}。

重新启动Elasticsearch服务器

#停止Elasticsearch进程
kill `ps -ef | grep Elasticsearch | grep -v grep | awk '{print $2}'`
#启动Elasticsearch进程
/elasticsearch-5.4.3/bin/elasticsearch -d

基本用法

在elasticsearch 1.x / 2.x上,可以直接访问elasticsearch-sql web前端页面:

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

在elasticsearch 5.x / 6.x上,需要先下载个网站插件 download and extract site.

具体下载地址是:

https://github.com/NLPchina/elasticsearch-sql/releases/download/5.4.1.0/es-sql-site-standalone.zip

启动插件:

cd site-server
npm install express --save
node node-server.js 
  • 简单的查询
http://localhost:9200/_sql?sql=select * from indexName limit 10
  • 解释SQL
http://localhost:9200/_sql/_explain?sql=select * from indexName limit 10

SQL用法

  • 简单查询

      SELECT * FROM bank WHERE age >30 AND gender = 'm'
    
  • 聚合查询

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

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

超越SQL

  • Search

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

    • 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功能

  • 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()
  • SQL floor
  • SQL split
  • SQL trim
  • SQL log
  • SQL log10
  • SQL substring
  • SQL round
  • SQL sqrt
  • SQL concat_ws
  • SQL union and minus

支持JDBC (处于试验阶段的功能)

Check details : JDBC Support

public void testJDBC() throws Exception {
        Properties properties = new Properties();
        properties.put("url", "jdbc:elasticsearch://127.0.0.1:9300/" + TestsConstants.TEST_INDEX);
        DruidDataSource dds = (DruidDataSource) ElasticSearchDruidDataSourceFactory.createDataSource(properties);
        Connection connection = dds.getConnection();
        PreparedStatement ps = connection.prepareStatement("SELECT  gender,lastname,age from  " + TestsConstants.TEST_INDEX + " where lastname='Heath'");
        ResultSet resultSet = ps.executeQuery();
        List<String> result = new ArrayList<String>();
        while (resultSet.next()) {
              System.out.println(resultSet.getString("lastname") + "," + resultSet.getInt("age") + "," + resultSet.getString("gender"))
        }
        ps.close();
        connection.close();
        dds.close();
    }

 

Beyond sql features

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

参考来源:https://github.com/NLPchina/elasticsearch-sql

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 博客之星2020 设计师:CY__0809 返回首页
实付 19.90元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值