建立及使用分區資料表

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

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

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

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

限制

分區資料表有下列限制:

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

建立分區資料表

您可在 BigQuery 中建立分區資料表:

當您在 BigQuery 中建立以資料欄為基礎的時間分區資料表時,每個資料集的資料表名稱不得重複。資料表名稱可以:

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

必要權限

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

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

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

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

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

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

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

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

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

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

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

主控台

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

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

  3. 將您的 CREATE TABLE DDL 陳述式輸入到「Query editor」(查詢編輯器) 的文字區域。

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

     #standardSQL
     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. 按一下 [More] (更多) 並選取 [Query settings] (查詢設定)查詢設定

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

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

傳統版 UI

  1. 前往 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

  2. 按一下 [Compose query]

  3. 將您的 CREATE TABLE DDL 陳述式輸入到「New Query」(新查詢) 的文字區域。

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

     #standardSQL
     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. 按一下 [Show Options] (顯示選項)

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

  6. 按一下 [Run query] (執行查詢)。當查詢完成時,資料表會顯示在導覽窗格中。

指令列

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

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

如果您要在預設專案以外的專案中建立資料表,請使用下列格式將專案 ID 新增至資料集:[PROJECT_ID]:[DATASET]

本文不示範 --destination_kms_key。如要深入瞭解此標記的使用資訊,請參閱客戶管理式加密金鑰

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

bq mk --table --expiration [INTEGER1] --schema [SCHEMA] --time_partitioning_field [COLUMN] --time_partitioning_expiration [INTEGER2] --description "[DESCRIPTION]" --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE]

其中:

  • [INTEGER1] 是資料表的預設生命週期 (以秒為單位)。最小值是 3600 秒 (1 小時)。到期時間為目前時間加整數值。如果您在建立時間分區資料表時設定了資料表到期時間,系統會忽略資料集的預設資料表到期時間設定。只要設定這個值,系統就會在指定時間到期後刪除資料表和所有分區。
  • [SCHEMA] 是格式為 [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] 的內嵌結構定義,或本機上 JSON 結構定義檔的路徑。
  • [COLUMN] 是用來建立分區之 TIMESTAMPDATE 資料欄的名稱。
  • [INTEGER2] 是資料表分區的預設生命週期 (以秒為單位)。這個值沒有下限。有效期限的時間值為分區日期加整數值。分區有效期限與資料表的有效期限無關,並不會覆寫資料表有效期限。如果您設定的分區有效期限比資料表有效期限長,則以資料表有效期限為優先。
  • [DESCRIPTION] 是置於括號中的資料表說明。
  • [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

結構定義以內嵌方式指定為: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  --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

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

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 來查詢分區資料表或將查詢結果寫入分區資料表。

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

[TABLE_NAME]$20160501

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

例如:

下列查詢會將資料寫入資料表 T 的 2018 年 2 月 1 日分區。資料表有兩個資料欄 — 一個 TIMESTAMP 資料欄名為 TS,一個 INT64 資料欄名為 a。因為查詢會將時間戳記 2018-02-01 寫入分區 $20180201,所以指令能夠成功完成。查詢在 US 多區域位置執行。

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

下列查詢也會嘗試將資料寫入資料表 T,但它將時間戳記 2018-01-31 寫入到分區 $20180201。這個查詢失敗了,因為您嘗試寫入的值不在分區日期範圍之內。

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

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

主控台

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

傳統版 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] 是您的位置名稱。如果您的資料位於 USEU 多區域位置,則 --location 是選用標記。例如,如果您是在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是包含新分區資料表之資料集的名稱。
  • [TABLE] 是您正使用查詢結果建立之分區資料表的名稱。
  • [QUERY] 是使用標準 SQL 語法的查詢。目前,您無法使用舊版 SQL 查詢分區資料表或將查詢結果寫入分區資料表。

示例:

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

bq --location=US 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 Traffic Fatality 公開資料集中擷取資料。資料表的 timestamp_of_crash TIMESTAMP 資料欄可用來建立分區。

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

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

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

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

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

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

[TABLE_NAME]$20160501

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

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

API

如要在透過載入工作建立資料表時定義分區設定,您可以填入 configuration.load.timePartitioning 訊息,並針對一段時間後到期的分區,在相關分區設定中加入 configuration.load.timePartitioning.expirationMs。填入 configuration.load.timePartitioning.field 後,您就能控制資料表是要以虛擬資料欄或使用者資料欄進行分區。

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")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.StringFieldType},
	{Name: "date", Type: bigquery.DateFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field:      "date",
	Expiration: 90 * 24 * time.Hour,
}
loader.WriteDisposition = bigquery.WriteEmpty

job, err := loader.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}

if status.Err() != nil {
	return fmt.Errorf("Job completed with error: %v", status.Err())
}

Python

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

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'my_dataset'
table_id = "us_states_by_date"

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
    bigquery.SchemaField("date", "DATE"),
]
job_config.skip_leading_rows = 1
job_config.time_partitioning = bigquery.TimePartitioning(
    type_=bigquery.TimePartitioningType.DAY,
    field="date",  # name of column to use for partitioning
    expiration_ms=7776000000,
)  # 90 days
uri = "gs://cloud-samples-data/bigquery/us-states/us-states-by-date.csv"

load_job = client.load_table_from_uri(
    uri, dataset_ref.table(table_id), job_config=job_config
)  # API request

assert load_job.job_type == "load"

load_job.result()  # Waits for table load to complete.

table = client.get_table(dataset_ref.table(table_id))
print("Loaded {} rows to table {}".format(table.num_rows, table_id))

控管分區資料表的存取權

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

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

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

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

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

使用分區資料表

取得分區資料表相關資訊

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

必要權限

如要取得資料表的相關資訊,您必須取得資料集的 READER 角色,或取得具備 bigquery.tables.get 權限的專案層級 IAM 角色。如果您取得專案層級的 bigquery.tables.get 權限,就能取得專案中所有資料表的相關資訊。所有預先定義的專案層級 IAM 角色都具備 bigquery.tables.get 權限,但 bigquery.userbigquery.jobUser 除外

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

如要深入瞭解 BigQuery 中的 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 虛擬資料欄。

指令列

發出 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 而非預設專案中。

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 權限的專案層級 IAM 角色。如果您取得專案層級的 bigquery.tables.list 權限,就能列出專案中任何資料集的資料表。所有預先定義的專案層級 IAM 角色都擁有 bigquery.tables.list 權限,但 bigquery.jobUser 除外

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

列出分區資料表

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

主控台

  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 主控台、傳統 BigQuery 網頁版 UI、bq query 指令或呼叫 jobs.insert 方法並設定 query 工作。

必要權限

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

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

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

列出分區資料表中的分區

您可以使用舊版 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] (未指定),然後選擇您資料的位置。如果資料位於 USEU 多地區位置,可保留未指定的處理位置設定。如果資料位於 USEU,則會自動偵測處理位置。

  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 if your data is in the `US` or the `EU` multi-region
    location. For example, if you are using BigQuery in
    the Tokyo region, 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 方法並設定會查詢資料表的 __PARTITIONS_SUMMARY__ 中繼資料表的 query 工作。

工作資源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 權限的預先定義專案層級 IAM 角色:

您還必須取得資料集層級的 READER 角色,或取得具備 bigquery.tables.getData 權限的專案層級 IAM 角色。所有預先定義的專案層級 IAM 角色都具備 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 |
+--------------+----------------------------+

後續步驟

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

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

這個網頁