SQL 转换与兼容性

更新时间:
复制 MD 格式

完成 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

说明

REGEXP_EXTRACT(s, r'(\d+)')

REGEXP_EXTRACT(s, '(\\d+)', 1)

MaxCompute 需要显式 group 索引,group 1 为标准匹配

REGEXP_CONTAINS(s, r'x')

REGEXP_INSTR(s, 'x') > 0

或对字面量模式使用 LIKE 'x'

SPLIT(s, ',')

SPLIT(s, ',')

兼容,返回 ARRAY<STRING>

ARRAY_TO_STRING(arr, ',')

CONCAT_WS(',', arr)

相同输出

STARTS_WITH(s, 'foo')

STARTSWITH(s, 'foo')

去掉下划线(兼容模式下大小写不敏感)

ENDS_WITH(s, 'bar')

ENDSWITH(s, 'bar')

同上

LPAD(s, n, pad) / RPAD(s, n, pad)

LPAD / RPAD

兼容

TO_HEX(b) / FROM_HEX(s)

HEX(b) / UNHEX(s)

兼容

LENGTH(s)(字符数)

CHAR_LENGTH(s)

兼容。MaxCompute 中 LENGTH 返回字节数

SUBSTR(s, 1, 3)

SUBSTR(s, 1, 3)

均从 1 开始索引

JSON 函数映射

BigQuery

MaxCompute

说明

PARSE_JSON(s)

JSON_PARSE(s)

STRING → 结构化 JSON。MaxCompute 不支持 wide_number_mode=>'round',默认为 'exact',超大数值可能报错

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 状态

迁移建议

QUALIFY(窗口函数结果过滤)

支持

直接迁移

PIVOT / UNPIVOT

支持

直接迁移,语法几乎相同

SELECT * EXCEPT(...)

支持

直接迁移

SELECT * REPLACE(expr AS col, ...)

支持

直接迁移

脚本变量(DECLARE、SET)

支持(session 变量)

语法略有不同,LHM 处理转换

多语句事务

Delta Table 行级 UPSERT/MERGE 保证数据一致性

通过 SDK 显式 BEGIN/COMMIT;交互式为每语句原子

CREATE OR REPLACE TABLE AS SELECT

支持

Delta Table 场景下的注意事项,请参考面向 BigQuery 迁移的表格式选型

BigQuery UDF(JavaScript)

需重写

迁至 MaxCompute Python UDF 或 SQL UDF

WITH RECURSIVE

支持

用迭代 SQL 或 PyODPS 模拟

BigQuery ML(CREATE MODEL)

支持

提供LLM模型和自定义模型

工具操作说明

LakeHouse Migration(LHM)配置与使用

  • 入口

    LakeHouse Migration(LHM)

  • 工作流程

    1. 建立转换作业。 作业名、源方言(BigQuery)、目标方言(MaxCompute)。

    2. 上传 SQL 文件。 单文件、zip 目录、或 OSS 路径。典型项目一次上传整个调度 SQL 目录。

    3. 配置选项。

      • 目标表格式假设(Append Delta / PK Delta / 标准表)—— LHM 据此选择正确的 DML 写法。

      • 兼容模式假设 —— 保持 odps.sql.bigquery.compatible = true 打开,跳过开关已覆盖的机械改写。

      • 自定义函数映射 —— 内部 BigQuery UDF 提供 MaxCompute UDF 对照表,LHM 会自动替换。

    4. 运行。 时长与语料规模线性相关;1 万条语料通常一小时内完成。

    5. 评审报告。 按类别过滤(auto / review / unsupported)。逐项打开左右对比 diff。评审项 accept 或编辑;不支持项按人工重写。

    6. 下载转换后的 SQL。 LHM 输出镜像目录结构的 MaxCompute 方言 SQL,可直接提交到 DataWorks 项目。

  • 三类输出的处理

    • 已自动转换

      针对迁移后表做冒烟测试,再推生产。

    • 需评审

      LHM 标注具体行号与假设(例如:“假设嵌套 UNNEST 只展开顶层字段”),确认后 accept 或编辑。

    • 不支持

      通常是 BigQuery UDF、WITH RECURSIVE、或平台特有能力(BigQuery ML)。

  • 迭代建议。 大规模迁移时按业务域或源目录分批跑 LHM,而非单次跑全量 —— 小报告更容易被评审团队分头处理。