從 AlloyDB 存取 BigQuery

本頁說明如何使用 Lakehouse Federation，透過 AlloyDB for PostgreSQL 介面存取儲存或可透過 BigQuery 存取的資料。

外部資料包裝函式支援多種 BigQuery 資源，可讓您查詢下列項目：

透過這項整合功能，您可以在 PostgreSQL 環境中將 BigQuery 資料集視為本機資料表，執行跨引擎分析。詳情請參閱 AlloyDB 的 Lakehouse Federation 總覽。

本頁假設您已擁有 AlloyDB 叢集和主要執行個體，以及 BigQuery 資料集和資料表。詳情請參閱「建立資料集」和「建立及使用資料表」。

事前準備

  1. 確認 AlloyDB for PostgreSQL 執行個體已設定 the bigquery_fdw.enabled 旗標。
  2. 熟悉支援的 BigQuery 資料類型和資料欄對應。
  3. 登入 Google Cloud 帳戶。如果您是 Google Cloud新手，歡迎 建立帳戶，親自評估產品在實際工作環境中的成效。新客戶還能獲得價值 $300 美元的免費抵免額，可用於執行、測試及部署工作負載。

  4. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  5. Verify that billing is enabled for your Google Cloud project.

  6. Enable the AlloyDB, Compute Engine, Resource Manager, and BigQuery APIs.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the APIs

  7. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

    Roles required to select or create a project

    • Select a project: Selecting a project doesn't require a specific IAM role—you can select any project that you've been granted a role on.
    • Create a project: To create a project, you need the Project Creator role (roles/resourcemanager.projectCreator), which contains the resourcemanager.projects.create permission. Learn how to grant roles.

    Go to project selector

  8. Verify that billing is enabled for your Google Cloud project.

  9. Enable the AlloyDB, Compute Engine, Resource Manager, and BigQuery APIs.

    Roles required to enable APIs

    To enable APIs, you need the Service Usage Admin IAM role (roles/serviceusage.serviceUsageAdmin), which contains the serviceusage.services.enable permission. Learn how to grant roles.

    Enable the APIs

    10.

  10. 啟用建立及連線至 AlloyDB for PostgreSQL 時所需的 Cloud API。

    啟用 API

  11. 在「確認專案」步驟中，按一下「下一步」，確認要變更的專案名稱。

  12. 在「啟用 API」步驟中，按一下「啟用」，啟用下列項目：

    • AlloyDB API
    • Compute Engine API
    • Cloud Resource Manager API
    • Service Networking API
    • BigQuery Storage API
    • BigQuery API

    如果您打算使用與 AlloyDB 位於相同 Google Cloud 專案的虛擬私有雲網路，設定 AlloyDB 的網路連線，則必須啟用 Service Networking API。

    如要使用位於不同 Google Cloud 專案的虛擬私有雲網路，設定 AlloyDB 的網路連線，則必須使用 Compute Engine API 和 Cloud Resource Manager API。

必要的角色

如要將 BigQuery 資料集的讀取權授予 AlloyDB 叢集服務帳戶，您必須具備下列權限。詳情請參閱「授予 AlloyDB BigQuery 資料集存取權」。

  • BigQuery 資料檢視者 (roles/bigquery.dataViewer) 或具有 bigquery.tables.get 和 bigquery.tables.getData 權限的任何自訂角色。如果授予資料表或檢視表的權限，這個角色可提供從資料表或檢視表讀取資料和中繼資料的權限。
  • BigQuery 讀取工作階段使用者 (roles/bigquery.readSessionUser) 或具有 bigquery.readsessions.create 和 bigquery.readsessions.getData 權限的任何自訂角色。可建立及使用讀取工作階段。
  • BigQuery 工作使用者 (roles/bigquery.jobUser) 或具有 bigquery.jobs.create 權限的任何自訂角色。提供使用 BigQuery API 在專案中執行工作 (包括查詢) 的權限。這個角色只能授予 Resource Manager 資源 (專案、資料夾和機構)。
  • Storage 物件檢視者 (roles/storage.objectViewer) 或具有 storage.objects.get 權限的任何自訂角色。提供存取 BigQuery 外部資料表的權限。必須在專案或 bucket 層級授予。

將 BigQuery 資料集存取權授予 AlloyDB

在 AlloyDB 叢集上啟用 Lakehouse Federation 功能後，您必須授予 AlloyDB 叢集服務帳戶 BigQuery 資料集的存取權。

使用 AlloyDB Studio 連結 BigQuery 資料表時， Google Cloud 控制台會自動授予叢集服務帳戶必要權限。

如要使用 gcloud CLI 授予存取權，請按照下列步驟操作：

gcloud

如要使用 gcloud CLI，可以安裝及初始化 Google Cloud CLI，也可以使用 Cloud Shell。

  1. 開啟 gcloud CLI。如果沒有安裝 gcloud CLI，請安裝並初始化 gcloud CLI，或使用 Cloud Shell。

  2. 請執行 gcloud beta alloydb clusters describe 指令。

    gcloud beta alloydb clusters describe CLUSTER --region=REGION

    更改下列內容：

    • CLUSTER：AlloyDB 叢集 ID。
    • REGION：AlloyDB 叢集的位置，例如 asia-east1、us-east1。如要查看完整地區清單，請參閱「管理執行個體位置」。

    輸出結果含有 serviceAccountEmail 欄位，這是叢集的服務帳戶。您也可以在「叢集總覽」頁面中找到服務帳戶。

  3. 授予必要權限。詳情請參閱「使用 IAM 控管資源存取權」。

    如果叢集服務帳戶沒有必要權限，對 BigQuery 資料表執行查詢時，會出現下列錯誤：

    • The user does not have bigquery.readsessions.create permissions
    • Permission bigquery.tables.get denied on table
    • Permission bigquery.tables.getData denied on table

設定擴充功能

如要設定擴充功能，請按照下列步驟操作：

控制台

  1. 前往「Clusters」(叢集) 頁面。

    前往叢集

  2. 按一下要使用的叢集 ID。

  3. 按一下導覽選單中的「AlloyDB Studio」。

  4. 登入資料庫。

  5. 在「Explorer」窗格中，展開相關結構定義。

  6. 按一下「BigQuery Tables」(BigQuery 資料表) 旁的「動作」選單，然後點選「Connect BigQuery table」(連結 BigQuery 資料表)。

  7. 在「連結 BigQuery 資料表」窗格中，選擇來源專案、來源資料集和資料表。

  8. 「查看及選取資料欄」表格會顯示所選表格中的資料欄。 選取要對應的資料欄。

  9. 在「Table name」(資料表名稱) 欄位中，輸入外部資料表的名稱。

  10. 選用：按一下「查看 SQL 指令」，即可查看產生的指令。

  11. 按一下「連結資料表」。對話方塊會顯示進度。完成程序後，您就可以像查詢 AlloyDB 中的任何資料表一樣，查詢該資料表。

psql

  1. 建立擴充功能。

    1. 按照「將 psql 用戶端連線至執行個體」一文中的操作說明，使用 psql 用戶端連線至 AlloyDB 執行個體。或者，您也可以使用 AlloyDB Studio。詳情請參閱「透過 Google Cloud 控制台管理資料」。

    2. 執行下列指令：

      CREATE EXTENSION bigquery_fdw;

  2. 建立外部伺服器，定義遠端 BigQuery 資料集的連線參數。

    CREATE SERVER BIGQUERY_SERVER_NAME FOREIGN DATA WRAPPER bigquery_fdw;

    更改下列內容：

    • BIGQUERY_SERVER_NAME：外部伺服器的專屬 ID。在特定資料庫中定義一次即可。您可以將 BIGQUERY_SERVER_NAME 替換成伺服器名稱。

  3. 執行 CREATE USER MAPPING 指令，建立使用者對應，將要連線至外部伺服器的本機 PostgreSQL 使用者對應至外部伺服器。

    CREATE USER MAPPING FOR USERNAME SERVER BIGQUERY_SERVER_NAME ;

    更改下列內容：

    • USERNAME：資料庫使用者名稱或存取外部資料表的 IAM 使用者。
    • BIGQUERY_SERVER_NAME：您建立的外部伺服器專屬 ID。

  4. 使用 CREATE FOREIGN TABLE 指令，定義與您要在 BigQuery 中存取的資料表相應的外來資料表。這個指令可讓您定義遠端資料表的結構。外部資料表可以包含 BigQuery 來源資料表中的所有資料欄，也可以只包含部分資料欄。

    CREATE FOREIGN TABLE TABLENAME (
COLUMN1_NAME DATA_TYPE,
COLUMN2_NAME DATA_TYPE,
... ) SERVER BIGQUERY_SERVER_NAME OPTIONS (project BIGQUERY_PROJECT_ID,
dataset BIGQUERY_DATASET_NAME,
table BIGQUERY_TABLE_NAME
[, mode EXECUTION_MODE]);

    更改下列內容：

    • TABLENAME：本機 AlloyDB 資料庫中的外部資料表名稱。
    • COLUMNX_NAME：AlloyDB 資料欄名稱。資料欄名稱必須與 BigQuery 來源資料表中對應的資料欄名稱完全一致。X 表示表格可建立多個資料欄。 名稱也必須與 BigQuery 資料欄的大小寫完全一致。如果 BigQuery 資料欄名稱包含大寫字母 (例如 employeeID)，則 AlloyDB 識別碼必須以雙引號括住 (例如 "employeeID")，才能保留大小寫混合或大寫字母。
    • DATA_TYPE：資料欄的資料類型。
    • BIGQUERY_SERVER_NAME：您建立的外部伺服器專屬 ID。
    • BIGQUERY_PROJECT_ID：BigQuery 資料集所在的專案 ID。
    • BIGQUERY_DATASET_NAME：資料表所屬的 BigQuery 資料集名稱。
    • BIGQUERY_TABLE_NAME：BigQuery 資料表名稱。
    • EXECUTION_MODE：選用。mode 選項可以是 query (使用 BigQuery API 執行複雜查詢)、storage (使用 BigQuery Storage API 快速大量讀取資料)，或是 auto (自動選擇模式)。預設值為 auto。詳情請參閱「BigQuery 外部資料包裝函式執行模式」。

    建立外部資料表後，您就可以像查詢 AlloyDB 中的任何資料表一樣，查詢這個資料表。

BigQuery 外部資料包裝函式執行模式

執行模式會決定 AlloyDB for PostgreSQL 與 BigQuery 互動以擷取資料的方式。BigQuery 外部資料包裝函式支援兩種執行模式：query 和 storage。選擇合適的模式至關重要，因為每種模式的效能特性和價格都不同。詳情請參閱 BigQuery 定價一文。

查詢模式

這個模式會使用 BigQuery API 從 BigQuery 擷取資料。這項功能會使用 BigQuery 的運算引擎，透過下推篩選器和匯總來執行複雜查詢。也就是說，WHERE 子句、GROUP BY 子句和匯總作業會在 BigQuery 上執行，然後再將資料傳回 PostgreSQL。這個模式也支援查詢 BigQuery 檢視表和外部資料表。

由於這項 API 提供結構化、分頁的資料列回應，適用於小型結果集，因此與 BigQuery Storage API 的串流替代方案相比，讀取大型資料集時會有處理量限制，且延遲時間較長。

儲存模式

這個模式會使用 BigQuery Storage API 從 BigQuery 擷取資料。這項功能會以二進位序列化格式，透過網路傳送結構化資料，進而實現高處理量的讀取作業。掃描 BigQuery 中的大型資料表時，建議使用這個模式。

不過，這個模式有一些限制。並非所有複雜的 SQL 作業都能下推至 BigQuery Storage API。舉例來說，匯總作業無法下推至 BigQuery，必須在 AlloyDB 中執行。這個模式也不支援查詢 BigQuery 檢視區塊和外部資料表。

自動模式

如果您未在 CREATE FOREIGN TABLE 指令中設定模式，預設模式會設為 auto。 使用 auto 模式時，AlloyDB 會選取基礎 API，以平衡效能並盡可能將 SQL 作業下推至 BigQuery。

後續步驟