[ElasticSearch] 数据查询分析 | 常用API URL
1 ES常用API URL
https://127.0.0.1:9200/
http://127.0.0.1:9200/_all?pretty
https://127.0.0.1:9200/_cluster/health?pretty
https://127.0.0.1:9200/_cat/health?v&pretty&h=status
status的意义:
+ green 绿灯: 所有分片都正确运行,集群非常健康。
+ yellow 黄灯: 所有主分片都正确运行,但是有副本分片缺失。
这种情况意味着 ES 当前还是正常运行的,但是有一定风险。
注意,在 Kibana4 的 server 端启动逻辑中,即使是黄灯状态,Kibana 4 也会拒绝启动,死循环等待集群状态变成绿灯后才能继续运行。
+ red 红灯: 有主分片缺失。这部分数据完全不可用。而考虑到 ES 在写入端是简单的取余算法,轮到这个分片上的数据也会持续写入报错。
https://127.0.0.1:9200/_cat
https://127.0.0.1:9200/_cat/indices?pretty
GET /_cat/indices/system-log-*
https://127.0.0.1:9200/_cat/shards?v
查看碎片情况(主要看: UNASSIGNED 的碎片)
https://127.0.0.1:9200/_cluster/health?level=shards&pretty
https://127.0.0.1:9200/_cat/plugins
[查看安装的插件(例如: ik分词器插件)]
https://127.0.0.1:9200/_cat/health
https://127.0.0.1:9200/_cat/master?pretty
https://127.0.0.1:9200/_cat/nodes?v
https://127.0.0.1:9200/_nodes?pretty
https://127.0.0.1:9200/_nodes/_master?pretty
http://127.0.0.1:9200/_cat/indices
[查看所有index]
GET /_cat/indices
GET /_cat/indices/tsp_*
GET /_cat/indices/eop_*
GET /_cat/indices/dws_*
https://127.0.0.1:9200/<索引名>/_close
https://127.0.0.1:9200/<索引名>/_recovery?pretty
https://127.0.0.1:9200/_searchguard/authinfo?pretty
https://127.0.0.1:9200/_searchguard/health
https://127.0.0.1:9200/_xpack?pretty
https://127.0.0.1:9200/_xpack/security/_authenticate?pretty
https://127.0.0.1:9200/_xpack/security/user/elastic/_password
https://127.0.0.1:9200/_xpack/license/
curl -XPOST --insecure -u admin:admin 'https://172.15.3.xx:9200/_xpack/license/start_trial?acknowledge=true&pretty
https://license.elastic.co/registration
https://repo1.maven.org/maven2/com/floragunn/search-guard-tlstool/1.7/search-guard-tlstool-1.7.tar.gz
https://artifacts.elastic.co/downloads/kibana/kibana-6.4.1-linux-x86_64.tar.gz
2 基于 opendistro-sql 插件的应用
2.1 插件简述【待完善】
2.2 插件安装
2.3 插件应用/查询数据 : Kibana - SQL Workbench
在安装有opendistro-sql插件的Kibana中使用
select
*
from dws_vb_drive_daily_di
where 1=1 and dt >= '2023-02-27' and dt <= '2023-02-28'
2.4 插件应用/查询数据 : Kibana - Dev-Tool / Curl
2.4.1 查询方式1(schema与datarows分离)
此方式,不带
format=json
参数,便于查询明细数据、聚合数据,尤其是便于查询聚合(group by)
数据
明细查询SQL
POST /_opendistro/_sql
{
"query":"select vin,start_time,sum_fuel_consum from tsp_vehicle_drive_behavior"
}
聚合查询SQL
POST /_opendistro/_sql
{
"query":"select vin , dt as eventDate, count(1) as cnt from dws_vb_drive_daily_di where 1=1 and dt >= '2023-02-27' and dt <= '2023-02-28' group by vin, dt order by dt asc"
}
2.4.2 查询方式2(format=json,schema与datarows合并为hits)
查询明细时,带上
format=json
参数便于查询明细数据,不便于查询聚合(group by)
数据
- 明细查询SQL
POST /_opendistro/_sql?format=json
{
"query":"select * from tsp_vehicle_drive_behavior"
}
- 聚合查询SQL
POST /_opendistro/_sql?format=json
{
"query":"select vin , dt as eventDate, count(1) as cnt from dws_vb_drive_daily_di where 1=1 and dt >= '2023-02-27' and dt <= '2023-02-28' group by vin, dt order by dt asc"
}
2.4.3 其他查询技巧
-
官方文档
-
查看有哪些索引(表)
show tables like %;
describe tables like %;
- 支持 join
POST /_opendistro/_sql
{
"query":"select b.* from dws_vb_drive_daily_di a inner join dws_vb_drive_daily_di b on a.vin = b.vin and a.dt = b.dt"
}
- 日期函数
POST /_opendistro/_sql
{
"query":"select vin , date_format(dt, 'yyyy-MM-dd') as timeQuantum, mileage , power_consum as powerConsum , fuel_consum as fuelConsum FROM dws_vb_drive_daily_di group by vin, date_format(dt, 'yyyy-MM-dd'), mileage, power_consum, fuel_consum"
}
POST /_opendistro/_sql
{
"query":"select vin , date_format(dt, 'yyyyMM') as timeQuantum, mileage , power_consum as powerConsum , fuel_consum as fuelConsum FROM dws_vb_drive_daily_di group by vin, date_format(dt, 'yyyyMM'), mileage, power_consum, fuel_consum"
}
Y 推荐文献
POST https://<host>:<port>/_opendistro/_sql
{
"query": "SELECT * FROM my-index LIMIT 50"
}
curl -XPOST https://localhost:9200/_opendistro/_sql -u admin:admin -k -d '{"query": "SELECT * FROM my-index LIMIT 10"}' -H 'Content-Type: application/json'
GET _opendistro/_sql?sql=select * from <your_index> limit 50
POST _opendistro/_sql
{
"query": "SELECT * FROM <your_index> LIMIT 50"
}
POST _opendistro/_sql?format=json|csv|raw
{
"query": "SELECT * FROM my-index LIMIT 50"
}
http://localhost:9200/_sql?sql=select * from indexName limit 10
http://localhost:9200/_plugin/sql
2.5 opendistro-sql插件的局限性
Q1 支持在WHERE/FROM的非聚合嵌套查询(子查询),但不支持聚合[group by]的嵌套查询(子查询)
样例如下:
- 非聚合的嵌套查询:支持
select
a.vin
FROM (
select
vin
FROM dws_vb_drive_daily_di
where 1=1
AND mileage>0
AND vin = 'RJZXHWBCSYC000001'
) as a
- 聚合的嵌套查询:不支持
select
a.vin,a.fuelMileage,a.powerMileage,a.totalAvgPowerConsum,a.totalAvgFuelConsum,a.mileage,a.tripDayCount
FROM (
select
vin
,SUM(fuel_mileage) AS fuelMileage
,SUM(ev_mileage) AS powerMileage
,AVG(avg_power_consum) AS totalAvgPowerConsum
,AVG(avg_fuel_consum) AS totalAvgFuelConsum
, sum(mileage) as mileage
,count(*) as tripDayCount
FROM dws_vb_drive_daily_di
where 1=1
AND mileage>0
AND vin = 'RJZXHWBCSYC000001'
group by vin
) as a
2.5.X 参考文献
X 推荐文献
本文作者:
千千寰宇
本文链接: https://www.cnblogs.com/johnnyzen
关于博文:评论和私信会在第一时间回复,或直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
日常交流:大数据与软件开发-QQ交流群: 774386015 【入群二维码】参见左下角。您的支持、鼓励是博主技术写作的重要动力!
本文链接: https://www.cnblogs.com/johnnyzen
关于博文:评论和私信会在第一时间回复,或直接私信我。
版权声明:本博客所有文章除特别声明外,均采用 BY-NC-SA 许可协议。转载请注明出处!
日常交流:大数据与软件开发-QQ交流群: 774386015 【入群二维码】参见左下角。您的支持、鼓励是博主技术写作的重要动力!