[ElasticSearch] 数据查询分析 | 常用API URL

1 ES常用API URL

https://127.0.0.1:9200/
    查询集群基本信息(版本/lucene版本/构建信息/集群名称等)

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

• 带密码认证的curl请求 (demo)

---- 方式1
$ curl -v -X GET 'http://127.0.0.1:9200/_cluster/health?pretty' -u 'test:password' \
> --header 'Content-Type: application/json'

---- 方式2
$ curl -v -X GET 'http://127.0.0.1:9200/_cluster/health?pretty' \
> --header 'Content-Type: application/json' \
> --header 'Authorization: Basic dGVzdDpwYXNzd29yZA=='
Note: Unnecessary use of -X or --request, GET is already inferred.
*   Trying 127.0.0.1:9200...
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
  0     0    0     0    0     0      0      0 --:--:-- --:--:-- --:--:--     0* Connected to 127.0.0.1 (127.0.0.1) port 9200 (#0)
> GET /_cluster/health?pretty HTTP/1.1
> Host: 127.0.0.1:9200
> User-Agent: curl/7.83.1
> Accept: */*
> Content-Type: application/json
> Authorization: Basic dGVzdDpwYXNzd29yZA==
>
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< content-type: application/json; charset=UTF-8
< content-length: 471
<
{ [471 bytes data]
100   471  100   471    0     0   1021      0 --:--:-- --:--:-- --:--:--  1032{
  "cluster_name" : "dev_es_xxx",
  "status" : "green",
  "timed_out" : false,
  "number_of_nodes" : 3,
  "number_of_data_nodes" : 3,
  "active_primary_shards" : 274,
  "active_shards" : 475,
  "relocating_shards" : 0,
  "initializing_shards" : 0,
  "unassigned_shards" : 0,
  "delayed_unassigned_shards" : 0,
  "number_of_pending_tasks" : 0,
  "number_of_in_flight_fetch" : 0,
  "task_max_waiting_in_queue_millis" : 0,
  "active_shards_percent_as_number" : 100.0
}

* Connection #0 to host 127.0.0.1 left intact

• header['Authorization'] BASIC 的值格式

Authorization: Basic base64('username:password')
例如，用户名=test，密码=password，则：Authorization: Basic dGVzdDpwYXNzd29yZA==

• 当ES集群启用HTTPS协议时

继续采用上面的方式请求时，将报：curl: (52) Empty reply from server 错误（本质原因是：不支持HTTP协议）
怎么正确的请求呢？参见↓

$ curl -v -X GET 'https://127.0.0.1:9200/_cluster/health?pretty' -k \
> --header 'Content-Type: application/json' \
> --header 'Authorization: Basic dGVzdDpwYXNzd29yZA=='

-k : 忽略TLS/SSL认证

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 deviceId,start_time,sum_fuel_consum from tsp_device_drive_behavior"
}

聚合查询SQL

POST /_opendistro/_sql
{
    "query":"select deviceId , 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 deviceId, 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_device_drive_behavior"
}

• 聚合查询SQL

POST /_opendistro/_sql?format=json
{
    "query":"select deviceId , 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 deviceId, 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 %;
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.deviceId = b.deviceId and a.dt = b.dt"
}

• 日期函数

POST /_opendistro/_sql
{
    "query":"select deviceId , 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 deviceId, date_format(dt, 'yyyy-MM-dd'), mileage, power_consum, fuel_consum"
}

POST /_opendistro/_sql
{
    "query":"select deviceId , date_format(dt, 'yyyyMM') as timeQuantum, mileage , power_consum as powerConsum , fuel_consum as fuelConsum FROM dws_vb_drive_daily_di group by deviceId, date_format(dt, 'yyyyMM'), mileage, power_consum, fuel_consum"
}

2.5 插件应用/查询数据 : Postman

含：ES的用户密码认证
Postman 提供的 Curl Shell

curl --location 'http://xx.yy.xx.hh:9200/_opendistro/_sql?format=json' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic dGVzdDpwYXNzd29yZA==' \
--data '{
    "query": "SELECT * FROM xxxxxx_status_snapshot where deviceId = '\''5464343543255'\''"
}'

• header['Authorization'] BASIC 的值格式

Authorization: Basic base64('username:password')
例如，用户名=test，密码=password，则：Authorization: Basic dGVzdDpwYXNzd29yZA==

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.deviceId
FROM ( 
	select 
		deviceId 
	FROM dws_vb_drive_daily_di 
	where 1=1 
		AND mileage>0 
		AND deviceId = 'RJZXHWBCSYC000001' 
) as a

• 聚合的嵌套查询：不支持

select 
	a.deviceId,a.fuelMileage,a.powerMileage,a.totalAvgPowerConsum,a.totalAvgFuelConsum,a.mileage,a.tripDayCount 
FROM ( 
	select 
		deviceId
		,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 deviceId = 'RJZXHWBCSYC000001' 
	group by deviceId 
) 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