Elasticsearch SQL的基本使用

    一、初步了解

    1.1 Elasticsearch SQL 是什么？

    2018年6月，Elasticsearch 6.3 版本 X-Pack 插件中引入新的 SQL 查询功能，虽然一直到现在，有经验的专业人员仍然更加喜欢使用 Elasticsearch DSL 来进行搜索，但是毫无疑问，Elasticsearch SQL 的查询语法对于新用户更加友好，毕竟大部分程序员并不想从头学习一门新的语言。下面是两条等价的查询语句：

    # Elasticsearch SQL 查询语句
POST /_sql?format=txt
{
  "query": """
  SELECT AVG(DistanceKilometers) AS AvgDistance, 
  OriginCountry 
  FROM kibana_sample_data_flights 
  WHERE DestWeather='Sunny' AND AvgTicketPrice>=300 
  GROUP BY OriginCountry 
  ORDER BY OriginCountry ASC LIMIT 5"""
}

# Elasticsearch DSL 查询语句
GET kibana_sample_data_flights/_search
{
  "size" : 0,
  "query" : {
    "bool" : {
      "must": [
        {
          "term" : {
            "DestWeather" : {
              "value" : "Sunny"
            }
          }
        },
        {
          "range" : {
            "AvgTicketPrice" : {
              "gte": 300
            }
          }
        }
      ]
    }
  },
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size": 5, 
        "sources" : [
          {
            "a1" : {
              "terms" : {
                "field" : "OriginCountry",
                "order" : "asc"
              }
            }
          }
        ]
      },
      "aggregations" : {
        "a2" : {
          "avg" : {
            "field" : "DistanceKilometers"
          }
        }
      }
    }
  }
}

    1.2 Elasticsearch SQL 不是什么？

    Elasticsearch SQL 提供了一个符合 ANSI SQL 规范的只读接口，即通过 Elasticsearch SQL，我们只能进行搜索，无法实现创建、更新、删除 Elasticsearch 的索引或者索引中的文档。

    Elasticsearch SQL 的查询功能，相比较于其他关系型数据库的 SQL 查询，有许多独特之处，例如: Elasticsearch SQL 不支持 join 查询语法，但却实现了更加强大的聚合分析函数。更多不同之处，下文将会详细介绍。

    1.3 环境说明

    本文测试环境为 Elasticsearch Cloud 免费试用的 Elasticsearch 集群，其中 Elasticsearch 和 Kibana 版本是 7.14.0，文中查询的索引 test_logs 即是 Kibana 示例索引kibana_sample_data_logs。

    # 新建索引别名
POST _aliases
{
  "actions": [
    {
      "add": {
        "index": "kibana_sample_data_logs",
        "alias": "test_logs"
      }
    }
  ]
}

    二、基本使用

    2.1 概念对应

    因为 Elasticsearch SQL 在设计之初就遵循着最小惊讶原则（principle of least surprise），所以我们可以在 Elasticsearch SQL 中查找到许多熟悉的 SQL 概念。

    SQL 中概念 Elasticsearch 对应的概念
    database cluster instance
    table index
    schema mapping
    row document
    column field

    2.2 连接方式

    2.2.1 SQL CLI 脚本

    # 进入安装目录，存在可执行脚本./bin/elasticsearch-sql-cli
# username password 为 Elasticsearch Cloud 试用时候的用户名密码
# host:port 可以从集群管理界面获取
./bin/elasticsearch-sql-cli https://username:password@host:port

# 进入sql终端，执行sql语句
show tables;

    2.2.2 JDBC 连接

    当前 JDBC 客户端是铂金会员功能，下面将演示使用 SQL Workbench 实现 JDBC 协议，执行查询语句。

    （1）在 SQL Workbench 中添加 Elasticsearch JDBC 驱动；

    （2）新建配置文件，创建连接实例，并且测试连接；

    （3）执行具体的查询语句；

    2.2.3 ODBC 连接

    和 JDBC 客户端相同， ODBC 客户端也是铂金会员功能，详细的使用教程可以参考官方教程，此处略。

    2.2.4 RESTful API 调用

    同 Elasticsearch DSL 类似，Elasticsearch SQL 实现了一套基于 HTTP 协议的 RESTful 接口

    2.3 基础查询

    下面的基础查询，默认在 Kibana 中操作。

    2.3.1 简单示例

    （1）SHOW TABLES， 获取集群的所有索引，包含别名信息；

    POST _sql?format=txt
{
  "query":"SHOW TABLES"
}


         name          |     type      |     kind      
-----------------------+---------------+---------------
kibana_sample_data_logs|TABLE          |INDEX          
test_logs              |VIEW           |ALIAS 
...                    |...            |...

    （2）SHOW COLUMNS IN test_logs，查看索引 test_logs 的字段信息（语句 DESC test_logs 作用相同）；

    POST _sql?format=txt
{
  "query":"SHOW COLUMNS IN test_logs"
}


POST _sql?format=txt
{
  "query":"DESC test_logs"
}


      column      |     type      |    mapping    
------------------+---------------+---------------
@timestamp        |TIMESTAMP      |datetime       
agent             |VARCHAR        |text           
agent.keyword     |VARCHAR        |keyword        
bytes             |BIGINT         |long           
clientip          |VARCHAR        |ip             
event             |STRUCT         |object     
...               |...            |...

    （3）SHOW FUNCTIONS， 查看当前支持的函数；

    POST _sql?format=txt
{
  "query":"SHOW FUNCTIONS"
}


      name       |     type      
-----------------+---------------
AVG              |AGGREGATE      
COUNT            |AGGREGATE      
FIRST            |AGGREGATE      
FIRST_VALUE      |AGGREGATE      
LAST             |AGGREGATE
...              |...

    （4）SELECT FROM，获取索引中的部分数据。

    POST _sql?format=txt
{
  "query":"""
    SELECT clientip, host, response 
    FROM test_logs 
    ORDER BY timestamp 
    DESC LIMIT 3
  """
}


   clientip    |           host            |   response    
---------------+---------------------------+---------------
118.151.35.151 |artifacts.elastic.co       |200            
99.76.103.49   |artifacts.elastic.co       |200            
41.77.243.255  |elastic-elastic-elastic.org|200

    上面通过4条语句，简单介绍了 Elasticsearch SQL 的基本查询，接下来将介绍更多的实用技巧。

    2.3.2 全文搜索

    Elasticsearch 的全文检索功能，在 Ealsticsearch SQL 中可以通过两个函数实现: match， query。

    （1）WHERE MATCH，实现全文搜索，效果同 mulit_match；

    # 查询 url 字段中包含 metricbeat的文档, score()函数返回相关性评分
POST _sql?format=txt
{
  "query":"""
    SELECT SCORE(), clientip, host, response 
    FROM test_logs 
    WHERE MATCH(url, 'metricbeat') 
    ORDER BY timestamp 
    DESC LIMIT 3
  """
}


SCORE()        |   clientip    |        host        |   response    
---------------+---------------+--------------------+-------------
2.7142088      |74.184.0.64    |artifacts.elastic.co|200            
2.7142088      |232.20.97.5    |artifacts.elastic.co|200            
2.7142088      |9.16.178.89    |artifacts.elastic.co|404

    （2）WHERE QUERY，实现全文搜索，效果同 query_string。

    POST _sql?format=txt
{
  "query":"""
    SELECT SCORE(), clientip, host, response 
    FROM test_logs 
    WHERE QUERY('url:metricbeat') 
    ORDER BY timestamp 
    DESC LIMIT 3
  """
}


SCORE()        |   clientip    |        host        |   response    
---------------+---------------+--------------------+--------------
2.7142088      |74.184.0.64    |artifacts.elastic.co|200            
2.7142088      |232.20.97.5    |artifacts.elastic.co|200            
2.7142088      |9.16.178.89    |artifacts.elastic.co|404

    2.3.3 深层分页

    实际使用过程中，一般全文搜索后，会得到的大量返回文档，经常需要分页展示。在Elasticsearch DSL 中我们一般会使用 from + size / scroll / search_after 等方式来实现，Elasticsearch SQL 中存在 fetch_size ，指定当前查询返回的文档数量，用于实现分页功能。Elasticsearch SQL 中 fetch_size，可以类比为 Elasticsearch DSL 中的 scroll（游标）。

    （1）第1次查询，指定 fetch_size = 10，表示获取 test_logs 中的 10 个文档，如果返回值中有 cursor，表示还有更多满足条件的文档；

    POST _sql?format=json
{
  "query":"""
    SELECT clientip, host, response 
    FROM test_logs 
    ORDER BY timestamp DESC
  """,
  "fetch_size": 10
}


{
  "columns" : [
    {
      "name" : "clientip",
      "type" : "ip"
    },
    {
      "name" : "host",
      "type" : "text"
    },
    {
      "name" : "response",
      "type" : "text"
    }
  ],
  "rows" : [
    [
      "118.151.35.151",
      "artifacts.elastic.co",
      "200"
    ]
    ...
  ],
  "cursor" : "g+azAwFaAXN...="
}

    （2）第2次查询，只需要添加前一次的 cursor；

    POST /_sql?format=json
{
 "cursor" : "g+azAwFaAXN...="
}


{
  "rows" : [
    [
      "41.77.243.255",
      "elastic-elastic-elastic.org",
      "200"
    ],
    ...
  ],
  "cursor" : "g+azAwFaAXN4Rk...="
}

    （3）最后一次查询，返回结果中将不存在 cursor ，表示已经到了最后一页。

    POST /_sql?format=json
{
  "cursor": "g+azAwFaAXN4Rk...="
}


{
  "columns" : [
    {
      "name" : "clientip",
      "type" : "ip"
    },
    {
      "name" : "host",
      "type" : "text"
    },
    {
      "name" : "response",
      "type" : "text"
    }
  ],
  "rows" : [
    [
      "118.151.35.151",
      "artifacts.elastic.co",
      "200"
    ]
  ]
}

    需要注意的是，

    a. 如果查询指定返回结构是 txt / csv 等，将无法获取到 carsor；

    b. 如果查询语句中包含有 LIMIT，fetch_size 将无法工作，即无法分页。

    2.4 SQL 与 DSL

    2.4.1 SQL 转换 为DSL

    Elasticsearch SQL 提供了 translate 接口，可以将 SQL 语法转换为对应的 DSL 语法。

    通过查看 DSL 语法，可以更好的学习 Elasticsearch DSL；也可以通过转化结果，反过来判断SQL 查询的正确性或者优化 SQL 查询。

    POST _sql/translate
{
  "query":"""
    SELECT clientip, url, response 
    FROM test_logs 
    WHERE MATCH(url, 'metricbeat') 
    ORDER BY timestamp 
    DESC LIMIT 10
  """
}


{
  "size" : 10,
  "query" : {
    "match" : {
      "url" : {
        "query" : "metricbeat",
        "operator" : "OR",
        "prefix_length" : 0,
        "max_expansions" : 50,
        "fuzzy_transpositions" : true,
        "lenient" : false,
        "zero_terms_query" : "NONE",
        "auto_generate_synonyms_phrase_query" : true,
        "boost" : 1.0
      }
    }
  },
  "_source" : false,
  "fields" : [
    {
      "field" : "clientip"
    },
    {
      "field" : "url"
    },
    {
      "field" : "response"
    }
  ],
  "sort" : [
    {
      "timestamp" : {
        "order" : "desc",
        "missing" : "_first",
        "unmapped_type" : "date"
      }
    }
  ]
}

    2.4.2 SQL 混合 DSL 语句

    如果遇到过滤条件逻辑关系非常复杂，我们可以在 SQL 中 添加 Elasticsearch DSL 过滤条件。

    POST /_sql?format=txt
{
  "query": """
    SELECT clientip, host, response 
    FROM test_logs 
    WHERE MATCH(url, 'metricbeat') 
    ORDER BY timestamp 
    DESC LIMIT 3
  """,
  "filter": {
    "range": {
      "response": {
        "gte": 200,
        "lt": 300
      }
    }
  }
}
   clientip    |        host        |   response    
---------------+--------------------+---------------
74.184.0.64    |artifacts.elastic.co|200            
232.20.97.5    |artifacts.elastic.co|200            
148.192.209.125|www.elastic.co      |200

    注意此时 filter 中的过滤条件，与 query 中的 WHERE 是逻辑 AND 关系。

    POST /_sql/translate
{
  "query": """
    SELECT clientip, host, response 
    FROM test_logs 
    WHERE MATCH(url, 'metricbeat') 
    ORDER BY timestamp DESC LIMIT 10
  """,
  "filter": {
    "range": {
      "response": {
        "gte": 200,
        "lt": 300
      }
    }
  }
}


{
  "size" : 10,
  "query" : {
    "bool" : {
      "must" : [
        {
          "match" : {
            "url" : {
              "query" : "metricbeat",
              "operator" : "OR",
              "prefix_length" : 0,
              "max_expansions" : 50,
              "fuzzy_transpositions" : true,
              "lenient" : false,
              "zero_terms_query" : "NONE",
              "auto_generate_synonyms_phrase_query" : true,
              "boost" : 1.0
            }
          }
        }
      ],
      "filter" : [
        {
          "range" : {
            "response" : {
              "from" : 200,
              "to" : 300,
              "include_lower" : true,
              "include_upper" : false,
              "boost" : 1.0
            }
          }
        }
      ],
      "adjust_pure_negative" : true,
      "boost" : 1.0
    }
  },
  "_source" : false,
  "fields" : [
    {
      "field" : "clientip"
    },
    {
      "field" : "host"
    },
    {
      "field" : "response"
    }
  ],
  "sort" : [
    {
      "timestamp" : {
        "order" : "desc",
        "missing" : "_first",
        "unmapped_type" : "date"
      }
    }
  ]
}

    2.4.3 SQL 与 DSL 的区别

    最直观的感觉，Elasticsearch SQL 查询语法更加简洁，适用于一般场景，而 Elasticsearch DSL 更加强大，可以实现更加复杂的查询条件。

    Elasticsearch DSL 相比较 Elasticsearch SQL ，有下面的优点：

    （1）Elasticsearch DSL 查询功能更加强大，

    a. 通过上面的 translate 接口，我们知道，Elasticsearch SQL 会被解析为 Elasticsearch DSL 然后再去执行。所以理论上，Elasticsearch DSL 可以实现所有的 Elasticsearch SQL 查询语句；

    b. Elasticsearch DSL 对复杂字段类型的支持更加完善，例如：array，object，nested，join 类型的各种查询；

    c. Elasticsearch DSL 中通过添加 script ，实现各种灵活的计算功能。

    （2）Elasticsearch DSL 查询接口，可以携带更多的参数，实现更多的查询优化，例如：

    # 参数 track_total_hits 可以返回查询的 hit 文档数量
# 参数 timeout 可以自定义查询的超时时间
GET test_logs/_search
{
  "timeout": "60s",
  "track_total_hits": true,
  "query": {
    "match_all": {}
  }
}

    （3）Elasticsearch DSL 可以满足更多的查询需求，例如：高亮查询结果、自定义相关性评分等等；

    （4）Elasticsearch DSL 查询结果携带更多的信息，例如：查询耗时、分片信息、每个文档的_id 等等。

    Elasticsearch SQL 相比较 Elasticsearch DSL ，有下面的优点：

    （1）查询语句更加简洁，返回结果更加结构化，更容易处理；

    （2）更容易上手，满足数据分析人员的大部分日常查询；

    （3）通过进一步抽象，可以更好的兼容其他引擎。例如：很多公司会实现统一的 SQL 查询语言，通过判断查询的数据，连接到不同的数据库引擎（ MySQL/ Elasticsearch/ Apache Druid/ Apache Druid 等），显然，自己实现的语言更容易解析为 Elasticsearch SQL。

    2.5 聚合分析

    Elasticsearch 的聚合分析功能非常强大，在实际生产环境中，通过聚合分析再结合图表展示，我们可以实现许多实用功能，例如：查看网站访问 IP 分布情况，某些服务的 P99 延迟等等。

    2.5.1 复合聚合

    Elasticsearch SQL 中提供了 group by 语法用于桶聚合分析，对于 group by 语法，Elasticsearch 内部将转换为复合聚合（composite aggregation）。

    例如：下面的查询根据返回状态码分类，统计对应的 http 请求的 body 字节大小平均值和请求次数。

    POST _sql/translate
{
  "query":"""
    SELECT response, count(*) AS count_docs, 
    avg(bytes) AS avg_bytes 
    FROM test_logs 
    GROUP BY response
  """
}


{
  "size" : 0,
  "_source" : false,
  "aggregations" : {
    "groupby" : {
      "composite" : {
        "size" : 1000,
        "sources" : [
          {
            "565ba6a7" : {
              "terms" : {
                "field" : "response.keyword",
                "missing_bucket" : true,
                "order" : "asc"
              }
            }
          }
        ]
      },
      "aggregations" : {
        "945f09f1" : {
          "avg" : {
            "field" : "bytes"
          }
        }
      }
    }
  }
}




POST _sql?format=txt
{
  "query":"""
    SELECT response, count(*) AS count_docs, 
    avg(bytes) AS avg_bytes 
    FROM test_logs 
    GROUP BY response
  """
}


   response    |  count_docs   |    avg_bytes    
---------------+---------------+-----------------
200            |12832          |5897.852711970075
404            |801            |5049.242197253433
503            |441            |0.0

    2.5.2 多层聚合

    既然 group by 会被转化为复合聚合，我们可以通过 group by 多个字段，实现多层聚合。

    例如：我们对测试数据（访问日志），先通过访问域名分类，然后再根据返回状态码分类，统计对应的 http 请求的 body 字节大小平均值 。

    POST _sql?format=txt
{
  "query":"""
    SELECT host, response,
    avg(bytes) AS avg_bytes 
    FROM test_logs 
    GROUP BY host, response
  """
}


             host              |   response    |    avg_bytes    
-------------------------------+---------------+-----------------
artifacts.elastic.co           |200            |6449.373109243697
artifacts.elastic.co           |404            |6111.981818181818
artifacts.elastic.co           |503            |0.0              
cdn.elastic-elastic-elastic.org|200            |5771.631123919308
cdn.elastic-elastic-elastic.org|404            |5527.292452830188
cdn.elastic-elastic-elastic.org|503            |0.0              
elastic-elastic-elastic.org    |200            |4775.802409638554
elastic-elastic-elastic.org    |404            |2397.616         
elastic-elastic-elastic.org    |503            |0.0              
www.elastic.co                 |200            |5315.617331812999
www.elastic.co                 |404            |4757.891666666666
www.elastic.co                 |503            |0.0

    2.5.3 直方图

    在我们的使用场景中，经常需要对日期进行直方图聚合，Elasticsearch SQL 中存在 histogram函数，可以实现。

    POST _sql?format=txt
{
  "query":"""
    SELECT host, 
    HISTOGRAM(timestamp, INTERVAL 1 MONTH) as h, 
    count(*) AS count_docs
    FROM test_logs 
    GROUP BY host, h
  """
}


             host              |           h            |  count_docs   
-------------------------------+------------------------+---------------
artifacts.elastic.co           |2021-10-01T00:00:00.000Z|124            
artifacts.elastic.co           |2021-11-01T00:00:00.000Z|3141           
artifacts.elastic.co           |2021-12-01T00:00:00.000Z|3223           
cdn.elastic-elastic-elastic.org|2021-10-01T00:00:00.000Z|33             
cdn.elastic-elastic-elastic.org|2021-11-01T00:00:00.000Z|1123           
cdn.elastic-elastic-elastic.org|2021-12-01T00:00:00.000Z|1099           
elastic-elastic-elastic.org    |2021-10-01T00:00:00.000Z|5              
elastic-elastic-elastic.org    |2021-11-01T00:00:00.000Z|215            
elastic-elastic-elastic.org    |2021-12-01T00:00:00.000Z|332            
www.elastic.co                 |2021-10-01T00:00:00.000Z|87             
www.elastic.co                 |2021-11-01T00:00:00.000Z|2371           
www.elastic.co                 |2021-12-01T00:00:00.000Z|2321

    2.5.4 聚合分页

    在实际使用场景中，如果分桶过多，可能会出现消耗过多资源、查询响应超时、经典的桶太多报错（too many buckets）等问题。幸运的是，我们可以通过聚合分页，避免上面的查询问题，而通过 Elasticsearch SQL 的 group by 和 fetch_size 结合使用，聚合分页将变得十分简洁。

    # 第1次查询，指定 fetch_size=10
POST _sql?format=json
{
  "query":"""
    SELECT host, response, 
    count(*) AS count_docs, 
    avg(bytes) AS avg_bytes 
    FROM test_logs 
    GROUP BY host, response
  """,
  "fetch_size": 10
}


{
  "columns" : [
    {
      "name" : "host",
      "type" : "text"
    },
    {
      "name" : "response",
      "type" : "text"
    },
    {
      "name" : "count_docs",
      "type" : "long"
    },
    {
      "name" : "avg_bytes",
      "type" : "double"
    }
  ],
  "rows" : [
    [
      "artifacts.elastic.co",
      "200",
      5950,
      6449.373109243697
    ],
		...
  ],
  "cursor" : "g+azAwFaA...="
}


# 第2次查询（也即最后1次查询)
POST _sql?format=json
{
  "cursor" : "g+azAwFaA...="
}

    2.5.5 管道聚合

    实际使用过程中，我们经常需要对聚合结果，再次过滤，而通过管道聚合可以轻松实现。在Elasticsearch SQL 中，提供了 having 语法，实现相同的管道聚合功能。

    例如：我们先根据 host，response 分类，然后计算出每个分类的请求次数，还需要过滤count_docs <= 1000 的分类。

    POST _sql?format=txt
{
  "query":"""
    SELECT host, response, 
    count(*) AS count_docs
    FROM test_logs 
    GROUP BY host, response 
    HAVING count_docs > 1000
  """
}


             host              |   response    |  count_docs   
-------------------------------+---------------+---------------
artifacts.elastic.co           |200            |5950           
cdn.elastic-elastic-elastic.org|200            |2082           
www.elastic.co                 |200            |4385

    2.6 常见错误

    2.6.1 数组类型字段展示报错

    # tags字段值是一个字符串列表
GET test_logs/_search
{
  "size": 1, 
  "_source": ["tags", "host"]
}


{
  "took" : 2,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 10000,
      "relation" : "gte"
    },
    "max_score" : 1.0,
    "hits" : [
      {
        "_index" : "kibana_sample_data_logs",
        "_type" : "_doc",
        "_id" : "kvVI_nwBeLgjH98YdNyO",
        "_score" : 1.0,
        "_source" : {
          "host" : "artifacts.elastic.co",
          "tags" : [
            "success",
            "info"
          ]
        }
      }
    ]
  }
}


# 无法select tags字段
{
  "query":"SELECT tags,host FROM test_log"
}


{
  "error" : {
    "root_cause" : [
      {
        "type" : "verification_exception",
        "reason" : "Found 1 problem\nline 1:23: Unknown index [test_log]"
      }
    ],
    "type" : "verification_exception",
    "reason" : "Found 1 problem\nline 1:23: Unknown index [test_log]"
  },
  "status" : 400
}




# 可以通过配置参数 field_multi_value_leniency，只返回列表中的第1个元素，但不会报错
POST _sql?format=txt
{
  "query":"""
    SELECT tags FROM test_logs 
    WHERE timestamp = '2021-08-22T03:30:25.131Z' 
    LIMIT 1
  """,
  "field_multi_value_leniency": true
}

    2.6.2 不支持 flattened 类型字段

    # flattened_field 字段是 flattened 类型


POST _sql?format=txt
{
  "query": "DESC test1"
}


    column     |     type      |    mapping    
---------------+---------------+---------------
flattened_field|OTHER          |unsupported

    2.6.3 索引或者字段名称包含特殊字符

    Elasticsearch SQL 中索引名称或者字段名称 都会被解析为标识符（Identifier），如果名称中包含特殊字符，例如：中划线（-），点（.），星号（*）等，需要使用双引号（"）括起来。

    # Elasticsearch SQL 中如果索引名称或者字段名称包含中划线
POST _sql?format=txt
{
  "query":"SELECT f1, f2, f-3 FROM yz-test"
}


{
  "error" : {
    "root_cause" : [
      {
        "type" : "parsing_exception",
        "reason" : "line 1:27: extraneous input '-' expecting {<EOF>, ...}"
      }
    ],
    "type" : "parsing_exception",
    "reason" : "line 1:27: extraneous input '-' expecting {<EOF>, ...}"
  },
  "status" : 400
}


POST _sql?format=txt
{
  "query":"SELECT f1, f2, \"f-3\" FROM \"yz-test\""
}


      f1       |      f2       |      f-3      
---------------+---------------+---------------
111            |222            |33

    三、SQL语言

    3.1 源码位置

    Elasticsearch SQL 源码位于 xpack 目录下，源码位置

    3.2 组成部分

    SELECT [TOP [ count ] ] select_expr [, ...]
[ FROM table_name ]
[ WHERE condition ]
[ GROUP BY grouping_element [, ...] ]
[ HAVING condition]
[ ORDER BY expression [ ASC | DESC ] [, ...] ]
[ LIMIT [ count ] ]
[ PIVOT ( aggregation_expr FOR column IN ( value [ [ AS ] alias ] [, ...] ) ) ]

    上面是 Elasticsearch SQL 的语法规范，接下来将展示常见的组成语句，更加详细的信息可以查看官方文档。

    查询语句 语句描述 示例
    DESC 描述具体索引字段列表 DESC test_logs
    SHOW 返回集群中索引列表，函数列表，索引字段列表 SHOW TABLESSHOW FUNCTIONSSHOW COLUMNS FROM test_logs
    SELECT 指定查询返回的列 SELECT url FROM test_logs
    FROM 指定查询索引 SELECT url FROM test_logs
    WHERE 指定查询的过滤条件 SELECT url FROM test_logs WHERE response = 200
    GROUP BY 指定聚合 SELECT host, response, count(*) AS count_docs FROM test_logs GROUP BY host
    ORDER BY 指定排序 SELECT SCORE(), url FROM test_logs WHERE MATCH(url, 'filebeat') ORDER BY SCORE() DESC
    LIMIT 指定返回数量 SELECT SCORE(), url FROM test_logs WHERE MATCH(url, 'filebeat') ORDER BY SCORE() DESC LIMIT 10
    HAVING 指定管道聚合 SELECT host, response, count(*) AS count_docs, avg(bytes) AS avg_bytes FROM test_logs GROUP BY host, response HAVING count_docs > 1000"
    ...

    注意，上面的查询语句组成部分有序，如果组成顺序不正确，查询语句解析阶段将会报错。

    3.3 支持函数

    Elasticsearch SQL 中包含非常多实用函数，下面罗列了其中最常用的一部分，还有许多特定场景使用的函数，可以查看官方文档。例如：有关距离计算的 ST_Distance，有关日期计算的 DAY_OF_WEEK 等。

    函数类型 函数名称 函数描述
    数学计算 ABS，LOG，RANDOM，SIN，EXP, ... 一些数值计算函数
    全文搜索 MATCH, QUERY, SCOER MATCH函数将转化为 multi_match queryQUERY函数将转化为 query_stringSCORE函数将返回 文档的相关性评分
    直方图聚合 HISTOGRAM 直方图聚合，一般用于日期分组
    聚合度量函数 COUNT, COUNT(ALL), COUNT(DISTINCT) 统计数量
    AVG, MIN, MAX, SUM 数值计算：平均值、最值、求和
    FIRST，LAST 第一个或者最后一个值
    PERCENTILE 百分位计算
    类型转化 CAST 字段类型转化，例如CAST('123' AS INT)
    ...

    四、局限

    （1） 只能执行查询语句

    Elasticsearch SQL 只实现了ANSI SQL规范标准的子集，无法添加、更新、删除索引或者索引中的文档。

    （2） 十分复杂的查询解析会报错

    （3）不支持 join 连表查询语法

    Elasticsearch 中通过添加 join 字段类型，在 Elasticsearch DSL 中通过 has_parent，has_child 可以实现简单的联表查询，但是 Elasticsearch SQL 目前不支持。

    （4） 只支持部分子查询语句

    Elasticsearch SQL 的子查询语句正常执行条件是：整个查询语句可以解析为单条 Elasticsearch DSL 查询语句。

    （5） 查询 Limit 最大值为1000

    当需要返回多条记录（大于1000）时候，无法通过直接指定 Limit 值获取，因为超过 1000 的 limit 值会被转换为 1000，此时需要使用 fetch_size （游标滚动），fetch_size 的默认最大值为10000， 如果需要返回的记录大于10000条，需要通过多次查询组合， 获得结果。

    （6）多层桶聚合无法指定每层的分桶数量，也无法指定每层的排序

    用数字化手段保障系统稳定，从这里开始！

    立即注册 了解更多

    联系我们

    加入社区

    微信扫码
    加入官方交流群

    立即体验

    在线开通，按量计费，真正的云服务！

    立即开始

    选择观测云版本

    免费版 商业版 企业版

    代码托管平台

    GitHub Gitee