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

news/2024/12/22 1:20:06/

使用此插件,您可以使用熟悉的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


http://www.ppmy.cn/news/494241.html

相关文章

linux软路由 iptv,LEDE x64软路由实现任意网口观看上海电信4K IPTV或上网

本帖最后由 tony1975 于 2019-7-7 01:05 编辑 (2)修改无线路由器配置 如果每个卧室的有线网口都可以供机顶盒使用,上述工作完成后就大功告成了。 但是,本人二层房间只有一个有线网口,原来是连接无线AP使用,如果分配给了机顶盒,就等于没有了其他信息点,所以,之前升级了原…

kettle 7 在linux执行,Kettle 在Linux下执行pan.sh 出现报错 [HIVE2] couldn't be found

源码如下:[dcp@etlsvr1 pdi-ce-7.0.0.0-25]$ ./pan.sh -file=lnr.ktr ####################################################################### WARNING: no libwebkitgtk-1.0 detected, some features will be unavailable Consider installing the package with apt-get…

Docker下RabbitMQ安装配置

文章目录 1. 搜索 MQ2. 拉取镜像3. 启动docker镜像4. 查看web管理界面看是否可以访问 端口访问 RabbitMQ 节点绑定到端口&#xff08;开放服务器 TCP 套接字&#xff09;以接受客户端和 CLI 工具连接。其他进程和工具&#xff08;例如 SELinux&#xff09;可能会阻止 RabbitMQ …

编译vs2017项目报错

报错&#xff1a;error C2061: 语法错误: 标识符“BOOL” 解决方案&#xff1a; 添加头文件&#xff1a;#include <WinDef.h> 报错&#xff1a; fatal error C1189: #error: "No Target Architecture" 解决方案&#xff1a;添加头文件&#xff1a;#include &l…

webSocket(一)

一、webSocket是什么&#xff1f; webSocket协议是基于TCP的一种新的协议&#xff0c;它实现了浏览器与服务器全双工通信&#xff0c;允许服务器主动发送信息给客户端。使用场景&#xff1a;弹幕&#xff0c;聊天&#xff0c;实时监控&#xff0c;股票行情推送。 形式为&…

Spring Cloud 微服务开发:入门、进阶与源码剖析 —— 10.2 Spring Cloud Sleuth 入门

10.2 Spring Cloud Sleuth 入门 10.2.1 Spring Cloud Sleuth 入门案例 先简单介绍一下案例目标,在本案例中,我们将会使用Feign、RestTemplate和新线程的方式调用下游服务,来观察每个请求在整体调用链中的TraceID和SpanID是否一致,包含两个服务,一个是Sleuth-Provider,另…

FastText的内部机制

文章来源&#xff1a;https://towardsdatascience.com/fasttext-under-the-hood-11efc57b2b3 译者 | Revolver fasttext是一个被用于对词向量和句子分类进行高效学习训练的工具库&#xff0c;采用c编写&#xff0c;并支持训练过程中的多进程处理。你可以使用这个工具在监督和…

关于 Linux中systemd的一些笔记

写在前面 嗯&#xff0c;准备RHCA&#xff0c;学习整理这部分知识博文内容涉及&#xff1a; systemd简述对于unit的信息的介绍通过systemctl命令控制Service unit的DemoService unit配置文件内容,权值的一些介绍 傍晚时分&#xff0c;你坐在屋檐下&#xff0c;看着天慢慢地黑下…