使用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": "从前面一次查询中获得"
}