在正文之前，请先思考三个问题:

第一个问题，你有注意过 Spark 和 Presto 中同义但不同名的函数吗，比如 instr 和 strpos？接下来要介绍的统一 SQL 可以帮助你自动适应多引擎。

第二个问题，你有纠结过 map 字段中有哪些 key 以及它的含义是什么吗？接下来要介绍的虚拟列语法可以让你不再纠结。

第三个问题，你是如何复用相同的 case when 语句的，是手动复制吗？接下来要介绍的 SQL Define function 语法可以让你避免手动复制，很方便地复用。

本文将介绍的内容可以概括为，一套 SQL 两种语法，帮助用户降低指标的管理成本，提升数据分析的开发效率。

01

指标管理的常见方式

什么是 OLAP 当中的指标？从业务视角来看，从内容平台来讲常用的指标有用户活跃度、点赞数、评论数；从广告平台来讲常用的指标有曝光量、点击率、转化率；从电商平台来讲则有销售额、订单数量、转化率等等。从技术的视角来说，其实就是用 SQL 去查一些库表列。下面根据上文提出的三个问题，介绍一些指标管理的常见方式。

1.1 整数除法在不同引擎的差异

SQL 查询在不同引擎之间是存在差异的，例如整数的除法。举一个点击率的例子，如下图所示，点击率等于点击数除以曝光数，但业务通常会将点击数、曝光数这两个指标定义为 int，这就会导致使用 Presto 计算时查出 int 结果，而使用 Hive 则会查出一个 double 结果。

9b837d100d5bdeb0f80afb5e36eb2554_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

针对此场景，有如下三种常见的解决方案：

查 Presto 时手动改 SQL。但这很不利于推广，因为需要在 Presto 使用一套 SQL， Hive 使用一套 SQL。
二次开发 Presto，允许它对齐 Hive 的行为。但这对于 Presto 本身来说侵入性太强。
使用 Calcite 自动改写 SQL 从而支持多引擎。这也是本文推荐的方案。

1.2 大 MAP/JSON 字段

前文提到的第二个问题是针对大 map 字段而言的，先解释为什么会产生大 map：

假设有一个场景，业务方非常着急看到某个指标数据，而直接添加列肯定来不及，只能复用 ODS 层的某个 map 或者 JSON 字段。这种场景越多 map 字段就会越大，久而久之就会难以维护。

这个难维护的问题常见解决方式只有两种：

（1）登记在 Hive 之外的元数据服务。这需要有一套成熟的数据管理服务，如果服务不成熟会带来噪声，反而增加管理的成本。

（2）维护在内部的文档，但文档本身很容易落后甚至丢失。有充足能力的大厂可能会选第一种，否则只能选第二种。后文要介绍的虚拟链，会以远低于第一种的成本来达到更好的效果。

1.3 频繁变化的 CASE WHEN

最后看文章开头提到的第三个问题：频繁变化的 case when。假设有一个场景，业务需要对城市进行编码便于统一业务指标，如通过 code 统一避免对“北京”的描述不一致，使用的时候再按照需求使用 case when 进行解码。

af27362ac514686aa03c0229fe62531e_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

但随着业务的发展，SQL 中相应的 case when 片段需要不断被修改，业界针对频繁的变化也有如下常见解决方案：

按需新增，手动通知下游修改（费时费力、容易遗漏）；
使用 UDF（变更发布成本高）；
一次枚举所有城市（国内业务可以适用但也会增加维护的 overhead，不适合国际化业务）；
使用 View（字段和表产生冗余，权限也需要单独维护）。

其中第一种需要通知下游，后三种无需通知下游，但是均满足不了字节跳动高速发展的业务需求。因为使用 UDF 的变更发布成本很高，Hive UDF 发布 Maven Jar、上传到 HDFS 非常麻烦；一次枚举所有城市，国内业务可以做到但会增加维护的 overhead，且国际化业务不可能办到，毕竟不可能枚举出全球的所有城市；而使用 View 会导致字段和表产生冗余，权限也需要单独维护。字节跳动对权限的管理非常严格，用户不可能接受冗余的一套权限。因此引入了本文要介绍的 SQL Define Function 方案。

最开始的三个问题以及他们常见的解决方案现在都已经清楚了，接下来本文要介绍的一套 SQL 两种语法方案，可以实现避开之前常见解决方案存在的缺陷，更好地解决这三个问题。

20b10bb59eb731b26149aa98cdd88ef9_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

02

指标管理的最佳实践

此部分将介绍一套 SQL 两种语法在字节跳动的具体实践案例。

2.1 统一成一套 SQL 的实践案例

用户使用 ByteQuery SQL 编写业务指标，而 ByteQuery SQL 是在 ANSI 上兼容了 Hive 和 Spark 的一种部分语法。其中 ByteQuery 是字节跳动的查询引擎，可以实现相同语义的 Presto SQL 或者 Spark SQL 改写，例如除法的 int 可以替换成 double，Hive 的 instr 可以替换成 Presto 的 strpos，Hive 的 date_sub 可以替换成 Presto 和 date_add，以及很多其他的语法改写。

2.2 虚拟列的实践案例

虚拟列可以理解为列级别的视图。前文提到第二个问题的解决方案中有视图，但视图本身会产生冗余的字段和表，从而给鉴权带来负担。但列级别的视图其实是把整个表级别的视图粒度切细，可以按照列来分，在真正查询时也就像表视图一样展开，这样就可以避免冗余字段。用户需要哪一个字段或哪一个指标，针对它单独建虚拟列即可。

举一个具体的例子，如下图所示，有一个 age+1 的虚拟列 virtual_age，用户查询写的其实是 select virtual_age from students，在真正执行时 ByteQuery 会进行改写，将 virtual_age 改写成 age+1 ，这时交给 Presto 或者 Spark 去执行的就是 select age+1 的结果。

77e914c2ad6658b3cbd77fd5d895c87c_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

字节跳动的具体使用案例

如下图所示，假设有一个场景，在广告业务中有个复杂的 JSON，历史上出现过近千个 key，我们可以使用虚拟列将它存在 Hive 中，这就是添加虚拟列的 add Virtual com 语句，其中所有的指标都已进行脱敏处理。线上时会有相应的业务含义，补充上相应 comments 后，下游可以直接看到 show virtual columns from students 那张表。通过一段 SQL 就可以看到相应的所有虚拟列以及它的 comment。上线了虚拟列后，表 owner 就再也没有接到过询问 JSON map 里面的字段含义的 oncall。

baa7227d8fc228d30cdd41f78539884a_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

以上为虚拟列的基础使用场景，但其实虚拟列还可以提供更强大的能力，也就是虚拟列有单独的埋点，可以让用户知道虚拟列的使用情况，具体的使用方式如下：

（1）当某个虚拟列的使用频率很高时，会将其作为 Hive 的列真实的存下来，以达到更快的查询速度。如果真实存的列和 Hive 列保持一致的列名，那么下游完全无需感知列的切换。

（2）当某个虚拟列最近三个月都没有使用的时候，就会将其下线，从而清理冗余指标，降低维护成本。

总之，虚拟列其实就是为 Hive 列的新增提供了一个可量化的数据支撑。

2.3 SQL Defined Function 实践案例

SQL Defined Function 是一种特殊的语法，允许用 SQL 去定义函数来实现对 SQL 表达式的封装。如下图所示，下图中给出了创建的一个新 SQL Defined Function，Create SQL function，然后写可以指定它的 comments，还有它返回的表达式。其他用户想要复用这个 function 里面的表达式就非常方便，直接 select 这个 function 传入相应的参数就可以实现，而不需要复制整个表达式片段。

f377defc5ed0807e30b6e818fb0f0213_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

SQL Defined Function 在字节跳动中的具体应用

在字节跳动的广告业务中，需要对广告主的细化行业进行分类，此时就可以使用 SQL Defined Function 将它存储在 Hive 中。需要注意这个 define function 和 DB 强绑定，必须要写一个 DATABASE 的 name。

在新建时，需要在广告的数据库下定义一个名为 classify 的 function，这个 function 里面有一个行业 ID 字段。下图中返回的表达式取出了简单的一行，当其行业 ID 是 1999 时，我们返回金融类。对其他的也可以做相应的映射，这样下游在使用时，就不需要复制 case when 的表达式，而是直接 select 这个 ad.classify，然后把行业 ID 传进来，即可得到相应的分类结果。

14031fcdf5fac2d0854bf187b861f84a_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

如果想知道这里面具体的分类逻辑，可以通过 DESC 这个 function 来看具体的分类标准。跟虚拟列一样，SQL Defined Function 也有单独的埋点，可以统计一个函数的使用情况。因此当函数的口径发生变化，我们可以根据埋点自动通知到函数的使用者。需要注意的是，通知使用者后，无需使用者去改 SQL，他们只需要知道这个函数的口径已发生过变化就可以了。

目前在字节跳动，每天有数百位的数据分析和数据开发人员在使用 SQL Define Function，为业务节约大量的口径对齐成本。

2.4 虚拟列与SQL Define Function结合使用的实践案例

如下图所示，在字节跳动的广告业务中，我们允许虚拟列和 SQL Define Function 结合来兼容一个指标的历史数据。举一个具体的例子，添加一个虚拟列，这个虚拟列用到了两个 SDF，分别是历史的统计口径和新的统计口径，而这个统计口径在今年发生了变更。那在虚拟列定义的时候，就可以用一个 if date 小于 20230101 来进行划分。

如果是老的逻辑，那么走 SDF1；如果是新的逻辑，则走 SDF2。这样就可以实现对指标的历史数据进行兼容。

ea00627069ebe308af19890142222892_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

03

指标管理的实现原理

此部分将介绍此技术的实现原理，包括如何实现统一 SQL、如何实现虚拟列以及如何实现 SQL Define Function 三个部分的内容。

3.1 如何实现统一 SQL

对于如何实现统一 SQL，我们使用了 Apache Calcite。Calcite 支持解析标准 SQL，然后翻译成各种方言。

如下图所示，图中给出了 Calcite 源码中对于各种方言的支持，左侧是 BigQuery、Calcite、ClickHouse、DB2 等各种引擎的方言，右侧是 Presto 相应的方言改写的各种实现。业界关于这一部分已有很多成熟的案例可以参考，例如领英的 Coral 就用 Calcite 来进行 SQL 翻译，使用的就是左边这一部分代码。而且 Coral 已经被 Trino，也就是原来的 PrestoSQL 集成来读取 Hive 的 view 了。

121cbd8dd2767010a6c6999519bfe154_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

3.2 如何实现虚拟列

Apache Calcite 本身支持虚拟列的解析， Flink 就使用它实现了计算列。如下图所示，图中给出了 Apache Calcite 中解析的那一部分代码。

194e8fea9030accf96f6127e8dd0a06a_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

3.3 如何实现 SQL Define Function

实现 SQL Define Function 需要用到 Calcite，不仅要用到 Calcite，还需要用到 Hive。

如下图所示，从 Calcite 的角度，要实现对 DDL 的解析，需要一套 DDL 去管理 function，实现 function 的创建、删除、修改，也需要在使用 function 的 DML 和 DQL 中进行 function 的自动替换，将它改为相应的表达式。而从 Hive 的角度来看，Hive 提供元数据存储，我们一般都将数据存储在 MySQL 中。存储好数据后，我们需要实现相应增删改查的 thrift 接口。

c48daa32b83f7c8ac4ec3d683c5d1849_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

本文将实现 SQL Define Function 的逻辑大致分为的三步：

3.3.1 新建 Hive 中的 MySQL 表，增加“增删改查”相应的 thrift 接口

如下图所示，图中给出了 MySQL 的表结构。MySQL 表结构里存储了 function 的 ID，db 的 ID，其中 db_id 只是逻辑上关联到了 Hive 中 dbs 表。但字节的 MySQL 并不允许外键约束，所以我们其实没有将它存储为外键。

然后还有函数的名称，函数的参数名，函数的参数类型，函数的表达式，函数的注释，这些都是在 create SQL function 时指定的一些信息。下面的 owner 和 create time 是在执行语句时 ByteQuery 补充上去的。

最后这里用到了一个唯一约束。注意这里的唯一约束是 DB_ID 和 function name，也就是说其他函数名可以相同，但同一个 DB 下的函数名是不允许相同的。

ceb8dcec32108302a77e59ee73205adc_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

3.3.2 ByteQuery 对 SQL Define Function 的语法定义

如下图所示，ByteQuery 对 SQL Define Function 定义了 create SQL function，drop SQL function，replace SQL function，desc SQL function 以及 show SQL functions from db。这些语法其实都是直接扩展了 Calcite 的 Parser.jj，在执行时就是调用 Hive 实现“增删改查”的 thrift 接口。

8903dcbfde36b2fbdc04d777562803fb_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

3.3.3 Apache Calcite 提供 SQL 改写能力，将 function 替换为 expression。

这里也用到了 Calcite 提供的强大 SQL 改写能力。如下图所示，图中截出来的是 Calcite 中一个改写的通用位置。除了 SQL Define Function 可以在这里做改写，很多引擎语法的兼容也可以。真正在实现的时候，我们只需要去重载这一个函数就可以了。其中 function 的一些元数据信息是从 Hive 中的 thrift 接口来的。

ab89a42213a5af139b3f4b18dd30cc9c_640_wx_fmt=png&from=appmsg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1.webp

04

指标管理的未来规划

一是支持翻译成更多的 SQL 方言。目前已经支持了 Hive Presto 和 Spark，之后可以支持 ClickHouse，Elasticsearch 甚至更多的 NoSQL 数据库。

二是虚拟列的独立鉴权。目前的虚拟列直接复用了之前的列权限，可以无需申请新的权限。但由于现在有业务方反馈希望虚拟列进行独立的鉴权，未来会考虑将虚拟列的鉴权配置成表 onwer 来控制。如果想要开启虚拟列的独立鉴权就可以开启；如果不想开启，虚拟列就仍然使用原先列的权限。

三是允许 SQL Define Function 的重载。前文强调了当下我们不允许同一个库下面有同名 Function，但这不太满足业务需求，因为业务可能需要 Function 按参数类型来进行重载，未来我们计划能够实现允许 SQL Define Function 的重载。

四是比 SQL Define Function 更强大的 SQL Macro（允许 Join、Aggregate）。因为目前的 SQL Define Function 只支持表达式，我们未来还可以支持更强大的 SQL Macro，相当于一个自定义的参数化视图，可以允许 join，Aggregate 等等。

最后是虚拟列和 SQL Define Function 的自动挖掘。我们可以分析每天的查询中有哪些高频使用的指标还没被存为虚拟列，没被存为 SQL Define Function，当挖掘到了这些后就可以推动业务方进行相应的改造，提升相应的数据查询效率。

以上就是字节跳动基于 Apache Calcite 的多引擎指标管理的技术原理与最佳实践，我们团队也持续将先进技术、最佳实践提炼并打造成对外商业化产品，目前火山引擎湖仓一体分析服务 LAS 对外提供服务，帮助企业轻松搭建大数据云原生湖仓，也有一系列多引擎指标管理方案，欢迎对这方面有需求、感兴趣的用户来体验，我们提供友好、开发、敏捷的新人上手体验资源与环境。

视频回放：https://www.bilibili.com/video/BV11u411M7Th/?vd_source=c09f0713b2507369924e94f4fec6c133