将 MySQL 数据加载到 BigQuery 中

您可以使用 BigQuery Data Transfer Service for MySQL 连接器将数据从 MySQL 加载到 BigQuery。它支持托管在本地环境、Cloud SQL 以及其他公有云提供商（例如 Amazon Web Services [AWS] 和 Microsoft Azure）中的 MySQL 实例。借助 BigQuery Data Transfer Service，您可以安排周期性转移作业，以将 MySQL 中的最新数据添加到 BigQuery 中。

准备工作

所需的角色

如果您想要为 Pub/Sub 设置转移作业运行通知，请确保拥有 pubsub.topics.setIamPolicy Identity and Access Management (IAM) 权限。如果您只是要设置电子邮件通知，则无需 Pub/Sub 权限。如需了解详情，请参阅 BigQuery Data Transfer Service 运行通知。

如需获得创建 MySQL 数据转移作业所需的权限，请让您的管理员为您授予项目的 BigQuery Admin (roles/bigquery.admin) IAM 角色。 如需详细了解如何授予角色，请参阅管理对项目、文件夹和组织的访问权限。

此预定义角色包含创建 MySQL 数据转移作业所需的权限。如需查看所需的确切权限，请展开所需权限部分：

所需权限

创建 MySQL 数据转移作业需要以下权限：

  • bigquery.transfers.update
  • bigquery.datasets.get

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

网络连接数

如果公共 IP 地址不适用于 MySQL 数据库连接，您必须设置网络连接。

如需详细了解所需的网络设置，请参阅以下文档：

限制

MySQL 数据转移作业受到以下限制：

  • 与 MySQL 数据库的同时连接数上限由 MySQL 配置参数 max_connections 确定。默认情况下，此值设置为 151 个连接，但可以根据需要配置为更高的限制。因此，单个 MySQL 数据库的并发迁移作业运行数不得超过该上限。此限制还意味着，并发迁移作业的数量应限制为小于 MySQL 数据库支持的最大并发连接数的值。
  • 在 MySQL 中，某些数据类型会映射到 BigQuery 中的字符串类型，以避免任何数据丢失。例如，在 MySQL 中定义的没有明确精度和标度的数值类型会映射到 BigQuery 中的字符串类型。

将 MySQL 数据加载到 BigQuery 中

从下列选项中选择一项：

控制台

  1. 进入数据传输页面。

    转到“数据传输”

  2. 点击 创建转移作业。

  3. 在来源类型部分的来源中，选择 MySQL。

  4. 在转移配置名称部分的显示名中，输入转移作业的名称。转移作业名称可以是任何易于识别该作业的值，以便您以后在需要修改时找到作业。

  5. 在时间表选项部分，执行以下操作：

    • 选择重复频率。如果您选择小时、天（默认）、周或月选项，还必须指定频率。您还可以选择自定义选项来创建更具体的重复频率。如果您选择按需选项，则只有当您手动触发转移作业时，此数据转移作业才会运行。
    • 如果适用，请选择立即开始或从设置的时间开始选项，并提供开始日期和运行时间。

  6. 在目标设置部分的数据集字段中，选择您创建用来存储数据的数据集，或点击新建数据集，然后创建一个数据集用作目标数据集。

  7. 在数据源详细信息部分，执行以下操作：

    • 对于网络连接，选择现有网络连接或点击创建网络连接。如需了解详情，请参阅本文档的网络连接部分。
    • 对于主机，输入 MySQL 数据库服务器的主机名或 IP 地址。
    • 对于端口号，请输入 MySQL 数据库服务器的端口号。
    • 对于数据库名称，输入 MySQL 数据库的名称。
    • 对于用户名，请输入发起 MySQL 数据库连接的 MySQL 用户的用户名。
    • 在密码部分，输入启动 MySQL 数据库连接的 MySQL 用户的密码。
    • 对于 Encryption mode（加密模式），请从菜单中选择 Full（完整），以在连接到 MySQL 数据库时启用完整 SSL 验证；或者选择 Disable（停用），以停用 SSL 验证。

    • 对于要迁移的 MySQL 对象，请执行以下任一操作：

      • 点击浏览以选择转移所需的 MySQL 表，然后点击选择。
      • 手动输入要转移的 MySQL 对象中的表的名称。

    “创建转移”页面上的数据源详细信息

  8. 可选：在服务账号菜单中，指定用于授权转移的自定义服务账号。确保所用的服务账号具有所有必要的角色和权限。如需了解详情，请参阅以服务账号身份转移所有权。

  9. 可选：在通知选项部分，执行以下操作：

    • 如需启用电子邮件通知，请点击电子邮件通知切换开关，将其切换到开启位置。启用此选项后，转移作业管理员会在转移作业运行失败时收到电子邮件通知。
    • 如需为转移作业配置 Pub/Sub 运行通知，请点击 Pub/Sub 通知切换开关，将其切换到开启位置。您可以选择主题名称，也可以点击创建主题来创建一个主题。

  10. 点击保存。

bq

输入 bq mk 命令并提供转移作业创建标志 --transfer_config：

bq mk
    --transfer_config
    --project_id=PROJECT_ID
    --data_source=DATA_SOURCE
    --display_name=DISPLAY_NAME
    --target_dataset=DATASET
    --params='PARAMETERS'

替换以下内容：

  • PROJECT_ID（可选）：您的 Google Cloud 项目 ID。如果未提供 --project_id 标志来指定具体项目，则系统会使用默认项目。
  • DATA_SOURCE：数据源，即 mysql。
  • DISPLAY_NAME：数据转移作业配置的显示名称。转移作业名称可以是任何可让您在需要修改转移作业时识别该转移作业的名称。
  • DATASET：数据转移作业配置的目标数据集。

  • PARAMETERS：所创建转移作业配置的参数（采用 JSON 格式）。例如 --params='{"param":"param_value"}'。以下是 MySQL 转移作业的参数：

    • networkAttachment（可选）：要连接到 MySQL 数据库的网络连接的名称。
    • connector.database：MySQL 数据库的名称。
    • connector.endpoint.host：数据库的主机名或 IP 地址。
    • connector.endpoint.port：数据库的端口号。
    • connector.authentication.username：数据库用户的用户名。
    • connector.authentication.password：数据库用户的密码。 connector.connectionType
    • connector.connectionType（可选）：用于确定连接网址的连接类型。可以是 SERVICE、SID 或 TNS。如果未提供，则默认为 SERVICE。
    • connector.encryptionMode：加密模式。此值可以是 FULL（在连接到 MySQL 数据库时进行完整的 SSL 验证）或 DISABLE（不进行 SSL 验证）。
    • assets：要从 MySQL 数据库转移的 MySQL 表的名称列表（作为转移的一部分）。

例如，以下命令会创建名为 My Transfer 的 MySQL 转移：

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=mysql
    --display_name='My Transfer'
    --params='{"assets":["DB1/DEPARTMENT","DB1/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.encryptionMode":"FULL",
        "connector.database":"DB1",
        "connector.endpoint.host":"54.74.220.23",
        "connector.endpoint.port":3306}'

API

使用 projects.locations.transferConfigs.create 方法并提供 TransferConfig 资源实例。

数据类型映射

下表列出了 MySQL 数据类型与对应的 BigQuery 数据类型。

MySQL 数据类型 BigQuery 数据类型
BIT BOOLEAN
TINYINT INTEGER
BOOL，BOOLEAN BOOLEAN
SMALLINT INTEGER
MEDIUMINT INTEGER
INT，INTEGER INTEGER
BIGINT BIGNUMERIC
FLOAT FLOAT
DOUBLE FLOAT
DECIMAL BIGNUMERIC
DATE DATE
DATETIME TIMESTAMP
TIMESTAMP TIMESTAMP
TIME TIME
YEAR DATE
CHAR STRING
VARCHAR STRING
BINARY BYTES
VARBINARY BYTES
TINYBLOB BYTES
TINYTEXT STRING
BLOB BYTES
TEXT STRING
MEDIUMBLOB BYTES
MEDIUMTEXT STRING
LONGBLOB BYTES
LONGTEXT STRING
ENUM STRING
SET STRING

问题排查

如果您在设置数据转移作业时遇到问题，请参阅 MySQL 转移作业问题。

后续步骤