 方言指令指南：从 Prompt 到可执行的正确 SQL)
Metabase Metabot 的 Databricks (Spark SQL) 方言指令指南从 Prompt 到可执行的正确 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/metabaseMetabase 的 AI 助手 Metabot 在为 Databricks 数据源生成 SQL 时依赖一份独立的方言指令文件 databricks.md。这份文件以 Prompt 片段的形式把 Databricks 所采用的Spark SQL 及其 Delta Lake 扩展的语法细节完整注入给大语言模型避免模型套用 PostgreSQL、MySQL 等其它方言的写法。读完本文你将掌握这份方言指令在 Metabot 中的加载机制与触发条件以及其中覆盖的标识符引用、字符串/日期/数组/JSON/窗口函数、Delta Lake 时间旅行、性能优化模式等全部规则从而能读懂、调试甚至自行维护面向 Databricks 的 AI SQL 生成提示词。方言指令在 Metabot 中的角色一份被技能化加载的 Prompt在深入语法细节前先理解这份文档在仓库中的定位。它并不是给人读的普通 SQL 手册而是被 Metabase 的 AI 系统当作SQL 方言技能dialect skill注册的指令文件文件按目录约定存放在 resources/metabot/prompts/dialects/ 下与 athena、bigquery、clickhouse、mysql、postgresql、snowflake、sqlserver 等 14 个方言文件并列在 skills.clj 中方言技能由程序化扫描该目录的 Markdown 文件注册而来SQL dialect skills are registered programmatically from theresources/metabot/prompts/dialects/files并生成为:sql-dialect-databricks这类隐藏技能不进入技能目录展示每个驱动都可以通过defmulti llm-sql-dialect-resource定义于 driver.clj默认实现返回 nil声明自己对应的方言指令资源路径。例如 h2、mysql、postgres、sqlite 驱动均实现了该方法指向各自方言文件关键的映射逻辑在 skills.clj 的engine-dialect中它把前端查看上下文提供的驱动名如sparksql解析为方言文件名注释明确指出它并非 1:1 映射——:sparksql与 Databricks 共用 databricks.md。这也印证了底层 JDBC 方言统一在 driver/sql/util.clj 中:databricks与:sparksql都映射到Dialect/SparkSql。触发时机上Metabot 会从用户当前所在的Native SQL 编辑器上下文:sql_engine字段提取方言名见 user_context.clj 的extract-sql-dialect随后 dialect-preload-parts 会把该方言技能的正文作为一条合成的load_skill工具调用及其结果预加载进消息流中——因此大模型在回答 Databricks 相关查询时本文件中的全部规则已经在上下文中。同样地Metabot 之外的一站式 SQL 生成管线也会在构造系统提示词时加载方言指令load-dialect-instructions见 llm/api.clj以 memoize 方式读取对应资源文件与 schema 等信息一起拼入 prompt。下面逐节展开这份指令文件本身的技术内容。标识符引用规则反引号、大小写与字符串字面量Databricks 使用 Spark SQL其标识符规则与大多数数据库不同是 LLM 最容易出错的地方标识符一律使用反引号backticksmy column、table-name。空格、连字符、保留字等特殊字符必须用反引号包裹标识符默认大小写不敏感字符串字面量使用单引号string value。SELECT Column Name, select FROM my-table这里select是保留字作列名的典型场景。对照本仓库其他方言文件可发现PostgreSQL/Snowflake 使用双引号、BigQuery 使用反引号而 Databricks 与 BigQuery 同为反引号详见文末对比表——指令文件特意强调这一点就是为了防止模型从 Postgres 背景迁移出double quote的写法。字符串操作CONCAT 与完整函数集Spark SQL 同时支持CONCAT函数与||运算符两种拼接方式其余字符串函数覆盖大小写转换、去空格、截取、长度、替换、拆分、定位、填充与正则处理-- Concatenation: CONCAT function or || operator SELECT CONCAT(first_name, , last_name) AS full_name SELECT first_name || || last_name AS full_name -- String functions SELECT LOWER(name), UPPER(name), INITCAP(name), TRIM(name), LTRIM(name), RTRIM(name), SUBSTR(name, 1, 3), -- 1-indexed, SUBSTRING also works LENGTH(name), CHAR_LENGTH(name), REPLACE(name, old, new), SPLIT(csv_col, ,), -- Returns ARRAYSTRING SPLIT(csv_col, ,)[0], -- Array access (0-indexed) INSTR(name, sub), -- Find position (1-indexed result) LEFT(name, 3), RIGHT(name, 3), LPAD(num, 5, 0), RPAD(name, 10, ), REGEXP_EXTRACT(text, pattern, 0), REGEXP_REPLACE(text, pattern, replacement) -- Pattern matching SELECT * FROM t WHERE name LIKE A% SELECT * FROM t WHERE name RLIKE ^[A-Z] -- Regex match SELECT * FROM t WHERE name REGEXP ^[A-Z] -- Same as RLIKE值得注意的易错点都被指令显式标注SUBSTR从 1 开始计数、SPLIT返回数组且下标从 0 开始、INSTR返回的也是 1 起始的位置、REGEXP与RLIKE等价。这些边界约定正是 LLM 在跨方言迁移时最容易写错的地方。日期与时间截断、运算、提取与格式化Databricks 的日期时间 API 与 MySQL/Postgres 差异巨大指令文件按四类给出示例-- Current date/time SELECT CURRENT_DATE, -- DATE (no parens) CURRENT_TIMESTAMP, -- TIMESTAMP NOW() -- Same as CURRENT_TIMESTAMP -- Date truncation SELECT DATE_TRUNC(MONTH, order_date) -- YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND SELECT TRUNC(order_date, MM) -- Alternative syntax -- Date arithmetic SELECT DATE_ADD(order_date, 7), -- Add days DATE_SUB(order_date, 7), -- Subtract days ADD_MONTHS(order_date, 1), -- Add months order_date INTERVAL 7 DAY, -- INTERVAL syntax order_date INTERVAL 2 HOUR, DATEDIFF(end_date, start_date), -- Days between (end - start) MONTHS_BETWEEN(end_date, start_date) -- Extraction SELECT YEAR(order_date), MONTH(order_date), DAY(order_date), DAYOFWEEK(order_date), -- 1Sunday, 7Saturday DAYOFYEAR(order_date), HOUR(ts), MINUTE(ts), SECOND(ts), QUARTER(order_date), WEEKOFYEAR(order_date), EXTRACT(YEAR FROM order_date) -- Standard SQL -- Formatting and parsing SELECT DATE_FORMAT(order_date, yyyy-MM-dd), DATE_FORMAT(ts, yyyy-MM-dd HH:mm:ss), TO_DATE(2024-01-15, yyyy-MM-dd), TO_TIMESTAMP(2024-01-15 10:30:00, yyyy-MM-dd HH:mm:ss), UNIX_TIMESTAMP(ts), -- To Unix epoch FROM_UNIXTIME(epoch_seconds) -- From Unix epoch重要提示Databricks 的日期格式模式采用Java SimpleDateFormat语义——yyyy年、MM月、dd日、HH24 小时制、mm分钟、ss秒。这与 BigQuery 的%Y-%m-%d风格strftime和 Postgres/Snowflake 的YYYY-MM-DD风格都不同是生成日期格式化 SQL 时的高频错误源。另外注意CURRENT_DATE不带括号DAYOFWEEK返回 1周日到 7周六与 MySQL1周一的约定不同。类型转换CAST、::简写与 TRY_CAST-- Standard 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 STRING) -- Double colon shorthand (Databricks SQL) SELECT string_col::INT -- TRY_CAST (returns NULL on failure) SELECT TRY_CAST(potentially_bad_data AS INT) -- Type names: STRING, INT, BIGINT, SMALLINT, TINYINT, FLOAT, DOUBLE, DECIMAL(p,s), -- BOOLEAN, DATE, TIMESTAMP, BINARY, ARRAYT, MAPK,V, STRUCT...除了标准的CAST(col AS TYPE)Databricks SQL 支持 Postgres 风格的::简写。关键的安全转换函数是TRY_CAST转换失败时返回 NULL 而不是抛错非常适合清洗脏数据。指令还枚举了完整的类型名清单包括复合类型ARRAYT、MAPK,V、STRUCT...提醒模型 Spark SQL 的强类型复合结构。NULL 处理COALESCE 家族与条件表达式SELECT COALESCE(nullable_col, default), -- First non-null value NVL(nullable_col, default), -- Two-argument (same as IFNULL) IFNULL(nullable_col, default), -- Two-argument alias NVL2(col, not null, null), -- If col not null, return 2nd, else 3rd NULLIF(col, ), -- Returns NULL if col IF(condition, true_val, false_val), -- Ternary expression CASE WHEN col IS NULL THEN N/A ELSE col ENDSpark SQL 提供了丰富的空值处理函数COALESCE取首个非空值NVL/IFNULL是两个参数的别名关系NVL2是三参数版Oracle 风格IF是三元表达式函数MySQL 风格与CASE WHEN等价。这些多来源的 API 混搭正是 Spark SQL 方言大杂烩的特征指令文件将它们并列方便模型按场景选用。数组0 起始下标、EXPLODE 与序列生成Spark SQL 的数组是头等公民指令文件给出完整操作面-- Array literal SELECT ARRAY(1, 2, 3) -- Array access (0-indexed!) SELECT my_array[0] AS first_element -- Array functions SELECT SIZE(arr), -- Array length (also: CARDINALITY) ARRAY_CONTAINS(arr, value), -- Membership test ARRAY_POSITION(arr, value), -- Find index (1-indexed result, 0 if not found) ELEMENT_AT(arr, 1), -- 1-indexed access, supports negative ARRAY_JOIN(arr, , ), -- Join to string (also: CONCAT_WS) ARRAY_DISTINCT(arr), -- Remove duplicates ARRAY_SORT(arr), ARRAY_UNION(arr1, arr2), ARRAY_INTERSECT(arr1, arr2), ARRAY_EXCEPT(arr1, arr2), FLATTEN(nested_arr), -- Flatten nested array SLICE(arr, 1, 3) -- Slice from index 1, length 3 -- EXPLODE: flatten array to rows SELECT id, exploded_val FROM t LATERAL VIEW EXPLODE(arr) AS exploded_val -- EXPLODE with index SELECT id, pos, val FROM t LATERAL VIEW POSEXPLODE(arr) AS pos, val -- Inline EXPLODE (simpler syntax) SELECT id, EXPLODE(arr) FROM t -- Generate sequence SELECT SEQUENCE(1, 10) -- [1, 2, ..., 10] SELECT SEQUENCE(1, 10, 2) -- [1, 3, 5, 7, 9] SELECT SEQUENCE(DATE2024-01-01, DATE2024-12-31, INTERVAL 1 MONTH)需要注意下标约定的双重标准方括号下标是 0 起始my_array[0]而ELEMENT_AT、ARRAY_POSITION、SLICE是 1 起始且ELEMENT_AT支持负数从尾部倒序。EXPLODE负责把数组展开为多行既有传统的LATERAL VIEW EXPLODE语法也有更简洁的内联写法。SEQUENCE还能按步长或 INTERVAL 生成日期序列是构造日期骨表date spine的基础。结构体与 MapSTRUCT、NAMED_STRUCT 与点号访问-- Struct literal SELECT STRUCT(1 AS id, Alice AS name) AS person SELECT NAMED_STRUCT(id, 1, name, Alice) AS person -- Struct access (dot notation) SELECT person.id, person.name FROM t -- Map literal SELECT MAP(key1, val1, key2, val2) AS my_map -- Map access SELECT my_map[key1] SELECT MAP_KEYS(my_map), MAP_VALUES(my_map) -- Explode map SELECT key, value FROM t LATERAL VIEW EXPLODE(my_map) AS key, value结构体用STRUCT(expr AS name, ...)或NAMED_STRUCT(name, value, ...)构造用点号访问字段Map 用MAP(k1, v1, k2, v2)构造用方括号访问键值EXPLODE展开为key, value两列。JSON 处理GET_JSON_OBJECT、:路径语法与 schema 推断-- Extract from JSON string SELECT GET_JSON_OBJECT(json_str, $.field), -- Returns STRING GET_JSON_OBJECT(json_str, $.nested.path), GET_JSON_OBJECT(json_str, $.array[0]), JSON_TUPLE(json_str, field1, field2), -- Multiple fields at once FROM_JSON(json_str, structid:int,name:string), -- Parse to struct TO_JSON(struct_col) -- Struct to JSON string -- JSON path with : syntax (Databricks SQL) SELECT json_col:field, json_col:nested.path, json_col:array[0] -- Schema inference SELECT SCHEMA_OF_JSON(json_str) -- Get schema of JSONDatabricks 对 JSON 的处理方式颇具特色传统 API 是GET_JSON_OBJECT(json_str, $.path)返回 STRING嵌套与数组路径都走$语法同时 Databricks SQL 原生支持:冒号路径语法直接穿透 JSON 字段json_col:field。FROM_JSON需要显式声明目标 struct schemaSCHEMA_OF_JSON则可以自动推断 schema两者配合可用于探索半结构化数据。窗口函数排名、偏移、聚合与命名窗口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), -- With default value 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, SUM(amt) OVER (ORDER BY dt ROWS UNBOUNDED PRECEDING) AS running_total, AVG(amt) OVER (ORDER BY dt ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg FROM t WINDOW w AS (PARTITION BY cat ORDER BY dt)指令覆盖三类窗口场景排名类ROW_NUMBER/RANK/DENSE_RANK、偏移与取值类LAG带默认值、LEAD、FIRST_VALUE/LAST_VALUE配合ROWS帧、NTH_VALUE、以及聚合窗口累计求和、7 日移动平均。WINDOW w AS (...)命名窗口语法可以复用窗口定义。特别提醒LAST_VALUE默认只取当前帧末行需显式声明ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING才能拿到分组内的真正最后一个值。聚合COLLECT_LIST 与近似统计SELECT COUNT(*), COUNT(DISTINCT col), SUM(amount), AVG(amount), MIN(val), MAX(val), COLLECT_LIST(col), -- Aggregate to array (with duplicates) COLLECT_SET(col), -- Aggregate to array (distinct) ARRAY_AGG(col), -- Same as COLLECT_LIST CONCAT_WS(, , COLLECT_LIST(name)), -- String aggregation APPROX_COUNT_DISTINCT(col), -- HyperLogLog approximate count PERCENTILE_APPROX(col, 0.5), -- Approximate median PERCENTILE(col, 0.5) -- Exact percentile (for smaller datasets) FROM t GROUP BY category除基础聚合外Spark SQL 的特色是数组聚合COLLECT_LIST保留重复、COLLECT_SET去重、ARRAY_AGG与COLLECT_LIST同义配合CONCAT_WS可做字符串拼接聚合。大数据场景推荐APPROX_COUNT_DISTINCTHyperLogLog 近似计数和PERCENTILE_APPROX近似中位数PERCENTILE为精确计算适合小数据集。CTE、QUALIFY 与高阶函数现代 SQL 特性CTE标准WITH ... AS (...)链式写法指令用活跃用户 × 近 30 天订单的 JOIN 示例展示复用WITH active_users AS ( SELECT * FROM users WHERE status active ), recent_orders AS ( SELECT * FROM orders WHERE order_date DATE_SUB(CURRENT_DATE, 30) ) SELECT * FROM active_users a JOIN recent_orders r ON a.id r.user_idQUALIFY 子句Databricks 支持在 SELECT 后直接过滤窗口函数结果免去一层子查询例如每个类别金额前 3-- Get top 3 per category (Databricks supports QUALIFY!) SELECT * FROM sales QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY amount DESC) 3高阶函数Spark SQL 允许以 Lambda 表达式操作数组这在传统 SQL 中几乎没有对标-- TRANSFORM: apply function to each array element SELECT TRANSFORM(arr, x - x * 2) SELECT TRANSFORM(arr, (x, i) - x i) -- With index -- FILTER: filter array elements SELECT FILTER(arr, x - x 0) -- AGGREGATE: reduce array SELECT AGGREGATE(arr, 0, (acc, x) - acc x) -- EXISTS: check if any element matches SELECT EXISTS(arr, x - x 100) -- FORALL: check if all elements match SELECT FORALL(arr, x - x 0)TRANSFORM/FILTER/AGGREGATE/EXISTS/FORALL配合x -Lambda 语法让数组处理完全在 SQL 层完成。文末对比表显示高阶函数是 Databricks 相对 BigQuery/PostgreSQL/Snowflake 的独有能力。Delta Lake 专属语法时间旅行与 MERGE作为 Delta Lake 的扩展指令文件专门给出三类能力-- Time travel (query historical versions) SELECT * FROM my_table VERSION AS OF 5 SELECT * FROM my_table TIMESTAMP AS OF 2024-01-15 10:00:00 SELECT * FROM my_tablev5 -- Describe history DESCRIBE HISTORY my_table -- MERGE (upsert) MERGE INTO target t USING source s ON t.id s.id WHEN MATCHED THEN UPDATE SET * WHEN NOT MATCHED THEN INSERT *时间旅行支持按版本号VERSION AS OF、时间戳TIMESTAMP AS OF以及vN简写三种方式读取历史快照DESCRIBE HISTORY查看表变更历史MERGE INTO ... USING ... WHEN MATCHED THEN UPDATE SET * / WHEN NOT MATCHED THEN INSERT *实现标准的 upsert 语义SET *表示更新所有列。性能考虑分区裁剪与谓词下推指令文件专门为 LLM 强调了两条性能铁律分区裁剪-- Good: filter on partition column SELECT * FROM events WHERE event_date 2024-01-15 -- Bad: function on partition column prevents pruning SELECT * FROM events WHERE DATE(event_timestamp) 2024-01-15直接对分区列做等值过滤可触发分区裁剪而把分区列包进函数如DATE(...)会阻止裁剪导致全表扫描。谓词下推-- Good: simple predicates push down to storage SELECT * FROM t WHERE status active AND amount 100 -- Bad: complex expressions may not push down SELECT * FROM t WHERE UPPER(status) ACTIVE简单谓词可下推到存储层过滤对列施加函数如UPPER(status) ACTIVE的复杂表达式则可能无法下推被迫在计算层过滤。这两组 Good/Bad 对比正是引导模型生成高效查询的规则化表达。常见模式安全除法、条件聚合、日期骨表与透视安全除法——避免除零错误的三种等价写法SELECT TRY_DIVIDE(numerator, denominator), -- Returns NULL if denominator is 0 numerator / NULLIF(denominator, 0), -- Alternative IF(denominator 0, 0, numerator / denominator)条件聚合——用COUNT_IF与SUM(IF(...))实现按条件计数/求和SELECT COUNT(*) AS total, COUNT_IF(status active) AS active_count, SUM(IF(type revenue, amount, 0)) AS revenue, SUM(CASE WHEN region US THEN amount END) AS us_amount FROM t日期骨表生成——结合EXPLODE与SEQUENCE生成连续日期序列SELECT EXPLODE(SEQUENCE(DATE2024-01-01, DATE2024-12-31, INTERVAL 1 DAY)) AS dt透视PIVOT与逆透视UNPIVOT——Databricks SQL 原生支持SELECT * FROM t PIVOT ( SUM(amount) FOR year IN (2023, 2024) ) SELECT * FROM t UNPIVOT ( amount FOR year IN (2023, 2024) )注意 UNPIVOT 中作为列名的2023、2024需要反引号包裹与本文开头的标识符引用规则呼应。跨方言对比为什么需要独立的 Databricks 指令指令文件末尾的对比表系统性地总结了 Databricks 与其他主流方言的关键差异这正是 LLM 跨方言生成 SQL 时出错的高发区FeatureDatabricksBigQueryPostgreSQLSnowflakeIdentifier quotesbacktickbacktickdoubledoubleArray index0-based0-based1-based0-basedArray aggregateCOLLECT_LISTARRAY_AGGARRAY_AGGARRAY_AGGExplode arrayEXPLODE/LATERAL VIEWUNNESTUNNESTFLATTENJSON pathGET_JSON_OBJECTor:JSON_VALUE-,-:pathApprox countAPPROX_COUNT_DISTINCTAPPROX_COUNT_DISTINCTN/AAPPROX_COUNT_DISTINCTSafe castTRY_CASTSAFE_CASTN/ATRY_CASTDate formatJava patterns%Y-%m-%dYYYY-MM-DDYYYY-MM-DDQUALIFYYesYesNoYesHigher-order functionsYesNoNoNo可以直观看到标识符引用反引号 vs 双引号、数组展开方式LATERAL VIEW EXPLODEvsUNNEST、日期格式模式Java 模式 vs strftime以及高阶函数这类独有能力都是差之毫厘、谬以千里的细节。这就是 Metabase 将方言指令独立成文件、并按数据库驱动动态注入 Prompt 的根本原因——在 Metabot 的 SQL 编辑场景中只要检测到当前编辑器绑定的是 Databricks/Spark SQL 数据源这份 databricks.md 就会作为load_skill预加载结果进入模型上下文从源头规避方言串味导致的语法错误。小结databricks.md是 Metabase Metabot 面向 Databricks 数据源生成 SQL 的语法宪章它系统覆盖了标识符引用、字符串/日期时间/类型转换、NULL 处理、数组/结构体/Map/JSON 复合类型、窗口函数、聚合、CTE、QUALIFY、高阶函数、Delta Lake 时间旅行与 MERGE、性能优化模式及常用分析模式并以跨方言对比表收束易错点。在仓库中它经由 skills.clj 的目录扫描注册为隐藏方言技能通过 driver.clj 的驱动到资源映射:sparksql与 Databricks 共用此文件最终由 dialect-preload-parts 在 SQL 编辑上下文中预加载给大模型。理解这份文件就等于掌握了让 AI 在 Databricks 上写出正确、高效 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创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
