Metabase Metabot 的 H2 SQL 方言指南LLM 生成 SQL 的规则参考与技能加载机制【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase本文以 Metabase 仓库中的 H2 方言指令文件 为主体完整讲解 H2 数据库 SQL 方言中标识符引用、字符串与日期时间函数、类型转换、数组、JSON、窗口函数、CTE、MERGE 等关键规则并深入仓库源码说明这份文件如何被 MetabotMetabase 的 AI 助手注册为按需加载的方言技能以及在 SQL 校验失败时如何参与错误提示。读完后你既能把它当作 H2 SQL 编写的实战速查手册也能理解 LLM 在 Metabase 中生成与修复方言 SQL 的底层机制。这份文件在 Metabase 中的角色resources/metabot/prompts/dialects/目录下存放着 15 种数据库引擎的方言指令文件h2.md、postgresql.md、mysql.md、bigquery.md等。从源码结构看这些文件并不是简单的文档而是 Metabot 的技能skill资源技能注册中心 中的dialect-skills函数会扫描metabot/prompts/dialects/资源目录下的所有.md文件按文件名去掉扩展名即引擎名的规则例如h2.md对应引擎h2把每个文件注册为一个隐藏技能技能 ID 形如sql-dialect-h2见 skills.clj 中的dialect-skill-id与dialect-skills这些方言技能是隐藏技能skills-for-profile会把它们从技能目录中剔除因此 Agent 无法通过load_skill工具显式加载它们它们只会通过dialect-preload-parts的预加载机制注入上下文skills.clj 的命名空间注释中说明了这一设计系统提示模板 的上下文中携带:sql_dialect键取自当前查看上下文中数据库的小写sql_engine据此计算方言相关的模板变量并用:sql_dialect_loaded标记该方言技能是否已被加载见 prompts.clj。也就是说当用户在 H2 类型的数据库连接上提问时本文件的内容会作为 H2 方言规则注入 LLM 上下文约束其生成的 SQL当 LLM 生成的 SQL 通过H2方言校验失败时工具结果指令 的sql-validation-error-instructions会生成一条包含错误详情和通用排查清单方言函数名差异、引号风格、拼接运算符、日期字面量、NULL 处理、大小写敏感性的提示引导模型对照本文件的规则自我修复。理解了这个加载机制下面就可以把整份文档当作H2 引擎下 LLM 必须遵守的 SQL 规则清单来阅读。标识符引用规则H2 是轻量级 Java SQL 数据库常用于测试与嵌入式场景。方言规则的第一条是标识符处理标识符使用双引号MyColumn、table-name不带引号的标识符默认被转换为大写字符串字面量使用单引号string valueH2 支持多种兼容模式PostgreSQL、MySQL 等。SELECT CamelCase, reserved-word FROM My Table无引号标识符默认大写这一条与其他方言如 PostgreSQL 默认小写是显著差异也是跨方言 SQL 迁移时最容易踩的坑之一——文档末尾的对照表对此有汇总。字符串操作-- 连接|| 运算符或 CONCAT 函数 SELECT first_name || || last_name AS full_name SELECT CONCAT(first_name, , last_name) AS full_name -- 字符串函数 SELECT LOWER(name), UPPER(name), TRIM(name), LTRIM(name), RTRIM(name), SUBSTR(name, 1, 3), -- 1-based别名SUBSTRING LENGTH(name), CHAR_LENGTH(name), REPLACE(name, old, new), POSITION(sub IN name), -- 查找子串位置 LOCATE(sub, name), -- 替代写法1-based LEFT(name, 3), RIGHT(name, 3), LPAD(str, 10, 0), RPAD(str, 10, ), REPEAT(str, 3), REVERSE(str), SPACE(5), -- 5 个空格 REGEXP_REPLACE(text, pattern, replacement), REGEXP_LIKE(text, pattern) -- 返回 BOOLEAN -- 模式匹配 SELECT * FROM t WHERE name LIKE A% SELECT * FROM t WHERE name LIKE A% ESCAPE \ -- 自定义转义字符 SELECT * FROM t WHERE REGEXP_LIKE(name, ^[A-Z]) -- 正则匹配要点H2 同时支持||与CONCAT两种拼接写法SUBSTR/LOCATE/POSITION均从 1 开始计数正则能力通过REGEXP_REPLACE与返回布尔值的REGEXP_LIKE提供。日期与时间-- 当前日期/时间 SELECT CURRENT_DATE, -- DATE CURRENT_TIME, -- TIME CURRENT_TIMESTAMP, -- TIMESTAMP NOW(), -- 等价于 CURRENT_TIMESTAMP LOCALTIME, LOCALTIMESTAMP -- 不带时区 -- 日期截断 SELECT TRUNC(order_date) -- 截断到天 SELECT DATE_TRUNC(MONTH, order_date) -- YEAR, QUARTER, MONTH, WEEK, DAY, HOUR SELECT TRUNCATE(order_date, MM) -- 替代语法 -- 日期算术 SELECT DATEADD(DAY, 7, order_date), -- 加 7 天 DATEADD(MONTH, 1, order_date), -- 加 1 个月 DATEADD(HOUR, 2, ts), -- 加 2 小时 order_date INTERVAL 7 DAY, -- INTERVAL 语法 order_date 7, -- 直接加天数整数 DATEDIFF(DAY, start_date, end_date), -- 天数差 DATEDIFF(MONTH, start_date, end_date) -- 提取 SELECT YEAR(order_date), MONTH(order_date), DAY(order_date), DAYOFWEEK(order_date), -- 1周日, 7周六 DAYOFYEAR(order_date), HOUR(ts), MINUTE(ts), SECOND(ts), QUARTER(order_date), WEEK(order_date), EXTRACT(YEAR FROM order_date), -- 标准 SQL EXTRACT(EPOCH FROM ts) -- Unix 时间戳秒 -- 格式化与解析 SELECT FORMATDATETIME(order_date, yyyy-MM-dd), FORMATDATETIME(ts, yyyy-MM-dd HH:mm:ss), PARSEDATETIME(2024-01-15, yyyy-MM-dd), PARSEDATETIME(2024-01-15 10:30:00, yyyy-MM-dd HH:mm:ss)重要日期格式模式使用 Java SimpleDateFormat 规则yyyy年、MM月、dd日、HH24 小时制、mm分、ss秒。这与 PostgreSQL 的to_char/to_timestamp模式、MySQL 的%Y-%m-%d风格完全不同是 LLM 跨方言生成日期函数时最常出错的地方也是校验失败指令中明确提示检查的项之一Date/time literal syntax and formatting functions见 instructions.clj。几个值得注意的 H2 特性DAYOFWEEK从 1周日到 7周六编号TRUNC、DATE_TRUNC、TRUNCATE三种截断写法并存日期加减既可以用DATEADD也可以直接把整数加到日期上order_date 7表示加 7 天。类型转换-- CAST 语法 SELECT CAST(string_col AS INT) SELECT CAST(string_col AS DOUBLE) SELECT CAST(string_col AS DATE) SELECT CAST(string_col AS TIMESTAMP) SELECT CAST(123 AS VARCHAR) -- 类型名INT, INTEGER, BIGINT, SMALLINT, TINYINT, BOOLEAN, -- DOUBLE, REAL, FLOAT, DECIMAL(p,s), NUMERIC(p,s), -- VARCHAR, CHAR, CLOB, DATE, TIME, TIMESTAMP, -- BINARY, BLOB, UUID, ARRAY, JSONH2 2.x 的类型清单里包含ARRAY与JSON这两类原生类型是后续两节的讨论基础。NULL 处理SELECT COALESCE(nullable_col, default), -- 第一个非空值 NVL(nullable_col, default), -- 两参数别名IFNULL IFNULL(nullable_col, default), -- 两参数 NVL2(col, not null, null), -- col 非空返回第 2 个参数否则返回第 3 个 NULLIF(col, ), -- col 时返回 NULL CASEWHEN(condition, true_val, false_val), -- H2 特有的三元函数 CASE WHEN col IS NULL THEN N/A ELSE col ENDNVL/NVL2/IFNULL是 Oracle 风格的兼容写法CASEWHEN(cond, true_val, false_val)三元函数则是 H2 特有MySQL 的IF()对应物。这些函数名差异正是文档对照表中NULL 处理差异的体现也是sql-validation-error-instructions中NULL handling differences between dialects一条的直接来源。布尔表达式SELECT * FROM t WHERE flag TRUE SELECT * FROM t WHERE flag IS TRUE -- NULL 安全 SELECT * FROM t WHERE flag IS NOT FALSE -- 布尔函数 SELECT GREATEST(1, 2, 3), -- 返回 3 LEAST(1, 2, 3), -- 返回 1 DECODE(status, A, Active, I, Inactive, Unknown) -- switch 表达式IS TRUE/IS NOT FALSE的 NULL 安全写法在flag可能为 NULL 时与 TRUE行为不同编写过滤条件时需注意选择。DECODE是 Oracle 风格的分支表达式在 H2 中可用。数组-- 数组字面量 SELECT ARRAY[1, 2, 3] SELECT (1, 2, 3) -- 替代语法 -- 数组访问1-based SELECT my_array[1] AS first_element -- 数组函数 SELECT CARDINALITY(arr), -- 数组长度 ARRAY_LENGTH(arr), -- 别名 ARRAY_CONTAINS(arr, value), -- 成员测试 ARRAY_CAT(arr1, arr2), -- 拼接数组 ARRAY_APPEND(arr, element), ARRAY_SLICE(arr, 1, 3), -- 切片下标 1 到 3 ARRAY_GET(arr, 1) -- 取元素1-based -- UNNEST把数组展平为行 SELECT * FROM UNNEST(ARRAY[1, 2, 3]) SELECT t.id, u.* FROM t CROSS JOIN UNNEST(t.arr) AS u(element)关键提醒H2 的数组下标从 1 开始这一点文档用大写强调1-indexed!。对照表中也指出 BigQuery 数组下标从 0 开始——同一份取第一个元素的查询在两个引擎上写法不同是跨方言迁移的典型陷阱。UNNEST配CROSS JOIN是数组列展平为多行的标准套路。JSON 处理-- JSON 列类型 SELECT JSON {name: Alice, age: 30} -- JSON 提取H2 2.x SELECT json_col FORMAT JSON, -- 输出为 JSON 字符串 json_col.name, -- 点号记法访问 JSON_VALUE(json_col, $.name), -- 提取标量 JSON_QUERY(json_col, $.nested), -- 提取 JSON 对象/数组 JSON_ARRAY(1, 2, 3), -- 构造 JSON 数组 JSON_OBJECT(key: value) -- 构造 JSON 对象这里需要适用前提JSON 提取语法针对 H2 2.x 版本文档注释中已标明H2 2.x。JSON_VALUE提取标量、JSON_QUERY提取对象/数组与 SQL/JSON 标准的分工一致json_col.name点号记法是 H2 的便利写法。窗口函数SELECT ROW_NUMBER() OVER (PARTITION BY cat ORDER BY amt DESC), RANK() OVER (PARTITION BY cat ORDER BY amt DESC), DENSE_RANK() OVER (PARTITION BY cat ORDER BY amt DESC), SUM(amt) OVER (PARTITION BY cat), LAG(amt, 1, 0) OVER (ORDER BY dt), -- 带默认值 LEAD(amt) OVER (ORDER BY dt), FIRST_VALUE(amt) OVER (PARTITION BY cat ORDER BY dt), LAST_VALUE(amt) OVER ( PARTITION BY cat ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ), NTH_VALUE(amt, 2) OVER w, NTILE(4) OVER (ORDER BY amt), PERCENT_RANK() OVER w, CUME_DIST() OVER w, SUM(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total FROM t WINDOW w AS (PARTITION BY cat ORDER BY dt)H2 支持完整的标准窗口函数集与WINDOW命名窗口子句。两个易错点LAST_VALUE默认帧只看到当前行要取分区最后一行必须显式写出ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWINGLAG的第三个参数是越界时的默认值。文档对照表指出每组 Top-N场景在 H2 中用窗口函数实现PostgreSQL 有DISTINCT ONBigQuery 有QUALIFY因此 H2 下的 Top-N 查询需要ROW_NUMBER() ... WHERE rn n这种子查询包装。聚合函数SELECT COUNT(*), COUNT(DISTINCT col), SUM(amount), AVG(amount), MIN(val), MAX(val), GROUP_CONCAT(name SEPARATOR , ), -- 字符串聚合MySQL 模式 LISTAGG(name, , ), -- 标准 SQL 字符串聚合 ARRAY_AGG(col), -- 聚合为数组 BOOL_AND(flag), BOOL_OR(flag), -- 布尔聚合 BIT_AND(col), BIT_OR(col), -- 位聚合 STDDEV_POP(col), STDDEV_SAMP(col), -- 标准差 VAR_POP(col), VAR_SAMP(col) -- 方差 FROM t GROUP BY category字符串聚合注意方言差异标准写法是LISTAGGGROUP_CONCAT是 MySQL 兼容模式下的写法对照表中 H2 一栏写LISTAGG。在 H2 默认模式下生成 SQL 时应优先使用LISTAGG。公共表表达式CTE-- 标准 CTE WITH active_users AS ( SELECT * FROM users WHERE status active ) SELECT * FROM active_users -- 递归 CTE WITH RECURSIVE subordinates AS ( SELECT id, name, manager_id, 1 AS depth FROM employees WHERE id 1 UNION ALL SELECT e.id, e.name, e.manager_id, s.depth 1 FROM employees e JOIN subordinates s ON e.manager_id s.id ) SELECT * FROM subordinates递归 CTE 用WITH RECURSIVE声明示例演示了组织树上下级关系逐层展开并维护depth层数的经典写法。MERGE 语句UpsertMERGE INTO target_table t USING source_table s ON t.id s.id WHEN MATCHED THEN UPDATE SET t.name s.name, t.amount s.amount WHEN NOT MATCHED THEN INSERT (id, name, amount) VALUES (s.id, s.name, s.amount)H2 的 upsert 通过MERGE实现对照表PostgreSQL 用ON CONFLICTMySQL 用ON DUPLICATE KEYBigQuery 也用MERGE。匹配则更新、不匹配则插入是同步场景如增量数据落地的核心模式。表值构造器Table Value Constructor-- 构造内联表 SELECT * FROM (VALUES (1, a), (2, b), (3, c)) AS t(id, name) -- 用于 INSERT INSERT INTO t (id, name) VALUES (1, a), (2, b), (3, c)VALUES子句可以直接作为表源参与查询这是没有SELECT 1 UNION ALL SELECT 2时代惯用法的现代替代写法。序列与自增主键-- 自增最常用 CREATE TABLE t ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) ) -- 序列 CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SELECT NEXT VALUE FOR my_seq SELECT CURRVAL(my_seq) -- 当前值 -- 标识列SQL 标准 CREATE TABLE t ( id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY, name VARCHAR(100) )H2 同时支持三种自增方案AUTO_INCREMENTMySQL 风格、标准GENERATED ALWAYS AS IDENTITY以及独立SEQUENCE对象配合NEXT VALUE FOR/CURRVAL取值。LIMIT 与分页-- 基本限制 SELECT * FROM t LIMIT 100 -- 偏移 SELECT * FROM t ORDER BY id LIMIT 100 OFFSET 200 -- 标准语法 SELECT * FROM t ORDER BY id FETCH FIRST 100 ROWS ONLY SELECT * FROM t ORDER BY id OFFSET 200 ROWS FETCH NEXT 100 ROWS ONLYLIMIT/OFFSET与 SQL 标准的OFFSET ... FETCH ...两套分页语法均可用。兼容模式H2 支持通过连接 URL 设置兼容模式来改变 SQL 行为-- 在连接 URL 中设置模式 jdbc:h2:mem:test;MODEPostgreSQL -- 可用模式REGULAR默认、DB2、Derby、HSQLDB、MariaDB、 -- MySQL、Oracle、PostgreSQL、MSSQLServer可用的模式包括REGULAR默认、DB2、Derby、HSQLDB、MariaDB、MySQL、Oracle、PostgreSQL、MSSQLServer。这一点解释了上文为什么GROUP_CONCAT被标注为MySQL 模式——同一份 H2 实例在不同 MODE 下的可识别语法集合是不同的跨方言移植 SQL 时若目标 H2 开着兼容模式可用函数会发生变化。常用模式Common Patterns安全除法SELECT CASEWHEN(denominator 0, 0, numerator / denominator), numerator / NULLIF(denominator, 0)两种防零除写法H2 特有的CASEWHEN三元函数或把分母包进NULLIF使结果变为 NULL。条件聚合SELECT COUNT(*) AS total, SUM(CASE WHEN status active THEN 1 ELSE 0 END) AS active_count, SUM(CASE WHEN type revenue THEN amount ELSE 0 END) AS revenue FROM tSUM(CASE WHEN ... THEN ... END)是各方言通用的条件计数/求和套路。生成序列Generate Series-- 使用系统范围表 SELECT x FROM SYSTEM_RANGE(1, 10) -- 1 到 10 -- 日期范围 SELECT DATEADD(DAY, x, DATE 2024-01-01) AS dt FROM SYSTEM_RANGE(0, 364)H2 用SYSTEM_RANGE生成连续整数对照表PostgreSQL 用GENERATE_SERIESBigQuery 用GENERATE_ARRAY再配合DATEADD即可生成日期序列常用于补全日历表。手动行转列PivotingSELECT category, SUM(CASE WHEN year 2023 THEN amount ELSE 0 END) AS 2023, SUM(CASE WHEN year 2024 THEN amount ELSE 0 END) AS 2024 FROM sales GROUP BY category用SUM CASE组合实现透视。注意列别名2023、2024以数字开头必须按标识符引用规则加双引号。忽略重复插入Insert Ignore / On Duplicate-- 存在则跳过MERGE 的替代 MERGE INTO t (id, name) KEY (id) VALUES (1, updated_name)MERGE INTO ... KEY (...) VALUES (...)是 H2 的简写形式以KEY指定的列为冲突键一行 SQL 完成存在则更新、不存在则插入。与其他方言的关键差异对照文档最后给出了跨方言速查表这是 LLM以及开发者在跨引擎写 SQL 时最实用的部分特性H2PostgreSQLMySQLBigQuery标识符引号双引号双引号反引号反引号大小写规则大写化小写化保持原样不区分数组下标1 起1 起N/A0 起字符串连接\|\|\|\|CONCAT\|\|或CONCAT日期加减DATEADDINTERVALDATE_ADDDATE_ADD字符串聚合LISTAGGSTRING_AGGGROUP_CONCATSTRING_AGG生成序列SYSTEM_RANGEGENERATE_SERIESN/AGENERATE_ARRAYUpsertMERGEON CONFLICTON DUPLICATE KEYMERGE每组 Top-N窗口函数DISTINCT ON窗口函数QUALIFY其中对 H2 影响最大的三条是标识符默认大写化无引号写法与 PostgreSQL/BigQuery 不同、数组下标 1 起与 BigQuery 相反、日期加减用DATEADD与 MySQL/BigQuery 的DATE_ADD不同。总结从规则文档到 Agent 上下文回到本文开头介绍的机制h2.md 的每一节规则——引号风格、函数名、1-based 下标、DATEADDvsDATE_ADD、LISTAGGvsGROUP_CONCAT、MERGEupsert——都对应着 校验失败指令 中要求模型排查的检查项。skills.clj 将该文件按sql-dialect-h2注册为隐藏技能后当 Metabot 面对 H2 连接的用户提问时这些规则会进入 LLM 的上下文使生成的 SQL 在方言层面与 H2 引擎保持一致校验环节报错时同样的规则清单又作为修复指南被引用。对于维护者而言修改这份 Markdown 文件以及同目录下其他 14 个方言文件即可调整 Agent 的 SQL 生成行为无需改动任何 Clojure 代码从源码结构看load-skills!在命名空间加载时执行且可被 REPL 重新调用意味着编辑文件后刷新注册表即可生效。【免费下载链接】metabaseThe easy-to-use open source Business Intelligence and Embedded Analytics tool that lets everyone work with data :bar_chart:项目地址: https://gitcode.com/GitHub_Trending/me/metabase创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考