使用批量 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（法兰克福）

准备工作

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

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

启用 BigQuery Migration API

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

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

前往 BigQuery Migration API
点击启用。

收集源文件

源文件必须是包含源方言有效 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，请参阅创建存储桶，以及从文件系统上传对象。

提交转换作业

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

批量转换客户端

安装批量转换客户端和 Google Cloud CLI。
生成 gcloud CLI 凭据文件。
在批量转换客户端安装目录中，使用您选择的文本编辑器打开 config.yaml 文件并修改以下设置：
- project_number：输入要用于批量转换作业的项目的编号。您可以在项目的 Google Cloud 控制台欢迎页面的项目信息窗格中找到此信息。
- gcs_bucket：输入批量转换客户端应在转换作业处理期间用来存储文件的 Cloud Storage 存储桶的名称。
- input_directory：输入包含源文件和任何元数据文件的目录的绝对或相对路径。
- output_directory：输入转换后文件的目标目录的绝对或相对路径。
保存更改并关闭 config.yaml 文件。
将源文件和元数据文件放在输入目录中。
使用以下命令运行批量转换客户端：
```
bin/dwh-migration-client
```
创建转换作业后，您可以在 Google Cloud 控制台的转换作业列表中查看其状态。
可选。转换作业完成后，便可以删除该作业在您指定的 Cloud Storage 存储桶中创建的文件，以避免产生存储费用。

控制台

这些步骤假定您已将源文件上传到 Cloud Storage 存储桶中。

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

转到 BigQuery
在导航面板的迁移部分中，点击 SQL 转换。
点击开始转换。
填写转换配置对话框。
1. 在显示名称部分，输入转换作业的名称。名称可以包含字母、数字或下划线。
2. 在处理位置部分，选择要运行转换作业的位置。例如，如果您在欧洲，并且不希望您的数据跨越任何位置边界，那么请选择 eu 区域。当您选择与源文件存储桶相同的位置时，转换作业效果最佳。
3. 在源方言部分，选择要转换的 SQL 方言。
4. 在目标方言部分，选择 BigQuery。
点击下一步。
在源位置部分，指定包含待转换文件的 Cloud Storage 文件夹的路径。您可以按 bucket_name/folder_name/ 格式输入路径，也可以使用浏览选项。
点击下一步。
在目标位置部分，指定用于存储转换后文件的目标 Cloud Storage 文件夹的路径。您可以按 bucket_name/folder_name/ 格式输入路径，也可以使用浏览选项。
如果您执行的转换不需要指定默认对象名称或从来源到目标的名称映射，请跳到第 11 步。否则，请点击下一步。
填写所需的可选设置。
1. 可选。在默认数据库部分，输入要用于源文件的默认数据库名称。转换程序会使用此默认数据库名称来解析缺少数据库名称的 SQL 对象的完全限定名称。
2. 可选。在架构搜索路径部分，指定当转换程序需要解析源文件中缺少架构名称的 SQL 对象的完全限定名称时要搜索的架构。如果源文件使用多个不同的架构名称，请点击添加架构名称，以便为可能引用的每个架构名称添加一个值。
  
  转换程序会搜索您提供的元数据文件，以通过架构名称对表进行验证。如果无法从元数据确定确切的架构，则您输入的第一个架构名称将用作默认值。如需详细了解如何使用默认架构名称，请参阅默认架构。
3. 可选。如果要指定名称映射规则以在转换期间重命名源系统和 BigQuery 之间的 SQL 对象，您可以提供包含名称映射对的 JSON 文件，也可以使用 Google Cloud 控制台指定要映射的值。
  
  如需使用 JSON 文件，请执行以下操作：
  1. 点击上传用于名称映射的 JSON 文件。
  2. 浏览到采用适当格式的名称映射文件的位置，选择该文件，然后点击打开。
    
    请注意，该文件必须小于 5 MB。
  如需使用 Google Cloud 控制台，请执行以下操作：
  1. 点击添加名称映射对。
  2. 在来源列的数据库、架构、关系和特性字段中，添加源对象名称的相应部分。
  3. 在目标列的相应字段中，添加 BigQuery 中的目标对象名称的各个部分。
  4. 在类型部分，选择描述您要映射的对象的对象类型。
  5. 重复第 1 - 4 步，直到指定完所需的所有名称映射对。请注意，使用 Google Cloud 控制台时，最多只能指定 25 个名称映射对。
点击创建以启动转换作业。

创建转换作业后，您可以在转换作业列表中查看其状态。

API

向 projects.locations.workflows.create 方法提供 MigrationWorkflow 资源的实例，并提供受支持的任务类型。

支持的任务类型

Amazon Redshift SQL - Redshift2BigQuery_Translation
Apache HiveQL 和 Beeline CLI - HiveQL2BigQuery_Translation
IBM Netezza SQL 和 NZPLSQL - Netezza2BigQuery_Translation
Teradata 和 Teradata Vantage - Teradata2BigQuery_Translation
Apache Spark SQL - SparkSQL2BigQuery_Translation
Azure Synapse T-SQL - AzureSynapse2BigQuery_Translation
MySQL SQL - MySQL2BigQuery_Translation
Oracle SQL、PL/SQL、Exadata - Oracle2BigQuery_Translation
PostgreSQL SQL - Postgresql2BigQuery_Translation
Presto 或 Trino SQL - Presto2BigQuery_Translation
Snowflake SQL - Snowflake2BigQuery_Translation
SQL Server T-SQL - SQLServer2BigQuery_Translation
Vertica SQL - Vertica2BigQuery_Translation

限制

整合转换 API 仅支持在 1.5 小时内完成的转换作业。

所需权限

如需获得使用整合转换 API 创建转换作业所需的权限，请让您的管理员为您授予 parent 资源的 MigrationWorkflow Editor (roles/bigquerymigration.editor) IAM 角色。如需详细了解如何授予角色，请参阅管理访问权限。

此预定义角色可提供使用整合转换 API 创建转换作业所需的权限。如需查看所需的确切权限，请展开所需权限部分：

所需权限

如需使用整合转换 API 创建转换作业，您需要具备以下权限：

bigquerymigration.workflows.create
bigquerymigration.workflows.get

您也可以使用自定义角色或其他预定义角色来获取这些权限。

示例：创建批量转换

以下 curl 命令会创建一个批量转换作业，其中输入和输出文件存储在 Cloud Storage 中。source_target_mapping 字段包含一个列表，用于将源 literal 条目映射到目标输出的可选相对路径。

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

替换以下内容：

TYPE：转换的任务类型，用于确定源和目标方言。
TARGET_BASE：所有转换输出的基本 URI。
BASE：作为转换源读取的所有文件的基本 URI。
TOKEN：用于身份验证的令牌。如需生成令牌，请使用 gcloud auth print-access-token 命令或 OAuth 2.0 Playground（使用范围 https://www.googleapis.com/auth/cloud-platform）。
PROJECT_ID：处理转换的项目。
LOCATION：用于处理转换的项目的位置。

上述命令返回一个响应，其中包含以 projects/PROJECT_ID/locations/LOCATION/workflows/WORKFLOW_ID 格式编写的工作流 ID。如需通过 API 查看转换结果，请参阅整合转换 API 结果。

示例：使用字符串字面量输入和输出创建转换作业

以下 curl 命令使用字符串字面量输入和输出创建转换作业。source_target_mapping 字段包含一个列表，用于将源目录映射到目标输出的可选相对路径。

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

替换以下内容：

TYPE：转换的任务类型，用于确定源和目标方言。
PATH：字面量条目的标识符，类似于文件名或路径。
STRING：要转换的字面量输入数据（例如 SQL）字符串。
TARGETS：用户希望以 literal 格式在响应中直接返回的预期目标。这些变量应采用目标 URI 格式（例如 GENERATED_DIR + target_spec.relative_path + source_spec.literal.relative_path）。此列表中未列出的任何内容都不会在响应中返回。生成的目录 GENERATED_DIR（用于常规 SQL 转换）是 sql/。
TOKEN：用于身份验证的令牌。如需生成令牌，请使用 gcloud auth print-access-token 命令或 OAuth 2.0 Playground（使用范围 https://www.googleapis.com/auth/cloud-platform）。
PROJECT_ID：处理转换的项目。
LOCATION：用于处理转换的项目的位置。

浏览转换输出

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

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

Google Cloud 控制台输出

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

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

转到 BigQuery
在导航面板的迁移部分中，点击 SQL 转换。
在转换作业列表中，找到要查看其转换详细信息的作业。然后点击转换作业名称。
在结果部分中，您可以查看整体转换成功率、已处理的语句数量和作业时长。
选择日志摘要标签页可查看转换问题摘要，包括问题类别、建议的操作以及每个问题发生的频率。您还可以选择问题类别以查看与该问题类别相关的日志消息（预览版）。
选择日志消息标签页以查看有关每个转换问题的更多详细信息，包括问题类别、特定问题消息以及指向出现问题的文件的链接。您可以在日志消息标签页中选择一个问题，以打开显示输入和输出文件（如果适用）的代码标签页（预览版）。
选择“转换配置”标签页以查看转换作业配置详细信息。

整合转换 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 控制台中查看摘要文件，请按以下步骤操作：

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

转到 BigQuery
在导航面板的迁移部分中，点击 SQL 转换。
在转换作业列表中，找到相关作业，然后点击状态列中的显示详细信息。
在转换报告部分中，点击 batch_translation_report.csv。
在对象详情页面上，点击经过身份验证的网址行中的值以在浏览器中查看该文件。

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

列	说明
时间戳	出现问题时的时间戳。
FilePath	与问题关联的源文件的路径。
文件名	与问题关联的源文件的名称。
ScriptLine	出现问题的行号。
ScriptColumn	出现问题的列号。
TranspilerComponent	发生警告或错误的转换引擎内部组件。此列可能为空。
环境	与警告或错误关联的转换方言环境。此列可能为空。
ObjectName	源文件中与警告或错误关联的 SQL 对象。此列可能为空。
严重程度	问题的严重程度，即警告或错误。
类别	转换问题的类别。
SourceType	此问题的来源。此列中的值可以是 `SQL`（表示输入 SQL 文件中的问题）或 `METADATA`（表示元数据包中的问题）。
消息	转换问题的警告或错误消息。
ScriptContext	源文件中与问题关联的 SQL 代码段。
操作	我们建议您采取的解决该问题的操作。

代码标签页

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

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

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

转到 BigQuery
在导航面板的迁移部分中，点击 SQL 转换。
在转换作业列表中，找到相关作业，然后点击状态列中的显示详细信息。
选择代码标签页。

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

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

转换后的文件

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

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

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

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

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

转到 BigQuery
在导航菜单的迁移部分中，点击 SQL 转换。
在转换作业列表中，找到相关作业，然后点击 更多选项 > 打开交互式转换。

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 转换器迁移代码

所需权限

位置

准备工作

启用 BigQuery Migration API

收集源文件

创建元数据文件

映射 SQL 对象名称

确定如何提交转换作业

创建配置 YAML 文件

将输入文件上传到 Cloud Storage

提交转换作业

批量转换客户端

控制台

API

支持的任务类型

限制

所需权限

所需权限

示例：创建批量转换

示例：使用字符串字面量输入和输出创建转换作业

浏览转换输出

Google Cloud 控制台输出

整合转换 API 结果

摘要报告

代码标签页

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

转换后的文件

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

限制

配额和限制

排查转换错误

RelationNotFound 或 AttributeNotFound 转换问题

价格

后续步骤

`RelationNotFound` 或 `AttributeNotFound` 转换问题