將 PostgreSQL 資料載入 BigQuery

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

限制

PostgreSQL 資料移轉作業有下列限制：

• 單一 PostgreSQL 資料庫可同時執行的遷移作業數量上限，取決於 PostgreSQL 資料庫支援的並行連線數量上限。並行傳輸作業的數量應限制在小於 PostgreSQL 資料庫支援的並行連線數量上限。

• 單一移轉設定在特定時間只能支援一次資料移轉作業。如果排定在第一筆資料移轉作業完成前執行第二筆資料移轉作業，則系統只會完成第一筆資料移轉作業，並略過與第一筆作業重疊的任何其他資料移轉作業。

  為避免在單一轉移設定中略過轉移作業，建議您設定重複頻率，增加大型資料轉移作業之間的時間間隔。

• 資料移轉期間，PostgreSQL 連接器會識別已建立索引和已分割的鍵欄，以便平行批次移轉資料。因此，我們建議您在資料表中指定主鍵欄或使用索引欄，以提升資料轉移的效能並降低錯誤率。請考量下列事項：

  • 如果您有索引或主鍵限制，建立平行批次時，系統僅支援下列資料欄類型：
    • INTEGER
    • TINYINT
    • SMALLINT
    • FLOAT
    • REAL
    • DOUBLE
    • NUMERIC
    • BIGINT
    • DECIMAL
    • DATE
  • 如果 PostgreSQL 資料移轉作業未使用主鍵或索引資料欄，每個資料表最多只能支援 2,000,000 筆記錄。

增量移轉限制

• 浮水印欄只能選擇 TIMESTAMP 欄。
• 只有含有有效浮水印欄的資產，才支援增量擷取。
• 浮水印資料欄中的值必須單調遞增。
• 增量轉移作業無法同步處理來源資料表中的刪除作業。
• 單一轉移設定只能支援增量或完整擷取。
• 第一次執行增量擷取後，就無法更新 asset 清單中的物件。
• 首次執行增量擷取後，您就無法在轉移設定中變更寫入模式。
• 第一次執行增量擷取後，您就無法變更浮水印欄或主鍵。
• 目的地 BigQuery 資料表會使用提供的主鍵叢集，並受分群資料表限制約束。

• 首次將現有轉移設定更新為增量擷取模式時，更新後的第一次資料轉移作業會轉移資料來源中的所有可用資料。後續的增量資料轉移作業只會轉移資料來源中的新資料列和更新資料列。

• 建議您在浮水印資料欄上建立索引。這個連接器會使用浮水印資料欄做為遞增轉移中的篩選器，因此為這些資料欄建立索引可提升效能。

• 進行增量轉移時，必須使用更新後的資料類型對應。

資料擷取選項

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

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

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

• 「加密資料，並驗證 CA 和主機名稱」模式。這個模式會使用 TCPS 通訊協定透過 TLS 完整驗證伺服器。這項功能會加密所有傳輸中的資料，並驗證資料庫伺服器的憑證是否由信任的憑證授權單位 (CA) 簽署。這個模式也會檢查您連線的主機名稱，是否與伺服器憑證中的一般名稱 (CN) 或主體別名 (SAN) 完全相符。這個模式可防止攻擊者使用其他網域的有效憑證，冒充資料庫伺服器。

  如果主機名稱與憑證 CN 或 SAN 不符，連線就會失敗。您必須設定 DNS 解析，使其與憑證相符，或使用其他安全模式。使用這個模式可獲得最安全的防護，避免中間人 (PITM) 攻擊。

• 「加密資料，但僅驗證 CA」模式。這個模式會透過 TCPS 通訊協定使用 TLS 加密所有資料，並驗證伺服器的憑證是否由用戶端信任的 CA 簽署。不過，這個模式不會驗證伺服器的主機名稱。只要憑證有效且由可信任的 CA 發行，這個模式就能成功連線，無論憑證中的主機名稱是否與您連線的主機名稱相符。

  如果您想確保連線至憑證由信任的 CA 簽署的伺服器，但主機名稱無法驗證，或您無法控管主機名稱設定，請使用這個模式。

• 僅加密模式。這個模式會加密用戶端與伺服器之間傳輸的所有資料。不會執行任何憑證或主機名稱驗證。

  這個模式會保護傳輸中的資料，提供一定程度的安全性，但可能容易受到中間人攻擊。

  如果您需要確保所有資料都經過加密，但無法或不想驗證伺服器的身分，請使用這個模式。使用私人虛擬私有雲時，建議採用這個模式。

• 「未加密或未驗證」模式。這個模式不會加密任何資料，也不會執行任何憑證或主機名稱驗證。所有資料都會以純文字形式傳送。

  在處理機密資料的環境中，不建議使用這個模式。建議您只在安全無虞的獨立網路中，將這個模式用於測試。

信任的伺服器憑證 (PEM)

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

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

您可以在轉移設定的「信任的 PEM 憑證」欄位中指定 PEM 編碼憑證，但須符合下列規定：

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

完整或累加轉移

設定 PostgreSQL 轉移作業時，您可以在轉移作業設定中選取「完整」或「增量」寫入偏好設定，指定資料載入 BigQuery 的方式。預先發布版支援增量轉移。

或者，您也可以設定增量資料移轉作業 (預覽版)，只移轉上次資料移轉作業後變更的資料，而不是在每次資料移轉作業中載入整個資料集。如果您已設定增量資料移轉，則必須指定「附加」或「新增或更新」寫入模式，定義增量資料移轉期間資料寫入 BigQuery 的方式。以下各節說明可用的寫入模式。

附加寫入模式

附加寫入模式只會將新資料列插入目的地資料表。這個選項會嚴格附加移轉的資料，不會檢查現有記錄，因此這個模式可能會導致目的地表格中的資料重複。

選取附加模式時，必須選取浮水印欄。PostgreSQL 連接器必須使用浮水印資料欄，才能追蹤來源資料表中的變更。

Upsert 寫入模式

系統會檢查主鍵，以更新資料列或在目標資料表中插入新資料列。您可以指定主鍵，讓 PostgreSQL 連接器判斷需要哪些變更，才能讓目的地資料表與來源資料表保持同步。如果在資料移轉期間，指定的主鍵出現在目標 BigQuery 資料表中，PostgreSQL 連接器就會使用來源資料表中的新資料更新該列。如果資料移轉期間沒有主鍵，PostgreSQL 連接器就會插入新列。

選取「新增或更新」模式時，必須選取浮水印欄和主鍵：

• PostgreSQL 連接器必須使用水位線資料欄，才能追蹤來源資料表中的變更。

  選取每次修改資料列時都會更新的水印資料欄。建議使用與 UPDATED_AT 或 LAST_MODIFIED 資料欄類似的資料欄。

• 主鍵可以是資料表上的一或多個資料欄，PostgreSQL 連接器需要這些資料欄來判斷是否需要插入或更新資料列。

  選取包含非空值的資料欄，這些值在資料表的所有資料列中都是不重複的。建議您使用包含系統產生 ID、專屬參照代碼 (例如自動遞增 ID) 或不可變動的時間序列 ID 的資料欄。

  為避免資料遺失或損毀，您選取的主鍵資料欄必須具有不重複的值。如果您對所選主鍵欄的唯一性有疑慮，建議改用附加寫入模式。

增量擷取行為

在資料來源中變更資料表結構定義時，這些資料表的增量資料移轉作業會以以下方式反映在 BigQuery 中：

事前準備

• 在 PostgreSQL 資料庫中建立使用者。
• 確認您已完成啟用 BigQuery 資料移轉服務的一切必要動作。
• 請建立 BigQuery 資料集來儲存您的資料。
• 請確認您具備必要角色，才能完成本文中的工作。

必要的角色

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

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

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

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

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

網路連線數

如果 PostgreSQL 資料庫連線沒有可用的公開 IP 位址，請務必設定網路附件。

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

• 如果是從 Cloud SQL 轉移，請參閱「設定 Cloud SQL 執行個體存取權」。
• 如果是從 AWS 轉移，請參閱「設定 AWS-Cloud de Confiance VPN 和網路附件」。
• 如要從 Azure 轉移，請參閱設定 Azure-Cloud de Confiance VPN 和網路附件。

設定 PostgreSQL 資料移轉

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

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

bq mk
    --transfer_config
    --target_dataset=mydataset
    --data_source=postgresql
    --display_name='My Transfer'
    --params='{"assets":["DB1/PUBLIC/DEPARTMENT","DB1/PUBLIC/EMPLOYEES"],
        "connector.authentication.username": "User1",
        "connector.authentication.password":"ABC12345",
        "connector.database":"DB1",
        "connector.endpoint.host":"192.168.0.1",
        "connector.endpoint.port":5432,
        "ingestionType":"incremental",
        "writeMode":"WRITE_MODE_APPEND",
        "watermarkColumns":["createdAt","createdAt"],
        "primaryKeys":[['dep_id'], ['report_by','report_title']],
        "connector.tls.mode": "ENCRYPT_VERIFY_CA_AND_HOST",
        "connector.tls.trustedServerCertificate": "PEM-encoded certificate"}'

      "primaryKeys":[['dep_id'], ['report_by','report_title']],
      "assets":["DB1/USER1/DEPARTMENT","DB1/USER1/EMPLOYEES"],
  

如要在正常時間表以外手動執行資料移轉作業，可以啟動回填作業。

資料類型對應

下表列出 PostgreSQL 資料類型對應的 BigQuery 資料類型。

疑難排解

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

定價

如要瞭解 PostgreSQL 移轉作業的定價資訊，請參閱資料移轉服務定價。

後續步驟

• 請參閱 BigQuery 資料移轉服務總覽。
• 瞭解如何管理移轉作業，包括取得移轉設定、列出移轉設定，以及查看移轉設定的執行記錄。
• 瞭解如何透過跨雲端作業載入資料。