本系列文章将围绕东南亚头部科技集团的真实迁移历程展开，逐步拆解 BigQuery 迁移至 MaxCompute 过程中的关键挑战与技术创新。本篇为第六篇，MaxCompute SQL 语法及函数功能增强。
注：客户背景为东南亚头部科技集团，文中用 GoTerra 表示。
业务背景和痛点
MaxCompute 和 BigQuery 都是业界领先的大数据处理平台，而 SQL 又是用户进行数据分析的主要工具。虽然大数据环境下的 SQL 语言通常都是基于 ANSI SQL 标准扩展而来的，但是每一个系统都有自己的方言特色，在一些语言细节和行为上存在着微妙的差别。
GoTerra 业务迁移面临着多方面全方位的挑战，其中最大的挑战之一是 10 万条 SQL 的转写问题。这些 SQL 有一部分复杂度非常高，有一两万行 SQL 代码，而且其中使用了非常多的比较高级的 SQL 特性。为了支持 SQL 转写，我们从多个团队调集了精兵强将，成立了专门的团队进行转换工具的开发工作。但是转换工具并不能解决所有的问题，有一些业务特性的差异必须通过对 MaxCompute 平台自身能力的增强和调整来实现。
方案概述
为了使用户业务能够平滑地从 BigQuery 迁移到 MaxCompute，首先需要对两者之间的 SQL 语法差异进行了详细地分析，并基于分析结果进行方案设计。迁移过程中，我们需要重点关注 BigQuery 中的如下两类特性：
- BigQuery 独有特性：这是指在启动业务迁移的时候，MaxCompute 还没有提供的语法特性或者功能。针对这一类的特性，需要对 MaxCompute 进行增强，提供和 BigQuery 类似的业务功能。两个平台都提供但是具体行为存在差异的那些特性。对于这种情况，MaxCompute 为了保持行为兼容，不能直接修改自己的行为。MaxCompute 的做法是增加一个 odps.sql.bigquery.compatible 语法开关，打开这个开关之后的语法行为会尽量和 BigQuery 保持一致。
在本次 GoTerra 搬栈项目中，MaxCompute SQL 主要新增了如下功能：
- auto partition 表unnest 语法
- Delta Table 能力增强Merge INTO 支持多次 update 和 insert
- delete from 支持别名新增 30+内建函数
在 bigquery 兼容模式下调整如下语法行为以保持和 BigQuery 一致：
列别名解析
CTE 支持输出同名列
隐式类型转换规则
某些类型转换行为
pivot 列名生成规则
下文中我们选取部分重点内容进行进一步描述
MaxCompute 新增功能
一、Auto partition
MaxCompute 和 BigQuery 的分区表概念说明
MaxCompute 和 BigQuery 都支持分区表，但是它们的底层设计思路有很大的差异。
MaxCompute 关于分区表的底层设计思路和 Hive 类似。它允许用户根据一个或多个列对表进行划分，从而将表中的数据分散存储在不同的物理位置上。下面是 MaxCompute 创建分区表的一个例子：
create table foo_table(id bigint) partitioned by (pt string);假如这个 table 有两个 partition pt='2025-06-29'和 pt='2025-06-30'，那么这两个 partition 中的数据，它们的 pt 列的取值分别为'2025-06-29'和'2025-06-30'。
BigQuery 支持时间单位列分区(Time-unit column partitioning)和提取时间分区(Ingestion time partitioning)，分区列的数据类型只能是时间类型，包括 DATE、TIMESTAMP 或 DATETIME。Time-unit column partitioning 表的建表语句如下：
create table my_dataset.foo_table (id int64, d date) partition by date_trunc(d, MONTH);从这个建表语句中可以看到，它的底层逻辑是先对时间列按照指定的函数进行运算，上面的例子是使用 date_trunc 函数对时间列进行截取操作，截取的粒度是 MONTH，然后根据截取的结果来作为分区的依据。也就是说，对于同一个分区中的数据，它们的时间列的具体取值可能是不同的，但是都处于相同的某个时间区间内。
BigQuery 在 Time-unit column partitioning 的基础上，又提供了 Ingestion time partitioning 功能。表中有一个名字为_PARTITIONTIME 的伪列，标记每行数据的提取时间，它会按照建表语句被截取至相应的边界（例如每小时或每天）。建表语句如下：
create table my_dataset.foo_ingestion_hour (a string) PARTITION BY TIMESTAMP_TRUNC(_PARTITIONTIME, HOUR);向 Ingestion time partitioning 表里插入数据的时候，可以指定伪列_PARTITIONTIME 的值，也可以不指定。假如不指定，则系统会根据当前时间来自动填充。
-- 插入数据，不指定伪列_PARTITIONTIME的值，由系统自动填充insert into my_dataset.foo_ingestion_hour(a) values('hi1');-- 插入数据，指定伪列_PARTITIONTIME的值insert into my_dataset.foo_ingestion_hour(_PARTITIONTIME, a)values (timestamp '2024-11-02 14:00:00', 'hi2');Auto partition 表
MaxCompute 通过 Auto partition 表来实现和 BigQuery Time-unit column partitioning 类似的功能。在建表语句中，通过如下语法来创建 auto partition 表：
AUTO PARTITIONED BY (trunc_time(<col>, <datePart>) [as alias])举例如下：
CREATE TABLE newtable (id INT64, d DATE) AUTO PARTITIONED BY (trunc_time(d, 'month') as ds);上述建表语句生成的 table 有 3 列，分别是 id, d 和 ds。ds 是一个 string 类型的伪列（pseduo-column），它对应于对于列 d 的取值进行 trunc_time 运算后的取值。
Ingestion time partition 表
MaxCompute 在 auto partition table 的基础上来构建 ingestion time partition 表的能力。建表的时候通过指定 tblproperties 来标识 ingestion time partition 表。建表语句示例如下：
-- 指定分区的粒度是hourcreate tablefoo_ingestion_hourly(_partitiontime timestamp_ntz, a string) auto partitioned by (trunc_time(_partitiontime, 'hour')) tblproperties('ingestion_time_partition'='true');-- 指定分区的粒度是daycreate table foo_ingestion_daily(_partitiontime timestamp_ntz, a bigint) auto partitioned by (trunc_time(_partitiontime, 'day')) tblproperties('ingestion_time_partition'='true');使用如下方式插入数据：
-- 不指定伪列_PARTITIONTIME，由系统自动生成insert into foo_ingestion_hourly(a) values('hi1');insert into foo_ingestion_daily(a) values(100);-- 指定伪列_PARTITIONTIME的值insert into foo_ingestion_hourly(_PARTITIONTIME, a) values (timestamp_ntz '2024-11-02 14:00:00', 'hi2');insert into foo_ingestion_daily(_PARTITIONTIME, a) values (timestamp_ntz '2024-11-02 00:00:00', 200);auto partition 表支持分区裁剪
分区表的主要优势在于它可以显著减少扫描的数据量。例如，在查询时如果指定了某个分区的条件，则只需扫描该分区的数据而不是整个表的数据，从而大大加快了查询速度。
为了下面的举例中描述方便，首先假设建表语句为：
create table table_daily(a bigint, ts timestamp) auto partitioned by (trunc_time(ts, 'day') as pt);它支持在如下一些条件下进行分区裁剪：
- 使用 partition 列来进行数据过滤。例如：
select * from table_daily where pt >= '2024-09-14';2. 直接使用时间列来进行数据过滤，例如：
select * from table_daily where ts between timestamp '2024-09-14 00:00:00' and timestamp '2024-09-15 00:00:00';3. 对时间列调用 trunc_time 函数，并且 trunc 的粒度（year/month/day/hour）和建表语句对应，支持分区裁剪。例如：
select * from table_daily where trunc_time(ts, 'day') = '2024-09-14';4. 对时间列调用 datetrunc 函数，并且 trunc 的粒度（year/month/day/hour）和建表语句对应，支持分区裁剪
select * from table_daily where datetrunc(ts, 'day') = timestamp '2024-09-14 00:00:00';5. 对于其他的时间函数，部分函数支持分区裁剪（具体可以参考后续产品文档的说明），例如：
select * from table_daily where to_date(ts, 'Asia/Jakarta') > date '2024-03-14'6. 假如分区裁剪条件涉及到 scalar suBigQueryuery，系统会先计算 scalar suBigQueryuery 的值，然后根据 suBigQueryuery 的返回值来进行分区裁剪，例如：
-- 系统会先计算select max(ts) from other_table的值，根据所得到的结果来对table_daily进行分区裁剪select * from table_daily where ts = (select max(ts) from other_table);二、内建函数能力增强
我们对 MaxCompute 的内置函数能力进行了扩展，增加新的内建函数，并对已有内建函数的功能进行增强。
时间/时期函数
- 日期时间构造能力增强，增加新的 format，构造的时候允许指定时区信息
- 新增 TO_TIME/TO_TIMESTAMP/TO_TIMESTAMP_NTZ/TIMESTAMP 函数新增 TIME_ADD/TIME_SUB/TIME_DIFF/TIME_TRUNC/FORMAT_TIME 函数新增 CURRENT_DATE/CURRENT_TIMESTAMP_NTZ/CURRENT_MICROS 函数增强 TO_DATE/TO_CHAR 函数功能DATETRUNC/TO_CHAR/TO_DATE/TO_TIMESTAMP_NTZ/TO_TIME/TIMESTAMP 支持时区参数增强时区格式
- 时间函数支持指定更多的处理格式
- DATETRUNC 支持 quarter/week(weekday)/isoweek 参数DATEDIFF 支持 week/week(weekday)/isoweek/ff6 参数DATEADD 支持 quarter/week/ff6 参数LAST_DAY 支持 year/isoyear/quarter/month/week/week(weekday)/isoweek 参数WEEKOFYEAR 支持 week(weekday)参数新增 ISOYEAR 函数
网络 IP 数据处理相关函数
- 新增 NET_IP_NET_MASK 函数新增 NET_IP_FROM_STRING/NET_SAFE_IP_FROM_STRING 函数新增 NET_IP_TO_STRING/NET_IPV4_TO_INT64 函数新增 NET_HOST/NET_PUBLIC_SUFFIX/NET_REG_DOMAIN 函数
字符串及二进制转换
- 新增 BASE32 函数新增 CODEPOINT_ARRAY 函数新增 SAFE_CONVERT_BYTES_TO_STRING 函数新增 FORMAT_STRING 函数增强 REGEXP_EXTRACT 函数功能REVERSE 函数支持输入 binary
正则表达式相关函数
- 新增 REGEXP_CONTAINS 函数增强 REGEXP_EXTRACT/REGEXP_EXTRACT_ALL 函数功能
Json 类型相关函数
- 新增 JSON_STRIP_NULLS 函数增强 JSON_EXTRACT 函数功能TO_JSON 函数对于 struct value 为 NULL 时做兼容性处理
聚合函数
- 新增基于近似聚合算法的 HLL++函数 HLL_COUNT_INIT/HLL_COUNT_MERGE/HLL_COUNT_MERGE_PARTIAL/HLL_COUNT_EXTRACT新增 PERCENTILE_CONT/PERCENTILE_DISC 函数新增 STRING_AGG/ARRAY_AGG 函数新增 APPROX_QUANTILES 函数
地理函数
- 新增 ST_S2CELLIDFROMPOINT/ST_S2CELLIDNUMFROMPOINT 函数ST_ARRAY 函数支持输入 array
除了新增函数和增强已有函数来提供和 BigQuery 相同的计算能力，对于部分具有相近功能的函数，设计了精准的函数转换规则，将两个平台的内建函数进行了一对一映射，确保搬迁后函数行为一致。
三、bigquery 兼容模式
通过设置 odps.sql.bigquery.compatible 的取值，可以调整 MaxCompute 的行为和 BigQuery 尽量保持一致，下面举几个例子
对列别名（alias）解析的影响
如下 query，MaxCompute 默认会报错，因为用户的 query 中 group by a，这里的 a 是 ambiguous，可能是 t1.a，也可能是 t2.a
-- MaxCompute默认行为set odps.sql.bigquery.compatible=false;with t1 as (select 1 a, 2 b), t2 as (select 1 a, 2 b) select t1.a as a from t1 join t2 on t1.a=t2.a group by a; -- 会报错：Semantic analysis exception - a is ambiguous, can be both t1.a or t2.a但是同样的 query 在 BigQuery 里可以运行。原因是在 select 语句中有 t1.a as a，也就是说为 t1.a 分配了一个别名 a，导致 group by a 中的 a 被解释成了 t1.a
-- BigQuery的行为：with t1 as (select 1 a, 2 b), t2 as (select 1 a, 2 b) select t1.a as a from t1 join t2 on t1.a=t2.a group by a; -- 输出结果1bigquery 兼容模式下，上述 query 也能在 MaxCompute 运行
-- MaxCompute在bigquery兼容模式下的行为set odps.sql.bigquery.compatible=true;with t1 as (select 1 a, 2 b), t2 as (select 1 a, 2 b)select t1.a as a from t1 join t2 on t1.a=t2.a group by a;-- 输出+------------+| a |+------------+| 1 |+------------+CTE 输出支持同名列
如下 query，MaxCompute 默认会报错。原因是 MaxCompute 检查出 CTE 的输出列中有重名，输出了两个列的列名都是 a
-- MaxCompute默认行为set odps.sql.bigquery.compatible=false;with t as (select 1 as a, 2 as a, 3 as b)select b from t;-- 会报错：Semantic analysis exception - column reference xxx is ambiguous但是同样的 query 在 BigQuery 里可以运行
-- BigQuery的行为：with t as (select 1 as a, 2 as a, 3 as b)select b from t;-- 输出结果3bigquery 兼容模式下，上述 query 也能在 MaxCompute 运行
-- MaxCompute在bigquery兼容模式下的行为set odps.sql.bigquery.compatible=true;with t as (select 1 as a, 2 as a, 3 as b)select b from t;-- 输出隐式类型转换规则
如下 query，MaxCompute 默认会报错。
-- MaxCompute默认行为set odps.sql.type.system.odps2=true;set odps.sql.bigquery.compatible=false;select '1970-1-2' + interval 1 day;-- 报错，原因是类型不匹配，string类型和interval类型之间不能进行相加操作Semantic analysis exception - invalid operand type(s) STRING,INTERVAL_DAY_TIME for operator '+'但是上述 query 在 BigQuery 可以运行，它可以把这个 query 中的 string 类型隐式类型转换为 date 类型，相当于 select date '1970-1-2' + interval 1 day;
-- BigQuery的行为：select '1970-1-2' + interval 1 day;-- 输出结果1970-01-03T00:00:00bigquery 兼容模式下，上述 query 也能在 MaxCompute 运行，并且行为和 BigQuery 保持一致
-- MaxCompute在bigquery兼容模式下的行为set odps.sql.type.system.odps2=true;set odps.sql.bigquery.compatible=true;select '1970-1-2' + interval 1 day-- 输出结果1970-01-03 00:00:00业务价值
经过前文所描述的 SQL 语法功能增强之后，在 bigquery 兼容模式下，MaxCompute 的语法特性已经能够非常好的兼容 BigQuery。配合转换工具，GoTerra 项目组顺利地完成了客户 SQL 的转写工作，有力地支撑客户的业务从 GCP 平台迁移到 MaxCompute 平台。整个业务切换中用户的业务运行平稳，用户体验良好。
迁移到 MaxCompute 之后，配合 MaxCompute 平台的其他核心业务特性和性能优化措施，SQL 的整体查询性能和效率也有了很大的提高，这进一步体现了 MaxCompute 平台在语法兼容性、高性能和稳定性等诸多方面的整体优势。
