分區資料表簡介

分區資料表會劃分為多個區段 (稱為分區)，管理和查詢資料時更加方便。將大型資料表分成較小的分區，可以提高查詢效能，並且可以透過減少查詢讀取的位元組數來控制費用。您可以指定用來區隔資料表的分區資料欄，將資料表分區。

當查詢作業對分區資料欄套用符合的篩選器時，BigQuery 就能掃描符合篩選條件的分區，並略過其餘的分區。這項程序稱為「修剪」。

在分區資料表中，資料會儲存在實體區塊中，每個區塊都包含一個資料分區。在每個修改資料表的作業中，分區資料表都會保留其排序屬性的各種中繼資料。BigQuery 可根據中繼資料，在執行查詢前更準確地估算查詢費用。

使用分割的時機

在下列情況下，建議您將資料表分區：

  • 您想只掃描部分資料表，藉此提升查詢效能。
  • 您的資料表作業超出標準資料表配額，您可以將資料表作業範圍限定為特定分區資料欄值，藉此提高分區資料表配額。
  • 您想在執行查詢前先瞭解查詢費用。BigQuery 會在分區資料表執行查詢前，提供查詢費用估算值。如要估算查詢費用，請縮減分區資料表，然後發出查詢模擬測試，估算查詢費用。
  • 您需要下列任一分區層級管理功能：

在下列情況下，請考慮叢集資料表，而非分區資料表：

  • 您需要的精細程度比分區允許的更高。
  • 您的查詢通常會對多個資料欄使用篩選或匯總。
  • 資料欄或資料欄群組中的值基數很大。
  • 您不需要在執行查詢前取得嚴格的費用估算。
  • 分區後，每個分區的資料量會很小 (大約小於 10 GB)。建立許多小型分區會增加資料表的中繼資料，並可能影響查詢資料表時的中繼資料存取時間。
  • 分區數量過多，超出分區資料表限制。
  • 您的 DML 作業經常修改資料表中的大部分分區 (例如每隔幾分鐘)。

在這種情況下，您可以根據使用者定義的排序屬性，對特定資料欄中的資料進行叢集處理，藉此加快查詢速度。

您也可以結合叢集和資料表分區，獲得更精細的排序結果。如要進一步瞭解這種做法，請參閱「合併叢集和分區資料表」。

分區類型

本節說明資料表的分區方式。

整數範圍分區

您可以根據特定INTEGER資料欄中的值範圍，將資料表分區。如要建立整數範圍分區資料表，請提供：

  • 分區資料欄。
  • 範圍分區的起始值 (含)。
  • 範圍分區的結束值 (不含)。
  • 分區內每個範圍的間隔。

舉例來說，假設您使用下列規格建立整數範圍分區：

引數 值
資料欄名稱 customer_id
開始 0
結束 100
interval 10

資料表會依 customer_id 資料欄分區為間隔為 10 的多個範圍。0 到 9 的值會放在一個分區中，10 到 19 的值放在下一個分區中，以此類推，直到 99 為止。超出這個範圍的值會放入名為 __UNPARTITIONED__ 的分區。凡是 customer_id 為 NULL 的資料列，都會進入名為 __NULL__ 的分割區。

如要瞭解整數範圍分區資料表，請參閱建立整數範圍分區資料表。

依時間單位資料欄分區

您可以在資料表的 DATE、TIMESTAMP 或 DATETIME 資料欄上對資料表分區。當您將資料寫入資料表時，BigQuery 會根據資料欄中的值，自動將資料放入正確的分區。

如果是 TIMESTAMP 和 DATETIME 資料欄，分區的精細程度可以是每小時、每天、每月或每年。對於 DATE 欄，分割區可具有每日、每月或每年精細程度。分區界線以世界標準時間 (UTC) 為準。

舉例來說，假設您依 DATETIME 資料欄對資料表進行分區，並採用每月分區。如果將下列值插入表格，系統會將資料列寫入下列分割區：

列值 分區 (每月)
DATETIME("2019-01-01") 201901
DATETIME("2019-01-15") 201901
DATETIME("2019-04-30") 201904

此外，系統還會建立兩個特別的分區：

  • __NULL__：包含分區資料欄中值為 NULL 的資料列。
  • __UNPARTITIONED__：包含分區資料欄值早於 1960-01-01 或晚於 2159-12-31 的資料列。

如要瞭解時間單位資料欄分區資料表，請參閱建立時間單位資料欄分區資料表。

依擷取時間分區

建立以擷取時間為依據的分區資料表時，BigQuery 會根據擷取資料的時間，自動將資料列指派至分區。你可以選擇以小時、天、月或年為單位，劃分資料分割。分區界線以世界標準時間為準。

如果使用更精細的時間間隔時，資料可能會達到每個資料表的分區數量上限，請改用較粗略的時間間隔。舉例來說，您可以改為按月分區，而非按日分區，藉此減少分區數量。您也可以叢集分割資料欄，進一步提升效能。

擷取時間分區資料表有名為 _PARTITIONTIME 的虛擬資料欄。這個資料欄的值是每個資料列的擷取時間，並截斷至分割區界線 (例如每小時或每天)。舉例來說，假設您建立以小時為單位的擷取時間分區資料表，並在下列時間傳送資料：

擷取時間 _PARTITIONTIME 分區 (每小時)
2021-05-07 17:22:00 2021-05-07 17:00:00 2021050717
2021-05-07 17:40:00 2021-05-07 17:00:00 2021050717
2021-05-07 18:31:00 2021-05-07 18:00:00 2021050718

由於本範例中的資料表使用每小時分區，因此 _PARTITIONTIME 的值會截斷至小時界限。BigQuery 會使用這個值判斷資料的正確分區。

您也可以將資料寫入特定分區。舉例來說，您可能想要載入歷來資料或調整時區。您可以使用 0001-01-01 至 9999-12-31 之間的任何有效日期。不過，DML 陳述式無法參照 1970-01-01 之前或 2159-12-31 之後的日期。詳情請參閱「將資料寫入特定分區」一文。

您也可以使用 _PARTITIONDATE，而非 _PARTITIONTIME。_PARTITIONDATE 虛擬資料欄包含的世界標準時間日期與 _PARTITIONTIME 虛擬資料欄中的值相對應。

選取每日、每小時、每月或每年分割

依時間單位欄或擷取時間分割資料表時，您可以選擇分區的精細程度為每日、每小時、每月或每年。

  • 每日分區是預設分區類型。如果資料分布在很廣的日期範圍內，或是資料會隨著時間持續新增，則每日分區是不錯的選擇。

  • 如果資料表含有大量資料，且時間範圍很短 (通常是不到六個月的時間戳記值)，請選擇每小時分割。如果選擇每小時分區，請確保分區數量在分區限制內。

  • 如果資料表每天的資料量相對較少，但涵蓋的日期範圍很廣，請選擇每月或每年分區。如果工作流程需要頻繁更新或新增涵蓋廣泛日期範圍的資料列 (例如超過 500 個日期)，也建議使用這個選項。在這些情況下，請使用每月或每年分區，並在分區資料欄上進行分群，以獲得最佳效能。詳情請參閱本文的合併叢集和分區資料表一節。

合併叢集資料表和分區資料表

您可以結合資料表分區與資料表叢集處理，獲得精細的排序結果，進一步最佳化查詢。

叢集資料表包含叢集資料欄，可根據使用者定義的排序屬性排序資料。這些叢集資料欄中的資料會排序到儲存區塊中，這些區塊的大小會根據資料表大小調整。執行依叢集資料欄篩選的查詢時，BigQuery 只會根據叢集資料欄掃描相關區塊，而不是整個資料表或資料表分區。如果同時使用資料表分區和叢集處理，您會先將資料表資料區隔為分區，然後依據叢集資料欄，將每個分區中的資料叢集處理。

建立經過叢集和分區處理的資料表時，您可以獲得更精細的排序結果，如下圖所示：

比較未叢集或分區的資料表，以及叢集和分區的資料表。

分區與資料分割的比較

資料表分片是指使用 [PREFIX]_YYYYMMDD 等命名前置字元，將資料儲存在多個資料表中的做法。

建議您使用分區，而非資料表分片，因為分區資料表的執行效能較佳。使用資料分割資料表時，BigQuery 必須為每個資料表保留一份結構定義和中繼資料的複本。BigQuery 也可能需要驗證每個查詢資料表的權限。這個做法也會增加查詢的負擔，並影響查詢效能。

如果您先前建立的是日期資料分割資料表，可以將其轉換為擷取時間分區資料表。詳情請參閱將日期資料分割資料表轉換成擷取時間分區資料表。

分區修飾符

分區修飾符可讓您參照資料表中的分區。舉例來說，您可以使用這些函式將資料寫入特定分區。

分區修飾符的格式為 table_name$partition_id，其中 partition_id 區段的格式取決於分區類型：

分區類型 格式 範例
每小時 yyyymmddhh my_table$2021071205
每日 yyyymmdd my_table$20210712
每月 yyyymm my_table$202107
每年 yyyy my_table$2021
整數範圍 range_start my_table$40

瀏覽分區中的資料

如要瀏覽指定分區中的資料，請使用 bq head 指令和分區修飾符。

舉例來說，下列指令會列出 2018-02-24 分區中 my_dataset.my_table 前 10 列的所有欄位：

    bq head --max_rows=10 'my_dataset.my_table$20180224'

匯出資料表資料

從分區資料表匯出所有資料的處理程序與從非分區資料表匯出資料相同。詳情請參閱匯出資料表資料。

如要從個別分區匯出資料，請使用 bq extract 指令，並將分區修飾符附加至資料表名稱。例如，my_table$20160201。您也可以將分區名稱附加至資料表名稱，以從 __NULL__ 和 __UNPARTITIONED__ 分區匯出資料，例如 my_table$__NULL__ 或 my_table$__UNPARTITIONED__。

限制

分區資料表有下列限制：

  • 您無法使用舊版 SQL 查詢分區資料表或將查詢結果寫入分區資料表。

  • BigQuery 不支援依多個資料欄分區。您只能使用一個資料欄來分割資料表。

  • 您無法直接將現有的非分區資料表轉換為分區資料表。分區策略是在建立資料表時定義。請改用 CREATE TABLE 陳述式，查詢現有資料表中的資料，然後建立新的分區資料表。

  • 時間單位資料欄分區資料表有下列限制：

    • 分區資料欄必須是純量 DATE、TIMESTAMP 或 DATETIME 資料欄。資料欄模式可以是 REQUIRED 或 NULLABLE，但不能是 REPEATED (以陣列為基礎)。
    • 此外，分區資料欄必須是頂層欄位。您無法將 RECORD (STRUCT) 中的分葉欄位當成分區欄使用。

    如要瞭解時間單位資料欄分區資料表，請參閱建立時間單位資料欄分區資料表。

  • 整數範圍分區資料表有下列限制：

    • 分區資料欄必須是 INTEGER 資料欄。資料欄模式可能是 REQUIRED 或 NULLABLE，但不能是 REPEATED (以陣列為基礎)。
    • 此外，分區資料欄必須是頂層欄位。您無法將 RECORD (STRUCT) 中的分葉欄位當成分區欄使用。

    如要瞭解整數範圍分區資料表，請參閱建立整數範圍分區資料表。

配額與限制

BigQuery 中的分區資料表已定義了限制。

配額和限制也適用於您可以針對分區資料表執行的各類型工作，包括：

如要深入瞭解所有配額和限制，請參閱配額與限制一文。

資料表價格

在 BigQuery 中建立和使用分區資料表時，向您收取的費用是以在分區中儲存的資料量以及針對資料執行的查詢量為計算依據：

許多分區資料表作業都是免費的，包括將資料載入分區、複製分區，以及從分區匯出資料。雖然這些作業都是免費的，但仍受限於 BigQuery 的配額和限制。如需所有免費作業的相關資訊，請參閱定價頁面上的免費作業項目一節。

如要瞭解在 BigQuery 中控管費用的最佳做法，請參閱「在 BigQuery 中控管費用」

表格安全性

分區資料表的存取權控管方式與標準資料表相同。詳情請參閱資料表存取權控管簡介。

後續步驟