深入解析 Metabase Metabot 的 ClickHouse SQL 方言指令从 Agent 提示工程到方言级 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 仓库中的 resources/metabot/prompts/dialects/clickhouse.md 展开它既是一份面向 LLM 的 ClickHouse SQL 方言指令也是 Metabase AI 助手Metabot / Agent在原生 SQL 编辑器中生成方言级查询的内嵌知识。读完本文你将掌握 ClickHouse 方言的完整语法要点标识符引用、类型系统、数组/JSON/窗口函数、PREWHERE/FINAL/LIMIT BY 等特殊子句并理解该指令文件如何通过sql-dialect-clickhouse技能被按需加载进 Agent 的上下文中。这份文档在 Metabase 中的角色一个隐藏的 SQL 方言技能clickhouse.md并不是一份给人类阅读的普通文档而是 Metabase AI Agent 的方言级技能dialect skill。在 src/metabase/metabot/skills.clj 的命名空间注释中明确写道SQL dialect skills are registered programmatically from theresources/metabot/prompts/dialects/files and surfaced only bydialect-preload-parts.其注册逻辑在 skills.clj 中(def ^:private dialects-dir metabot/prompts/dialects) (defn- dialect-skill-id Skill id (keyword) for a SQL engine, e.g. \postgresql\ - :sql-dialect-postgresql. [engine] (keyword (str sql-dialect- engine))) (defn- dialect-skills A hidden skill per dialect instruction file under resources/metabot/prompts/dialects/. [] (for [filename (markdown-resource-names dialects-dir) :let [engine (str/replace filename #\.md$ )]] (resolve-skill {:id (dialect-skill-id engine) :title (str engine SQL dialect) :description (str SQL dialect instructions for engine .) :dialect engine :body (str/trim (slurp (io/resource (str dialects-dir / filename))))})))要点如下文件名为clickhouse.md去掉扩展名即引擎名clickhouse注册为:sql-dialect-clickhouse技能这些技能是隐藏的skills-for-profile会从技能目录清单中移除:dialect字段的技能因此 Agent 不能通过猜测 id 主动load_skill只能通过dialect-preload-parts被预加载技能 ID 通过 dialect-skill 解析先尝试把引擎名直接当作方言名再通过metabase.driver/llm-sql-dialect-resource走 driver → 方言文件的映射。预加载机制把方言指令注入消息流dialect-preload-parts 会在 SQL 编辑器会话中把方言技能正文作为一次合成的load_skill工具调用及结果注入到消息流中(defn dialect-preload-parts Given a SQL engine name (e.g. \postgres\, as extracted from viewing context), return AISDK parts that preload its dialect skill body into the message stream as a synthetic load_skill tool call result. These sit below the system cache breakpoint, so the cached system prefix stays identical across databases. [engine] (or (when-let [skill (dialect-skill engine)] (let [skill-id (:id skill) call-id (str skill_preload_ (name skill-id))] [{:type :tool-input :id call-id :function load_skill :arguments {:ids [(name skill-id)]}} {:type :tool-output :id call-id :result {:output (:body skill)}}])) []))这意味着无论 Agent 当前连接的是 ClickHouse 还是 PostgreSQL系统提示前缀都保持一致可缓存只有当用户进入某个数据库的原生 SQL 编辑器时对应方言的完整指令才作为工具调用结果追加到上下文中。driver 到方言文件的映射metabase.driver/llm-sql-dialect-resource是一个多方法driver.clj默认返回nil由各 driver 覆写。ClickHouse 驱动的实现位于 modules/drivers/clickhouse/src/metabase/driver/clickhouse.clj(defmethod driver/llm-sql-dialect-resource :clickhouse [_] metabot/prompts/dialects/clickhouse.md)类似地postgres.clj、mysql.clj、sqlite.clj、h2.clj 也各自指向resources/metabot/prompts/dialects/下对应的方言文件。ClickHouse 方言文件与 athena.md、bigquery.md、databricks.md、redshift.md、snowflake.md、sqlserver.md 等 14 个方言文件共同构成方言指令库。标识符引用与字符串字面量ClickHouse 的标识符引用规则与多数方言不同指令文件给出三条铁律标识符使用双引号或反引号column、column字符串字面量使用单引号string value标识符区分大小写SELECT Column-Name, another column FROM my_table类型系统ClickHouse 是强类型系统核心类型如下-- Integers: Int8, Int16, Int32, Int64, Int128, Int256 -- Unsigned: UInt8, UInt16, UInt32, UInt64, UInt128, UInt256 -- Floats: Float32, Float64 -- Decimal: Decimal(P, S), Decimal32(S), Decimal64(S), Decimal128(S) -- Strings: String, FixedString(N) -- Date/Time: Date, Date32, DateTime, DateTime64(precision, timezone) -- Complex: Array(T), Tuple(T1, T2, ...), Map(K, V), Nullable(T), LowCardinality(T) -- JSON: JSON (experimental), Object(json)值得注意的类型细节整数族从Int8覆盖到Int256并有对应的无符号UInt*系列DateTime64需要指定精度与可选时区例如DateTime64(3, Asia/Shanghai)Decimal(P, S)由精度 P 与标度 S 控制另有简写Decimal32(S)/Decimal64(S)/Decimal128(S)复杂类型Nullable(T)与LowCardinality(T)会影响存储与查询性能Map(K, V)在列式场景中常用于稀疏属性。从 Metabase 侧看ClickHouse 驱动在 clickhouse.clj 中声明了能力位:expression-literals、:expressions/date、:expressions/float、:expressions/integer、:expressions/text均为true说明 MBQL 表达式层可以下推为 ClickHouse 原生类型表达式而:convert-timezone为false、:test/date-time-type为false说明时区转换与部分日期测试在 ClickHouse 上不受支持。字符串操作ClickHouse 提供极其丰富的字符串函数指令文件覆盖了拼接、大小写、裁剪、截取、替换、拆分、定位与格式化-- Concatenation: concat() function or || operator SELECT concat(first_name, , last_name) SELECT first_name || || last_name -- String functions SELECT lower(name), upper(name), trim(name), trimLeft(name), trimRight(name), substring(name, 1, 3), -- 1-indexed length(name), -- Bytes lengthUTF8(name), -- Characters replace(name, old, new), splitByChar(,, csv), -- Returns Array(String) splitByString(, , csv), -- Split by multi-char delimiter arrayStringConcat(arr, , ), -- Join array to string position(name, sub), -- Find position (1-indexed) positionCaseInsensitive(name, sub), reverse(name), leftPad(toString(num), 5, 0), format({} {}, first_name, last_name) -- Python-style formatting -- Pattern matching SELECT * FROM t WHERE name LIKE A% SELECT * FROM t WHERE name ILIKE a% -- Case-insensitive SELECT * FROM t WHERE match(name, ^[A-Z]) -- Regex SELECT * FROM t WHERE name REGEXP ^[A-Z] -- Synonym SELECT extract(text, pattern) -- Extract first match SELECT extractAll(text, pattern) -- Extract all matches as array SELECT replaceRegexpAll(text, pattern, repl)关键坑点length()返回字节数而非字符数多字节字符如中文、emoji场景必须改用lengthUTF8()substring()是1-based 索引。日期与时间ClickHouse 严格区分Date、DateTime、DateTime64三种时间类型并提供了完整的截断、运算、提取、格式化函数族-- Current date/time SELECT today(), -- Date now(), -- DateTime now64(), -- DateTime64 -- Date truncation SELECT toStartOfYear(dt), toStartOfQuarter(dt), toStartOfMonth(dt), toStartOfWeek(dt), -- Monday toStartOfDay(dt), toStartOfHour(dt), toStartOfMinute(dt), toStartOfSecond(dt), toMonday(dt), -- Explicit Monday toDate(dt), -- Truncate to Date date_trunc(month, dt) -- Standard syntax (also works) -- Date arithmetic SELECT dt INTERVAL 7 DAY, dt - INTERVAL 1 MONTH, addDays(dt, 7), addMonths(dt, 1), addYears(dt, 1), subtractDays(dt, 7), dateDiff(day, start_date, end_date), -- Difference in units dateDiff(month, start_date, end_date), age(day, start_date, end_date) -- Same as dateDiff -- Extraction SELECT toYear(dt), toMonth(dt), toDayOfMonth(dt), toDayOfWeek(dt), -- 1Monday, 7Sunday toDayOfYear(dt), toHour(dt), toMinute(dt), toSecond(dt), toYYYYMM(dt), -- 202401 toYYYYMMDD(dt), -- 20240115 toUnixTimestamp(dt), -- Unix epoch -- Formatting and parsing SELECT formatDateTime(dt, %Y-%m-%d %H:%M:%S), formatDateTime(dt, %F), -- ISO date parseDateTime(2024-01-15, %Y-%m-%d), parseDateTimeBestEffort(Jan 15, 2024), -- Auto-detect format toDateTime(2024-01-15 10:30:00), toDate(2024-01-15)注意toStartOfWeek默认以周一为一周起点toDayOfWeek返回 1Monday、7Sunday。Metabase 的datetime-diff功能位在 clickhouse.clj 中为true因此 MBQL 的日期差表达式可以下推为dateDiff/age类实现。类型转换ClickHouse 支持标准CAST语法也推荐更显式的to*函数族并提供安全转换失败返回 NULL 或零值-- CAST syntax SELECT CAST(string_col AS Int64) SELECT CAST(string_col AS Float64) SELECT CAST(string_col AS Date) SELECT CAST(string_col AS Nullable(Int64)) -- to* functions (preferred, more explicit) SELECT toInt64(string_col), toFloat64(string_col), toString(number_col), toDate(string_col), toDateTime(string_col), toDecimal64(string_col, 2), -- With scale toUUID(string_col) -- Safe casting (returns NULL or default on failure) SELECT toInt64OrNull(potentially_bad), toInt64OrZero(potentially_bad), toDateOrNull(string_col)实践中CAST对非法输入会直接抛错而to*OrNull/to*OrZero系列可以容忍脏数据是 ETL 与探索性分析的首选。NULL 处理ClickHouse 对 NULL 的处理与 MySQL 等方言有明显差异SELECT coalesce(nullable_col, default), ifNull(nullable_col, default), -- Two-argument version nullIf(col, ), -- Returns NULL if col isNull(col), isNotNull(col), -- Returns 0/1 assumeNotNull(col) -- Optimistic: removes Nullable wrapper重要原则ClickHouse 严格区分Nullable(T)与T非 Nullable 列中不可能出现 NULL。assumeNotNull是乐观优化——它只是剥掉Nullable包装如果底层真有 NULL 会导致未定义行为仅在你能保证数据非空时使用。数组数组在 ClickHouse 中是一等公民操作能力极强-- Array literal SELECT [1, 2, 3], array(1, 2, 3) -- Array access (1-indexed!) SELECT arr[1] AS first_element -- Array functions SELECT length(arr), arrayConcat(arr1, arr2), arrayPushBack(arr, element), arrayPushFront(element, arr), arrayPopBack(arr), arrayPopFront(arr), arraySlice(arr, 2, 3), -- Start at index 2, take 3 elements arrayReverse(arr), arraySort(arr), arrayReverseSort(arr), arrayDistinct(arr), arrayUniq(arr), -- Count unique has(arr, value), -- Membership test hasAll(arr, [1, 2]), -- Contains all hasAny(arr, [1, 2]), -- Contains any indexOf(arr, value), -- Position (0 if not found) arrayFirst(x - x 5, arr), -- First matching lambda arrayFilter(x - x 5, arr), -- Filter with lambda arrayMap(x - x * 2, arr), -- Transform with lambda arrayReduce(sum, arr), -- Reduce with aggregate function arrayJoin(arr), -- Expand array to rows (special!) arrayZip(arr1, arr2) -- Zip into array of tuples -- ARRAY JOIN (expand array to rows) SELECT id, element FROM t ARRAY JOIN arr AS element SELECT id, element, idx FROM t ARRAY JOIN arr AS element, arrayEnumerate(arr) AS idx -- With index关键区别arrayJoin()SELECT 中的函数与ARRAY JOINFROM 中的连接类型行为完全不同——前者通过复制行来展开数组后者是一种真正的 join 类型。需要带索引展开时用arrayEnumerate(arr)配合ARRAY JOIN。元组-- Tuple literal SELECT (1, Alice, 3.14) AS person SELECT tuple(1, Alice, 3.14) -- Tuple access SELECT person.1, person.2 -- 1-indexed! SELECT tupleElement(person, 1) -- Named tuples SELECT CAST((1, Alice) AS Tuple(id Int64, name String)) AS person SELECT person.name, person.id元组元素访问同样是 1-basedperson.1若需按字段名访问需先 CAST 为具名元组类型。JSON 处理ClickHouse 的 JSON 函数既能按路径参数提取也支持 JSONPath 语法-- Extract from JSON string SELECT JSONExtractString(json_col, key), JSONExtractInt(json_col, nested, value), -- Path as arguments JSONExtractFloat(json_col, amount), JSONExtractBool(json_col, active), JSONExtractRaw(json_col, nested), -- Returns JSON string JSONExtractArrayRaw(json_col, items), -- Array of JSON strings JSONExtractKeys(json_col), -- Get keys JSONLength(json_col, items), -- Array length JSON_VALUE(json_col, $.key), -- JSONPath syntax JSON_QUERY(json_col, $.nested) -- Check path exists SELECT JSONHas(json_col, key)JSONExtract*系列把路径作为独立参数传入如JSONExtractInt(json_col, nested, value)而JSON_VALUE/JSON_QUERY使用标准的$.keyJSONPath 语法两者可按习惯混用。窗口函数ClickHouse 完整支持 ANSI 窗口函数指令文件给出了覆盖排位、偏移、取值与分桶的示例SELECT row_number() OVER (PARTITION BY cat ORDER BY amt DESC), rank() OVER w, dense_rank() OVER w, sum(amt) OVER (PARTITION BY cat), lag(amt, 1, 0) OVER (ORDER BY dt), lead(amt) OVER (ORDER BY dt), first_value(amt) OVER w, last_value(amt) OVER w, nth_value(amt, 2) OVER w, ntile(4) OVER (ORDER BY amt) FROM t WINDOW w AS (PARTITION BY cat ORDER BY dt ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)注意此处使用了WINDOW w AS (...)子句定义窗口别名多个窗口函数可复用。Metabase 驱动层面clickhouse.clj的能力位声明:window-functions/cumulative与:window-functions/offset为true测试环境除外说明 Metabase 的 MBQL 窗口聚合可以下推到 ClickHouse 执行。聚合ClickHouse 的聚合函数体系以函数 组合器combinators著称是方言中差异最大的部分SELECT count(), countDistinct(col), sum(amount), avg(amount), min(val), max(val), groupArray(col), -- Aggregate to array groupArrayDistinct(col), groupUniqArray(col), -- Unique values as array arrayStringConcat(groupArray(col), , ), -- String aggregation -- Approximate functions (faster for large data) uniq(col), -- Approximate count distinct uniqExact(col), -- Exact count distinct uniqCombined(col), -- Higher cardinality approx quantile(0.5)(amount), -- Median quantiles(0.25, 0.5, 0.75)(amount), -- Multiple quantiles quantileTDigest(0.95)(amount), -- T-digest algorithm topK(10)(col), -- Top K frequent values -- Conditional aggregates with -If combinator sumIf(amount, status active), countIf(status done), avgIf(amount, region US), -- Array aggregates with -Array combinator sumArray(arr_col), -- Sum elements across rows avgArray(arr_col) FROM t GROUP BY category重要提示ClickHouse 的聚合组合器-If、-Array、-Merge、-State非常强大应优先使用sumIf而非sum(CASE WHEN ...)。近似函数uniq、quantileTDigest、topK在超大数据集上以极小精度损失换取数量级的速度提升是 OLAP 场景的标配。公共表表达式CTEClickHouse 支持标准WITH子句多个 CTE 可以链式定义WITH active_users AS ( SELECT * FROM users WHERE status active ), user_stats AS ( SELECT user_id, count() AS order_count FROM orders GROUP BY user_id ) SELECT * FROM active_users a JOIN user_stats s USING (user_id)特殊子句PREWHERE / SAMPLE / FINAL / LIMIT BY / WITH FILL这些是 ClickHouse 区别于通用 SQL 的杀手锏语法指令文件逐一给出了用法。PREWHERE在读取列之前过滤-- More efficient than WHERE for selective filters SELECT * FROM events PREWHERE event_date 2024-01-15 WHERE event_type clickPREWHERE在读取数据列之前先做过滤对选择性高的过滤条件可以显著减少 IO尤其当查询只用到少数列时但要注意PREWHERE无法使用所有过滤条件适合与WHERE配合分层过滤。SAMPLE随机采样SELECT * FROM events SAMPLE 0.1 -- 10% sample SELECT * FROM events SAMPLE 10000 -- Approximately 10000 rowsSAMPLE支持比例0~1 小数或近似行数两种写法适合在探索阶段快速验证查询逻辑。FINAL对 ReplacingMergeTree 去重SELECT * FROM events FINAL -- Apply merge logicFINAL会强制应用 MergeTree 族的合并语义如ReplacingMergeTree的按版本去重代价是查询性能下降适合数据量可控或对一致性要求高的场景。LIMIT BY按组取 Top N-- Top 3 per category without window functions SELECT * FROM products ORDER BY category, price DESC LIMIT 3 BY categoryLIMIT n BY col是 ClickHouse 特有的每组取前 N 条在排好序的前提下可以替代窗口函数实现分组 Top K写法更简洁。WITH FILL填补时间序列空洞SELECT toDate(dt) AS day, count() AS events FROM events GROUP BY day ORDER BY day WITH FILL FROM 2024-01-01 TO 2024-01-31 STEP INTERVAL 1 DAYWITH FILL会在ORDER BY结果中自动补齐缺失的时间点按STEP INTERVAL 1 DAY递增非常适合生成连续的时间序列缺失日期的指标为默认值 0无需再手写日期拼表。性能考量指令文件给出的三条性能铁律与 ClickHouse 的列式存储原理直接相关分区裁剪-- Good: filter on partition column SELECT * FROM events WHERE event_date 2024-01-15 -- Bad: function on partition column SELECT * FROM events WHERE toDate(event_datetime) 2024-01-15对分区列做函数运算会使分区裁剪失效全表扫描在所难免应尽量让过滤条件直接命中分区键。优先使用近似函数-- Faster: approximate count distinct SELECT uniq(user_id) FROM events -- Slower: exact count distinct SELECT count(DISTINCT user_id) FROM events大数据量下uniq基于 HyperLogLog 思想比精确count(DISTINCT ...)快得多。避免 SELECT *-- Good: select only needed columns (ClickHouse is columnar!) SELECT user_id, event_type FROM events -- Bad: reads all columns SELECT * FROM events列式存储下选列越少读取的 IO 越少SELECT *会读取所有列的数据块。常见模式安全除法SELECT if(denominator 0, 0, numerator / denominator), numerator / nullIf(denominator, 0)两种写法都避免了除零异常前者显式返回 0后者把除数转 NULL 使结果为 NULL。条件聚合SELECT count() AS total, countIf(status active) AS active_count, sumIf(amount, type revenue) AS revenue FROM t一次扫描完成多种条件的统计比多次CASE WHEN求和更高效、更易读。日期脊柱生成Date SpineSELECT arrayJoin( arrayMap(x - toDate(2024-01-01) x, range(toUInt32(dateDiff(day, 2024-01-01, 2024-12-31) 1))) ) AS dt利用rangearrayMaparrayJoin三连生成连续日期序列是补齐时间轴如填充无交易日期的经典技巧。与其他方言的差异对照指令文件末尾给出了 ClickHouse 与 PostgreSQL、BigQuery、MySQL 的对比表这是 Agent 写跨库迁移 SQL 时的速查依据FeatureClickHousePostgreSQLBigQueryMySQLIdentifier quotesdoubledoublebacktickbacktickArray index1-based1-based0-basedN/ADate truncatetoStartOfMonthDATE_TRUNCDATE_TRUNCDATE_FORMATCount distinct approxuniq()N/AAPPROX_COUNT_DISTINCTN/AConditional aggsumIf()FILTER (WHERE)COUNTIFSUM(CASE)String agggroupArrayarrayStringConcatSTRING_AGGSTRING_AGGGROUP_CONCATArray expandARRAY JOINUNNESTUNNESTN/ATop K per groupLIMIT BYWindow funcQUALIFYWindow func几个最容易被跨库经验误导的差异点数组索引ClickHouse 与 PostgreSQL 都是 1-based而 BigQuery 是 0-based近似去重uniq()是 ClickHouse 独有PostgreSQL 与 MySQL 无对应语法条件聚合ClickHouse 用-If组合器sumIfPostgreSQL 用FILTER (WHERE)BigQuery 用COUNTIFMySQL 只能SUM(CASE)数组展开ClickHouse 用ARRAY JOINPostgreSQL/BigQuery 用UNNESTMySQL 无对应能力分组 Top KClickHouse 的LIMIT BY是独有语法其他库只能依赖窗口函数BigQuery 另有QUALIFY。在 Metabase 中使用 ClickHouse 的工程佐证这份方言指令文件是 Metabase ClickHouse 驱动完整能力的一个切面。从驱动源码 modules/drivers/clickhouse/src/metabase/driver/clickhouse.clj 可以看到配套的工程细节驱动以:parent #{:sql-jdbc}注册clickhouse.clj显示名为ClickHouse并启用clickhouse.jdbc.v2系统属性默认连接参数为{:user default :password :dbname default :host localhost :port 8123}clickhouse.clj即本地默认 HTTP 端口 8123驱动额外支持proxy_host、proxy_type、server_time_zone、use_server_time_zone、use_server_time_zone_for_dates、socket_tcp_nodelay等连接参数clickhouse.clj:qualified-name-components返回[:schema]即 ClickHouse 的 database 位于 schema 位置生成db.table两级限定名clickhouse.clj原生 SQL 美化复用 MySQL 的格式化器(sql.u/format-sql-and-fix-params :mysql native-form)clickhouse.clj。该驱动由 ClickHouse 官方最初开发并维护后并入 Metabase 作为官方支持的驱动详见 modules/drivers/clickhouse/README.md。总结从指令文件到方言级 SQL 生成resources/metabot/prompts/dialects/clickhouse.md虽然只有一屏代码但它承载了三重职责Agent 的方言知识库当用户连接 ClickHouse 数据库并打开原生 SQL 编辑器时Metabot 通过 dialect-preload-parts 将该文件全文注入上下文指导 LLM 使用 ClickHouse 专属语法1-based 数组、toStartOfMonth、sumIf、ARRAY JOIN、PREWHERE、LIMIT BY等避免生成看起来对但跑不通的通用 SQL方言差异的权威速查文件的对比表覆盖了 ClickHouse 与 PostgreSQL/BigQuery/MySQL 在标识符、数组、聚合、去重上的核心差异是跨数据库迁移与多方言开发时的第一手资料驱动能力的配套文档它与 modules/drivers/clickhouse/src/metabase/driver/clickhouse.clj 的llm-sql-dialect-resource实现一一对应构成驱动能力位 方言指令双轨体系。如果你正在为 Metabase 接入 ClickHouse 做数据分析或在其他 AI 编程工具中复刻这套方言提示工程本文档的完整代码块与上文整理的加载链路都是可直接照搬的实战素材。【免费下载链接】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），仅供参考