从 Teradata 迁移到 BigQuery:查询转换概览

上次更新日期:2020 年 9 月
版本说明

本文是系列内容中的一篇,该系列讨论了如何将架构和数据从 Teradata 迁移到 BigQuery。本文简要介绍如何转换专为 Teradata 编写的 SQL 语句,使之与 BigQuery 兼容。

本系列讨论了转换 Teradata 的具体细节,包括以下几个部分:

如需简要了解如何从本地数据仓库转移到 Google Cloud 上的 BigQuery,请参阅相关系列内容,开篇是将数据仓库迁移到 BigQuery:简介和概览

简介

BigQueryTeradata 数据库均符合 ANSI/ISO SQL:2011 标准。此外,Teradata 还创建了一些 SQL 标准扩展,以启用特定于 Teradata 的功能。

相比之下,BigQuery 则不支持这些专用扩展。因此,在从 Teradata 迁移到 BigQuery 的过程中,可能需要重构某些查询。如果查询仅使用 BigQuery 支持的 ANSI/ISO SQL 标准,则具有以下额外的好处:帮助确保可移植性,以及使查询与底层数据仓库不产生关联。

本文档解决了您在将 SQL 查询从 Teradata 迁移到 BigQuery 时可能遇到的一些难题。它阐述了在端到端分阶段迁移的情况下,应在何时应用这些转换。

Teradata SQL 差异

本部分简要讨论了 Teradata SQL 和 BigQuery 标准 SQL 之间的显著差异,以及一些用于在两种方言之间进行转换的策略。本文档并未完全列出所有差异。如需了解详细信息,请参阅 Teradata 到 BigQuery 的 SQL 转换参考

数据定义语言

数据定义语言 (DDL) 用于定义数据库架构。它包含一部分 SQL 语句,例如 CREATEALTERDROP

在大多数情况下,这些语句在 Teradata SQL 和标准 SQL 之间是等效的。以下列出了需要注意的例外情况,但并非详尽无遗:

  • BigQuery 不支持索引操作选项,例如 CREATE INDEXPRIMARY INDEX。查询数据时,BigQuery 不使用索引。得益于它使用 Dremel 的基础模型、使用 Capacitor 的存储技术以及大规模并行架构,它可以快速生成结果。
  • 约束是应用于单个列或整个表的检查。BigQuery 仅支持 NOT NULL 约束。
  • MULTISET:用于在 Teradata 中允许重复行。
  • CASESPECIFIC:用于指定字符数据比较和排序的大小写情况。

数据类型

相较于 Teradata,BigQuery 支持更为简洁的一组数据类型,它将 Teradata 类型组映射到一个标准的 SQL 数据类型。例如:

  • INTEGERSMALLINTBYTEINTBIGINT 均映射到 INT64
  • CLOBJSONXMLUDT 和其他包含大量字符字段的类型映射到 STRING
  • 包含二进制信息的 BLOBBYTEVARBYTE 类型映射到 BYTES

对于日期,主要类型(DATETIMETIMESTAMP)在 Teradata 和 BigQuery 中是等效的。但是,您需要映射 Teradata 中的其他特殊日期类型,例如:

  • TIME_WITH_TIME_ZONE 映射到 TIME
  • TIMESTAMP_WITH_TIME_ZONE 映射到 TIMESTAMP
  • INTERVAL_HOURINTERVAL_MINUTE 和其他 INTERVAL_* 类型映射到 BigQuery 中的 INT64
  • PERIOD(DATE)PERIOD(TIME) 和其他 PERIOD(*) 类型映射到 STRING

BigQuery 不直接支持多维数组。您必须先创建一个结构体数组,每个结构体包含一个 ARRAY 类型的字段。

SELECT 语句

通常情况下,SELECT 语句的语法在 Teradata 和 BigQuery 之间兼容。本部分指出了通常必须在迁移过程中处理的差异。

标识符

BigQuery 可让您将以下内容用作标识符:项目;数据集;表或视图;列。

作为无服务器产品,BigQuery 没有集群、环境或固定端点的概念,因此项目指定数据集的资源层次结构

在 Teradata 的 SELECT 语句中,可以使用完全限定的列名称。BigQuery 始终会引用表(而不是项目或数据集)中的列名或别名。

例如,以下是一些用于在 BigQuery 中使用标识符的选项。

从表中隐式推断出的列:

SELECT
 c
FROM
 project.dataset.table

或使用显式表引用:

SELECT
 table.c
FROM
 project.dataset.table

或使用显式表别名:

SELECT
 t.c
FROM
 project.dataset.table t
别名引用

在 Teradata 的 SELECT 语句中,您可以在同一查询中定义和引用别名。例如,下面的代码段将 flag 定义为列别名,然后立即在封装的 CASE 语句中引用。

SELECT
 F AS flag,
 CASE WHEN flag = 1 THEN ...

在标准 SQL 中,不允许在同一查询中的列之间进行引用。如需进行转换,请将逻辑移至嵌套查询中:

SELECT
 q.*,
 CASE WHEN q.flag = 1 THEN ...
FROM (
 SELECT
   F AS flag,
   ...
) AS q

示例占位符 F 本身可以作为一个返回单列的嵌套查询。

使用 LIKE 进行过滤

在 Teradata 中,LIKE ANY 运算符可用于过滤结果,以获得指定的一组可能选项。例如:

SELECT*
FROM t1
WHERE a LIKE ANY ('string1', 'string2')

如需将具有此运算符的语句转换为标准 SQL,您可以将 ANY 后面的列表拆分为多个 OR 谓词:

SELECT*
FROM t1
WHERE a LIKE 'string1' OR a LIKE 'string2'
QUALIFY 子句

Teradata 的 QUALIFY 子句是 SELECT 语句中的条件子句,它会根据用户指定的搜索条件过滤先前经过计算和排序的分析函数的结果。它的语法结构为 QUALIFY 子句后跟分析函数(如 ROW_NUMBERRANK)和要查找的值:

SELECT a, b
FROM t1
QUALIFY ROW_NUMBER() OVER (PARTITION BY a ORDER BY b) = 1

Teradata 客户通常使用此功能快速进行排名并返回结果,而无需其他子查询。

通过将 WHERE 条件添加到封装查询,即可将 QUALIFY 子句转换为 BigQuery:

SELECT a, b
FROM (
 SELECT a, b,
 ROW_NUMBER() OVER (PARTITION BY A ORDER BY B) row_num
 FROM t1
) WHERE row_num = 1

如需查看示例,请参阅相关的快速入门

数据操纵语言

数据操纵语言 (DML) 用于列出、添加、删除和修改数据库中的数据。它包括 SELECTINSERTDELETEUPDATE 语句。

虽然这些语句的基本形式在 Teradata SQL 和标准 SQL 中是相同的,但是 Teradata 包含了其他的非标准子句和特殊的语句构造,需要您在迁移时进行转换。以下部分列出了最常见的语句、主要区别和推荐的转换,但并非详尽无遗。

INSERT 语句

BigQuery 是一种企业数据仓库,专注于联机分析处理 (OLAP)。使用特定于点的 DML 语句(例如,使用许多 INSERT 语句执行脚本)是尝试将 BigQuery 视为联机事务处理 (OLTP) 系统,但这不是正确的方法。

BigQuery DML 语句适用于批量更新,因此每个修改数据的 DML 语句都会启动一个隐式事务。因此,您应尽可能对 DML 语句进行分组,以免产生不必要的事务开销。

例如,如果您具有 Teradata 中的下面一组语句,按照原样在 BigQuery 中运行它们是一种反模式:

INSERT INTO t1 (...) VALUES (...);
INSERT INTO t1 (...) VALUES (...);

您可以将先前的脚本转换为一个单独的 INSERT 语句,该语句会执行批量操作:

INSERT INTO t1 VALUES (...), (...)

使用大量 INSERT 语句的一个典型场景是,根据现有表创建一个新表。在 BigQuery 中,请勿使用多个 INSERT 语句,正确的做法是使用 CREATE TABLE ... AS SELECT 语句创建一个新表,并在一次操作中插入所有行。

UPDATE 语句

Teradata 中的 UPDATE 语句类似于标准 SQL 中的 UPDATE 语句。它们之间的重要区别如下:

  • SETFROM 子句的顺序相反。
  • 必须移除 UPDATE 中用作表别名的任何 Teradata 相关名称
  • 在标准 SQL 中,每条 UPDATE 语句都必须包含 WHERE 关键字,后跟条件。如需更新表中的所有行,请使用 WHERE true

以下示例显示了 Teradata 中使用联接的 UPDATE 语句:

UPDATE t1
FROM t1, t2
SET
 b = t2.b
WHERE a = t2.a;

标准 SQL 中的等效语句如下:

UPDATE t1
SET
 b = t2.b
FROM t2
WHERE a = t2.a;

上一部分中有关在 BigQuery 中执行大量 DML 语句的注意事项也适用于这种情况。我们建议您使用一个 MERGE 语句来取代多个 UPDATE 语句。

DELETE 语句

标准 SQL 要求 DELETE 语句包含 WHERE 子句。在 Teradata 中,如果您要删除表中的所有行,则 DELETE 语句不一定要包含 WHERE 语句。(如果要删除特定行,Teradata DML 也需要 WHERE 子句)。在转换期间,必须将所有缺少的 WHERE 子句添加到脚本中。只有当表中的所有行都将被删除时,才需要进行此项更改。

例如,Teradata SQL 中的以下语句将从表中删除所有行。ALL 是可选子句:

DELETE t1 ALL;

转换为标准 SQL 的方法如下:

DELETE FROM t1 WHERE TRUE;

存储过程

Teradata 中的存储过程结合了 SQL 和控制语句。存储过程接受参数,这些参数使您可以构建 Teradata 数据库的自定义接口。

存储过程作为 BigQuery 脚本的一部分受支持。

但在某些情况下,其他功能可能更合适。这些替代功能视存储过程的使用方式而定。例如:

  • 计划查询替换用于运行定期查询的触发器。
  • Cloud Composer 中定义的工作流替换控制查询的复杂执行及其相互依存关系的存储过程。
  • 将用作 API 的存储过程重构为使用参数化查询BigQuery API 的数据仓库。这项变更意味着您必须使用其他编程语言(例如 Java 或 Go)重新构建存储过程的逻辑,然后使用代码中的参数来调用 SQL 查询。

重构和替换存储过程中存在的业务逻辑是一项细微的工作,需要您十分熟悉目标平台的数据层和体系结构最佳做法。根据迁移的复杂程度,您可以选择与我们的专业合作伙伴签订服务合同。

在迁移期间转换

在迁移过程中,您必须检查 Teradata SQL 语句和脚本,并确定是否需要将 Teradata SQL 语句转换为 BigQuery 中使用的标准 SQL。与使用迭代迁移的总体建议一样,我们建议您系统地处理此工作。

选择用例

我们之前已定义了一个用例,因为所有的数据集、数据处理、系统和用户交互都需要用例才能实现业务价值。用例包括数据仓库中的一组表、向这些表提供数据的上游流程,以及使用这些表中的数据的下游流程,如下图所示:

用例流程:从上游(本地)到旧版数据仓库再到下游过程。

部分上游流程示例(也称为上游数据流水线)包括来自数据湖、OLTP 系统、CRM 和日志记录应用的 Feed。部分下游流程示例包括信息中心、报告,其他系统的 Feed、业务应用和分析人员使用的临时查询。

在您为迁移选择用例时,请选择那些下游流程的主要部分为内部报告或定义明确的数据输出的用例(如 Feed 或 API)。在初始迁移迭代中选择这些用例类型有以下几大优势:

  • 帮助员工熟悉所需的转换,以便您可以估计后续迭代所需的工作量。
  • 可以更轻松地设置数据准确率的自动测试,因为可以使用脚本比较它们的输出。
  • 支持实时进行视觉比较,相较于原始数据输出,更容易呈现给非技术利益相关者。

转换步骤

如需将用例从 Teradata 迁移到 BigQuery,请按照架构和数据传输概览文档中的建议进行操作。每当将表移至 BigQuery 以及需要修改下游或上游流程时,您还需要对更改所涉及的查询和存储过程进行评估,确定是否必须转换这些查询和过程。

如果需要转换,请遵循 Teradata SQL 差异部分和 Teradata 到 BigQuery 的 SQL 转换参考中的指南,使用符合 ISO SQL:2011标准 SQL 创建新查询。

利用转换后的查询,按照公司用于测试和连续部署的最佳做法在受控环境中测试下游或上游流程。另外,您也可以查看如何在 Google Cloud 中创建软件发布流水线

我们建议您从数据仓库迁移的早期迭代开始,创建不同的资源来协助转换。例如,考虑开发软件库,用于在可能的情况下实现与用例相适应的通用查询转换。该库将有助于后续迭代。您还可以使用这些库和文档指南来培训您的员工,让他们熟悉标准 SQL,了解在 BigQuery 中处理 BigQuery 没有的 Teradata 语法的最佳选项。

后续步骤