使用批量 SQL 转换器迁移代码

本文档介绍如何在 BigQuery 中使用批量 SQL 转换器将用其他 SQL 方言编写的脚本转换为 GoogleSQL 查询。本文档适用于熟悉 Google Cloud 控制台的用户。

批量 SQL 转换器是 BigQuery Migration Service 的一部分。批量 SQL 转换器可以将以下 SQL 方言转换为 GoogleSQL：

• Amazon Redshift SQL
• Apache HiveQL 和 Beeline CLI
• IBM Netezza SQL 和 NZPLSQL
• Teradata 和 Teradata Vantage
  • SQL
  • Basic Teradata Query (BTEQ)
  • Teradata 并行传输 (TPT)

此外，预览版还支持转换以下 SQL 方言：

• Apache Spark SQL
• Azure Synapse T-SQL
• MySQL SQL
• Oracle SQL、PL/SQL、Exadata
• PostgreSQL SQL
• Trino 或 PrestoSQL
• Snowflake SQL
• SQL Server T-SQL
• SQLite
• Vertica SQL

所需权限

您必须拥有项目的以下权限才能启用 BigQuery Migration Service：

• resourcemanager.projects.get
• serviceusage.services.enable
• serviceusage.services.get

您需要拥有项目的以下权限才能访问和使用 BigQuery Migration Service：

• bigquerymigration.workflows.create
• bigquerymigration.workflows.get
• bigquerymigration.workflows.list
• bigquerymigration.workflows.delete
• bigquerymigration.subtasks.get

• bigquerymigration.subtasks.list

  或者，您可以使用以下角色获取相同的权限：

  • bigquerymigration.viewer - 只读权限。
  • bigquerymigration.editor - 读写权限。

要访问 Cloud Storage 存储桶的输入和输出文件，您需要拥有以下权限：

• 针对源 Cloud Storage 存储桶的 storage.objects.get 权限。
• 针对源 Cloud Storage 存储桶的 storage.objects.list 权限。
• 针对目标 Cloud Storage 存储桶的 storage.objects.create 权限。

您可以从以下角色获得上述所有必要的 Cloud Storage 权限：

• roles/storage.objectAdmin
• roles/storage.admin

位置

批量 SQL 转换器可在以下处理位置使用：

• us（美国多区域）
• eu（欧盟多区域）
• southamerica-east1（圣保罗）
• us-central1（爱荷华）
• asia-northeast1（东京）
• asia-south1（孟买）
• asia-southeast1（新加坡）
• australia-southeast1（悉尼）
• europe-central2（华沙）
• europe-north1（芬兰）
• europe-west1（比利时）
• europe-west2（伦敦）
• europe-west3（法兰克福）

准备工作

在提交转换作业之前，请完成以下步骤：

1. 启用 BigQuery Migration API。
2. 收集包含待转换的 SQL 脚本和查询的源文件。
3. 可选。创建元数据文件以提高转换的准确性。
4. 可选。确定是否需要将源文件中的 SQL 对象名称映射到 BigQuery 中的新名称。确定必要的名称映射规则。
5. 确定用于提交转换作业的方法。
6. 将源文件上传到 Cloud Storage。

启用 BigQuery Migration API

如果您的 Google Cloud CLI 项目是在 2022 年 2 月 15 日之前创建的，请按如下方式启用 BigQuery Migration API：

1. 在 Google Cloud 控制台中，转到 BigQuery Migration API 页面。

   前往 BigQuery Migration API

2. 点击启用。

收集源文件

源文件必须是包含源方言有效 SQL 的文本文件。源文件也可以包含注释。尽量使用您可以使用的任何方法确保 SQL 有效。

创建元数据文件

为帮助该服务生成更准确的转换结果，我们建议您提供元数据文件。不过，这并非强制性要求。

您可以使用 dwh-migration-dumper 命令行提取工具生成元数据信息，也可以提供自己的元数据文件。元数据文件准备就绪后，您可以将其与源文件一起添加到转换来源文件夹中。转换程序会自动检测这些文件并利用它们来转换源文件，您无需配置任何额外设置即可实现该转换。

如需使用 dwh-migration-dumper 工具生成元数据信息，请参阅生成转换元数据。

如需提供您自己的元数据，请将源系统中 SQL 对象的数据定义语言 (DDL) 语句收集到单独的文本文件中。

映射 SQL 对象名称

您可以选择在批量转换期间执行输出名称映射。执行输出名称映射时，您需要指定名称映射规则，以便将源系统中 SQL 对象的名称更改为 BigQuery 中的新名称。例如，假设您在源系统中拥有对象 schema1.table1，并且希望在 BigQuery 中将该对象命名为 project1.dataset1.table1。那么，如果您执行输出名称映射，则应在启动批量转换作业之前定义名称映射规则。您可以在配置作业时手动输入这些规则，也可以创建包含名称映射规则的 JSON 文件并上传该文件。

确定如何提交转换作业

您可以通过以下三种方式提交批量转换作业：

• 批量转换客户端：通过更改配置文件中的设置来配置作业，并使用命令行提交作业。此方法不要求您将源文件手动上传到 Cloud Storage。客户端在转换作业处理期间仍使用 Cloud Storage 存储文件。

  批量转换客户端是一个开源 Python 客户端，可让您转换本地机器上的源文件，并将转换后的文件输出到本地目录。您可以通过更改客户端配置文件中的一些设置来配置客户端的基本用法。如果您愿意，还可以配置客户端来处理更复杂的任务，例如宏替换以及转换输入和输出的预处理和后处理。如需了解详情，请参阅批量转换客户端的自述文件。

• Google Cloud 控制台：使用界面配置和提交作业。此方法要求您将源文件上传到 Cloud Storage。

• BigQuery Migration API：以编程方式配置和提交作业。此方法要求您将源文件上传到 Cloud Storage。

创建配置 YAML 文件

您可以酌情创建和使用配置 YAML 文件，以自定义批量转换。这些文件可用于通过各种方式对转换输出进行转换。例如，您可以创建配置 YAML 文件，以在转换期间更改 SQL 对象的大小写。

如果要使用 Google Cloud 控制台或 BigQuery Migration API 执行批量转换作业，您可以将配置 YAML 文件上传到包含源文件的 Cloud Storage 存储桶。

如果您要使用批量转换客户端，则可以将配置 YAML 文件放在本地转换输入文件夹中。

将输入文件上传到 Cloud Storage

如果您要使用 Google Cloud 控制台或 BigQuery Migration API 执行转换作业，则必须将包含待转换查询和脚本的源文件上传到 Cloud Storage。您还可以将任何元数据文件或配置 YAML 文件上传到包含源文件的同一 Cloud Storage 存储桶。如需详细了解如何创建存储桶并将文件上传到 Cloud Storage，请参阅创建存储桶，以及从文件系统上传对象。

提交转换作业

请按照以下步骤启动转换作业、监控其进度并查看结果。

curl -d "{
  \"tasks\": {
     string: {
        \"type\": \"TYPE\",
        \"translation_details\": {
           \"target_base_uri\": \"TARGET_BASE\",
           \"source_target_mapping\": {
              \"source_spec\": {
                 \"base_uri\": \"BASE\"
              }
           },
        }
     }
  }
  }" \
  -H "Content-Type:application/json" \
  -H "Authorization: Bearer TOKEN" -X POST https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows

curl -d "{
  \"tasks\": {
     string: {
        \"type\": \"TYPE\",
        \"translation_details\": {
        \"source_target_mapping\": {
           \"source_spec\": {
              \"literal\": {
              \"relative_path\": \"PATH\",
              \"literal_string\": \"STRING\"
              }
           }
        },
        \"target_return_literals\": \"TARGETS\",
        }
     }
  }
  }" \
  -H "Content-Type:application/json" \
  -H "Authorization: Bearer TOKEN" -X POST https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows

浏览转换输出

运行转换作业后，您可以在 Google Cloud 控制台中查看有关该作业的信息。如果您使用 Google Cloud 控制台运行作业，则可以在您指定的目标 Cloud Storage 存储桶中查看作业结果。如果您使用整合转换 API 运行作业，则可以运行另一个包含工作流 ID 的命令来检索作业结果。如果您使用批量转换客户端运行作业，则可以在您指定的输出目录中查看作业结果。批量 SQL 转换器会将以下文件输出到指定目标位置：

• 转换后的文件。
• CSV 格式的转换摘要报告。
• 所使用的输出名称映射（采用 JSON 格式）。

Google Cloud 控制台输出

如需查看转换作业详情，请按照以下步骤操作：

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在导航面板的迁移部分中，点击 SQL 转换。

3. 在转换作业列表中，找到要查看其转换详细信息的作业。然后点击转换作业名称。

4. 在结果部分中，您可以查看整体转换成功率、已处理的语句数量和作业时长。

5. 选择日志摘要标签页可查看转换问题摘要，包括问题类别、建议的操作以及每个问题发生的频率。您还可以选择问题类别以查看与该问题类别相关的日志消息（预览版）。

6. 选择日志消息标签页以查看有关每个转换问题的更多详细信息，包括问题类别、特定问题消息以及指向出现问题的文件的链接。您可以在日志消息标签页中选择一个问题，以打开显示输入和输出文件（如果适用）的代码标签页（预览版）。

7. 选择“转换配置”标签页以查看转换作业配置详细信息。

整合转换 API 结果

异步转换完成后，使用以下命令指定转换作业工作流 ID 来检索结果：

curl \
-H "Content-Type:application/json" \
-H "Authorization:Bearer TOKEN" -X GET https://bigquerymigration.googleapis.com/v2alpha/projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID

替换以下内容：

• TOKEN：用于身份验证的令牌。如需生成令牌，请使用 gcloud auth print-access-token 命令或 OAuth 2.0 Playground（使用范围 https://www.googleapis.com/auth/cloud-platform）。
• PROJECT_ID：处理转换的项目。
• LOCATION：用于处理转换的项目的位置。
• WORKFLOW_ID：创建转换工作流时生成的 ID。

摘要报告

摘要报告是一个 CSV 文件，其中包含一个表，显示了转换作业处理期间出现的所有警告和错误消息。

如需在 Google Cloud 控制台中查看摘要文件，请按以下步骤操作：

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在导航面板的迁移部分中，点击 SQL 转换。

3. 在转换作业列表中，找到相关作业，然后点击状态列中的显示详细信息。

4. 在转换报告部分中，点击 batch_translation_report.csv。

5. 在对象详情页面上，点击经过身份验证的网址行中的值以在浏览器中查看该文件。

下表介绍了摘要文件中的各列：

代码标签页

通过代码标签页，您可以查看特定转换作业的输入和输出文件的详细信息。在代码标签页中，您可以检查转换作业中使用的文件、查看输入文件及其转换的对照比较以发现任何不准确的情况，并查看作业中特定文件的日志摘要和消息。

要访问代码标签页，请按以下步骤操作：

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在导航面板的迁移部分中，点击 SQL 转换。

3. 在转换作业列表中，找到相关作业，然后点击状态列中的显示详细信息。

4. 选择代码标签页。

所使用的输出名称映射文件

此 JSON 文件包含转换作业所使用的输出名称映射规则。由于名称映射规则冲突，或者缺少在转换期间识别的 SQL 对象的名称映射规则，此文件中的规则可能与您为转换作业指定的输出名称映射规则不同。查看此文件以确定名称映射规则是否需要更正。如果需要这样做，请创建新的输出名称映射规则来解决您发现的任何问题，并运行新的转换作业。

转换后的文件

对于每个源文件，系统都会在目标路径中生成一个相应的输出文件。输出文件包含转换后的查询。

使用交互式 SQL 转换器调试批量转换的 SQL 查询

您可以通过与源数据库相同的元数据或对象映射信息，使用 BigQuery 交互式 SQL 转换器来查看或调试 SQL 查询。完成批量转换作业后，BigQuery 会生成转换配置 ID，其中包含有关适用于查询的作业元数据、对象映射或架构搜索路径的信息。您可以将批量转换配置 ID 与交互式 SQL 转换器搭配使用，以运行具有指定配置的 SQL 查询。

如需使用批量转换配置 ID 启动交互式 SQL 转换，请按照以下步骤操作：

1. 在 Google Cloud 控制台中，转到 BigQuery 页面。

   转到 BigQuery

2. 在导航菜单的迁移部分中，点击 SQL 转换。

3. 在转换作业列表中，找到相关作业，然后点击 more_vert 更多选项 > 打开交互式转换。

BigQuery 交互式 SQL 转换器现在会使用相应的批量转换配置 ID 打开。如需查看交互式转换的配置 ID，请点击交互式转换器中的更多 > 转换设置。

限制

转换器无法转换 SQL 以外的语言的用户定义函数 (UDF)，因为转换器无法解析这些语言的此类函数，从而无法确定其输入和输出数据类型。这会导致引用这些 UDF 的 SQL 语句的转换不准确。为了确保在转换期间正确引用非 SQL 语言的 UDF，请使用有效的 SQL 来创建具有相同签名的占位符 UDF。

例如，假设您有一个使用 C 语言编写的 UDF，用来计算两个整数的总和。为了确保引用此 UDF 的 SQL 语句能够正确转换，请创建一个与该 C 语言版 UDF 共用签名的占位符 SQL UDF，如以下示例所示：

CREATE FUNCTION Test.MySum (a INT, b INT)
  RETURNS INT
  LANGUAGE SQL
  RETURN a   b;

将此占位符 UDF 保存在一个文本文件中，并将该文件添加为转换作业的一个源文件。如此，转换器就能了解 UDF 定义并确定预期的输入和输出数据类型。

配额和限制

• BigQuery Migration API 配额适用。
• 每个项目最多可以有 10 个活跃的转换任务。
• 虽然源文件和元数据文件的总数没有硬性限制，但我们建议让文件数量少于 1000 个，以获得更好的性能。

排查转换错误

RelationNotFound 或 AttributeNotFound 转换问题

Translation 最适合元数据 DDL。如果找不到 SQL 对象定义，则转换引擎会引发 RelationNotFound 或 AttributeNotFound 问题。我们建议您使用元数据提取器生成元数据包，以确保所有对象定义都存在。添加元数据是解决大多数转换错误的建议第一步，因为这通常可以解决许多因缺少元数据而间接引发的其他错误。

如需了解详情，请参阅生成元数据以进行转换和评估。

价格

批量 SQL 转换器可免费使用。但是，用于存储输入和输出文件的存储空间会产生正常费用。如需了解详情，请参阅存储价格。

后续步骤

详细了解迁移数据仓库的以下步骤：

• 迁移概览
• 迁移评估
• 架构和数据传输概览
• 数据流水线
• 交互式 SQL 转换
• 数据安全和治理
• 数据验证工具