管理分區和叢集建議

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

推薦引擎的運作方式

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	二氧化碳排放量低2
	歐盟多個區域	eu
	法蘭克福	europe-west3	二氧化碳排放量低
	倫敦	europe-west2	二氧化碳排放量低2
	荷蘭	europe-west4	二氧化碳排放量低
	蘇黎世	europe-west6	二氧化碳排放量低
美洲
	愛荷華州	us-central1	二氧化碳排放量低2
	拉斯維加斯	us-west4
	洛杉磯	us-west2
	蒙特婁	northamerica-northeast1	二氧化碳排放量低2
	北維吉尼亞州	us-east4
	奧勒岡州	us-west1	二氧化碳排放量低2
	鹽湖城	us-west3
	聖保羅	southamerica-east1	二氧化碳排放量低
	多倫多	northamerica-northeast2	二氧化碳排放量低2
	美國 (多個區域)	us

事前準備

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

所需權限

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

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

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

如要進一步瞭解 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 定價。

配額與限制

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

疑難排解

問題：系統不會為特定表格顯示最佳化建議。

系統可能不會針對符合下列條件的資料表顯示分區建議：

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

系統可能不會針對符合下列條件的資料表顯示叢集建議：

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

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

• 資料表的寫入成本高，因為資料操縱語言 (DML) 作業會造成這類情況。
• 過去 30 天內沒有人讀取該資料表。
• 預估每月省下的金額太少 (少於 1 個運算單元小時)。