完成 Schema 转换与数据类型映射后,BigQuery 迁移的核心工作是 SQL 移植,涉及看板查询、调度脚本和分析师自定义查询等大量 SQL 资产。本文档介绍如何高效完成 SQL 迁移。
目标
开启 MaxCompute 的 BigQuery 兼容模式,了解它自动消除哪些 BigQuery 差异。
将日期/时间、字符串、JSON、数组等常见 BigQuery 函数类别转换为 MaxCompute 等价函数。
识别 MaxCompute 已原生支持的语法构造(尤其是 UNNEST、MERGE INTO、JSON),以及剩余需要人工改写的差异。
使用 LakeHouse Migration(LHM)批量转换大规模 SQL,结合转换报告将注意力集中在需要人工评审的项上。
BigQuery 兼容模式
对于已在 BigQuery 上构建数据体系的用户,将 SQL 迁移到 MaxCompute SQL 通常意味着大量的语法改写工作。MaxCompute 提供了 BigQuery 兼容模式,通过Flag开关 odps.sql.BigQuery.compatible 启用,从而在部分查询语义、类型转换、内置函数行为及结果格式等方面对齐 BigQuery 的行为,尽可能降低将 BigQuery SQL 迁移到 MaxCompute SQL的改造成本。
使用说明
MaxCompute 提供一个 Project/session 级兼容开关,开启后在部分查询语义、类型转换、内置函数行为及结果格式等方面对齐 BigQuery 的行为。
SET odps.sql.bigquery.compatible = true;注意:
BigQuery 兼容模式覆盖的是SQL改写和脚本迁移中可能遇到的语法和行为差异,而非对 BigQuery SQL语法和函数等行为的完整复刻。
在实际迁移中,需要通过 LHM工具完成 BigQuery SQL 到 MaxCompute SQL 的批量改写。MaxCompute BigQuery 兼容模式并不能覆盖全部的语法和函数差异,定位上是与 LHM 工具的补充:工具负责 SQL 的自动转换,BigQuery 兼容模式则兜底那些工具难以改写、但在运行时可以自动对齐的行为差异,两者配合使用可进一步降低迁移成本。
能力概述
开启后,MaxCompute 优化器会在以下三大类行为上对齐 BigQuery,覆盖了30+行为差异:
类型转换与表达式
查询语义与语法
函数行为差异
以下通过几个示例来帮助理解MaxCompute BigQuery兼容模式的行为能力,更多完整能力请参考:BigQuery兼容模式介绍
示例 1:GROUP BY ALL(自动推导分组键)
场景:BigQuery 提供 GROUP BY ALL 语法糖,系统会根据 SELECT 列表自动推导分组列,不需要逐列写出来。
MaxCompute 默认行为:把 all 当成普通列名解析,若不存在该列则报错
BigQuery Compatible Mode:支持 GROUP BY ALL,自动从 SELECT 中提取非聚合列作为分组键
-- MaxCompute默认模式
SELECT region, product, SUM(amount)
FROM sales
GROUP BY ALL;
-- FAILED: column `all` cannot be resolved
-- BigQuery Compatible Mode
SET odps.sql.BigQuery.compatible = true;
SELECT region, product, SUM(amount)
FROM sales
GROUP BY ALL;
-- 等价于 GROUP BY region, product
-- 返回:North Apple 250, North Banana 80, South Apple 200, South Banana 250示例 2:重复输出列名自动去重
场景:子查询或 CTE 中出现同名列时(例如 SELECT 1 AS a, 2 AS a),两个引擎处理方式完全不同。
MaxCompute 默认行为:直接报歧义错误,需要手动重命名
BigQuery Compatible Mode:自动为后续同名列追加 _1、_2 后缀,确保结果集列名唯一
-- MaxCompute默认模式
WITH t AS (SELECT 1 AS a, 2 AS a) SELECT * FROM t;
-- FAILED: column reference t.a is ambiguous
-- BigQuery Compatible Mode
SET odps.sql.BigQuery.compatible = true;
WITH t AS (SELECT 1 AS a, 2 AS a) SELECT * FROM t;
-- +------+------+
-- | a | a_1 |
-- +------+------+
-- | 1 | 2 |示例 3:DATEDIFF(TIMESTAMP 入参)计算逻辑
场景:两个 TIMESTAMP 之间的分钟差,两个引擎的算法不同。
MaxCompute 默认行为:先按单位截断到边界,再作差。00:02:01 截到分钟为 00:02,00:00:59 截到 00:00,差 = 2
BigQuery Compatible Mode:先算总差值,再按单位换算。实际差 62 秒 = 1 个完整分钟,结果 = 1
-- MaxCompute默认模式
SELECT DATEDIFF(
TIMESTAMP '2025-03-11 00:02:01',
TIMESTAMP '2025-03-11 00:00:59',
'mi');
-- 返回: 2
-- BigQuery Compatible Mode
SET odps.sql.BigQuery.compatible = true;
SELECT DATEDIFF(
TIMESTAMP '2025-03-11 00:02:01',
TIMESTAMP '2025-03-11 00:00:59',
'mi');
-- 返回: 1示例 4:CAST(STRING AS BOOLEAN) 严格解析
场景:把字符串转成布尔值,MaxCompute 和BigQuery 对"什么算 true/false"的理解差异巨大。
MaxCompute默认模式:任何非空字符串都算 true(包括 'yes'、'false'、'1'),空字符串算 false
BigQuery Compatible Mode:只接受 true / false(不区分大小写),其他任意值(包括 'yes'、'1'、'')都报错
-- MaxCompute默认模式
SELECT CAST('yes' AS BOOLEAN); -- true
SELECT CAST('false' AS BOOLEAN); -- true
SELECT CAST('' AS BOOLEAN); -- false
-- BigQuery Compatible Mode
SET odps.sql.BigQuery.compatible = true;
SELECT CAST('true' AS BOOLEAN); -- true
SELECT CAST('FaLsE' AS BOOLEAN); -- false
SELECT CAST('yes' AS BOOLEAN); -- Error
SELECT CAST('1' AS BOOLEAN); -- Error 典型函数转换规则
概述
以下各节列举了 BigQuery 迁移中最常见的函数与语法差异及对应的 MaxCompute 写法。这里仅做不完全列举,目的是帮助建立对BigQuery和MaxCompute 函数语法差异的理解 —— 实际生产 SQL 中可能遇到的函数远多于此。
本节列出的所有转换规则均已在 LakeHouse Migration(LHM)中实现自动化改写;LHM 能覆盖的函数范围也远大于下文所列。因此,无需逐条记忆这些映射,只需在使用 LHM 转换时,能够理解改写背后的逻辑即可。
日期与时间函数映射
日期/时间函数是典型迁移中最大的一类改写。下表按函数族列出核心映射。
加减与差值(统一收敛到 DATEADD / DATEDIFF)
BigQuery 函数 | MaxCompute 函数 | 说明 |
TIMESTAMP_ADD/SUB | DATEADD(TIMESTAMP, delta, datepart) | |
DATETIME_ADD/SUB | DATEADD(TIMESTAMP_NTZ, delta, datepart) | SUB 为负增量 |
DATE_ADD/SUB | DATEADD(DATE, delta, datepart) | |
TIME_ADD/SUB | TIME_ADD/TIME_SUB(BIGINT, delta, datepart) | BQ TIME → MC BIGINT |
TIMESTAMP_DIFF | DATEDIFF(TIMESTAMP, TIMESTAMP, datepart) | |
DATETIME_DIFF | DATEDIFF(TIMESTAMP_NTZ, TIMESTAMP_NTZ, datepart) | |
DATE_DIFF | DATEDIFF(DATE, DATE, datepart) | |
TIME_DIFF | TIME_DIFF(BIGINT, BIGINT, datepart) | BQ TIME → MC BIGINT |
截断
BigQuery | MaxCompute |
TIMESTAMP_TRUNC / DATETIME_TRUNC | DATETRUNC(value, datepart) |
DATE_TRUNC | DATETRUNC(value, datepart) |
格式化与解析(参数顺序翻转)
BigQuery | MaxCompute |
FORMAT_TIMESTAMP(format, ts) | TO_CHAR(ts, format) |
FORMAT_DATETIME(format, dt) | TO_CHAR(dt, format) |
FORMAT_DATE(format, d) | TO_CHAR(d, format) |
FORMAT_TIME(format, time) | FORMAT_TIME(bigint, format) |
PARSE_TIMESTAMP(format, str) | TO_TIMESTAMP(str, format) |
PARSE_DATETIME(format, str) | TO_TIMESTAMP_NTZ(str, format) |
PARSE_DATE(format, str) | TO_DATE(str, format) |
PARSE_TIME(format, str) | TO_TIME(str, format) |
几个主要差异说明:
datepart 格式不同
BigQuery 用
strftime风格 token(%Y、%m、%d、%H、%M、%S);MC 使用缩写:MICROSECOND → ff6、MILLISECOND → ff3、SECOND → ss、MINUTE → mi、HOUR → hh、DAY → dd、MONTH → mm、YEAR → yyyy。FORMAT / PARSE 参数顺序翻转。
BigQuery 是 (format, value),MaxCompute 是 (value, format)。这是手动改写时最常犯的错。
时区。
BigQuery
TIMESTAMP内部是 UTC,按显示时区渲染。MaxCompute 中TIMESTAMP带时区,TIMESTAMP_NTZ不带 。建议:整体设置 Project 时区为 UTC(SET odps.sql.timezone = UTC;)对齐 BigQuery 行为。
字符串函数映射
BigQuery | MaxCompute | 说明 |
|
| MaxCompute 需要显式 group 索引,group 1 为标准匹配 |
|
| 或对字面量模式使用 |
|
| 兼容,返回 |
|
| 相同输出 |
|
| 去掉下划线(兼容模式下大小写不敏感) |
|
| 同上 |
|
| 兼容 |
|
| 兼容 |
|
| 兼容。MaxCompute 中 |
|
| 均从 1 开始索引 |
JSON 函数映射
BigQuery | MaxCompute | 说明 |
PARSE_JSON(s) | JSON_PARSE(s) | STRING → 结构化 JSON。MaxCompute 不支持 |
JSON_VALUE(col, '$.f') JSON_EXTRACT_SCALAR(col, '$.f') | GET_JSON_OBJECT(col, '$.f') | 返回 STRING 标量。对原生 JSON 列也可用 col['f'] 语法 |
JSON_QUERY(col, '$.path') JSON_EXTRACT(col, '$.path') | JSON_EXTRACT(col, '$.path') | 返回 JSON 子串。开启兼容模式后行为与 BigQuery 一致 |
JSON_VALUE_ARRAY(col, '$.path') JSON_EXTRACT_STRING_ARRAY(col, '$.path') | FROM_JSON(GET_JSON_OBJECT(col, '$.path'), 'array<string>') | 返回 ARRAY<STRING>。BigQuery 返回 JSON 字符串数组,MaxCompute 返回 STRING 数组 |
JSON_EXTRACT_ARRAY(col, '$.path') JSON_QUERY_ARRAY(col, '$.path') | JSON_EXPLODE(col, '$.path') | BigQuery 接受 STRING 和 JSON 两种入参;MaxCompute 的 JSON_EXPLODE 仅接受 JSON 类型,STRING 入参需先 JSON_PARSE 转换 |
TO_JSON_STRING(json_col) | TO_JSON(json_col) | 结构化 JSON → STRING。带 pretty_print 参数时展开为 CASE WHEN $1 THEN JSON_PRETTY(JSON_PARSE(TO_JSON($0))) ELSE TO_JSON($0) END |
高级语法差异
以下构造在迁移中仍需关注:
BigQuery 特性 | MaxCompute 状态 | 迁移建议 |
| 直接迁移 | |
| 直接迁移,语法几乎相同 | |
| 直接迁移 | |
| 直接迁移 | |
脚本变量( | (session 变量) | 语法略有不同,LHM 处理转换 |
多语句事务 | Delta Table 行级 UPSERT/MERGE 保证数据一致性 | 通过 SDK 显式 |
| Delta Table 场景下的注意事项,请参考面向 BigQuery 迁移的表格式选型 | |
BigQuery UDF(JavaScript) | 需重写 | 迁至 MaxCompute Python UDF 或 SQL UDF |
| 用迭代 SQL 或 PyODPS 模拟 | |
BigQuery ML( | 提供LLM模型和自定义模型 |
工具操作说明
LakeHouse Migration(LHM)配置与使用
入口
工作流程
建立转换作业。 作业名、源方言(
BigQuery)、目标方言(MaxCompute)。上传 SQL 文件。 单文件、zip 目录、或 OSS 路径。典型项目一次上传整个调度 SQL 目录。
配置选项。
目标表格式假设(Append Delta / PK Delta / 标准表)—— LHM 据此选择正确的 DML 写法。
兼容模式假设 —— 保持
odps.sql.bigquery.compatible = true打开,跳过开关已覆盖的机械改写。自定义函数映射 —— 内部 BigQuery UDF 提供 MaxCompute UDF 对照表,LHM 会自动替换。
运行。 时长与语料规模线性相关;1 万条语料通常一小时内完成。
评审报告。 按类别过滤(
auto/review/unsupported)。逐项打开左右对比 diff。评审项 accept 或编辑;不支持项按人工重写。下载转换后的 SQL。 LHM 输出镜像目录结构的 MaxCompute 方言 SQL,可直接提交到 DataWorks 项目。
三类输出的处理
已自动转换
针对迁移后表做冒烟测试,再推生产。
需评审
LHM 标注具体行号与假设(例如:“假设嵌套 UNNEST 只展开顶层字段”),确认后 accept 或编辑。
不支持
通常是 BigQuery UDF、
WITH RECURSIVE、或平台特有能力(BigQuery ML)。
迭代建议。 大规模迁移时按业务域或源目录分批跑 LHM,而非单次跑全量 —— 小报告更容易被评审团队分头处理。