安装es-sql 插件
- 下载插件安装包,找到es对应版本的安装包
wget https://github.com/NLPchina/elasticsearch-sql/releases/download/6.2.4.0/elasticsearch-sql-6.2.4.0.zip
- 将安装包解压,放到es安装路径下的plugins目录下
# 如果已经安装了其他插件需要,新建一个目录,然后把zip包拷贝到新建的目录下解压
cd {es_home}/elasticsearch-6.2.4/plugins/
mkdir elasticsearch
mv {sqlzip_home}/elasticsearch-sql-6.2.4.0.zip .
unzip elasticsearch-sql-6.2.4.0.zip
mv elasticsearch/* .
- 修改es配置文件,支持跨域
cd {es_home}/elasticsearch-6.2.4/config
vi elasticsearch.yml
# 添加
http.cors.enabled: true
http.cors.allow-origin: "*"
http.cors.allow-methods: "GET,POST"
http.cors.allow-credentials: true
- 重启es服务
./elasticsearch -d
安装es-sql 可是化工具
准备工作,需要安装node.js,修改es配置文件,支持跨域操作
1.下载安装文件
下载地址
wget https://github.com/NLPchina/elasticsearch-sql/releases/download/5.4.1.0/es-sql-site-standalone.zip
2.解压
unzip es-sql-site-standalone.zip
解压后悔生成两个文件夹, _site和site-server
3.编译
cd site-server/
npm install express --save
# 修改端口号,默认是8080
vi site_configuration.json
4. 后台启动
nohup node node-server.js &
5.访问
访问地址
在版本号后面添加es的http访问地址
http://ip:port/
es-sql 语句
参考:https://github.com/NLPchina/elasticsearch-sql/wiki/Join
# 普通条件查询
select * from index where day = "20190722"
# 普通聚合查询
select count(*)as counts, day from index where day between '20190722' and '20190723' group by day
select count(*) from index where day = "20190722"
# 父子表 nested,childFile查询
# 子查询 nested
select * from index
where nested("message",message.day="20191218")
# 子查询聚合 group by 只能是keyword
select nested("message.action"),count(*) from index
where day = "20191218"
group by terms(field="message.action",nested="message")
# 关联查询 ,两个索引关联查询
select a.agentId as a1,b.agentId as b1 from index1 b join index2 a
on a.agentId = b.agentId