指标怎么自动生成SQL？——指标平台的DSL设计要点

指标管理手册第 12 题约 10 分钟读完

本文目录

先说结论
业务场景：拖拽生成SQL的核心需求
方法一：DSL的语义抽象（核心）
方法二：原子语义模型
方法三：模板引擎+AST两种实现
方法四：跨表/跨主题的JOIN处理
方法五：查询优化的关键规则
面试高频追问
实战踩坑：DSL生成的SQL让Hologres CPU打满
总结

先说结论

DSL设计的3层模型：①指标语义层（业务概念：指标、维度、过滤、时间）②查询逻辑层（SELECT/WHERE/GROUP BY的抽象）③SQL方言层（Hologres/MaxCompute/MySQL等的具体翻译）。三层解耦才能跨引擎、跨场景复用。

DSL不是新发明SQL，而是「业务视角的SQL」。让业务用「我要看美国近7日的GMV按渠道切分」这种自然描述，平台自动转成SQL。核心抽象是「指标 + 维度 + 过滤 + 时间窗口」四元组。

最大的难点是「跨表/跨主题的指标计算」。比如"客单价=GMV/订单数"，GMV在主题表A，订单数在主题表B，自动JOIN还要避免性能爆炸。这是DSL深水区。

先把这段讲顺；面试官追问时，再展开下面的原理和细节。

详细讲解

指标平台的灵魂是「定义一次，到处可用」——业务方在UI上拖拽指标和维度，背后自动生成SQL。这道题考的是DSL（领域特定语言）设计能力和SQL语义模型——能答到原子语义模型、跨方言适配、查询优化路由就是Senior的水平。我在某元宇宙社交App做指标平台时，光DSL就迭代了3版，今天分享踩过的所有坑。

业务场景：拖拽生成SQL的核心需求

BI工具上业务方做了这么个查询：「按国家×渠道看近7日 GMV 和客单价 (剔除测试用户)」。背后需要平台生成的SQL大概是：

-- 业务方拖拽生成的目标SQL
SELECT
  country,
  channel,
  SUM(pay_amount_usd)            AS gmv,
  SUM(pay_amount_usd)/NULLIF(COUNT(DISTINCT order_id), 0) AS avg_order_value
FROM dws_trade_user_country_channel_1d
WHERE dt BETWEEN '2023-09-25' AND '2023-10-01'
  AND user_type != 'TEST'
GROUP BY country, channel;
 
-- 平台需要做的事：
-- 1. 把「GMV」「客单价」翻译成SUM/COUNT表达式
-- 2. 选最优物化表（country×channel粒度的预聚合）
-- 3. 时间窗口转dt过滤
-- 4. 业务过滤「剔除测试用户」转WHERE条件

方法一：DSL的语义抽象（核心）

V3版本的DSL是这样的JSON结构（参考LookML、Cube.js）：

{
  "metrics": [
    {"id": "gmv", "alias": "gmv_usd"},
    {"id": "avg_order_value", "alias": "aov"}
  ],
  "dimensions": [
    {"id": "country"},
    {"id": "channel"}
  ],
  "time_range": {
    "field": "event_date",
    "start": "2023-09-25",
    "end": "2023-10-01"
  },
  "filters": [
    {"field": "user_type", "op": "!=", "value": "TEST"}
  ],
  "order_by": [{"field": "gmv_usd", "direction": "DESC"}],
  "limit": 100
}

这个JSON是BI拖拽的产物。平台收到后做的事：①查字典找metric/dimension/filter的物理映射 ②选最优表 ③拼SQL ④执行。整个过程对业务方透明。

方法二：原子语义模型

DSL的核心是"原子操作"——把指标拆成最小语义单元。我用四元组：

元素	示例	字典字段	DSL展开
metric	GMV	formula='SUM(pay_amount_usd)'	SELECT SUM(pay_amount_usd)
dimension	country	field='country', table='dim_user'	GROUP BY country
filter	剔除测试用户	条件='user_type != TEST'	WHERE user_type != 'TEST'
time	近7日	field='dt', range=[s, e]	WHERE dt BETWEEN s AND e

每个原子有标准的JSON Schema和翻译规则，新增场景只需要扩schema。元宇宙社交App走这套抽象后，新指标接入从平均3天压到3小时。

方法三：模板引擎+AST两种实现

DSL→SQL的实现有两种：

# 方案A：Jinja2模板（简单场景）
from jinja2 import Template
template = Template("""
SELECT
  {% for d in dimensions %}{{d.field}},{% endfor %}
  {% for m in metrics %}{{m.formula}} AS {{m.alias}}{% if not loop.last %},{% endif %}{% endfor %}
FROM {{table}}
WHERE dt BETWEEN '{{time_range.start}}' AND '{{time_range.end}}'
  {% for f in filters %} AND {{f.field}} {{f.op}} '{{f.value}}'{% endfor %}
GROUP BY {% for d in dimensions %}{{d.field}}{% if not loop.last %}, {% endif %}{% endfor %}
""")
sql = template.render(...)
 
# 方案B：SQLGlot AST（复杂场景）
import sqlglot
from sqlglot import exp
 
# 用AST节点构建，能做语义优化、跨方言转换
ast = exp.Select().from_(table).group_by(*dim_fields)
for m in metrics:
    ast = ast.select(exp.Alias(this=parse_formula(m), alias=m.alias))
for f in filters:
    ast = ast.where(f.to_expression())
sql = ast.sql(dialect='hologres')

Jinja适合<50个查询场景的MVP，AST适合企业级（跨方言、查询优化、语义校验）。元宇宙社交App最终用SQLGlot，能从一份AST生成Hologres、Spark SQL、Presto三种方言——这对多引擎环境特别有价值。

方法四：跨表/跨主题的JOIN处理

难点是当指标分散在不同主题表时怎么JOIN。比如GMV在交易主题表，DAU在用户主题表，要算"人均GMV"必须JOIN。我的做法是"维度桥"：

# 维度桥配置：标记哪些维度可以跨主题JOIN
DIMENSION_BRIDGE = {
    'user_id': {  # user_id是连接所有用户主题的桥
        'tables': ['dws_user_action_1d', 'dws_trade_user_1d'],
        'join_type': 'LEFT JOIN',  # 一般LEFT保证不丢用户
    },
    'order_id': {
        'tables': ['dws_trade_order_1d', 'dws_trade_refund_1d'],
        'join_type': 'LEFT JOIN',
    }
}
 
def build_join(metrics):
    tables = set(m.source_table for m in metrics)
    if len(tables) == 1:
        return f"FROM {tables.pop()}"
    # 多表场景：找共同的桥维度
    bridge_dim = find_common_bridge(tables)
    if not bridge_dim:
        raise CrossSubjectJoinNotSupported(...)
    return generate_join_sql(tables, bridge_dim)

如果连桥都找不到（比如用户主题和商品主题），平台直接拒绝生成SQL——逼业务方拆成两个查询。这是「设计的边界」，比硬撑出垃圾SQL好。

方法五：查询优化的关键规则

DSL生成的SQL不能直接交OLAP，要先过一道优化层：

OPTIMIZATION_RULES = [
    'choose_materialized_view',  # 优先选物化视图（参考第10题）
    'partition_pruning',         # 强制加分区过滤（避免全表扫）
    'predicate_pushdown',        # WHERE下推到子查询
    'remove_redundant_distinct', # 去掉冗余DISTINCT
    'limit_default',             # 没指定LIMIT加默认1000
    'reject_full_scan',          # 拒绝全表扫描的SQL
    'detect_cartesian_join',     # 拒绝笛卡尔积
]

最关键的是"reject_full_scan"——业务方时间范围没填或填得过宽时，平台直接拒绝并提示。元宇宙社交App上线这条后，OLAP集群CPU平均负载从75%降到50%。

面试高频追问

追问一：DSL和ORM（如MyBatis、SQLAlchemy）有什么区别？

ORM是「数据库行对象」的抽象（一行映射一个对象），DSL是「业务概念」的抽象（一个指标映射多种表达）。ORM的写法离SQL很近，DSL的写法离业务很近。指标平台用ORM行不通——业务方不可能学Python写session.query()，必须给他们"拖拽指标和维度"的体验。这是工具定位的根本差异。

追问二：跨方言（Hologres和MaxCompute都要支持）怎么做？

用SQLGlot/Apache Calcite这类AST工具。先把DSL编译成「中间表示」（IR），再针对每种方言生成具体SQL。难点是方言差异（如Hologres没有LATERAL VIEW、MaxCompute的MAP_AGG语法不同）——这些差异要在IR→方言的映射层处理。元宇宙社交App用SQLGlot做了Hologres+Spark+Presto三方言，方言相关代码占总代码30%，但保证了平台跨引擎可用。

追问三：DSL执行的SQL权限怎么管？

两层权限：①指标级——某些敏感指标（如财务、用户PII）只允许特定角色查 ②行级——结合数据脱敏，不同用户看不同行。我用的方案是DSL层做指标级权限，SQL生成时append行级过滤（如`AND department_id IN (用户所属部门)`）。MaxCompute和Hologres本身都有Row-Level Security，可以借力。这是企业级指标平台必不可少的能力。

实战踩坑：DSL生成的SQL让Hologres CPU打满

V2版本Generator上线第一天，OLAP集群CPU从40%飙到100%——业务方拖了一个无时间过滤的查询，扫了10亿行明细。我紧急下线了平台，改了三件事：①时间范围必填（默认近7天）②扫描超1亿行的SQL拒绝执行 ③加查询并发限制（同一用户最多3并发）。重新上线后再没出过类似事故。

教训：DSL平台必须有「防御式优化」——不信任业务方的输入，自动加保护。商业产品如DataWorks默认就有这些保护，自研要自己加。这是平台稳定性的生命线。

总结

回答这道题的高分结构：先讲分层模型（语义/逻辑/方言）→ 给DSL Schema（JSON四元组）→ 讲实现（模板/AST两种）→ 讲跨表JOIN（维度桥）→ 讲优化（防御式规则）。能聊到SQLGlot/Calcite、维度桥、跨方言、行级权限就是Staff级别答题。

下一篇聊治理类话题：指标血缘怎么做？——从字段级到指标级的血缘怎么落地

你们的指标平台用什么模板引擎？跨方言怎么处理？评论区聊聊～

关注「Wood聊大数据」| 9年大数据老兵，聊技术、聊职场、聊成长

刷题进度保存在当前浏览器