[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 其他查询技巧

• 官方文档

  • SQL>Basic Queries - Opendistro
  • SQL>Complex Queries - Opendistro
  • SQL>Functions - Opendistro
  • SQL>Aggregation Functions - Opendistro

• 查看有哪些索引（表）

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 推荐文献

• https://opendistro.github.io/for-elasticsearch/

  • https://opendistro.github.io/for-elasticsearch-docs/docs/sql/
  • https://opendistro.github.io/for-elasticsearch-docs/docs/sql/workbench/
  • https://opendistro.github.io/for-elasticsearch/

• opendistro-for-elasticsearch/sql - github

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'

• 使用opendistro_sql插件实现SQL语句查询集群数据 - aliyun

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"
}

• Elasticsearch插件：elasticsearch-sql安装和使用 - CSDN

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 参考文献

• Can't compute sql sub-queries - Github/opendistro-sql#sql
• Complex queries#Subquery - Opendistro

X 推荐文献

• Elasticsearch——使用_cat查看Elasticsearch状态 - 博客园
• es查看集群信息命令_cat和_cluster - 博客园
• ElasticSearch中的date类型 - CSDN