建立及使用分區資料表

本文件說明如何建立及使用依 DATETIMESTAMP 資料欄分區的資料表。如要瞭解擷取時間分區資料表,請參閱建立及使用擷取時間分區資料表一文。

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

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

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

限制

分區資料表有下列限制:

  • 分區資料欄必須是純量 DATETIMESTAMP 資料欄。資料欄模式可能是 REQUIREDNULLABLE,但不能是 REPEATED (以陣列為基礎)。
  • 此外,分區資料欄必須是頂層欄位。您無法將 RECORD (STRUCT) 中的分葉欄位當成分區資料欄使用。
  • 您無法使用舊版 SQL 查詢分區資料表或將查詢結果寫入分區資料表。

建立分區資料表

您可以透過下列方式,在 BigQuery 中建立分區資料表:

資料表命名

在 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 角色和權限,請參閱存取權控管一文。

建立含結構定義的空白分區資料表

您無法建立沒有結構定義的空白分區資料表。必須要有結構定義,才能識別用來建立分區的資料欄。

建立含結構定義的空白分區資料表時,您可以執行下列作業:

  • 使用 CLI 以內嵌方式提供結構定義
  • 使用 CLI 指定 JSON 結構定義檔
  • 呼叫 API 的 tables.insert 方法時,在資料表資源中提供結構定義

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

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

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

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

Console

  1. 在導覽面板的「Resources」(資源) 區段,展開您的專案並選取資料集。

  2. 在視窗右側的詳細資料面板中,按一下 [Create table] (建立資料表)

  3. 在「Create table」(建立資料表) 面板的「Source」(來源) 區段中:

    • 在「Create table from」(使用下列資料建立資料表) 部分,選取 [Empty table] (空白資料表)
  4. 在「Destination」(目的地) 區段中:

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

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

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

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

  6. 在「Partition and cluster settings」(分區與叢集設定) 部分,按一下 [No Partitioning] (無分區),然後依序選取 [Partition by field] (依欄位分區)DATETIMESTAMP 資料欄。如果結構定義不包含 DATETIMESTAMP 資料欄,則此選項不可用。

  7. (選用) 在「Partitioning filter」(分區篩選器) 部分,勾選 [Require partition filter] (需要分區篩選器) 方塊,藉此要求使用者加入指定了待查詢分區的 WHERE 子句。使用分區篩選器可以降低成本並提升效能。詳情請參閱查詢分區資料表一文。

  8. (選用) 按一下 [Advanced options] (進階選項),並在「Encryption」(加密) 部分按一下 [Customer-managed key] (客戶管理的金鑰),即可使用 Cloud Key Management Service 金鑰。如果您保留Google 代管的金鑰設定,BigQuery 會加密靜態資料

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

DDL

資料定義語言 (DDL) 陳述式可讓您使用標準 SQL 查詢語法建立和修改資料表及檢視表。

詳情請參閱使用資料定義語言陳述式一文。

如要在 GCP Console 中使用 DDL 陳述式建立分區資料表:

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。
    前往 GCP Console

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

  3. 在「Query editor」(查詢編輯器) 文字區域中,輸入您的 CREATE TABLE DLL 陳述式

    下列查詢會建立名為 newtable 的資料表,這個資料表按 transaction_date DATE 資料欄分區,並且有三天的分區到期時間。

     CREATE TABLE
       mydataset.newtable (transaction_id INT64,
         transaction_date DATE)
     PARTITION BY
       transaction_date
     OPTIONS
       ( partition_expiration_days=3,
         description="a table partitioned by transaction_date" )

  4. 按一下 [Run] (執行)。當查詢完成時,資料表會顯示在「Resources」(資源) 窗格中。

傳統版 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 陣列的形式輸入資料表結構定義:

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

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

    • 在「Partitioning Type」(分區類型) 部分,按一下 [None] (無),接著選擇 [Day] (日)
    • 在「Partitioning Field」(分區欄位) 部分,選擇 TIMESTAMPDATE 資料欄。預設值為 _PARTITIONTIME,此值會建立擷取時間分區資料表。
    • (選用) 勾選 [Require partition filter] (需要分區篩選器) 方塊,藉此要求使用者加入指定了待查詢分區的 WHERE 子句。使用分區篩選器可以降低成本並提升效能。詳情請參閱查詢分區資料表一文。
    • (選用) 在「Destination encryption」(目的地加密) 部分,選擇 [Customer-managed encryption] (客戶管理的加密),即可使用 Cloud Key Management Service 金鑰來加密資料表。如果您保留 Default 設定,BigQuery 會使用 Google 代管的金鑰來加密靜態資料
  6. 按一下 [Create Table] (建立資料表)

CLI

使用 mk 指令搭配 --table 標記 (或 -t 捷徑)、--schema 標記與 --time_partitioning_field 標記。您可以透過內嵌方式或透過 JSON 結構定義檔提供資料表結構定義。

選用參數包括 --expiration--description--time_partitioning_expiration--destination_kms_key--require_partition_filter--label。目前 --time_partitioning_type 唯一支援的值是 DAY,所以不需要這個標記。

如果您要在非預設的專案中建立資料表,請使用下列格式將專案 ID 新增至資料集:project_id:dataset

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

輸入下列指令,建立含有結構定義的空白分區資料表:

bq mk --table \
--expiration integer1 \
--schema schema \
--time_partitioning_field column \
--time_partitioning_expiration integer2 \
--[no]require_partition_filter \
--description "description" \
--label key:value, key:value \
project_id:dataset.table

其中:

  • 「integer1」是資料表的預設生命週期 (以秒為單位),最小值是 3,600 秒 (1 小時)。到期時間為目前的世界標準時間加上整數值。如果您在建立時間分區資料表時設定了資料表到期時間,系統會忽略資料集的預設資料表到期時間設定。只要設定這個值,系統就會在指定時間到期後刪除資料表和所有分區。
  • 「schema」是格式為「field:data_type, field:data_type」的內嵌結構定義,或本機上 JSON 結構定義檔的路徑。
  • 「column」是用來建立分區的 TIMESTAMPDATE 資料欄名稱。
  • 「integer2」是資料表分區的預設生命週期 (以秒為單位)。這個值沒有下限。到期時間為分區日期加上整數值。分區到期時間與資料表的到期時間無關,並不會覆寫資料表到期時間。如果您設定的分區到期時間比資料表到期時間長,則以資料表到期時間為優先。
  • 「description」是置於括號中的資料表說明。
  • 「key:value」是代表標籤key:value 組合。您可以用逗號分隔的清單輸入多個標籤。
  • 「project_id」是您的專案 ID。
  • 「dataset」是專案中的資料集。
  • 「table」是您所建立的分區資料表名稱。

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

範例:

輸入下列指令,在預設專案的 mydataset 中建立名為 mypartitionedtable 的分區資料表。分區到期時間設為 86,400 秒 (1 天)、資料表到期時間設為 2,592,000 秒 (1 個月 - 30 天)、說明設為 This is my partitioned table,而標籤則設為 organization:development。此指令使用 -t 捷徑而非 --table

--require_partition_filter 標記可用於要求使用者加入指定了待查詢分區的 WHERE 子句。使用分區篩選器可以降低成本並提升效能。詳情請參閱查詢分區資料表一文。

結構定義以內嵌方式指定為:ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING。指定的 TIMESTAMP 欄位 ts 可用來建立分區。

bq mk -t \
--expiration 2592000 \
--schema 'ts:TIMESTAMP,column1:STRING,column2:INTEGER,coumn4:STRING' \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--require_partition_filter \
--description "This is my partitioned table" \
--label org:dev \
mydataset.mypartitionedtable

輸入下列指令,在 myotherproject 而非您的預設專案中建立名為 mypartitionedtable 的分區資料表。分區到期時間設為 259,200 秒 (3 天)、說明設為 This is my partitioned table,而標籤則設為 organization:development。此指令使用 -t 捷徑而非 --table。這個指令不會指定資料表到期時間。如果資料集有預設資料表到期時間,系統會直接套用這個時間。如果資料集沒有預設資料表到期時間,資料表將永不過期,但分區會在 3 天內過期。

結構定義在本機 JSON 檔案 /tmp/myschema.json 中指定。結構定義包含名為 tsTIMESTAMP 欄位,這個欄位可用來建立分區。

bq mk -t \
--expiration 2592000 \
--schema /tmp/myschema.json \
--time_partitioning_field ts \
--time_partitioning_expiration 86400  \
--description "This is my partitioned table" \
--label org:dev \
myotherproject:mydataset.mypartitionedtable

您可以在建立資料表後,使用 CLI 來更新分區資料表的資料表到期時間分區到期時間說明標籤

API

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

Go

在試行此示例之前,請至 BigQuery 快速入門導覽課程:使用用戶端程式庫,按照 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
sampleSchema := bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.IntegerFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
metadata := &bigquery.TableMetadata{
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "date",
		Expiration: 90 * 24 * time.Hour,
	},
	Schema: sampleSchema,
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metadata); err != nil {
	return err
}

Python

在試行此示例之前,請至 BigQuery 快速入門導覽課程:使用用戶端程式庫,按照 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_ref = client.dataset('my_dataset')

table_ref = dataset_ref.table("my_partitioned_table")
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
table = bigquery.Table(table_ref, schema=schema)
table.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days

table = client.create_table(table)

print(
    "Created table {}, partitioned on column {}".format(
        table.table_id, table.time_partitioning.field
    )
)

從查詢結果建立分區資料表

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

當您從查詢結果建立分區資料表時,必須使用標準 SQL。目前,系統不支援使用舊版 SQL 來查詢分區資料表或將查詢結果寫入分區資料表。

分區修飾符可讓您將查詢結果寫入特定分區。例如,要將結果寫入 2016 年 5 月 1 日的分區,請使用下列分區修飾符:

table_name$20160501

當您使用分區修飾符將查詢結果寫入特定分區時,要寫入分區的資料必須符合資料表的分區配置。所有寫入分區的資料列,其值都必須落在分區日期內。

例如:

下列查詢會從 2018 年 2 月 1 日擷取資料,並將資料寫入資料表 mytable$20180201 分區。資料表有兩個資料欄:一個名為 TSTIMESTAMP 資料欄,另一個名為 aINT64 資料欄。

bq query \
--nouse_legacy_sql  \
--destination_table=mytable$20180201 \
'SELECT
   TIMESTAMP("2018-02-01") AS TS,
   2 AS a'

下列查詢會從 2018 年 1 月 31 日擷取資料,並試圖將資料寫入資料表 mytable$20180201 分區。由於您試圖寫入的資料不在分區的日期中,因此查詢將會失敗。

bq query \
--nouse_legacy_sql  \
--destination_table=T$20180201 \
'SELECT
   TIMESTAMP("2018-01-31") as TS,
   2 as a'

如要瞭解如何在分區資料表中附加或重述 (取代) 資料,請參閱附加至與覆寫時間分區資料表資料一節。如要進一步瞭解如何查詢分區資料表,請參閱查詢分區資料表一文。

從查詢結果建立分區資料表

如要從查詢結果建立分區資料表:

Console

您在使用 GCP Console 查詢資料時,無法為目標資料表指定分區選項。

傳統版 UI

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

CLI

輸入 bq query 指令,指定 --destination_table 標記以依據查詢結果建立永久性資料表,並指定 --time_partitioning_field 標記來建立分區目標資料表。目前 --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_field column \
--use_legacy_sql=false \
'query'

其中:

  • 「location」是您位置的名稱,--location 是選用標記。舉例來說,假設您是在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案來設定位置的預設值。
  • 「project_id」是您的專案 ID。
  • 「dataset」是含有新分區資料表的資料集名稱。
  • 「table」是您要使用查詢結果建立的分區資料表名稱。
  • 「query」是採用標準 SQL 語法的查詢。您目前無法使用舊版 SQL 查詢分區資料表或將查詢結果寫入分區資料表。

範例:

輸入下列指令,將查詢結果寫入 mydataset 中名為 mypartitionedtable 的分區目標資料表。mydataset 位於您的預設專案中。查詢會從非分區資料表 NHTSA 交通死亡事故公開資料集裡擷取資料。資料表的 timestamp_of_crash TIMESTAMP 資料欄可用來建立分區。

bq query \
--destination_table mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

輸入下列指令,將查詢結果寫入 mydataset 中名為 mypartitionedtable 的分區目標資料表。mydataset 位於 myotherproject 而非預設專案中。查詢會從非分區資料表 NHTSA 交通死亡事故公開資料集裡擷取資料。資料表的 timestamp_of_crash TIMESTAMP 資料欄可用來建立分區。

bq query \
--destination_table myotherproject:mydataset.mypartitionedtable \
--time_partitioning_field timestamp_of_crash \
--use_legacy_sql=false \
'SELECT
   state_number,
   state_name,
   day_of_crash,
   month_of_crash,
   year_of_crash,
   latitude,
   longitude,
   manner_of_collision,
   number_of_fatalities,
   timestamp_of_crash
 FROM
   `bigquery-public-data`.nhtsa_traffic_fatalities.accident_2016
 LIMIT
   100'

API

如要將查詢結果儲存至永久性分區資料表中,請呼叫 jobs.insert 方法,設定 query 工作,然後加入 destinationTabletimePartitioning 屬性的值。

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

載入資料時建立分區資料表

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

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

分區修飾符可讓您將資料載入特定分區。例如,要將 2016 年 5 月 1 日產生的所有資料載入 20160501 分區,請使用下列分區修飾符:

table_name$20160501

當您使用分區修飾符將資料載入特定分區時,要載入分區的資料必須符合資料表的分區配置。所有寫入分區的資料列,其值都必須落在分區日期內。

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

控管分區資料表的存取權

您無法直接指派資料表或檢視表的存取權控管設定。您可以授予的最低 BigQuery 資源存取層級為資料集層級。如要設定資料表和檢視表的存取權,請在資料集層級或更高層級授予 Cloud IAM 角色。

在資料集層級授予角色,即可讓實體對特定資料集裡的資料表和檢視表執行指定作業。如要瞭解如何調整資料集存取權控管設定,請參閱控管資料集存取權一文。

您也可以在 Google Cloud Platform 資源階層中的更高層級 (例如專案、資料夾或機構層級) 授予 Cloud IAM 角色。在較高層級授予角色可讓實體存取更多資源。舉例來說,在專案層級將特定角色授予實體,可讓該實體擁有適用於專案中所有資料集的權限。如要進一步瞭解如何授予資源存取權,請參閱 Cloud IAM 說明文件中的授予、變更及撤銷資源的存取權一文。

您也可以建立 Cloud IAM 自訂角色。建立自訂角色之後,您就能依據要讓實體執行的特定作業授予權限。

如要進一步瞭解角色和權限,請參閱下列文章:

使用分區資料表

取得分區資料表相關資訊

您可以透過下列方式,取得資料表的相關資訊:

  • 使用 GCP Console 或 BigQuery 傳統網頁版 UI
  • 使用 bq show CLI 指令
  • 呼叫 tables.get API 方法
  • 使用用戶端程式庫

所需權限

您至少要具備 bigquery.tables.get 權限,才能取得資料表相關資訊。以下是具有 bigquery.tables.get 權限的預先定義 Cloud IAM 角色:

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

此外,具備 bigquery.datasets.create 權限的使用者在建立資料集時,會獲得 bigquery.dataOwner 存取權。 bigquery.dataOwner 存取權可讓使用者擷取資料表中繼資料。

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

取得分區資料表資訊

如何查看分區資料表的相關資訊:

主控台

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

  2. 在導覽面板的「Resources」(資源) 區段,展開您的專案,然後按一下清單中的資料表名稱。

  3. 按一下「Query editor」(查詢編輯器) 下方的 [Details] (詳細資料)。這個分頁會顯示資料表的說明和相關資訊。

    資料表詳細資料

  4. 按一下 [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

輸出內容應如下所示:

{
  "creationTime": "1563236533535",
  "description": "This is my partitioned table",
  "etag": "/ABcDEo7f8GHijKL2mnOpQr==",
  "expirationTime": "1565828533000",
  "id": "myproject:mydataset.mypartitionedtable",
  "kind": "bigquery#table",
  "labels": {
    "org": "dev"
  },
  "lastModifiedTime": "1563236533576",
  "location": "US",
  "numBytes": "0",
  "numLongTermBytes": "0",
  "numRows": "0",
  "requirePartitionFilter": true,
  "schema": {
    "fields": [
      {
        "name": "ts",
        "type": "TIMESTAMP"
      },
      {
        "name": "column1",
        "type": "STRING"
      },
      {
        "name": "column2",
        "type": "INTEGER"
      },
      {
        "name": "column3",
        "type": "STRING"
      }
    ]
  },
  "selfLink": "https://bigquery.googleapis.com/bigquery/v2/projects/myproject/datasets/mydataset/tables/mypartitionedtable",
  "tableReference": {
    "datasetId": "mydataset",
    "projectId": "myproject",
    "tableId": "mypartitionedtable"
  },
  "timePartitioning": {
    "expirationMs": "86400000",
    "field": "ts",
    "requirePartitionFilter": true,
    "type": "DAY"
  },
  "type": "TABLE"
}

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

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

輸出內容應如下所示:

[
  {
    "name": "ts",
    "type": "TIMESTAMP"
  },
  {
    "name": "column1",
    "type": "STRING"
  },
  {
    "name": "column2",
    "type": "INTEGER"
  },
  {
    "name": "column3",
    "type": "STRING"
  }
]

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.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

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

列出分區資料表

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

主控台

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

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

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

傳統版 UI

  1. 在 BigQuery 網頁版 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 (field: source_date, expirationMs: 86400000) |
| myview                  | VIEW  |                      |                                                   |
+-------------------------+-------+----------------------+---------------------------------------------------+

範例:

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

bq ls --format=pretty mydataset

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

bq ls --format=pretty myotherproject:mydataset

API

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

列出分區資料表中的分區

您可以使用舊版 SQL 查詢 __PARTITIONS_SUMMARY__ 中繼資料表,列出分區資料表中的分區。

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

所需權限

您至少要具備 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 角色,請參閱存取權控管一文。

列出分區資料表中的分區

您可以使用舊版 SQL 列出分區資料表中的分區。如要列出分區資料表中的分區:

主控台

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。
    前往 GCP Console

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

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

    #legacySQL
    SELECT
      partition_id
    FROM
      [dataset.table$__PARTITIONS_SUMMARY__]
    

    其中:

    • 「dataset」是含有該資料表的資料集。
    • 「table」是資料表名稱。
  4. 按一下 [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. 按一下 [Run query] (執行查詢)

CLI

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

bq --location=location query \
--use_legacy_sql=true \
'SELECT
  partition_id
FROM
  [dataset.table$__PARTITIONS_SUMMARY__]'

其中:

  • 「location」是您位置的名稱,--location 是選用標記。舉例來說,假設您是在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案來設定位置的預設值。
  • 「dataset.table」是含有該資料表的資料集。
  • 「dataset.table」是資料表名稱。

API

呼叫 jobs.insert 方法並設定 query 工作,這項工作會查詢資料表的 __PARTITIONS_SUMMARY__ 中繼資料表。

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

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

您目前無法使用 INFORMATION_SCHEMA 服務取得分區資料表的中繼資料。

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

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

後續步驟

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

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

這個網頁