管理分區資料表中的資料

本文件說明如何管理 BigQuery 中的分區資料表資料。您可以透過以下方式使用分區資料表中的資料：

• 將資料載入分區資料表
• 瀏覽 (或預覽) 分區資料表資料
• 查詢分區資料表資料
• 對分區資料表資料執行附加或覆寫操作
• 使用資料操縱語言陳述式來修改分區資料表資料
• 複製分區資料表資料
• 以串流方式將資料傳入分區資料表
• 匯出分區資料表資料

如需管理資料表結構定義的相關資訊，請參閱修改資料表結構定義一文。

將資料載入分區資料表

您可以在載入資料時建立分區資料表，或建立空的分區資料表並在稍後載入資料。將資料載入分區資料表時，如果是支援的資料格式，您可以使用結構定義自動偵測功能讓系統自動處理，也可以自行指定結構定義。

附註：如果您在 BigQuery 傳統網頁版 UI 中勾選 [Automatically detect] (自動偵測) 選項，系統會停用 [Partitioning Type] (分區類型) 選項。如要在將資料載入分區資料表時使用結構定義自動偵測功能，請使用 Cloud Console、CLI 或 API。

如需載入資料的詳細資訊，請參閱說明文件以瞭解來源資料的格式與位置：

• 如要深入瞭解如何將 Cloud Storage 的資料載入分區資料表，請參閱：

  • 載入 Avro 資料
  • 載入 CSV 資料
  • 載入 JSON 資料
  • 載入 ORC 資料
  • 載入 Parquet 資料
  • 載入 Datastore 匯出內容中的資料
  • 載入 Cloud Firestore 匯出內容中的資料

• 如需將資料從本機來源載入分區資料表的相關資訊，請參閱將資料從本機資料來源載入至 BigQuery。

瀏覽資料表中的資料

您可透過下列方式瀏覽分區資料表中的資料：

• 使用 Cloud Console 或 BigQuery 傳統網頁版 UI
• 使用指令列工具的 bq head 指令
• 呼叫 tabledata.list API 方法
• 使用用戶端程式庫

所需權限

您至少必須擁有 bigquery.tables.getData 權限，才能瀏覽資料表和分區的資料。以下是具有 bigquery.tables.getData 權限的預先定義 Cloud IAM 角色：

• bigquery.dataViewer
• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

此外，當具備 bigquery.datasets.create 權限的使用者建立資料集時，會獲得該資料集的 bigquery.dataOwner 存取權。bigquery.dataOwner 存取權可讓使用者瀏覽資料集內的資料表和分區資料。

如要進一步瞭解 BigQuery 中的 Cloud IAM 角色和權限，請參閱預先定義的角色和權限。

瀏覽分區資料表中的資料

如何瀏覽分區資料表資料：

主控台

1. 在 Cloud Console 的導覽窗格中，按一下資料集以列出其中的資料表與檢視表。

2. 按一下清單中的分區資料表。

3. 按一下 [Details] (詳細資料) 分頁標籤。

   

4. 記下「Number of Rows」(列數) 中的值。使用 CLI 或 API 時，您可能需要這個值來控管結果的起點。

   

5. 按一下 [Preview] (預覽) 分頁標籤。畫面上會顯示一組資料範例。請注意，您無法使用 Cloud Console 預覽個別分區。

傳統版 UI

1. 在 BigQuery 網頁版 UI 的導覽窗格中，按一下資料集左側的藍色箭號以展開選單，或按兩下資料集名稱。畫面上會顯示資料集中的資料表和檢視表。

2. 按一下清單中的分區資料表。

3. 按一下 [Details] (詳細資料) 並記下「Number of Rows」(列數) 中的值。使用 CLI 或 API 時，您可能需要這個值控管結果的起點。

4. 按一下 [Preview] (預覽)。畫面上會顯示一組資料範例。請注意，您無法使用 BigQuery 網頁版 UI 預覽個別分區。

   

CLI

發出 bq head 指令並搭配使用 --max_rows 旗標，列出特定數量的資料表列所包含的所有欄位。如未指定 --max_rows，則預設值為 100。使用分區修飾符指定要瀏覽的分區，例如，$20180224。

因為 bq head 指令不會建立查詢工作，所以 指令不會出現在您的查詢記錄中，也不會產生費用。

如要瀏覽資料表中的欄位子集 (包括巢狀和重複的欄位)，請使用 --selected_fields 旗標並以逗號分隔的清單形式輸入欄位。

如要指定顯示資料表中的資料前要略過的列數，請使用 --start_row=integer 旗標 (或 -s 捷徑)。預設值為 0。您可以使用 bq show 指令擷取資料表資訊，以擷取資料表中的列數。

如果您要瀏覽的資料表位於非預設專案中，請使用下列格式將專案 ID 新增至指令：project_id:dataset.table。

bq head \
--max_rows integer1 \
--start_row integer2 \
--selected_fields "fields" \
project_id:dataset.table$partition

其中：

• 「integer1」是要顯示的列數。
• 「integer2」是顯示資料前略過的列數。
• 「fields」是以逗號分隔的欄位清單。
• 「project_id」是您的專案 ID。
• 「dataset」是包含此資料表的資料集名稱。
• 「table」是要瀏覽的資料表名稱。
• 「$partition」是分區修飾符。

範例：

輸入下列指令，列出 "2018-02-24" 分區中 mydataset.mytable 內前 10 列的所有欄位。mydataset 是在您的預設專案中。

bq head --max_rows=10 mydataset.mytable$20180224

輸入下列指令，列出 "2016-09-01" 分區中 mydataset.mytable 內前 100 列的所有欄位。mydataset 是在 myotherproject 中，而不在您的預設專案中。

bq head --format=prettyjson 'mydataset.mycolumntable2$20160901'

輸入下列指令，只顯示 "2016-09-01" 分區中 mydataset.mytable 的 field1 和 field2。這個指令使用 - -start_row 旗標直接跳到第 100 列。mydataset.mytable 在您的預設專案中。

bq head \
--format=prettyjson \
--start_row 100 \
--selected_fields "state_number" \
'mydataset.mycolumntable2$20160901'

API

呼叫 tabledata.list 即可瀏覽整份資料表的資料。在 tableId 參數中指定資料表名稱和分區修飾符。

設定以下選用參數控管輸出：

• maxResults：傳回的結果數上限。
• selectedFields：傳回以逗號分隔的欄位清單。如果未指定，則會傳回所有欄位。
• startIndex：讀取的起始列，索引從零開始。

附註：如果您要求的 startIndex 超過最後一列，則這個方法雖然仍會成功傳回結果，但不會包含 rows 屬性。您可以呼叫 tables.get 方法並檢查 numRows 屬性，藉此查看資料表的列數。

傳回的值會以 JSON 物件包裝，而您必須剖析這個物件，如 tabledata.list 參考說明文件所述。

查詢分區資料表中的資料

將資料載入 BigQuery 後，您就可以查詢表格中的資料。BigQuery 支援以下兩種類型的查詢：

• 互動式查詢
• 批次查詢

根據預設，BigQuery 會執行互動式查詢，這表示查詢會盡快執行。

BigQuery 同時提供批次查詢。BigQuery 會代表您佇列每一個批次查詢，一等到資源可供使用就會開始查詢，通常幾分鐘內就會查詢。

您可以使用下列方式執行互動式和批次查詢：

• Cloud Console 中的查詢編輯器
• BigQuery 網頁版 UI 中的「Compose Query」(撰寫查詢) 選項
• BigQuery 指令列工具的 bq query 指令
• 可透過程式輔助方式呼叫 jobs.query 或查詢類型 jobs.insert 方法的 BigQuery REST API
• BigQuery 用戶端程式庫

詳情請參閱查詢分區資料表一文。

對分區資料表中的資料執行附加或覆寫操作

您可以使用載入或查詢作業來覆寫分區資料表中的資料。您可以執行載入附加作業或附加查詢結果，將其他資料附加到現有的分區資料表。

所需權限

如要覆寫現有的分區或將資料附加到現有分區，您至少要具備下列權限：

• bigquery.tables.create
• bigquery.tables.updateData
• bigquery.jobs.create

您可能還需要其他權限 (例如 bigquery.tables.getData)，才能存取您用來附加或覆寫的資料。

以下是包含 bigquery.tables.updateData 和 bigquery.tables.create 權限的預先定義 Cloud IAM 角色：

• bigquery.dataEditor
• bigquery.dataOwner
• bigquery.admin

以下是包含 bigquery.jobs.create 權限的預先定義 Cloud IAM 角色：

• bigquery.user
• bigquery.jobUser
• bigquery.admin

此外，當具備 bigquery.datasets.create 權限的使用者建立資料集時，會獲得該資料集的 bigquery.dataOwner 存取權。bigquery.dataOwner 存取權可讓使用者將資料附加到資料集內的資料表和分區，或覆寫其中的資料。

如要進一步瞭解 BigQuery 中的 Cloud IAM 角色和權限，請參閱預先定義的角色和權限。

使用載入工作

您可以使用 bq load 指令，或呼叫 jobs.insert 方法並設定 load 工作，將資料附加到分區或覆寫分區中的資料。Cloud Console 和 BigQuery 傳統網頁版 UI 不支援在載入工作中附加資料到分區或覆寫分區資料。

使用載入工作在特定分區中附加或覆寫資料時，請注意下列事項：

• 從 Cloud Storage 載入資料時，值區的位置必須與 BigQuery 資料集相同。
• 您要載入的資料必須符合資料表的分區配置。就寫入分區的資料列而言，所有列內含的值都必須落在分區日期內。
• 因為分區資料表中的分區共享表格結構定義，所以取代分區中的資料並不會取代資料表的結構定義，反而是新資料的結構定義必須與資料表的結構定義相容。如需在載入工作中更新資料表結構定義的相關資訊，請參閱管理資料表結構定義。
• 您將資料附加到擷取時間分區資料表時，如果沒有指定分區修飾符，系統會使用當下的分區。

如要使用載入工作來附加或覆寫分區資料表的資料，請指定目標資料表和分區修飾符，並將寫入配置旗標設為下列一項：

CLI 選項	API 選項	說明
--noreplace	WRITE_APPEND	將資料附加到現有的分區。如果沒有指定任何寫入配置選項，則預設動作是將資料附加到分區。
--replace	WRITE_TRUNCATE	覆寫 (重述) 分區。

分區修飾符代表特定日期並採用以下格式：

$YYYYMMDD

例如，以下指令會取代名稱為 mydataset.table1 的分區資料表中日期為 2016 年 1 月 1 日 (20160101) 的整個分區中的資料。系統會從 Cloud Storage 值區載入 JSON 資料。

bq load \
--replace \
--source_format=NEWLINE_DELIMITED_JSON \
'mydataset.table1$20160101' \
gs://mybucket/myfile.json

附註：分區修飾符的分隔符 ($) 是 Unix Shell 中的一種特殊變數。使用指令列工具時，您可能需要逸出修飾符。下列範例示範逸出分區修飾符：mydataset.table\$20160519、'mydataset.table$20160519'。

使用查詢工作

您可以使用 bq query 指令，或呼叫 jobs.insert 方法並設定 query 工作，將資料附加到分區或覆寫分區中的資料。Cloud Console 和 BigQuery 傳統網頁版 UI 不支援在查詢工作中附加資料到分區或覆寫分區資料。

使用查詢工作來附加或覆寫分區資料時，請注意下列事項：

• 您要查詢的資料表必須與您要執行附加或覆寫操作的資料表位於相同的位置。
• 在擷取時間分區資料表中對分區執行附加或覆寫操作時，您可以使用舊版 SQL 或標準 SQL 語法。
• 在分區資料表中對分區執行附加或覆寫操作時，您的查詢必須使用標準 SQL 語法。目前，系統不支援使用舊版 SQL 來查詢分區資料表或將查詢結果寫入分區資料表。
• 將查詢結果寫入分區時，要寫入分區的資料必須符合資料表的分區配置。就寫入分區的資料列而言，所有列內含的值都必須落在分區日期內。
• 您將資料附加到擷取時間分區資料表時，如果沒有指定分區修飾符，系統會使用當下的分區。

如要使用查詢結果來附加或覆寫分區資料，請指定目標資料表和分區修飾符，並將寫入配置設為下列一項：

CLI 選項	API 選項	說明
--append_table	WRITE_APPEND	將查詢結果附加到現有的分區。
--replace	WRITE_TRUNCATE	使用查詢結果覆寫 (重述) 分區。

例如，以下指令會使用查詢結果重編 table1 的 2016 年 3 月 1 日 (20160301) 分區資料。

bq query \
--use_legacy_sql=false \
--replace \
--destination_table 'mydataset.table1$20160301' \
'SELECT
  column1,
  column2
FROM
  mydataset.mytable'

如果目的地資料表存在且未分區，則系統會傳回下列錯誤：BigQuery error in query operation: Error processing job 'project_id job_id' Incompatible table partitioning specification. Expects partitioning specification interval (type:day), but input partitioning specification is none`.

如需使用查詢結果來附加或覆寫資料的詳細資訊，請參閱寫入查詢結果一文。

使用 DML 陳述式修改分區資料表資料

您可以使用標準 SQL 方言的 DML 陳述式來修改分區資料表中的資料。DML 陳述式可讓您針對大量的列執行更新、插入和刪除作業。如需使用 DML 搭配分區資料表的範例，請參閱使用 DML 陳述式更新分區資料表資料。

舊版 SQL 方言不支援 DML 陳述式。如要使用舊版 SQL 來更新或刪除資料，您必須刪除分區資料表，再使用新資料來重建該資料表。另外，您也可以撰寫查詢來修改資料，並將查詢結果寫入新的分區資料表。

複製分區資料表中的資料

您可以透過以下方式複製分區資料表：

• 使用 Cloud Console 或 BigQuery 傳統網頁版 UI
• 使用指令列工具的 bq cp 指令
• 呼叫 API 方法 jobs.insert 並設定複製工作
• 使用用戶端程式庫

如需複製資料表的更多相關資訊，請參閱複製資料表。

您可以使用指令列工具的 bq cp 指令，或呼叫 jobs.insert API 方法並設定 copy 工作，以複製一或多個分區。Cloud Console 或 BigQuery 傳統網頁版 UI 目前不支援複製分區。

如要深入瞭解複製分區，請參閱複製分區一節。

以串流方式將資料傳入分區資料表

如要以串流方式將資料傳入特定分區，請在指定串流資料目標資料表的 tableId 時，使用分區修飾符。例如，以下指令會將單一列以串流方式傳入 mydataset.mytable 分區資料表中日期為 2017 年 1 月 1 日 ($20170101) 的分區：

echo '{"a":1, "b":2}' | bq insert 'mydataset.mytable$20170101'

此指令用來示範如何使用分區修飾符。bq insert 指令僅用於測試。如要以串流方式將資料傳入 BigQuery，請使用 API 的 tabledata.insertAll 方法。如需將資料以串流方式傳入分區的相關資訊，請參閱以串流方式將資料傳入分區資料表一節。

使用分區修飾符以串流方式傳送資料時，您可以將資料串流至 (相對於目前日期，以目前的世界標準時間為準) 過去 30 天內和未來 5 天之間的分區。如要寫入不在這些允許日期範圍內的分區，您可以使用載入或查詢工作。

傳送串流資料時，如果您指定時間分區資料表做為目的地資料表，則每個分區都會有一個串流緩衝區。如將 writeDisposition 屬性設為 WRITE_TRUNCATE，當您在執行會覆寫分區的載入、查詢或複製工作時，系統會保留串流緩衝區。如果您要移除串流緩衝區，請對分區呼叫 tables.get，以確認串流緩衝區是空的。

匯出資料表資料

從分區資料表匯出所有資料的處理程序與從非分區資料表匯出資料相同。詳情請參閱匯出資料表中的資料。如要從個別分區匯出資料，請將分區修飾符 $date 附加至資料表名稱，例如：mytable$20160201。

您也可以將分區名稱附加至資料表名稱，以從 __NULL__ 和 __UNPARTITIONED__ 分區匯出資料，例如 mytable$__NULL__ 或 mytable$__UNPARTITIONED__。

您可以將分區資料表資料匯出為 CSV、JSON 或 Avro 格式。目前，您必須將資料匯入 Cloud Storage 值區。系統不支援將資料匯出至您的本機電腦，但您可使用 Cloud Console 或 BigQuery 傳統網頁版 UI 下載和儲存查詢結果。

後續步驟

如要進一步瞭解分區資料表的使用，請參閱：

• 建立及使用擷取時間分區表格
• 建立及使用分區資料表
• 管理分區資料表
• 查詢分區資料表