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": "從前面一次查詢中獲得"
}