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://wenku.baidu.com/view/997c9fdfadf8941ea76e58fafab069dc502247f1.html?wkts=1702021877344&bdQuery=ElasticSearch+sql+%E5%87%BD%E6%95%B0%E6%B8%85%E5%8D%95

https://www.elastic.co/guide/en/elasticsearch/reference/7.17/sql-functions-datetime.html