管理分區和叢集建議

本文說明分區和叢集建議工具的運作方式、如何查看建議和洞察資料，以及如何套用分區和叢集建議。

推薦工具的運作方式

BigQuery 分區和分群建議工具會產生分區或分群建議，協助您最佳化 BigQuery 資料表。建議引擎會分析 BigQuery 資料表的工作流程，並提供建議，協助您使用資料表分割或資料表叢集功能，進一步改善工作流程並降低查詢成本。

如要進一步瞭解建議工具服務，請參閱「建議工具總覽」。

分區和叢集建議工具會使用專案過去 30 天的工作負載執行資料，分析每個 BigQuery 資料表的分區和叢集設定是否不盡理想。推薦工具也會運用機器學習技術，預測不同分割或叢集設定可將工作負載執行作業最佳化多少。如果建議引擎發現資料表分區或叢集可大幅節省費用，就會產生建議。分區和分群建議工具會產生下列類型的建議：

每項建議都包含三個部分：

• 分區或叢集特定資料表的指引
• 資料表中要分區或叢集化的特定資料欄
• 套用建議後預估每月可省下的費用

為計算潛在工作負載節省量，建議工具會假設過去 30 天的歷來執行工作負載資料代表未來工作負載。

建議事項 API 也會以洞察的形式傳回資料表工作負載資訊。洞察是可協助您瞭解專案工作負載的發現，可提供更多背景資訊，說明分割區或叢集建議如何改善工作負載成本。

限制

• 分區和叢集建議工具不支援使用舊版 SQL 的 BigQuery 資料表。產生建議時，推薦工具會在分析中排除任何舊版 SQL 查詢。此外，如果對使用舊版 SQL 的 BigQuery 資料表套用分區建議，該資料表中的所有舊版 SQL 工作流程都會中斷。

  套用分割區建議前，請先將舊版 SQL 工作流程遷移至 GoogleSQL。

• BigQuery 不支援就地變更資料表的分區架構。您只能在資料表副本上變更資料表的分區。詳情請參閱「套用分割區建議」。

位置

分區和分群建議工具適用於下列處理位置：

	地區說明	區域名稱	詳細資料
亞太地區
	德里	asia-south2
	香港	asia-east2
	雅加達	asia-southeast2
	孟買	asia-south1
	大阪	asia-northeast2
	首爾	asia-northeast3
	新加坡	asia-southeast1
	雪梨	australia-southeast1
	台灣	asia-east1
	東京	asia-northeast1
歐洲
	比利時	europe-west1	二氧化碳排放量低2
	柏林	europe-west10	二氧化碳排放量低
	歐盟多區域	eu
	法蘭克福	europe-west3	二氧化碳排放量低2
	倫敦	europe-west2	二氧化碳排放量低2
	荷蘭	europe-west4	二氧化碳排放量低2
	蘇黎世	europe-west6	二氧化碳排放量低2
美洲
	愛荷華州	us-central1	二氧化碳排放量低2
	拉斯維加斯	us-west4
	洛杉磯	us-west2
	蒙特婁	northamerica-northeast1	二氧化碳排放量低2
	北維吉尼亞州	us-east4
	奧勒岡州	us-west1	二氧化碳排放量低2
	鹽湖城	us-west3
	聖保羅	southamerica-east1	二氧化碳排放量低2
	多倫多	northamerica-northeast2	二氧化碳排放量低
	美國多區域	us

事前準備

• 確認已為 Google Cloud 專案啟用 Gemini in BigQuery。
• 啟用 Recommender API。

所需權限

如要取得存取分區和叢集建議所需的權限，請要求管理員授予您 BigQuery 分區分群建議工具檢視者 (roles/recommender.bigqueryPartitionClusterViewer) 身分與存取權管理角色。如要進一步瞭解如何授予角色，請參閱「管理專案、資料夾和機構的存取權」。

這個預先定義的角色具備存取分割區和叢集建議所需的權限。如要查看確切的必要權限，請展開「必要權限」部分：

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

如要進一步瞭解 BigQuery 中的 IAM 角色和權限，請參閱「IAM 簡介」。

查看建議

本節說明如何使用 Google Cloud 控制台、Google Cloud CLI 或 Recommender API，查看分割區和叢集建議與深入分析。

選取下列選項之一：

gcloud recommender recommendations list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --recommender=google.bigquery.table.PartitionClusterRecommender \
    --format=FORMAT_TYPE \

gcloud recommender insights list \
    --project=PROJECT_NAME \
    --location=REGION_NAME \
    --insight-type=google.bigquery.table.StatsInsight \
    --format=FORMAT_TYPE \

curl
    -H "Authorization: Bearer $GCLOUD_AUTH_TOKEN"
    -H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/my-project/locations/us/recommenders/google.bigquery.table.PartitionClusterRecommender/recommendations

curl
-H "Authorization: Bearer $GCLOUD_AUTH_TOKEN"
-H "x-goog-user-project: PROJECT_NAME" https://recommender.googleapis.com/v1/projects/my-project/locations/us/insightTypes/google.bigquery.table.StatsInsight/insights

套用叢集建議

如要套用叢集建議，請執行下列任一操作：

• 直接將叢集套用至原始表格
• 將叢集套用至複製的資料表
• 在具體化檢視表中套用叢集

直接將叢集套用至原始資料表

您可以將叢集建議直接套用至現有的 BigQuery 資料表。這個方法比將建議套用至複製的資料表更快，但不會保留備份資料表。

請按照下列步驟，將新的叢集規格套用至未分區或分區資料表。

1. 在 bq 工具中，更新資料表的叢集規格，以符合新的叢集：

    bq update --clustering_fields=CLUSTER_COLUMN DATASET.ORIGINAL_TABLE 

   更改下列內容：

   • CLUSTER_COLUMN：您要叢集化的資料欄，例如 mycolumn
   • DATASET：包含資料表的資料集名稱，例如 mydataset
   • ORIGINAL_TABLE：原始資料表的名稱，例如 mytable

   您也可以呼叫 tables.update 或 tables.patch API 方法來修改叢集規格。

2. 如要根據新的叢集規格將所有資料列叢集化，請執行下列 UPDATE 陳述式：

   UPDATE DATASET.ORIGINAL_TABLE SET CLUSTER_COLUMN=CLUSTER_COLUMN WHERE true

將叢集套用至複製的資料表

將叢集建議套用至 BigQuery 資料表時，您可以先複製原始資料表，然後將建議套用至複製的資料表。這個方法可確保您在需要還原叢集設定變更時，原始資料不會遺失。

您可以使用這個方法，將叢集建議套用至未分區和已分區的資料表。

1. 前往 Google Cloud 控制台的「BigQuery」頁面。

   前往 BigQuery

2. 在查詢編輯器中，使用 LIKE 運算子建立空白資料表，並採用與原始資料表相同的中繼資料 (包括叢集規格)：

   CREATE TABLE DATASET.COPIED_TABLE
   LIKE DATASET.ORIGINAL_TABLE

   更改下列內容：

   • DATASET：包含資料表的資料集名稱，例如 mydataset
   • COPIED_TABLE：複製資料表的名稱，例如 copy_mytable
   • ORIGINAL_TABLE：原始資料表的名稱，例如 mytable

3. 在 Google Cloud 控制台中開啟 Cloud Shell 編輯器。

   啟用 Cloud Shell

4. 在 Cloud Shell 編輯器中，使用 bq update 指令更新所複製資料表的叢集規格，以符合建議的叢集：

    bq update --clustering_fields=CLUSTER_COLUMN DATASET.COPIED_TABLE 

   將 CLUSTER_COLUMN 替換為要用於叢集化的資料欄，例如 mycolumn。

   您也可以呼叫 tables.update 或 tables.patch API 方法來修改叢集規格。

5. 在查詢編輯器中，擷取原始資料表的資料表結構定義，以及分區和分群設定 (如有)。如要擷取結構定義，請查看原始資料表的檢視畫面：INFORMATION_SCHEMA.TABLES

   SELECT
     ddl
   FROM
     DATASET.INFORMATION_SCHEMA.TABLES
   WHERE
     table_name = 'DATASET.ORIGINAL_TABLE;'

   輸出內容是 ORIGINAL_TABLE 的完整資料定義語言 (DDL) 陳述式，包括 PARTITION BY 子句。如要進一步瞭解 DDL 輸出內容中的引數，請參閱 CREATE TABLE 陳述式。

   DDL 輸出內容會指出原始資料表中的分區類型：

   分區類型	輸出範例
   未分割	缺少 PARTITION BY 子句。
   依資料表欄分區	PARTITION BY c0
   	PARTITION BY DATE(c0)
   	PARTITION BY DATETIME_TRUNC(c0, MONTH)
   依擷取時間分區	PARTITION BY _PARTITIONDATE
   	PARTITION BY DATETIME_TRUNC(_PARTITIONTIME, MONTH)

6. 將資料擷取至複製的資料表。使用的程序取決於分區類型。

   • 如果原始資料表未分區，或依資料表欄分區，請將原始資料表的資料擷取到複製的資料表：

     INSERT INTO DATASET.COPIED_TABLE
     SELECT * FROM DATASET.ORIGINAL_TABLE

   • 如果原始資料表是依擷取時間分區，請按照下列步驟操作：

     1. 使用 INFORMATION_SCHEMA.COLUMNS 檢視畫面擷取資料欄清單，以形成資料擷取運算式：

        SELECT
        ARRAY_TO_STRING((
        SELECT
          ARRAY(
          SELECT
            column_name
          FROM
            DATASET.INFORMATION_SCHEMA.COLUMNS
          WHERE
            table_name = 'ORIGINAL_TABLE')), ", ")

        輸出內容是以半形逗號分隔的資料欄名稱清單。

     2. 將原始資料表中的資料擷取到複製的資料表：

        INSERT DATASET.COPIED_TABLE (COLUMN_NAMES, _PARTITIONTIME)
        SELECT *, _PARTITIONTIME FROM DATASET.ORIGINAL_TABLE

        將 COLUMN_NAMES 替換為上一個步驟的輸出內容 (以逗號分隔的資料欄清單)，例如 col1, col2, col3。

   您現在已取得叢集複製資料表，內含與原始資料表相同的資料。 在後續步驟中，您會以新叢集資料表取代原始資料表。

7. 將原始資料表重新命名為備份資料表：

   ALTER TABLE DATASET.ORIGINAL_TABLE
   RENAME TO DATASET.BACKUP_TABLE

   將 BACKUP_TABLE 替換為備份資料表的名稱，例如 backup_mytable。

8. 將複製的資料表重新命名為原始資料表：

   ALTER TABLE DATASET.COPIED_TABLE
   RENAME TO DATASET.ORIGINAL_TABLE

   原始資料表現在會根據叢集建議進行叢集處理。

• 存取權和權限，例如 IAM 權限、資料列層級存取權或資料欄層級存取權。
• 資料表構件，例如資料表本機副本、資料表快照或搜尋索引。
• 任何進行中的資料表程序狀態，例如任何具體化檢視區塊，或您複製資料表時執行的任何工作。
• 可使用時空旅行功能存取歷來資料表資料。
• 與原始資料表相關聯的任何中繼資料，例如 table_option_list 或 column_option_list。詳情請參閱「資料定義語言陳述式」。

如有任何問題，您必須手動將受影響的構件遷移至新表格。

    DROP TABLE DATASET.BACKUP_TABLE
    

在具體化檢視表中套用叢集

套用分區建議

如要套用分區建議，請務必將建議套用至原始表格的副本。BigQuery 不支援就地變更資料表的分區架構，例如將未分區的資料表變更為分區資料表、變更資料表的分區架構，或是建立與基礎資料表分區架構不同的具體化檢視區塊。您只能在資料表副本上變更資料表的分區。

將分區建議套用至複製的資料表

將分區建議套用至 BigQuery 資料表時，您必須先複製原始資料表，然後將建議套用至複製的資料表。這樣一來，如果需要還原分割區，原始資料就能保留下來。

下列程序會使用範例建議，依分區時間單位 DAY 將資料表分區。

1. 使用分區建議建立複製的資料表：

   CREATE TABLE DATASET.COPIED_TABLE
   PARTITION BY DATE_TRUNC(PARTITION_COLUMN, DAY)
   AS SELECT * FROM DATASET.ORIGINAL_TABLE

   更改下列內容：

   • DATASET：包含資料表的資料集名稱，例如 mydataset
   • COPIED_TABLE：複製資料表的名稱，例如 copy_mytable
   • PARTITION_COLUMN：您要分區的資料欄，例如 mycolumn

   如要進一步瞭解如何建立分區資料表，請參閱建立分區資料表。

2. 將原始資料表重新命名為備份資料表：

   ALTER TABLE DATASET.ORIGINAL_TABLE
   RENAME TO DATASET.BACKUP_TABLE

   將 BACKUP_TABLE 替換為備份資料表的名稱，例如 backup_mytable。

3. 將複製的資料表重新命名為原始資料表：

   ALTER TABLE DATASET.COPIED_TABLE
   RENAME TO DATASET.ORIGINAL_TABLE

   原始資料表現在會根據分區建議分區。

• 存取權和權限，例如 IAM 權限、資料列層級存取權或資料欄層級存取權。
• 資料表構件，例如資料表本機副本、資料表快照或搜尋索引。
• 任何進行中的資料表程序狀態，例如任何具體化檢視區塊，或您複製資料表時執行的任何工作。
• 可使用時空旅行功能存取歷來資料表資料。
• 與原始資料表相關聯的任何中繼資料，例如 table_option_list 或 column_option_list。詳情請參閱「資料定義語言陳述式」。
• 可使用舊版 SQL 將查詢結果寫入分區資料表。分區資料表不完全支援使用舊版 SQL。其中一個解決方法是將舊版 SQL 工作流程遷移至 GoogleSQL，再套用分割區建議。

如有任何問題，您必須手動將受影響的構件遷移至新表格。

    DROP TABLE DATASET.BACKUP_TABLE
    

定價

如要瞭解這項功能的定價詳情，請參閱 Gemini in BigQuery 定價總覽。

• 處理成本。套用建議時，您會對 BigQuery 專案執行資料定義語言 (DDL) 或資料操縱語言 (DML) 查詢。
• 儲存空間費用。如果您使用複製資料表的方法，複製 (或備份) 的資料表會佔用額外儲存空間。

系統會根據與專案相關聯的帳單帳戶，收取標準處理和儲存費用。詳情請參閱 BigQuery 計價方式一文。

配額與限制

如要瞭解這項功能的配額和限制，請參閱「Gemini in BigQuery 配額」。

疑難排解

問題：特定表格未顯示任何建議。

如果資料表符合下列條件，系統可能不會顯示分區建議：

• 資料表小於 100 GB。
• 資料表已分區或叢集。

如果資料表符合下列條件，可能不會顯示叢集建議：

• 表格小於 10 GB。
• 資料表已叢集化。

在下列情況下，系統可能會抑制分區和分群建議：

• 資料表因資料操縱語言 (DML) 作業而產生高昂的寫入費用。
• 過去 30 天內未讀取資料表。
• 預估每月省下的費用太少 (節省的運算單元時數不到 1 小時)。