使用SQL查询ES数据

32 进阶：使用SQL查询ES数据

环境准备

需要首先安装ES8.x 和 Kibana8.x，自行安装

利用脚本导入测试数据

这里使用的是python脚本，直接运行即可
Elasticsearch([“https://elastic:=k8dCIlKnwM=mSXHVGO8@192.168.64.7:9200”], verify_certs=False)
这一步部分填写你自己的信息即可

from elasticsearch import Elasticsearch
from faker import Faker
import ssl
import random
es = Elasticsearch(["https://elastic:=k8dCIlKnwM=mSXHVGO8@192.168.64.7:9200"], verify_certs=False)
# 连接到 Elasticsearch 实例# 创建一个 Faker 实例，用于生成有意义的假数据
fake = Faker()# 创建索引（类似于数据库中的表）
index_name = 'test_sql_query4'
es.indices.create(index=index_name, ignore=400)# 插入 2000 条有意义的数据
for i in range(2000):doc = {'name': random.choice(['秦', '楚', '齐', '燕', '韩', '魏', '赵']),'age': fake.random_int(min=18, max=99, step=1),'city': fake.city(),'occupation': fake.job(),'salary': fake.random_int(min=30000, max=100000, step=1000),'timestamp': fake.date_time_this_decade().strftime("%Y-%m-%d")}es.index(index=index_name, body=doc)print("数据插入完成。")

如何按照上面的步骤，安装成功后就可以学习今天的内容了

SQL学习

# 解析SQL
POST /_sql/translate
{"query": """SELECT * FROM "test_sql_query4" order by age  LIMIT 10"""
}

基本查询

POST /_sql?format=txt
{"query": """SELECT * FROM "test_sql_query4""""
}

排序查询

POST /_sql?format=txt
{"query": """SELECT * FROM "test_sql_query4" order by age desc"""
}

过滤查询

POST /_sql?format=txt
{"query": """SELECT * FROM "test_sql_query4" where name='秦' order by age desc"""
}POST /_sql?format=txt
{"query": """SELECT * FROM "test_sql_query4" where name in ('秦','赵') order by age desc"""
}

范围查询

POST /_sql?format=txt
{"query": """SELECT * FROM "test_sql_query4" where age<50 and age>30  order by age desc"""
}

分组查询(group)

POST /_sql?format=txt
{"query": """SELECT name,count(1) FROM "test_sql_query4" group by name"""
}

分组过滤查询(group+having)

POST /_sql?format=txt
{"query": """SELECT name,count(1) FROM "test_sql_query4" group by name having count(1)>300"""
}

聚合函数统计

POST /_sql?format=txt
{"query": """SELECT count(name),max(age),min(age),sum(salary) FROM "test_sql_query4""""
}

limit查询

POST /_sql?format=txt
{"query": """SELECT * FROM "test_sql_query4" order by age limit 10"""
}

分页查询

POST /_sql?format=json
{"query": """SELECT * FROM "test_sql_query4" order by age""","fetch_size":2
}

POST /_sql?format=json
{"cursor": "从前面一次查询中获得"
}