es中将sql转换成dsl

Bertha 。 2024-03-27 16:28 122阅读 0赞

## 1、背景 ##

在`es`中，有些时候我们知道`sql`语句是怎么写的，但是如果我想通过`dsl`来实现，那么该如何处理呢？其中在`es`中提供了将`sql`转换成`dsl`的api，此处我们来简单使用下。

## 2、准备数据 ##

### 2.1 mapping ###

PUT /index_translate
    {
      "mappings": {
        "properties": {
          "month": {
            "type": "keyword"
          },
          "brand": {
            "type": "keyword"
          },
          "salesVolume": {
            "type": "integer"
          }
        }
      }
    }

### 2.2 插入数据 ###

PUT /index_translate/_bulk
    {"index":{"_id":1}}
    {"month":"2023-01","brand":"宝马","salesVolume":100}
    {"index":{"_id":3}}
    {"month":"2023-02","brand":"大众","salesVolume":80}
    {"index":{"_id":4}}
    {"month":"2023-02","brand":"宝马","salesVolume":20}

## 3、使用sql实现一个简单的group by 操作 ##

### 1、sql ###

POST _sql?format=txt
    {
      "query": """
      select count(*), brand from index_translate where salesVolume>=10  group by brand having count(*) > 1
      """
    }

`注意：`并不是所有的sql语法都是支持的。

### 2、转换成dsl ###

POST _sql/translate
    {
      "query":"select count(*), brand from index_translate where salesVolume>=10  group by brand having count(*) > 1"
    }

### 3、运行结果 ###

![运行结果][aa473441200c4684889ecac9b76c9acf.png]

## 4、参考文档 ##

1、[https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html][https_www.elastic.co_guide_en_elasticsearch_reference_current_sql-translate.html]

[aa473441200c4684889ecac9b76c9acf.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/27/5c2b3274b94f49f18413d44e8bd4c8ad.png
[https_www.elastic.co_guide_en_elasticsearch_reference_current_sql-translate.html]: https://www.elastic.co/guide/en/elasticsearch/reference/current/sql-translate.html