管理分區資料表資料

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

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

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

將資料載入分區資料表

您可以在載入資料時建立分區資料表，或建立空的分區資料表並在稍後載入資料。將資料載入分區資料表時，您可以使用結構定義自動偵測功能取得支援的資料格式，也可以指定結構定義。

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

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

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

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

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

瀏覽資料表資料

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

• 使用 GCP 主控台或 BigQuery 傳統網頁版 UI
• 使用指令列工具的 bq head 指令
• 呼叫 API 方法 tabledata.list

所需權限

如要在資料集層級瀏覽資料表資料，您需要這個分區資料表所屬資料集的 READER 存取權。

如不使用資料集層級權限，您可以使用具備 bigquery.tables.getData 權限的身分與存取權管理 (IAM) 角色。所有預先定義的專案層級 IAM 角色均擁有 bigquery.tables.getData 權限，但 bigquery.user、bigquery.jobUser 和 bigquery.metadataViewer 除外。

此外，因為 bigquery.user 角色擁有 bigquery.datasets.create 權限，所以獲指派 bigquery.user 角色的使用者能讀取自己建立的任一資料集中任何資料表的資料。接受 bigquery.user 角色指派的使用者建立資料集時，如果沒有設定其他擁有者，則系統會將資料集的 OWNER 存取權授予該使用者。資料集的 OWNER 存取權可讓使用者完全控管該資料集和其中的所有資料表。

如要深入瞭解 BigQuery 中的 IAM 角色和權限，請參閱存取權控管一文。如要深入瞭解資料集層級角色，請參閱資料集的原始角色。

瀏覽分區資料表資料

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

主控台

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

2. 按一下清單中的分區資料表。您不能使用 BigQuery 網頁版 UI 來預覽個別分區。

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

   

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

   

5. 按一下 [Preview] (預覽) 分頁標籤。畫面上會顯示一組資料範例。

傳統版 UI

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

2. 按一下清單中的分區資料表。您不能使用 BigQuery 網頁版 UI 來預覽個別分區。

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

4. 按一下 [Preview] (預覽)。畫面上會顯示一組資料範例。

   

指令列

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

因為 bq head 指令不會建立查詢工作，所以 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 會代表您佇列每一個批次查詢，一等到資源可供使用就會開始查詢，通常幾分鐘內就會查詢。

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

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

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

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

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

所需權限

如要覆寫現有分區或將資料附加到現有分區，您必須要有資料集層級的 WRITER 存取權，或必須獲指派具備 bigquery.tables.updateData 權限的專案層級 IAM 角色。以下是具備 bigquery.tables.updateData 權限的預先定義專案層級 IAM 角色：

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

此外，因為 bigquery.user 角色擁有 bigquery.datasets.create 權限，所以獲指派 bigquery.user 角色的使用者能針對自己在資料集中建立的任何資料表分區執行覆寫或附加操作。當接受 bigquery.user 角色指派的使用者建立資料集時，如果沒有指定任何其他擁有者，系統就會將資料集的 OWNER 存取權授予該使用者。資料集的 OWNER 存取權可以讓使用者完全控管該資料集，以及其中的所有資料表和所有資料表分區。

您還須取得 bigquery.jobs.create 權限才能執行載入工作或查詢工作。以下是具備 bigquery.jobs.create 權限的預先定義專案層級 IAM 角色：

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

如要深入瞭解 BigQuery 中的 IAM 角色和權限，請參閱存取權控管一文。如要深入瞭解資料集層級角色，請參閱資料集的原始角色。

使用載入工作

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

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

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

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

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

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

$YYYYMMDD

例如，以下指令會取代名稱為 mydataset.table1 的分區資料表中日期為 2016 年 1 月 1 日 (20160101) 的整個分區中的資料。JSON 資料會從 Cloud Storage 值區載入。Cloud Storage 值區和 BigQuery 資料集均建立於 asia-northeast1 地區。

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

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

如需使用載入工作附加或覆寫資料的相關資訊，請參閱說明文件中的來源資料格式。

• 將 Avro 資料附加或覆寫至資料表
• 將 CSV 資料附加或覆寫至資料表
• 將 JSON 資料附加或覆寫至資料表
• 將 Cloud Datastore 資料附加或覆寫至資料表
• 將本機檔案附加或覆寫至資料表

使用查詢工作

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

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

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

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

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

例如，以下指令會使用查詢結果重述 table1 的 2016 年 3 月 1 日 (20160301) 分區資料。目標資料表和查詢資料表都位於 US 多區域位置：

bq --location=US 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][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 來更新或刪除資料，您必須刪除分區資料表，再使用新資料來重建該資料表。另外，您也可以撰寫查詢來修改資料，並將查詢結果寫入新的分區資料表。

複製分區資料表資料

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

• 使用 GCP 主控台或 BigQuery 傳統網頁版 UI
• 使用指令列工具的 bq cp 指令
• 呼叫 jobs.insert API 方法並設定複製工作

如要深入瞭解複製資料表的相關資訊，請參閱複製資料表一節。

如要複製一或多個分區，您可以使用指令列工具的 bq cp 指令，或呼叫 jobs.insert API 方法並設定複製工作。GCP 主控台或 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 值區。系統不支援匯出至您的本機電腦，但您可使用 GCP 主控台或 BigQuery 傳統網頁版 UI 下載和儲存查詢結果。

後續步驟

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

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