ElasticSearch SQL
[toc]
执行SQL
POST /_xpack/sql?format=json
{
"query": "SELECT * FROM ads_user_profile where name like '%睿'"
,"fetch_size":"1"
}
SQL转DSL
kibana
POST /_sql/translate
{
"query": "SELECT * FROM ads_user_profile_2 where name rlike '...航' "
,"fetch_size":"1"
}
或
POST /_xpack/sql/translate
{
"query": "SELECT * FROM ads_user_profile where name rlike '...航' "
,"fetch_size":"1"
}
/_xpack/sql/translate 已弃用
Scala调用Demo
import cn.hutool.core.codec.Base64Encoder
import cn.hutool.http.HttpUtil
/**
* SQL转DSL语句
*
* @date 2022/6/28 上午10:02
* @param esIP ES地址 ip:port
* @param esUsername ES用户名
* @param esPassword ES密码
* @author Jast
*/
class SqlConversionDSL(esIP: String, esUsername: String, esPassword: String) {
/**
* 查询前缀
*/
private val queryPrefix = """{"query": """".stripMargin
/**
* 查询后缀
*/
private val querySuffix = """"}""".stripMargin
/**
* SQL translate
*/
private val url = s"http://${esIP}/_sql/translate"
/**
* SQL转换DSL
*
* @name convert
* @date 2022/6/28 上午10:04
* @return java.lang.String
* @param sql
* @author Jast
*/
def convert(sql: String) = {
val query = queryPrefix + sql + querySuffix
val request = HttpUtil.createPost(url)
request.header("Authorization", "Basic " + Base64Encoder.encode(esUsername + ":" + esPassword))
request.body(query)
request.execute().body()
}
}
object SqlConversionDSL {
def main(args: Array[String]): Unit = {
val sqlConversionDSL = new SqlConversionDSL("172.16.24.143:9200", "elastic", "jast2@22#!")
val str = sqlConversionDSL.convert("""SELECT * FROM ads_user_profile_2 where name rlike '...航' """)
println(str)
}
}
查询结果，query部分为具体查询条件
"query":{"regexp":{"name.keyword":{"value":"...航","flags_value":255,"max_determinized_states":10000,"boost":1.0}}}
{
"size":1000,"query":{
"regexp":{
"name.keyword":{
"value":"...航","flags_value":255,"max_determinized_states":10000,"boost":1.0}}},"_source":false,"fields":[{
"field":"age"},{
"field":"aid"},{
"field":"area"},{
"field":"jast_update_time"},{
"field":"bind_cars"},{
"field":"city"},{
"field":"classification"},{
"field":"constellation"},{
"field":"day_of_birthday"},{
"field":"device_id"},{
"field":"domain_type"},{
"field":"education"},{
"field":"first_login_contact"},{
"field":"first_login_time","format":"strict_date_optional_time_nanos"},{
"field":"follow_wechat_gongzhonghao"},{
"field":"gender"},{
"field":"have_vw_cars"},{
"field":"interested_models"},{
"field":"interested_type"},{
"field":"is_have_kid"},{
"field":"is_vw_owner"},{
"field":"last_check","format":"strict_date_optional_time_nanos"},{
"field":"last_deal_big_area"},{
"field":"last_deal_car_model"},{
"field":"last_deal_car_type"},{
"field":"last_deal_city"},{
"field":"last_deal_date","format":"strict_date_optional_time_nanos"},{
"field":"last_deal_dealer"},{
"field":"last_deal_province"},{
"field":"last_deal_small_area"},{
"field":"last_login_contact"},{
"field":"last_login_time","format":"strict_date_optional_time_nanos"},{
"field":"marital_status"},{
"field":"member_level"},{
"field":"member_most_consume_type"},{
"field":"member_score"},{
"field":"month_of_birthday"},{
"field":"name"},{
"field":"network_type"},{
"field":"offline_channels"},{
"field":"one_id"},{
"field":"open_id"},{
"field":"phone"},{
"field":"phone_brand"},{
"field":"phone_os"},{
"field":"private_domain_channel_1"},{
"field":"private_domain_channel_2"},{
"field":"province"},{
"field":"public_domain_channel_1"},{
"field":"public_domain_channel_2"},{
"field":"registered_contact"},{
"field":"registered_time","format":"strict_date_optional_time_nanos"},{
"field":"result_date","format":"strict_date_optional_time_nanos"},{
"field":"search_words"},{
"field":"source"},{
"field":"test_drive_car_type"},{
"field":"test_drive_date","format":"strict_date_optional_time_nanos"},{
"field":"test_drive_dealer"},{
"field":"user_type"},{
"field":"user_vins"},{
"field":"users_cars"},{
"field":"year_of_birthday"}],"sort":[{
"_doc":{
"order":"asc"}}]}
SQL GroupBy 截取keyword某一段进行分组查询
POST /_xpack/sql/translate
{
"query": "SELECT SUBSTRING(jast_update_time,0,1),count(1) FROM ads_user_profile group by SUBSTRING(jast_update_time,0,1) "
,"fetch_size":"1"
}
转换为DSL
{
"size" : 0,
"_source" : false,
"aggregations" : {
"groupby" : {
"composite" : {
"size" : 1,
"sources" : [
{
"b15264e0" : {
"terms" : {
"script" : {
"source" : "InternalSqlScriptUtils.substring(InternalQlScriptUtils.docValue(doc,params.v0),params.v1,params.v2)",
"lang" : "painless",
"params" : {
"v0" : "jast_update_time",
"v1" : 0,
"v2" : 1
}
},
"missing_bucket" : true,
"value_type" : "string",
"order" : "asc"
}
}
}
]
}
}
}
}
时间函数
https://www.elastic.co/guide/en/elasticsearch/reference/7.17/sql-functions-datetime.html
