elasticsearch sql 转换成 dsl

张映 发表于 2021-04-23

分类目录: elasticsearch

标签:, ,

elasticsearch dsl写起来,还是比较麻烦的,特别是聚合的时候,推荐以下方式转换。

1,命令行下确定sql能执行

[root@bigserver4 ~]# cd /usr/share/elasticsearch/bin 

[root@bigserver4 bin]# ./elasticsearch-sql-cli

                       asticElasticE
                     ElasticE  sticEla
          sticEl  ticEl            Elast
        lasti Elasti                   tic
      cEl       ast                     icE
     icE        as                       cEl
     icE        as                       cEl
     icEla     las                        El
   sticElasticElast                     icElas
 las           last                    ticElast
El              asti                 asti    stic
El              asticEla           Elas        icE
El            Elas  cElasticE   ticEl           cE
Ela        ticEl         ticElasti              cE
 las     astic               last              icE
   sticElas                   asti           stic
     icEl                      sticElasticElast
     icE                       sticE   ticEla
     icE                       sti       cEla
     icEl                      sti        Ela
      cEl                      sti       cEl
       Ela                    astic    ticE
         asti               ElasticElasti
           ticElasti  lasticElas
              ElasticElast                        

                       SQL
                      6.8.13

sql> SELECT * from lianshan_test limit 1;
    test_id    |           test_name
---------------+-------------------------------
28             |test update 2020-11-18 17:30:27

sql> SELECT  * FROM lianshan_test WHERE MATCH(test_name, 'test');
    test_id    |           test_name
---------------+-------------------------------
28             |test update 2020-11-18 17:30:27
35             |es test 2020-11-18 17:30:14
32             |es test 2020-11-17 20:16:51
33             |es test 2020-11-17 20:17:33
34             |es test 2020-11-18 16:46:35    

# curl -XPOST "http://10.0.10.245:19200/_xpack/sql/translate?pretty" -H "Content-Type: application/json" -d '
> {
>     "query": "SELECT * from lianshan_test limit 1"
> }'
{
  "size" : 1,
  "_source" : {
    "includes" : [
      "test_name"
    ],
    "excludes" : [ ]
  },
  "docvalue_fields" : [
    {
      "field" : "test_id",
      "format" : "use_field_mapping"
    }
  ],
  "sort" : [
    {
      "_doc" : {
        "order" : "asc"
      }
    }
  ]
}

不同的版本写法是不一样的,请参考:

翻译:https://www.elastic.co/guide/en/elasticsearch/reference/6.8/sql-translate.html

执行:https://www.elastic.co/guide/en/elasticsearch/reference/6.8/sql-getting-started.html

2,kibana中执行sql,并转换

kibana sql 执行

kibana sql 执行

kibana sql 翻译成dsl

kibana sql 翻译成dsl



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/elasticsearch/2511.html