將 MySQL 資料載入 BigQuery

您可以使用 MySQL 連接器的 BigQuery 資料移轉服務，將資料從 MySQL 載入 BigQuery。支援託管於內部部署環境、Cloud SQL，以及其他公有雲供應商 (例如 Amazon Web Services (AWS) 和 Microsoft Azure) 的 MySQL 執行個體。您可以使用 BigQuery 資料移轉服務，安排週期性移轉工作，將 MySQL 的最新資料新增至 BigQuery。

事前準備

必要的角色

如要取得建立 BigQuery 資料移轉服務資料移轉作業所需的權限，請要求管理員授予您專案的 BigQuery 管理員 (roles/bigquery.admin) IAM 角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和組織的存取權」。

這個預先定義的角色包含建立 BigQuery 資料移轉服務資料移轉作業所需的權限。如要查看確切的必要權限，請展開「Required permissions」(必要權限) 部分：

所需權限

如要建立 BigQuery 資料移轉服務資料移轉作業，您必須具備下列權限：

  • BigQuery 資料移轉服務權限：
    • bigquery.transfers.update
    • bigquery.transfers.get
  • BigQuery 權限：
    • bigquery.datasets.get
    • bigquery.datasets.getIamPolicy
    • bigquery.datasets.update
    • bigquery.datasets.setIamPolicy
    • bigquery.jobs.create

您或許還可透過自訂角色或其他預先定義的角色取得這些權限。

詳情請參閱「授予 bigquery.admin 存取權」。

如要為 Pub/Sub 設定移轉作業執行通知，請確認您擁有 pubsub.topics.setIamPolicy 身分與存取權管理 (IAM) 權限。如果您只想設定電子郵件通知，則不需要擁有 Pub/Sub 權限。詳情請參閱 BigQuery 資料移轉服務執行通知。

網路連線數

如果 MySQL 資料庫連線沒有可用的公開 IP 位址，您必須設定網路連結。

如需必要網路設定的詳細操作說明，請參閱下列文件：

限制

MySQL 資料移轉作業有以下限制：

  • MySQL 資料庫的同時連線數量上限取決於 MySQL 設定參數 max_connections。預設值為 151 個連線，但可視需要設定更高的上限。因此，單一 MySQL 資料庫的同步移轉執行次數上限為該最大值。這項限制也表示，並行傳輸作業的數量應小於 MySQL 資料庫支援的並行連線數量上限。
  • 單一移轉設定在特定時間只能支援一次資料移轉作業。如果排定在第一次資料轉移完成前執行第二次資料轉移，則系統只會完成第一次資料轉移，並略過任何與第一次轉移重疊的資料轉移。
    • 為避免在單一轉移設定中略過轉移作業，建議您設定「重複頻率」，增加大型資料轉移作業之間的時間間隔。

資料擷取選項

以下各節提供設定 MySQL 資料移轉時的資料擷取選項相關資訊。

傳輸層安全標準 (TLS) 設定

MySQL 連接器支援傳輸層安全 (TLS) 設定，可加密傳輸至 BigQuery 的資料。MySQL 連接器支援下列 TLS 設定：

  • 加密資料，並驗證 CA 和主機名稱：這個模式會使用 TCPS 通訊協定透過 TLS 完整驗證伺服器。這項功能會加密所有傳輸中的資料，並驗證資料庫伺服器的憑證是否由信任的憑證授權單位 (CA) 簽署。這個模式也會檢查您連線的主機名稱，是否與伺服器憑證中的一般名稱 (CN) 或主體別名 (SAN) 完全相符。這個模式可防止攻擊者使用其他網域的有效憑證，冒充您的資料庫伺服器。
    • 如果主機名稱與憑證 CN 或 SAN 不符，連線就會失敗。您必須設定 DNS 解析，以符合憑證，或使用其他安全模式。
    • 使用這個模式可獲得最安全的防護，避免中間人 (PITM) 攻擊。
  • 加密資料，並僅驗證 CA：這個模式會透過 TCPS 通訊協定使用 TLS 加密所有資料，並驗證伺服器的憑證是否由用戶端信任的 CA 簽署。不過，這個模式不會驗證伺服器的主機名稱。只要憑證有效且由信任的 VA 核發，這個模式就會成功連線，無論憑證中的主機名稱是否與您連線的主機名稱相符。
    • 如果您想確保連線至憑證由信任的 CA 簽署的伺服器，但主機名稱無法驗證，或您無法控管主機名稱設定，請使用這個模式。
  • 僅加密：這個模式會加密用戶端與伺服器之間傳輸的所有資料。不會執行任何憑證或主機名稱驗證。
    • 這個模式會保護傳輸中的資料，提供一定程度的安全性，但可能容易受到中間人攻擊。
    • 如果您需要確保所有資料都經過加密，但無法或不想驗證伺服器的身分，請使用這個模式。使用私人虛擬私有雲時，建議採用這個模式。
  • 不加密或驗證：這個模式不會加密任何資料，也不會執行任何憑證或主機名稱驗證。所有資料都會以純文字形式傳送。
    • 在處理機密資料的環境中，不建議使用這個模式。
    • 建議您只在安全無虞的獨立網路中，將這個模式用於測試。

信任的伺服器憑證 (PEM)

如果您使用「加密資料，並驗證 CA 和主機名稱」模式或「加密資料，並驗證 CA」模式，也可以提供一或多個 PEM 編碼的憑證。在某些情況下，BigQuery 資料移轉服務需要驗證資料庫伺服器的身分，才能建立 TLS 連線，這時就需要這些憑證：

  • 如果您使用貴機構內私人 CA 簽署的憑證或自行簽署的憑證，請提供完整憑證鏈結或單一自行簽署憑證。如果是透過代管雲端服務 (例如 Amazon Relational Database Service (RDS)) 的內部 CA 簽發憑證，則必須執行這項操作。
  • 如果資料庫伺服器憑證是由公開 CA 簽署 (例如 Let's Encrypt、DigiCert 或 GlobalSign)，您就不需要提供憑證。BigQuery 資料移轉服務已預先安裝並信任這些公開 CA 的根憑證。

建立 MySQL 移轉設定時，您可以在「Trusted PEM Certificate」(信任的 PEM 憑證) 欄位中提供 PEM 編碼憑證，但須符合下列規定：

  • 憑證必須是有效的 PEM 編碼憑證鏈結。
  • 憑證必須完全正確。如果鏈結中缺少任何憑證或內容有誤，TLS 連線就會失敗。
  • 如果是單一憑證，您可以提供資料庫伺服器的單一自行簽署憑證。
  • 如果是私人 CA 核發的完整憑證鏈結，您必須提供完整的信任鏈結。包括資料庫伺服器的憑證，以及任何中繼和根 CA 憑證。

將 MySQL 資料載入 BigQuery

如要將 MySQL 資料新增至 BigQuery，請使用下列任一方法設定移轉作業設定：

控制台

  1. 前往「資料轉移」頁面。

    前往「資料轉移」頁面

  2. 按一下 「建立轉移作業」。

  3. 在「Source type」(來源類型) 部分，「Source」(來源) 請選取「MySQL」。

  4. 在「Transfer config name」(轉移設定名稱) 部分，「Display name」(顯示名稱) 請輸入移轉作業的名稱。移轉作業名稱可以是任意值，日後需要修改移轉作業時能夠據此識別。

  5. 在「Schedule options」(排程選項) 部分執行下列操作：

    • 選取重複頻率。如果選取「小時」、「天」(預設)、「週」或「月」選項，必須一併指定頻率。您也可以選取「Custom」(自訂)，建立專屬的重複頻率。如果選取「On-demand」(隨選)，這項資料移轉作業會在您手動觸發後執行。
    • 視情況選取「立即開始」或「在所設時間開始執行」選項，並提供開始日期和執行時間。

  6. 在「Destination settings」(目的地設定) 部分，「Dataset」(資料集) 請選取您為了儲存資料而建立的資料集，或按一下「Create new dataset」(建立新資料集)，然後建立一個做為目的地資料集。

  7. 在「Data source details」(資料來源詳細資料) 部分執行下列操作：

    • 在「Network attachment」(網路連結) 部分選取現有的網路連結，或是點選「Create Network Attachment」(建立網路連結)。詳情請參閱本文的「網路連線」一節。
    • 在「Host」(主機) 部分，輸入 MySQL 資料庫伺服器的 主機名稱或 IP 位址。
    • 在「Port number」(通訊埠編號) 部分，輸入 MySQL 資料庫伺服器的通訊埠編號。
    • 在「Database name」(資料庫名稱) 部分，輸入 MySQL 資料庫的名稱。
    • 在「Username」(使用者名稱) 部分，輸入啟動 MySQL 資料庫連線的 MySQL 使用者名稱。
    • 在「Password」(密碼) 部分，輸入啟動 MySQL 資料庫連線的 MySQL 使用者密碼。
    • 在「TLS Mode」(TLS 模式) 中，從選單選取一個選項。如要進一步瞭解 TLS 模式，請參閱「TLS 設定」。
    • 在「Trusted PEM Certificate」(信任的 PEM 憑證) 欄位中，輸入核發資料庫伺服器 TLS 憑證的憑證授權單位 (CA) 公開憑證。詳情請參閱「信任的伺服器憑證 (PEM)」一文。

    • 針對「要轉移的 MySQL 物件」，請執行下列其中一項操作：

      • 按一下「瀏覽」，選取移轉作業所需的 MySQL 資料表，然後按一下「選取」。
      • 手動輸入要轉移的 MySQL 物件中的資料表名稱。

  8. 選用：在「Notification options」(通知選項) 專區，執行下列操作：

    • 如要啟用電子郵件通知，請點選「電子郵件通知」切換按鈕，將其設為開啟。啟用這個選項之後，若移轉失敗，移轉作業管理員就會收到電子郵件通知。
    • 如要為移轉作業設定 Pub/Sub 執行通知，請點選「Pub/Sub notifications」(Pub/Sub 通知) 切換按鈕，將其設為開啟。您可以選取主題名稱，或點選「Create a topic」(建立主題) 來建立主題。

  9. 按一下 [儲存]。

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 轉移作業的參數：

    • connector.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.tls.mode：指定要用於這項轉移作業的 TLS 設定：
      • ENCRYPT_VERIFY_CA_AND_HOST 加密資料，並驗證 CA 和主機名稱
      • ENCRYPT_VERIFY_CA 加密資料，且只驗證 CA
      • ENCRYPT_VERIFY_NONE 僅適用於資料加密
      • DISABLE，因為沒有加密或驗證
    • connector.tls.trustedServerCertificate：(選用) 提供一或多個 PEM 編碼憑證。只有在 connector.tls.mode 為 ENCRYPT_VERIFY_CA_AND_HOST 或 ENCRYPT_VERIFY_CA 時才需要提供。
    • 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
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

API

請使用 projects.locations.transferConfigs.create 方法，並提供 TransferConfig 資源的執行個體。

儲存移轉設定後，MySQL 連接器會根據排程選項自動觸發移轉作業。每次執行轉移作業時，MySQL 連接器都會將 MySQL 中的所有可用資料轉移至 BigQuery。

如要手動執行資料轉移 (不在正常排程內)，可以啟動回填作業。

資料類型對應

下表列出 MySQL 資料類型與對應 BigQuery 資料類型的對應關係。

MySQL 資料類型 BigQuery 資料類型
BIT BOOLEAN
TINYINT INTEGER
BOOL、BOOLEAN INTEGER

在 MySQL 資料庫中，BOOL 和 BOOLEAN 資料類型會在內部儲存為 TINYINT(1)，支援 -128 到 127 範圍內的值。因此，BOOL 和 BOOLEAN 資料類型在轉移至 BigQuery 時會對應至 INTEGER。詳情請參閱數值資料類型語法。
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
JSON JSON

疑難排解

如果您無法順利設定資料移轉作業，請參閱 MySQL 移轉問題。

後續步驟