查询es时通过curl查询比较复杂,可以安装一个插件实现用sql查询:
1.到es的plugin目录下,安装插件,插件的版本与ES的版本都有对应关系。具体可参照:https://github.com/NLPchina/elasticsearch-sql。我的ES是elasticsearch-5.5.2,对应的插件是5.5.2.0版本
cd /mnt/elasticsearch-5.5.2/plugins
install https://github.com/NLPchina/elasticsearch-sql/releases/download/5.5.2.0/elasticsearch-sql-5.5.2.0.zip
安装成功之后可以在ES的plugins目录下看到sql文件
2.如果是ES集群,则每台都需要安装,并重启服务
- 查找ES进程号:ps -ef | grep elastic
- 杀掉ES进程:kill -9 pid
- 重启ES:把目录切换到elasticsearch的bin目录下,然后执行:sh elasticsearch -d
3.安装完插件之后就方便了
之前查询语句:
curl 'http://es-1:10200/index/type/_search?pretty' -H 'Content-Type:application/json' -d '{
"query":{
"bool":{
"must":[
{
"match":{
"activity_id":"1114"
}
},{
"match":{
"dt_ymd":"20190814"
}
}
]
}
}
}'
现在查询语句:
curl 'http://es-1:10200/_sql?pretty' -H 'Content-Type:application/json' -d 'select * from index where dt_ymd=20190814 and activity_id=1114'
好棒!!!
ps:这里有个坑,就是group by的时候,如果后面跟两个字段,默认只会返回第二个字段的10条数据。
curl http://es-1:9200/_sql -H 'Content-Type: application/json' -d 'SELECT sum( kpi_uv) FROM index where dt_ymd = 20190730 and dt_hour < 6 and cnv_node_id = 10000000 group by dt_hour, terms('field'='activity_id','size'=1000,'alias'='activity_id')'
其它查询语句:
聚合函数
curl 'http://es-1:9200/_sql?pretty' -H 'Content-Type:application/json' -d 'select sum(kpi_uv) from bi_recmd_tunan_result where dt_ymd=20190718 and dt_hour<11 and cnv_node_id=10000003 group by channel_id'
not in
curl 'http://es-1:10200/_sql?pretty' -H 'Content-Type:application/json' -d 'select * from c24hour_count_orders where c24halfhour_count_orders_day=20190830 and city_id not in ("1101")'
不等于
curl 'http://es-1:10200/_sql?pretty' -H 'Content-Type:application/json' -d 'select * from courier_location_index_20190731 where courierId=5013981 and cityId <> 1101'
直接使用distinct不支持去重,可以用group by
curl 'http://es-1:10200/_sql?pretty' -H 'Content-Type:application/json' -d 'select * from courier_location_index_20190731 where courierId=5013981 group by cityId'