《Elastic Stack 实战手册》——三、产品能力——3.5 进阶篇——3.5.17.Elasticsearch SQL (5) https://developer.aliyun.com/article/1227073
6. 常见错误
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 }
6.2 不支持 flattened 类型字段
# flattened_field 字段是 flattened 类型 POST _sql?format=txt { "query": "DESC test1" } column | type | mapping ---------------+---------------+--------------- flattened_field|OTHER |unsupported
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语言
1. 源码位置
Elasticsearch SQL 源码位于 xpack 目录下，源码位置
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 的语法规范，接下来将展示常见的组成语句，更加详细的信息可以查看官方文档。
注意，上面的查询语句组成部分有序，如果组成顺序不正确，查询语句解析阶段将会报错。
《Elastic Stack 实战手册》——三、产品能力——3.5 进阶篇——3.5.17.Elasticsearch SQL (7) https://developer.aliyun.com/article/1227068
