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

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

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

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

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

建立擷取時間分區資料表

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

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

在 BigQuery 中建立擷取時間分區資料表時,每個資料集中的資料表名稱不得重複。資料表名稱得符合下列條件:

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

所需權限

如要建立擷取時間分區表格,您必須具備資料集層級的 WRITER 存取權,或獲派具備 bigquery.tables.create 權限的專案層級身分與存取權管理角色。以下是擁有 bigquery.tables.create 權限的預先定義專案層級身分與存取權管理角色:

此外,因為 bigquery.user 角色具備 bigquery.datasets.create 權限,所以取得 bigquery.user 角色指派的使用者可以在該使用者建立的任何資料集中建立分區資料表。取得 bigquery.user 角色指派的使用者建立資料集時,系統會將該資料集的 OWNER 存取權授予該使用者。資料集的 OWNER 存取權可讓使用者完全控管該資料集,以及資料集中的所有資料表。

如要深入瞭解 BigQuery 中的身分與存取權管理角色和權限,請參閱存取權控制一文。如要深入瞭解資料集層級角色,請參閱資料集的原始角色一文。

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

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

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

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

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

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

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

主控台

  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. 按一下 [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 建立資料表後,即無法新增分區有效期限。

指令列

使用 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。如要深入瞭解此標記的使用資訊,請參閱客戶管理式加密金鑰

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

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

使用指定 timePartitioning 屬性和 schema 屬性的已定義表格資源,呼叫 tables.insert 方法。

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

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

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

主控台

使用主控台 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 --location=US 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 而非預設專案中。查詢會從非分區資料表中擷取資料 — USA Name Data 公開資料集。

bq --location=US 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 工作,然後納入 configuration.query.destinationTabletimePartitioning 屬性的值。

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

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

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

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

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

[TABLE_NAME]$20160501

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

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

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

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

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

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

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_。來源資料表也在預設專案中。mydataset 建立於 US 多地區位置。

bq --location=US 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_。來源資料表位在預設專案中。mydataset 建立於 US 多地區位置。

bq --location=US 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 自訂角色。如果建立自訂角色,您可依據您希望使用者、群組或服務帳戶可以執行的資料表作業來授予權限。

如要深入瞭解角色和權限,請參閱下列資訊:

使用擷取時間分區資料表

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

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

所需權限

如要取得資料表的相關資訊,您必須獲指派資料集的 READER 角色,或者包含 bigquery.tables.get 權限的專案層級身分與存取權管理角色。如果您取得專案層級 bigquery.tables.get 權限,就能取得專案中所有資料表的相關資訊。所有預先定義的專案層級身分與存取權管理角色都包含 bigquery.tables.get 權限,但 bigquery.jobUserbigquery.user 除外

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

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

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

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

主控台

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

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

  3. 按一下資料表名稱。

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

    資料表詳細資料

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

傳統版 UI

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

  2. 按一下資料表名稱。

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

    分區資料表詳細資料

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

    分區資料表結構定義

指令列

發出 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 主控台、傳統 BigQuery 網頁版 UI、bq ls CLI 指令或呼叫 tables.list API 方法,列出資料集中的資料表 (包括分區資料表)。

所需權限

如要列出資料集中的資料表,您必須獲派資料集的 READER 角色,或獲派具備 bigquery.tables.list 權限的專案層級身分與存取權管理角色。如果您取得專案層級的 bigquery.tables.list 權限,就能列出專案中任何資料集的資料表。所有預先定義的專案層級身分與存取權管理角色都擁有 bigquery.tables.list 權限,但 bigquery.jobUser 除外

如要深入瞭解 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 主控台、傳統 BigQuery 網頁版 UI、bq query 指令或呼叫 jobs.insert 方法並設定 query 工作,來執行查詢。

所需權限

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

您還必須獲派資料集層級的 READER 角色,或獲派具備 bigquery.tables.getData 權限的專案層級身分與存取權管理角色。所有預先定義的專案層級身分與存取權管理角色皆擁有 bigquery.tables.getData 權限,但 bigquery.userbigquery.jobUserbigquery.metadataViewer 除外

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

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

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

標準 SQL:

主控台

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

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

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

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

    其中:

    • [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」(新查詢) 方塊中輸入下列文字,以查詢表格的 _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__ 中繼資料表。如要執行查詢,則可以使用下列方式:使用主控台、傳統版 BigQuery 網頁版 UI、指令列工具的 bq query 指令,或呼叫 jobs.insert API 方法並設定查詢工作

目前,標準 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 日開始計算,並以毫秒為單位。

分區中繼資料表權限

如要執行使用 __PARTITIONS_SUMMARY__ 中繼資料表的查詢工作,您必須擁有 bigquery.jobs.create 權限。以下是擁有 bigquery.jobs.create 權限的預先定義專案層級身分與存取權管理角色:

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

分區中繼資料表範例

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

主控台

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

傳統版 UI

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

指令列

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__]

指令列

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__]

指令列

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 weather 資料集中查詢 2016 年前三天的氣溫,然後將結果寫入分區資料表中。因為您查詢的是公開資料集,所以此範例使用 --location=US 標記。BigQuery 公開資料集儲存在美國多地區位置。因為公開資料集儲存在美國,所以不能將公開資料查詢結果寫入其他地區的資料表,也不能聯結公開資料集中的資料表與其他地區的資料表。

步驟 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 --location=US 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

後續步驟

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

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

這個網頁