跳到主要内容

ES 查询详解：Calcite SQL 与原生 DSL

核心概念：报表管理 | ES 查询详解 | 概览

本页按 Elasticsearch 7.17.6、Calcite 1.39.0 说明，沿用现有 base_device、status、activation_time 示例。示例需要实际索引和字段存在，不能根据字段名称推断其数据类型。

先用现有统计语句确认结果，再逐步加筛选和动态日期。正式接入仍遵循“明文调试 → 生成密文 → GoView 执行”的流程，不把 SQL 明文填入 GoView。

核心概念：报表管理 | ES 查询详解 | 选择查询模式

选择查询模式​

对比项Calcite SQL原生 DSL
主后台选项Calcite SQL原生 DSL
GoView 选项SQL 翻译查询原生 DSL
模板内容SQLJSON 查询体
索引位置SQL 中的 FROM es.base_device页面“固定索引 / 别名”填写 base_device
字段写法_MAP['status']JSON 字段名 "status"
值参数:status完整 JSON 值节点 "${status}"
结果列SELECT 中的字段别名，如 status、count命中字段或聚合列，如 key、doc_count
适合场景已有 SQL 统计、筛选、分组与排序需要明确控制受支持的 ES 条件与聚合

Calcite SQL 的处理过程为：系统校验 SQL 和参数 → Calcite 解析并规划 → Elasticsearch 适配器转换查询 → ES 返回结果 → 系统整理成图表数据。Apache 对该适配器的定位是将 SQL 转换为 ES SEARCH JSON，参见官方适配器说明。

ES 官方还有名为“Elasticsearch SQL”的功能，但本系统的“Calcite SQL”入口不走该功能。查函数和语法时，应先看 Calcite 参考，再看当前 ES 适配器能否执行。

旧示例文件中包含 dataSource、sql、tableName、standardSql 的 JSON 是旧请求包装。当前页面只提取其中的 SQL 模板使用：选择数据源和 SQL 模式，不再照抄整段请求 JSON，也不在 SQL 模式填写 DSL 索引字段。

核心概念：报表管理 | ES 查询详解 | 读懂基本语句

读懂基本语句​

以下是已有示例中的状态分组查询：

select _MAP['status'] as status, count(*) as `count`
from es.base_device
where _MAP['status'] in (0, 1)
group by _MAP['status']
order by _MAP['status']
片段在本系统中的含义
es系统为 Calcite 注册的逻辑 schema 名称，不是 MySQL 库名，也不需要另建一个名为 es 的 ES 索引
base_device实际 ES 索引名，按当前示例使用
_MAPCalcite ES 适配器暴露的文档映射列
_MAP['status']从映射列中访问 status 字段；单引号中的内容是字段名
as status将输出列命名为 status，供图表映射使用
count(*) as `count` 统计各组文档数，输出列名为 count
where ... in (0, 1)先只保留 status 为数值 0 或 1 的文档
group by _MAP['status']再按 status 值分组
order by _MAP['status']最后按 status 升序排列，而不是按数量排序

例如实际存在这两类文档，结果可形成 status、count 两列；某个状态没有匹配数据时，不会自动补一行数量 0。图表使用 status 作分类字段、count 作数值字段。

大小写和引号​

本项目启用 Calcite 的 Lex.JAVA：标识符保留大小写并区分大小写，标识符引用使用反引号。官方定义见 1.39.0 Lex 配置。

  • 按示例写 _MAP 和小写 es；不要随意改成 _map 或 ES。
  • 字段键按实际映射书写，例如 _MAP['status']，不要把单引号换成反引号。
  • 使用 as status 或 as `count` 给输出列命名；字段引用和字符串值不能混用。
  • 官方通用示例可能使用双引号和不同 schema 名，不能忽略本系统的 Lex 设置直接照抄。
  • 尽量使用已确认可用的简单索引名、字段名和别名；系统对标识符还有额外校验，并非添加引号后任意名称都可用。
核心概念：报表管理 | ES 查询详解 | 常用统计示例

常用统计示例​

统计全部文档​

select count(*) as `count`
from es.base_device

适用于总量卡片。参数定义留空，动态参数填 []。COUNT(*) 表示文档行数；不要无意间改成某字段的计数，缺失字段会改变统计口径。

按全部状态分组​

select _MAP['status'] as status, count(*) as `count`
from es.base_device
group by _MAP['status']
order by _MAP['status']

与上一节的查询相比，这条语句没有 where ... in (0, 1)，不会主动排除其他状态。空值、缺失字段和多值字段应结合实际数据核对，不直接假设结果一定只有两组。

动态选择一个状态​

select count(*) as `count`
from es.base_device
where _MAP['status'] = :status

定义 status / LONG / CLIENT / 必填，动态参数填写：

[{"name":"status","value":1}]

这里的 LONG 示例以 status 为数值字段为前提。若实际字段保存字符串，应按真实类型配置 STRING，不能靠字段名猜类型。

:status 只替代条件值，不加单引号；字段仍固定为 _MAP['status']。不能使用 _MAP[:field] 动态切换字段，也不能把 :index 放在 FROM 中。

服务端对 Calcite 参数做类型化处理，使用者只需维护参数定义和 name/value 数组，不要自行拼接 SQL，也不要手动把占位符改为 ?。

查看少量明细​

排查字段内容时，可先使用明确的返回字段和较小 LIMIT：

select _MAP['status'] as status,
       _MAP['activation_time'] as activation_time
from es.base_device
where _MAP['status'] = 1
limit 10

此例用于观察字段，不保证结果顺序。需要固定排序时增加实际可排序字段，并在主后台验证；最大返回行数是资源限制，不能用来替代完整分页或导出。

核心概念：报表管理 | ES 查询详解 | 日期条件与已有宏

日期条件与已有宏​

现有示例中的两个日期查询分别为“恰好等于今天零点”和“本月时间范围”，统计口径不同。

恰好等于今天零点​

沿用原文件的等于条件：

select count(*) as `count` from es.base_device
where _MAP['status'] = 1
  and _MAP['activation_time'] = '$date_format(now, null, 0, d, yyyy-MM-dd) 00:00:00'

它只匹配零点这个时间值，不包含今天其他时刻。参数定义留空，动态参数填 []。

本月起点至下月起点​

select count(*) as `count` from es.base_device
where _MAP['status'] = 1
  and _MAP['activation_time'] >= '$date_format(now, null, 0, d, yyyy-MM)-01 00:00:00'
  and _MAP['activation_time'] < '$date_format(now, null, 1, M, yyyy-MM)-01 00:00:00'

左边包含本月 1 日零点，右边不包含下月 1 日零点，可避免相邻月份重复计数。参数定义留空，动态参数填 []。

$date_format(...) 是本系统兼容的旧模板宏，不是 MySQL DATE_FORMAT()，也不是 Calcite 或 ES 自带函数。大写 M 表示月，小写 m 表示分钟；旧 now 使用服务器默认时区。

新模板使用 SERVER_DATE​

新配置优先使用显式时区的服务器参数：

select count(*) as `count` from es.base_device
where _MAP['status'] = 1
  and _MAP['activation_time'] >= :monthStart
  and _MAP['activation_time'] < :nextMonthStart
参数名类型来源NOW 偏移单位时区格式
monthStartSTRINGSERVER_DATE0MONTHSAsia/Shanghai月初零点（1 日 00:00:00）
nextMonthStartSTRINGSERVER_DATE1MONTHSAsia/Shanghai月初零点（1 日 00:00:00）

两项均设为必填，GoView 动态参数填 []。这套 STRING 配置沿用已有示例的日期文本格式；仍需确认 activation_time 的实际 mapping、允许的日期格式与业务时区，不能推广为所有索引的统一写法。

如果字段是 keyword，范围条件按字符串规则比较，需要格式统一才能符合预期；如果是 date，需符合该字段配置的日期格式。不要直接对 text 日期字段套用范围统计。

查询“今天全天”时应使用“今天零点包含、明天零点不包含”的两个边界，而不是等于零点。参数配置方法见服务器日期参数。

核心概念：报表管理 | ES 查询详解 | 原生 DSL 对照

原生 DSL 对照​

筛选状态并返回明细​

模式选择“原生 DSL”，固定索引/别名填写 base_device。只将下面 JSON 放入模板，不添加请求方法或路径：

{
  "size": 10,
  "_source": ["status", "activation_time"],
  "query": {
    "bool": {
      "filter": [
        {"term": {"status": "${status}"}}
      ]
    }
  }
}

定义 status / LONG / CLIENT / 必填，动态参数仍为 [{"name":"status","value":1}]。服务端将完整 ${status} 值节点绑定成数字；不能写成 "prefix-${status}"。

term 用于精确值条件，全文检索通常考虑 match。字段是否适合精确匹配、排序或分组取决于 mapping，先确认字段类型。

按状态聚合​

{
  "size": 0,
  "query": {"terms": {"status": [0, 1]}},
  "aggs": {
    "by_status": {
      "terms": {"field": "status", "size": 2}
    }
  }
}

外层 size: 0 表示不返回明细；聚合中的 size: 2 表示最多返回两个状态桶。两处 size 作用不同。无动态参数时填 []。

本系统将桶结果整理成包含 aggregation_name、key、doc_count 的行，图表通常映射 key 为分类、doc_count 为数值。它不会自动变成 SQL 示例的 status/count 列，排序也不等同于 SQL 中按 status 升序。

ES 的 terms 聚合通常取前若干桶，分片统计在部分场景可能存在误差，不能把它一概视为完整分组导出。参见 7.17 terms 聚合说明。

当前开放的 DSL 范围​

类别当前支持或限制
查询条件bool、term、terms、match、match_phrase、range、exists、ids、match_all 的受控形式
聚合terms、avg、sum、min、max、value_count、stats；terms 必须显式填写 size，单个最多 100 且受总预算限制
排序使用数组中的字段名或简单对象，例如 [{"status":"asc"}]；不接受任意排序对象
脚本与深度遍历不开放 script、runtime 字段、PIT、scroll、search_after
其他聚合官方示例中的 date_histogram、cardinality、composite 等当前不在本系统白名单中
精确总数当前系统会把 track_total_hits 设为 false；不能照搬官方 track_total_hits: true 示例期待精确总量

原生 DSL 是在当前白名单内直接描述查询，不代表完整开放 ES API。需要总量卡片时，可先按前文 Calcite COUNT(*) 示例验证；不要把 DSL 的 total 字段直接当作可靠的精确总量。

核心概念：报表管理 | ES 查询详解 | 字段与版本问题排查

字段与版本问题排查​

先确认 mapping​

  • 数值字段与字符串字段要使用匹配的条件值类型。
  • text 通常用于全文检索，默认不支持直接按该字段进行排序、聚合；常见做法是使用实际存在的 keyword 字段或子字段，参见 ES 7.17 text 说明。
  • 不要对任何字段都机械追加 .keyword：子字段必须真实存在，而且聚合字段与原始文档中可直接取出的字段不一定相同。
  • 字段能显示出来，不代表一定能用于排序、分组或日期比较。

再区分失败阶段​

现象优先排查
模板签发或明文提交即被拒绝是否带了分号、注释、未开放函数、结构参数或不支持的 DSL 键
提示找不到 schema、表或列es、索引名、_MAP 大小写与字段访问形式是否正确
SQL 能解析，但转换或执行失败当前 Calcite ES 适配器是否支持该运算，不能仅看 SQL 总手册
分组或排序报字段错误mapping 类型、keyword 子字段及实际索引是否符合条件
日期查询没有数据等于零点与全天范围是否混淆，时区、日期格式和字段类型是否一致
SQL 改为 DSL 后图表为空固定索引、参数形式、返回列名和数据映射是否同步调整

版本兼容的判断边界​

目前源码声明 Calcite 1.39.0，目标 ES 为 7.17.6。但 Calcite 1.39.0 官方适配器说明列出的支持范围只到 ES 7.15.2，因此已有查询可用不能推出 JOIN、子查询、窗口函数或任意 MySQL 函数都可用。

语句需要同时满足：本系统允许 → Calcite 能解析 → ES 适配器能执行 → ES 7.17.6 及实际 mapping 接受。先保留一个可用的简单查询，每次只增加一项条件或聚合；升级版本后核对同一查询的列名、类型、结果和排序。

本页示例基于已有 SQL 与当前源码整理，没有连接数据库执行验证。完整官方入口及固定版本资料见官方查询手册与版本说明。

开发文档
AI 助手
Agent 列表
请选择一个 Agent 开始对话
AI 问答