將 PostgreSQL 資料載入 BigQuery

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

限制

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

  • 單一 PostgreSQL 資料庫可同時執行的最大轉移次數，取決於 PostgreSQL 資料庫支援的最大並行連線數。並行傳輸作業的數量應限制在小於 PostgreSQL 資料庫支援的並行連線數量上限。
  • 在 PostgreSQL 中，部分資料類型會對應至 BigQuery 中的字串類型，避免任何資料遺失。舉例來說，PostgreSQL 中定義的數值類型若沒有定義精確度和比例，就會對應至 BigQuery 中的字串類型。

事前準備

必要的角色

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

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

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

所需權限

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

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

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

網路連線數

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

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

設定 PostgreSQL 資料移轉

選取下列選項之一：

控制台

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

    前往「資料移轉」

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

  3. 在「來源類型」部分，選取「PostgreSQL」。

  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」(主機) 部分，輸入 PostgreSQL 資料庫伺服器的主機名稱或 IP 位址。
    • 在「Port number」(通訊埠編號) 中，輸入 PostgreSQL 資料庫伺服器的通訊埠編號。
    • 在「Database name」(資料庫名稱) 部分，輸入 PostgreSQL 資料庫的名稱。
    • 在「使用者名稱」欄位中，輸入啟動 PostgreSQL 資料庫連線的 PostgreSQL 使用者名稱。
    • 在「Password」(密碼) 部分，輸入啟動 PostgreSQL 資料庫連線的 PostgreSQL 使用者密碼。

    • 在「PostgreSQL objects to transfer」(要轉移的 PostgreSQL 物件) 欄位中，執行下列其中一項操作：

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

    「建立移轉」頁面上的資料來源詳細資料

  8. 選用：在「服務帳戶」選單中，指定自訂服務帳戶來授權移轉。確認使用的服務帳戶具備所有必要角色和權限。 詳情請參閱將擁有權轉移給服務帳戶。

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

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

  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：資料來源，即 postgresql。
  • DISPLAY_NAME：資料移轉設定的顯示名稱。移轉作業名稱可以是任意值，日後需要修改移轉作業時，能夠據此識別即可。
  • DATASET：資料移轉設定的目標資料集。

  • PARAMETERS：已建立移轉設定的 JSON 格式參數。例如：--params='{"param":"param_value"}'。以下是 PostgreSQL 轉移作業的參數：

    • networkAttachment (選用)：要連線至 PostgreSQL 資料庫的網路附件名稱。
    • connector.database：PostgreSQL 資料庫的名稱。
    • connector.endpoint.host：資料庫的主機名稱或 IP 位址。
    • connector.endpoint.port：資料庫的通訊埠號碼。
    • connector.authentication.username：資料庫使用者的使用者名稱。
    • connector.authentication.password：資料庫使用者的密碼。
    • assets：要從 PostgreSQL 資料庫轉移的 PostgreSQL 資料表名稱清單。

舉例來說，下列指令會建立名為 My Transfer 的 PostgreSQL 轉移作業：

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}'

API

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

資料類型對應

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

PostgreSQL 資料類型 BigQuery 資料類型
bigint INTEGER
bigserial INTEGER
bit(n) STRING
bit varying(n) STRING
boolean BOOLEAN
bytea BYTES
character STRING
character varying STRING
double precision FLOAT
integer INTEGER
numeric(precision, scale)/decimal(precision, scale) NUMERIC
real FLOAT
smallint INTEGER
smallserial INTEGER
serial INTEGER
text STRING
date DATE
time [ (p) ] [ without timezone ] TIMESTAMP
time [ (p) ] with time zone TIMESTAMP
timestamp [ (p) ] [ without timezone ] TIMESTAMP
timestamp [ (p) ] with time zone TIMESTAMP
xml STRING
tsquery STRING
tsvector STRING
uuid STRING
box STRING
cidr STRING
circle STRING
inet STRING
interval STRING
json STRING
jsonb STRING
line STRING
lseg STRING
macaddr STRING
macaddr8 STRING
money STRING
path STRING
point STRING
polygon STRING

疑難排解

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

後續步驟