建立及使用擷取時間分區資料表

本文說明如何在 BigQuery 中建立和使用擷取時間分區資料表。如要深入瞭解分區資料表,請參閱建立及使用分區資料表一文。

建立擷取時間分區資料表後,您可以執行下列作業:

  • 控管資料表資料的存取權
  • 取得分區資料表的相關資訊
  • 列出資料集中的分區資料表
  • 使用中繼資料表取得分區資料表的中繼資料

如要深入瞭解如何管理分區資料表,包括更新分區資料表屬性、複製分區資料表和刪除分區資料表,請參閱管理分區資料表一文。

建立擷取時間分區資料表

您可以使用下列方式,在 BigQuery 中建立擷取時間分區表格:

  • 使用 DDL CREATE TABLE 陳述式搭配 partition_expression
  • 手動使用 GCP 主控台、傳統 BigQuery 網頁版 UI 或指令列工具的 bq mk 指令
  • 呼叫 tables.insert API 方法,透過程式建立資料表
  • 從查詢結果建立
  • 載入資料時建立
  • 將日期資料分割資料表轉換成分區資料表

資料表命名

在 BigQuery 中建立資料表時,每個資料集中的資料表名稱不得重複。資料表名稱可以:

  • 最多包含 1,024 個字元
  • 包含字母 (大寫或小寫)、數字與底線

所需權限

如要建立資料表,您至少要具備下列權限:

  • 建立資料表的 bigquery.tables.create 權限
  • 使用載入工作、查詢工作或複製工作將資料寫入資料表的 bigquery.tables.updateData 權限
  • 執行查詢工作、載入工作或複製工作以將資料寫入資料表的 bigquery.jobs.create 權限

您可能還需要其他權限 (例如 bigquery.tables.getData),才能存取您要寫入資料表的資料。

以下是具有 bigquery.tables.createbigquery.tables.updateData 權限的預先定義 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 角色和權限,請參閱存取權控管

使用結構定義建立空的擷取時間分區資料表

使用結構定義建立空的擷取時間分區資料表時,您可以執行下列作業:

  • 使用 GCP 主控台或傳統 BigQuery 網頁版 UI 輸入結構定義
  • 使用指令列工具以內嵌方式提供結構定義
  • 使用指令列工具提交 JSON 結構定義檔案
  • 呼叫 API 的 tables.insert 方法時,在 table resource 中提供結構定義

如要深入瞭解如何指定資料表結構定義,請參閱指定結構定義一文。

建立時間分區資料表後,您可以執行下列作業:

  • 將資料載入資料表
  • 將查詢結果寫入資料表
  • 將資料複製到資料表

如何使用結構定義建立空的時間分區資料表:

主控台

  1. 從導覽面板的「Resources」(資源) 區段選取資料集。 按一下視窗右側的 [Create table] (建立資料表)建立資料表
  2. 在「Create table」(建立資料表) 頁面的「Source」(來源) 區段中,選取 [Create empty table] (建立空白資料表)建立資料表來源
  3. 在「Create table」(建立資料表) 頁面的「Destination」(目的地) 區段中:

    • 在「Dataset name」(資料集名稱) 部分選擇適當的資料集。 選取資料集

    • 在「Table name」(資料表名稱) 欄位中,輸入您在 BigQuery 中建立資料表時使用的資料表名稱。

    • 確認「Table type」(資料表類型) 設為「Native table」(原生資料表)

  4. 在「Schema」(結構定義) 區段中,輸入結構定義

    • 透過以下方式,手動輸入結構定義資訊:

      • 啟用 [Edit as text] (以文字形式編輯),然後以 JSON 陣列的形式輸入資料表結構定義。

      • 使用 [Add Field] (新增欄位) 手動輸入結構定義

  5. 在「Partition and cluster settings」(分區與叢集設定) 區段中:

    • 在「Partitioning」(分區) 部分,按一下 [No Partitioning] (無分區),然後選取 [Partition by ingestion time] (依擷取時間分區)依擷取分區
  6. (選用) 按一下 [Advanced options] (進階選項),在「Encryption」(加密) 部分,則請按一下 [Customer-managed key] (客戶管理的金鑰) ,即可使用 Cloud Key Management Service 金鑰。如果您保留 Google 代管的金鑰設定,BigQuery 會加密靜態資料

  7. 按一下 [Create table] (建立資料表)。

建立資料表後,您可以更新分區資料表的資料表到期時間說明標籤,但在使用 BigQuery 網頁版 UI 建立資料表後,即無法新增分區有效期限。

傳統版 UI

  1. 在導覽窗格中,按一下資料集名稱旁的向下箭號圖示 向下箭號圖示,然後按一下 [Create new table] (建立新資料表)

  2. 在「Create Table」(建立資料表) 頁面的「Source Data」(來源資料) 區段中,按一下 [Create empty table] (建立空白資料表)

  3. 在「Create Table」(建立資料表) 頁面的「Destination Table」(目標資料表) 區段中:

    • 在「Table name」(資料表名稱) 部分選擇適當的資料集,然後在資料表名稱欄位中,輸入您要建立的資料表名稱。
    • 確認「Table type」(資料表類型) 設為「Native table」(原生資料表)
  4. 在「Schema」(結構定義) 區段中,手動輸入結構定義

    • 您可以使用下列任一種方式,手動輸入結構定義資訊:

      • 按一下 [Edit as text] (以文字形式編輯),然後以 JSON 陣列的形式輸入資料表結構定義:

        以 JSON 陣列新增結構定義

      • 使用 [Add Field] (新增欄位) 來輸入結構定義:

        使用新增欄位新增結構定義

  5. 在「Options」(選項) 區段中:

  6. 按一下 [Create Table] (建立資料表)

建立資料表後,您可以更新分區資料表的資料表到期時間說明標籤,但您無法在使用 BigQuery 網頁版 UI 建立資料表後,新增分區到期時間。

CLI

使用 mk 指令搭配 --table 標記 (或 -t 捷徑),以及 --time_partitioning_type=DAY 標記。目前,--time_partitioning_type 唯一支援的值是 DAY。您可以提供資料表結構定義內嵌或使用 JSON 結構定義檔。選用參數包括 --expiration--description--time_partitioning_expiration--destination_kms_key--label。如果您要在預設專案以外的專案中建立資料表,請使用下列格式將專案 ID 新增至資料集:project_id:dataset

本文不示範 --destination_kms_key。如要進一步瞭解如何使用此標記,請參閱使用 Cloud KMS 金鑰保護資料

輸入下列指令,以建立含有結構定義的空擷取時間分區資料表:

bq mk --table \
--expiration [INTEGER1] \
--time_partitioning_type=DAY \
--time_partitioning_expiration [INTEGER2] \
--description "[DESCRIPTION]" \
-label [KEY:VALUE, KEY:VALUE] \
[PROJECT_ID]:[DATASET].[TABLE] \
[SCHEMA]

其中:

  • [INTEGER1] 是資料表的預設生命週期 (以秒為單位)。最小值是 3600 秒 (1 小時)。到期時間為目前世界標準時間加整數值。如果您在建立時間分區資料表時設定了資料表到期時間,系統會忽略資料集的預設資料表到期時間設定。只要設定這個值,系統就會在指定時間到期後刪除資料表和所有分區。
  • [INTEGER2] 是資料表分區的預設生命週期 (以秒為單位)。這個值沒有下限。有效期限的時間值為分區日期加整數值。分區到期時間與資料表的到期時間無關,並不會覆寫資料表到期時間。如果您設定的分區到期時間比資料表到期時間長,則以資料表到期時間優先。
  • [DESCRIPTION] 是置於括號中的資料表說明。
  • [KEY:VALUE] 是代表標籤的鍵/值組合。您可以用逗號分隔的清單輸入多個標籤。
  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是專案中的資料集。
  • [TABLE] 是您要建立的分區資料表的名稱。
  • [SCHEMA] 是格式為 [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] 的內嵌結構定義,或本機上 JSON 結構定義檔的路徑。

在指令列中指定結構定義時,不能納入 RECORD (STRUCT) 類型和資料欄說明,也不能指定資料欄的模式。所有模式均預設為 NULLABLE。如要包含說明、模式和 RECORD 類型,請改為提供 JSON 結構定義檔案

範例:

輸入下列指令,在預設專案的 mydataset 中建立名稱為 mytable 的分區資料表。分區有效期限設為 259,200 秒 (3 天)、資料表有效期限設為 2,592,000 秒 (1 個月 30 天)、說明設為 This is my time-partitioned table,而標籤則設為 organization:development。此指令使用 -t 捷徑而非 --table。結構定義為內嵌指定,如下所示: qtr:STRING,sales:FLOAT,year:STRING

bq mk -t \
--expiration 2592000 \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
--description "This is my time-partitioned table" \
--label organization:development \
mydataset.mytable \
qtr:STRING,sales:FLOAT,year:STRING

輸入下列指令,在 myotherproject 而非預設專案中建立名稱為 mytable 的時間分區資料表。分區有效期限設為 259,200 秒 (3 天)、說明設為 This is my time-partitioned table,而標籤則設為 organization:development。指令使用 -t 捷徑而非 --table。這個指令不會指定資料表到期時間。如果資料集有預設資料表有效期限,則會套用該有效期限。如果資料集沒有預設資料表有效期限,則資料表永不到期,但分區會在 3 天內到期。結構定義檔的路徑為 /tmp/myschema.json

bq mk -t \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
--description "This is my time-partitioned table" \
--label organization:development \
myotherproject:mydataset.mytable \
/tmp/myschema.json

建立資料表後,您可以更新擷取時間分區資料表的資料表到期時間分區到期時間說明標籤

API

呼叫 tables.insert 方法,並搭配指定 timePartitioning 屬性和 schema 屬性的已定義資料表資源

從查詢結果建立擷取時間分區資料表

如要從查詢結果建立分區資料表,請將結果寫入新的目標資料表。您可以透過查詢分區資料表或非分區資料表來建立新的分區資料表,但不能利用查詢結果將現有的資料表變更為分區資料表。

如要深入瞭解查詢時間分區資料表,請參閱查詢分區資料表一文。

主控台

使用主控台 BigQuery 網頁版 UI 查詢資料時,不能指定目的地資料表的分區選項。

傳統版 UI

使用傳統 BigQuery 網頁版 UI 查詢資料時,不能指定目的地資料表的分區選項。

CLI

輸入 bq query 指令,指定 --destination_table 標記以依據查詢結果建立永久性資料表,並指定 --time_partitioning_type=DAY 標記來建立擷取時間分區目標資料表。目前,--time_partitioning_type 唯一支援的值是 DAY

指定 use_legacy_sql=false 標記以使用標準 SQL 語法。如要將查詢結果寫入不在預設專案內的資料表,請使用下列格式將專案 ID 新增至資料集名稱:[PROJECT_ID]:[DATASET]

(選用) 提供 --location 標記,並將值設為您的位置

輸入下列指令,從查詢結果建立新的擷取時間分區目標資料表。

    bq --location=[LOCATION] query \
    --destination_table [PROJECT_ID]:[DATASET].[TABLE] \
    --time_partitioning_type=DAY \
    --use_legacy_sql=false \
    '[QUERY]'

其中:

  • [LOCATION] 是您的位置名稱,--location 是選用標記。例如,如果您在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是用來存放新時間分區表格的資料集名稱。
  • [TABLE] 是您要使用查詢結果建立的時間分區表格名稱。
  • [QUERY] 是採用標準 SQL 語法的查詢。

範例:

輸入下列指令,將查詢結果寫入 mydataset 中名稱為 mytable 的擷取時間分區目標資料表。該資料集位於預設專案中。查詢會從非分區資料表擷取資料 — USA Name Data 公開資料集。

bq query \
--destination_table mydataset.mytable \
--time_partitioning_type=DAY \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'

輸入下列指令,將查詢結果寫入 mydataset 中名稱為 mytable 的擷取時間分區目標資料表。資料集位於 myotherproject 而非預設專案中。查詢會從非分區資料表 (美國名稱資料公開資料集) 中擷取資料。

bq query \
--destination_table myotherproject:mydataset.mytable \
--time_partitioning_type=DAY \
--use_legacy_sql=false \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'

API

如要將查詢結果儲存到永久性擷取時間分區資料表,請呼叫 jobs.insert 方法,再設定 query 工作,然後納入 destinationTabletimePartitioning 屬性的值。

工作資源jobReference 區段中,於 location 屬性指定您的位置。

載入資料時建立擷取時間分區資料表

將資料載入新資料表時,您可以指定分區選項以建立擷取時間分區資料表。不一定要先建立空的分區資料表,然後再載入資料。您可以同時建立分區資料表和載入資料。

將資料載入 BigQuery 時,您可以提供資料表結構定義;若需支援的資料格式,可以使用結構定義自動偵測

分區裝飾器可讓您將資料載入特定分區。如要調整時區,請依據您偏好的時區,使用分區裝飾器將資料載入分區。例如,如果您位於太平洋標準時間 (PST) 時區,可利用相應的分區裝飾器將太平洋標準時間 2016 年 5 月 1 日產生的所有資料載入該日期的分區。

[TABLE_NAME]$20160501

如要深入瞭解如何載入資料,請參閱將資料載入 BigQuery 簡介一文。

將日期資料分割資料表轉換成擷取時間分區資料表

如果您之前已建立日期資料分割資料表,可以在 bq 指令列工具中使用 partition 指令,將整組相關資料表轉換成一個擷取時間分區資料表。日期資料分割資料表必須使用下列命名慣例:[TABLE]_YYYYMMDD (例如 mytable_20160101mytable_20160331)。

選用參數包括 --time_partitioning_expiration--location--time_partitioning_type。因為 --time_partitioning_type=DAY 是目前唯一支援的值,所以這是選用參數。如果您的來源資料表或目標資料表位於預設專案以外的專案,請使用下列格式將專案 ID 新增至資料集名稱中:[PROJECT_ID]:[DATASET]

輸入下列指令,將一系列日期資料分割資料表轉換成一個擷取時間分區資料表:

bq --location=[LOCATION] partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration [INTEGER] \
[PROJECT_ID]:[DATASET].[SOURCE_TABLE]_ \
[PROJECT_ID]:[DATASET].[DESTINATION_TABLE]

其中:

  • [LOCATION] 是您的位置名稱。--location 是選用標記。例如,如果您是在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案來設定位置的預設值。
  • [INTEGER] 是資料表分區的預設生命週期 (以秒為單位)。這個值沒有下限。到期時間為分區的世界標準時間日期加整數值。分區到期時間與資料表的到期時間無關,並不會覆寫資料表到期時間。如果您設定的分區到期時間比資料表到期時間長,則以資料表到期時間優先。
  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是專案中的資料集。
  • [SOURCE_TABLE]_ 是日期資料分割表格的前置字串。
  • [DESTINATION_TABLE] 是您要建立的分區表格名稱。

範例:

輸入下列指令,在預設專案的 mydataset 中建立名稱為 mytable_partitioned 的擷取時間分區資料表。分區有效期限設為 259,200 秒 (3 天)。日期資料分割來源資料表的前置字串為 sourcetable_。來源資料表也在預設專案中。

bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned

執行指令時會建立一個複製工作,此工作會從資料分割資料表產生分區。舉例來說,如果日期資料分割資料表是 sourcetable_20180126sourcetable_20180127,則複製工作會建立以下分區:mydataset.mytable_partitioned$20180126mydataset.mytable_partitioned$20180127

輸入下列指令,在 mydataset 中建立名稱為 mytable_partitioned 的擷取時間分區資料表。mydataset 是在 myotherproject 而非預設專案中。分區有效期限設為 86,400 秒 (1 天)。日期資料分割來源資料表的前置字串是 sourcetable_。來源資料表也在預設專案中。

bq partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 86400 \
mydataset.sourcetable_ \
myotherproject:mydataset.mytable_partitioned

輸入下列指令,在預設專案的 mydataset 中建立名稱為 mytable_partitioned 的擷取時間分區資料表。mydataset 建立於 asia-northeast1 地區。分區到期時間設為 259,200 秒 (3 天)。日期資料分割來源資料表的前置字串是 sourcetable_。來源資料表也在預設專案中。

bq --location=asia-northeast1 partition \
--time_partitioning_type=DAY \
--time_partitioning_expiration 259200 \
mydataset.sourcetable_ \
mydataset.mytable_partitioned

控管時間分區資料表的存取權

您不能直接指派資料表或分區的存取控制權。您可以設定資料集層級或專案層級的存取權控制,藉以控管資料表存取權。

資料集層級存取權控制會指定可以在該特定資料集中,對資料表執行作業的操作使用者、群組和服務帳戶。如果只指派資料集層級權限,則必須同時指派可以存取該專案的原始或預先定義專案層級角色,例如 bigquery.user

與其授予個別資料集的存取權,不如指派預先定義的專案層級 IAM 角色,因為這些角色可以一次授予專案內所有資料集裡所有資料表資料的存取權限。

您也可以建立 IAM 自訂角色。如果建立自訂角色,您可依據希望使用者、群組或服務帳戶可以執行的資料表作業授予權限。

如要進一步瞭解角色和授權,請參閱:

使用擷取時間分區資料表

取得擷取時間分區資料表相關資訊

您可以使用 GCP Console、傳統 BigQuery 網頁版 UI、bq show CLI 指令或呼叫 tables.get API 方法,藉以取得資料表相關資訊。

所需權限

如要取得資料表的相關資訊,您必須是資料集的 READER 角色,或必須取得具有 bigquery.tables.get 權限的預先定義專案層級身分與存取權管理角色。如果您有專案層級的 bigquery.tables.get 權限,就能取得專案中所有資料表的相關資訊。以下是具有 bigquery.tables.get 權限的預先定義 Cloud IAM 角色:

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

此外,經指派為 bigquery.user 角色的使用者還擁有 bigquery.datasets.create 權限。這可讓獲指派為 bigquery.user 角色的使用者針對自己建立的任何資料集,取得當中資料表的相關資訊。已取得 bigquery.user 角色的使用者在建立資料集時,也會同時獲得該資料集的 OWNER 存取權。資料集的 OWNER 存取權可讓使用者完全控管該資料集,以及其中的所有資料表。

如要進一步瞭解 BigQuery 中的身分與存取權管理角色和權限,請參閱存取權控管。如需資料集層級角色的詳細資訊,請參閱資料集的原始角色

取得擷取時間分區資料表資訊

如何查看擷取時間分區資料表的相關資訊:

主控台

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 在導覽面板的「Resources」(資源) 區段中,展開您的專案並選取資料集以列出資料表並查看資料表的內容

  3. 按一下資料表名稱。

  4. 按一下 [Details] (詳細資料)。這個分頁顯示資料表的說明與資料表資訊。

    資料表詳細資料

  5. 按一下 [Schema] (結構定義) 分頁標籤,查看資料表的結構定義。請注意,在表格中會載入其他虛擬資料欄 _PARTITIONTIME,資料欄含有資料的日期時間戳記。

傳統版 UI

  1. 在導覽窗格中,按一下資料集左側的向下箭號圖示 向下箭號圖示 以展開下拉式選單,或在資料集名稱上按兩下,畫面上會顯示資料集中的資料表和檢視表。

  2. 按一下資料表名稱。

  3. 按一下 [Details] (詳細資訊)。「Table Details」(資料表詳細資訊) 頁面會顯示資料表的說明和相關資訊。

    分區資料表詳細資料

  4. 按一下 [Schema] (結構定義) 分頁標籤,查看資料表的結構定義。請注意,在資料表中會載入其他虛擬資料欄 _PARTITIONTIME,資料欄含有資料的日期時間戳記。

    分區資料表結構定義

CLI

發出 bq show 指令以顯示所有資料表資訊。使用 --schema 標記,可以只顯示資料表結構定義資訊。--format 標記可用來控管輸出。

如果您要取得非預設專案中資料表的相關資訊,請使用下列格式將專案 ID 新增到資料集:[PROJECT_ID]:[DATASET]

bq show --schema --format=prettyjson [PROJECT_ID]:[DATASET].[TABLE]

其中:

  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是資料集名稱。
  • [TABLE] 是資料表名稱。

示例:

輸入下列指令,顯示 mydatasetmytable 的所有相關資訊。mydataset 位於預設專案中。

bq show --format=prettyjson mydataset.mytable

輸入下列指令,顯示 mydatasetmytable 的所有相關資訊。mydataset 位於 myotherproject 而非預設專案中。

bq show --format=prettyjson myotherproject:mydataset.mytable

輸入下列指令,可以只顯示 mydatasetmytable 的結構定義資訊。mydataset 是在 myotherproject 而非預設專案中。

顯示時間分區資料表的結構定義資訊時,不會顯示 _PARTITIONTIME 虛擬資料欄。

bq show --schema --format=prettyjson myotherproject:mydataset.mytable

API

呼叫 bigquery.tables.get 方法並提供任何相關參數。

列出資料集中的擷取時間分區資料表

您可以使用 GCP Console、傳統 BigQuery 網頁版 UI、bq ls CLI 指令,或是呼叫 tables.list API 方法,列出資料集裡的資料表 (包括分區資料表)。

所需權限

您至少要具備 bigquery.tables.list 權限,才能列出資料集裡的資料表。以下是具有 bigquery.tables.list 權限的預先定義 Cloud IAM 角色:

  • bigquery.user
  • bigquery.metadataViewer
  • bigquery.dataViewer
  • bigquery.dataOwner
  • bigquery.dataEditor
  • bigquery.admin

如要進一步瞭解 BigQuery 中的身分與存取權管理角色和權限,請參閱存取權控管

列出分區資料表

如何列出資料集中的資料表 (包括分區資料表):

主控台

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 在導覽面板的「Resources」(資源) 區段,展開專案並按一下資料集。

  3. 捲動清單來檢視該資料集中的資料表。資料表、分區資料表、模型和檢視表會分別以不同的圖示呈現。

傳統版 UI

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

  2. 捲動清單來檢視該資料集中的資料表。資料表和檢視表會分別以不同的圖示呈現。

    查看資料表

CLI

發出 bq ls 指令。--format 標記可用來控管輸出。如果您要列出預設專案以外的專案中的資料表,請使用下列格式將專案 ID 新增至資料集:[PROJECT_ID]:[DATASET]

bq ls --format=pretty [PROJECT_ID]:[DATASET]

其中:

  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是資料集名稱。

執行指令時,Type 欄位會顯示 TABLEVIEW。如為擷取時間分區資料表,則 Time Partitioning 欄位會顯示以毫秒為單位的 DAY 和分區到期時間 (如果有的話)。

例如:

+-------------------------+-------+----------------------+--------------------------------+
|         tableId         | Type  |        Labels        | Time Partitioning              |
+-------------------------+-------+----------------------+--------------------------------+
| mytable                 | TABLE | department:shipping  |  DAY (expirationMs: 259200000) |
| myview                  | VIEW  |                      |                                |
+-------------------------+-------+----------------------+--------------------------------+

示例:

輸入下列指令,列出預設專案中 mydataset 資料集中的資料表。

bq ls --format=pretty mydataset

輸入下列指令,列出 myotherproject 中資料集 mydataset 內的資料表。

bq ls --format=pretty myotherproject:mydataset

API

如要使用 API 列出資料表,請呼叫 tables.list 方法。

列出擷取時間分區資料表中的分區

您可以藉由查詢 _PARTITIONTIME 虛擬資料欄 (使用標準 SQL) 或 __PARTITIONS_SUMMARY__ 中繼資料表 (使用舊版 SQL),列出擷取時間分區資料表中的分區。

您可以使用 GCP Console、傳統 BigQuery 網頁版 UI、bq query 指令,或是呼叫 jobs.insert 方法並設定 query 工作來執行查詢。

所需權限

您必須具備 bigquery.jobs.create 權限,才能執行使用 __PARTITIONS_SUMMARY__ 中繼資料表或 _PARTITIONTIME 虛擬資料欄的查詢工作。以下是具備 bigquery.jobs.create 權限的預先定義專案層級身分與存取權管理角色:

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

您還必須取得資料集層級的 READER 角色,或取得具備 bigquery.tables.getData 權限的專案層級身分與存取權管理角色。除了 bigquery.userbigquery.jobUserbigquery.metadataViewer,其他所有預先定義的專案層級身分與存取權管理角色都具備 bigquery.tables.getData 權限。

如要進一步瞭解 BigQuery 中的身分與存取權管理角色和權限,請參閱存取權控管。如需有關資料集層級角色的詳細資訊,請參閱資料集的原始角色

列出擷取時間分區資料表中的分區

您可以使用標準 SQL (偏好方式) 或舊版 SQL,列出擷取時間分區資料表中的分區。方式如下:

標準 SQL:

主控台

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose new query] (撰寫新查詢) 按鈕。

  3. 在「Query editor」(查詢編輯器) 方塊中輸入下列文字,以查詢 _PARTITIONTIME 虛擬資料欄:

    SELECT
      _PARTITIONTIME as pt
    FROM
      `[DATASET].[TABLE]`
    GROUP BY 1
    

    其中:

    • [DATASET] 是含有該資料表的資料集。
    • [TABLE] 是資料表名稱。
  4. (選用) 按一下 [More] (更多) 並選取 [Query settings] (查詢設定)

    查詢設定

  5. (選用) 在「Processing location」(處理位置),按一下 [Auto-select] (自動選取),然後選擇資料的位置。您可以將處理位置保留設定為自動選取,系統就會自動幫您偵測位置。

    查詢處理位置

  6. 按一下 [Run] (執行)。

傳統版 UI

  1. 前往 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose query] (撰寫查詢) 按鈕。

  3. 在「New Query」(新查詢) 方塊中輸入下列文字,以查詢資料表的 _PARTITIONTIME 虛擬資料欄:

    #standardSQL
    SELECT
      _PARTITIONTIME as pt
    FROM
      `[DATASET].[TABLE]`
    GROUP BY 1
    

    其中:

    • [DATASET] 是含有該資料表的資料集。
    • [TABLE] 是資料表名稱。
  4. 按一下 [Show Options] (顯示選項)

  5. (選用) 在「Processing Location」(處理位置) 中,按一下 [Unspecified] (未指定),然後選擇您資料的位置

  6. 按一下 [Run query] (執行查詢)

CLI

使用 bq query 指令來輸入下列查詢:

bq --location=[LOCATION] query --use_legacy_sql=false '
SELECT
  _PARTITIONTIME as pt
FROM
  `[DATASET].[TABLE]`
GROUP BY 1'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional. For example, if you are using BigQuery in
    the Tokyo region, you can set the flag's value to `asia-northeast1`.
    You can set a default value for the location using the
    [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

呼叫 jobs.insert 方法並設定 query 工作,即可查詢資料表的 _PARTITIONTIME 虛擬資料欄。

工作資源jobReference 區段中,於 location 屬性指定您的位置。

舊版 SQL:

主控台

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose new query] (撰寫新查詢) 按鈕。

  3. 在「Query editor」(查詢編輯器) 方塊中輸入下列文字,以查詢 __PARTITIONS_SUMMARY__ 中繼資料表:

    #legacySQL
    SELECT
      partition_id
    FROM
      [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]
    

    其中:

    • [DATASET] 是含有該資料表的資料集。
    • [TABLE] 是資料表名稱。
  4. 按一下 [More] (更多) 並選取 [Query settings] (查詢設定)

    查詢設定

  5. 在「Processing location」(處理位置) 中,按一下 [Auto-select] (自動選取),然後選擇您資料的位置。如果資料位於 USEU 多地區位置,可將處理位置保留設定為自動選取。如果資料位於 USEU,則會自動偵測處理位置。

    查詢處理位置

  6. 按一下 [Run] (執行)。

傳統版 UI

  1. 前往 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose query] (撰寫查詢) 按鈕。

  3. 在「New Query」(新查詢) 方塊中輸入下列文字,以查詢資料表的 __PARTITIONS_SUMMARY__ 中繼資料表:

    #legacySQL
    SELECT
       partition_id
     FROM
       [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]
    

    其中:

    • [DATASET] 是含有該資料表的資料集。
    • [TABLE] 是資料表名稱。
  4. 按一下 [Show Options] (顯示選項)

  5. (選用) 在「Processing Location」(處理位置) 中,按一下 [Unspecified] (未指定),然後選擇您資料的位置

  6. 按一下 [Run query] (執行查詢)

CLI

使用 bq query 指令來輸入下列查詢:

bq --location=[LOCATION] query --use_legacy_sql=true '
SELECT
   partition_id
 FROM
   [[DATASET].[TABLE]$__PARTITIONS_SUMMARY__]'

Where:

  + `[LOCATION]` is the name of your location. The `--location` flag is
    optional. For example, if you are using BigQuery in
    the Tokyo region, you can set the flag's value to `asia-northeast1`.
    You can set a default value for the location using the
    [.bigqueryrc file](/bigquery/docs/bq-command-line-tool#setting_default_values_for_command-line_flags).
  + `[DATASET]` is the dataset that contains the table.
  + `[TABLE]` is the name of the table.

API

呼叫 jobs.insert 方法並設定 query 工作,即可查詢 __PARTITIONS_SUMMARY__ 中繼資料表。

工作資源jobReference 區段中,於 location 屬性指定您的位置。

使用中繼資料表取得資料表中繼資料

您可以使用稱為中繼資料表的特殊資料表,取得分區資料表的相關資訊。中繼資料表包含中繼資料,例如資料集中的資料表和視圖清單。中繼資料表皆為唯讀。

使用中繼資料表取得分區中繼資料

__PARTITIONS_SUMMARY__ 中繼資料表是一種特殊資料表,其內容代表時間分區資料表中分區的中繼資料。__PARTITIONS_SUMMARY__ 中繼資料表是唯讀資料表。

如要存取時間分區資料表中分區的中繼資料,請在查詢的 SELECT 陳述式中使用 __PARTITIONS_SUMMARY__ 中繼資料表。您可以透過以下方式執行查詢:

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

目前,標準 SQL 不支援分區修飾符的分隔符 ($),所以您無法使用標準 SQL 查詢 __PARTITIONS_SUMMARY__。使用 __PARTITIONS_SUMMARY__ 中繼資料表的舊版 SQL 查詢如下所示:

SELECT
  column
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]

其中:

  • 「dataset」是資料集的名稱。
  • 「table」是時間分區資料表的名稱。
  • 「column」是下列其中一項:
說明
project_id 專案名稱。
dataset_id 資料集名稱。
table_id 時間分區資料表的名稱。
partition_id 分區名稱 (日期)。
creation_time 分區的建立時間,從世界標準時間 1970 年 1 月 1 日開始計算,並以毫秒為單位。
last_modified_time 分區的前次修改時間,從世界標準時間 1970 年 1 月 1 日開始計算,並以毫秒為單位。

分區中繼資料表權限

您至少要具備 bigquery.jobs.create 權限,才能執行使用 __PARTITIONS_SUMMARY__ 中繼資料表的查詢工作。以下是具有 bigquery.jobs.create 權限的預先定義 Cloud IAM 角色:

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

此外,您還必須取得 bigquery.tables.getData 權限。以下是具有 bigquery.tables.getData 權限的預先定義 Cloud IAM 角色:

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

如要進一步瞭解 BigQuery 中的 Cloud IAM 角色,請參閱存取權控管

分區中繼資料表範例

下列查詢會擷取時間分區資料表 mydataset.mytable 的所有分區中繼資料。

主控台

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

傳統版 UI

#legacySQL
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

CLI

bq query --use_legacy_sql=true '
SELECT
  *
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

輸出內容如下:

+----------------+------------+----------------+--------------+---------------+--------------------+
|   project_id   | dataset_id |    table_id    | partition_id | creation_time | last_modified_time |
+----------------+------------+----------------+--------------+---------------+--------------------+
| myproject      | mydataset  | mytable        | 20160314     | 1517190224120 | 1517190224997      |
| myproject      | mydataset  | mytable        | 20160315     | 1517190224120 | 1517190224997      |
+----------------+------------+----------------+--------------+---------------+--------------------+

下列查詢列出 mydataset.mytable 中的分區前次修改的時間。

主控台

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

傳統版 UI

#legacySQL
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]

CLI

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  last_modified_time
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

輸出內容如下:

+--------------+--------------------+
| partition_id | last_modified_time |
+--------------+--------------------+
| 20160102     |      1471632556179 |
| 20160101     |      1471632538142 |
| 20160103     |      1471632570463 |
+--------------+--------------------+

如要以使用者可理解的格式顯示 last_modified_time 欄位,請使用 FORMAT_UTC_USEC 函式。例如:

主控台

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

傳統版 UI

#legacySQL
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.table1$__PARTITIONS_SUMMARY__]

CLI

bq query --use_legacy_sql=true '
SELECT
  partition_id,
  FORMAT_UTC_USEC(last_modified_time*1000) AS last_modified
FROM
  [mydataset.mytable$__PARTITIONS_SUMMARY__]'

輸出內容如下:

+--------------+----------------------------+
| partition_id |       last_modified        |
+--------------+----------------------------+
| 20160103     | 2016-08-19 18:49:30.463000 |
| 20160102     | 2016-08-19 18:49:16.179000 |
| 20160101     | 2016-08-19 18:48:58.142000 |
+--------------+----------------------------+

範例:使用查詢結果建立擷取時間分區資料表

在本例中,使用指令列工具建立擷取時間分區資料表,然後使用查詢結果將資料加到至三個分區當中。資料表將包含 2016 年前三天的天氣資料,按日期區分。

此範例在 NOAA GSOD 天氣資料集中查詢 2016 年前三天的氣溫,然後將結果寫入分區資料表中。

步驟 1. 在名稱為 mydataset 的資料集中建立名稱為 temps 的空擷取時間分區資料表。不需要指定資料表的結構定義。系統會使用查詢結果的結構定義作為資料表的結構定義。

bq mk --time_partitioning_type=DAY mydataset.temps

如要查看配置設定,請使用 bq show 指令:

bq show --format=prettyjson mydataset.temps

bq show 指令輸出中查看 timePartitioning 項目:

{
  ...
  "timePartitioning": {
    "type": "DAY"
  },
  "type": "TABLE"
}

步驟 2.NOAA GSOD 天氣資料集中查詢 2016 年前三天的氣溫,然後將結果寫入 temps 資料表中的個別分區。下列查詢使用 --destination_table 選項和標準 SQL 語法,將 100 列查詢結果寫入分區。

  1. 執行下列查詢,以便將 2016 年 1 月 1 日的氣溫寫入分區 mydataset.temps$20160101

    bq query --use_legacy_sql=false \
    --destination_table 'mydataset.temps$20160101' \
    'SELECT
       stn,
       temp
     FROM
       `bigquery-public-data`.noaa_gsod.gsod2016
     WHERE
       mo="01"
       AND da="01"
     LIMIT
       100'
    
  2. 執行下列查詢,以便將 2016 年 1 月 2 日的氣溫寫入分區 mydataset.temps$20160102

    bq --location=US query --use_legacy_sql=false \
    --destination_table 'mydataset.temps$20160102' \
    'SELECT
       stn,
       temp
     FROM
       `bigquery-public-data`.noaa_gsod.gsod2016
     WHERE
       mo="01"
       AND da="02"
     LIMIT
       100'
    
  3. 執行下列查詢,以便將 2016 年 1 月 3 日的氣溫寫入分區 mydataset.temps$20160103

    bq --location=US query --use_legacy_sql=false \
    --destination_table 'mydataset.temps$20160103' \
    'SELECT
       stn,
       temp
     FROM
       `bigquery-public-data`.noaa_gsod.gsod2016
     WHERE
       mo="01"
       AND da="03"
     LIMIT
       100'
    

步驟 3: 使用 bq show 指令,確認資料表中有 300 列。

bq show mydataset.temps

結果顯示結構定義和總列數。

  Last modified        Schema       Total Rows   Total Bytes     Expiration      Time Partitioning   Labels   kmsKeyName
 ----------------- ---------------- ------------ ------------- ----------------- ------------------- -------- ------------
  28 Jan 15:03:45   |- stn: string   300          4800          29 Jan 15:00:32   DAY
                    |- temp: float

後續步驟

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

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

這個網頁