管理分區資料表中的資料

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

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

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

將資料載入分區資料表

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

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

瀏覽資料表中的資料

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

  • 使用 GCP 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. 在 GCP Console 的導覽窗格中,按一下資料集以列出其中的資料表與資料檢視。

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

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

    資料表詳細資料

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

    列數

  5. 按一下 [Preview] (預覽) 分頁標籤。畫面上會顯示一組資料範例。請注意,您無法使用 GCP 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 指令不會建立查詢工作,所以 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.mytablefield1field2。這個指令使用 - -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:讀取的起始列,索引從零開始。

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

查詢分區資料表中的資料

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

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

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

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

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

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

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

所需權限

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

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

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

以下是具有 bigquery.tables.updateDatabigquery.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 工作,將資料附加到分區或覆寫分區中的資料。GCP 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

使用查詢工作

您可以使用 bq query 指令,或呼叫 jobs.insert 方法並設定 query 工作,將資料附加到分區或覆寫分區中的資料。GCP 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 來更新或刪除資料,您必須刪除分區資料表,再使用新資料來重建該資料表。另外,您也可以撰寫查詢來修改資料,並將查詢結果寫入新的分區資料表。

複製分區資料表中的資料

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

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

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

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

後續步驟

如要進一步瞭解分區資料表的使用方式,請參閱:

本頁內容對您是否有任何幫助?請提供意見:

傳送您對下列選項的寶貴意見...

這個網頁