企业官网建设流程全解析

深入解析 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), -- 1=Monday, 7=Sunday 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返回 1=Monday、7=Sunday。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 JOIN（FROM 中的连接类型）行为完全不同——前者通过复制行来展开数组，后者是一种真正的 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-based（person.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 场景的标配。

公共表表达式（CTE）

ClickHouse 支持标准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 = 'click'

PREWHERE在读取数据列之前先做过滤，对选择性高的过滤条件可以显著减少 IO（尤其当查询只用到少数列时），但要注意PREWHERE无法使用所有过滤条件，适合与WHERE配合分层过滤。

SAMPLE：随机采样

SELECT * FROM events SAMPLE 0.1 -- 10% sample SELECT * FROM events SAMPLE 10000 -- Approximately 10000 rows

SAMPLE支持比例（0~1 小数）或近似行数两种写法，适合在探索阶段快速验证查询逻辑。

FINAL：对 ReplacingMergeTree 去重

SELECT * FROM events FINAL -- Apply merge logic

FINAL会强制应用 MergeTree 族的合并语义（如ReplacingMergeTree的按版本去重），代价是查询性能下降，适合数据量可控或对一致性要求高的场景。

LIMIT BY：按组取 Top N

-- Top 3 per category without window functions SELECT * FROM products ORDER BY category, price DESC LIMIT 3 BY category

LIMIT 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 DAY

WITH 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 Spine）

SELECT arrayJoin( arrayMap(x -> toDate('2024-01-01') + x, range(toUInt32(dateDiff('day', '2024-01-01', '2024-12-31') + 1))) ) AS dt

利用range+arrayMap+arrayJoin三连生成连续日期序列，是"补齐时间轴"（如填充无交易日期）的经典技巧。

与其他方言的差异对照

指令文件末尾给出了 ClickHouse 与 PostgreSQL、BigQuery、MySQL 的对比表，这是 Agent 写跨库迁移 SQL 时的速查依据：

几个最容易被跨库经验误导的差异点：

• 数组索引：ClickHouse 与 PostgreSQL 都是 1-based，而 BigQuery 是 0-based；
• 近似去重：uniq()是 ClickHouse 独有，PostgreSQL 与 MySQL 无对应语法；
• 条件聚合：ClickHouse 用-If组合器（sumIf），PostgreSQL 用FILTER (WHERE)，BigQuery 用COUNTIF，MySQL 只能SUM(CASE)；
• 数组展开：ClickHouse 用ARRAY JOIN，PostgreSQL/BigQuery 用UNNEST，MySQL 无对应能力；
• 分组 Top K：ClickHouse 的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虽然只有一屏代码，但它承载了三重职责：

1. Agent 的方言知识库：当用户连接 ClickHouse 数据库并打开原生 SQL 编辑器时，Metabot 通过 dialect-preload-parts 将该文件全文注入上下文，指导 LLM 使用 ClickHouse 专属语法（1-based 数组、toStartOfMonth、sumIf、ARRAY JOIN、PREWHERE、LIMIT BY等），避免生成"看起来对但跑不通"的通用 SQL；
2. 方言差异的权威速查：文件的对比表覆盖了 ClickHouse 与 PostgreSQL/BigQuery/MySQL 在标识符、数组、聚合、去重上的核心差异，是跨数据库迁移与多方言开发时的第一手资料；
3. 驱动能力的配套文档：它与 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