建立及使用叢集資料表

本文件說明如何在 BigQuery 中建立及使用叢集資料表。

限制

在 BigQuery 中,叢集資料表具有以下限制:

  • 目前叢集僅支援分區資料表
  • 只能透過標準 SQL 語法查詢叢集資料表,以及將查詢結果寫入叢集資料表之中。
  • 只有在資料表建立後,才可以指定所欲叢集的資料欄。
  • 當叢集資料表建立完成後,所指定的叢集資料欄即無法更改。
  • 叢集資料欄只能是下列頂層且非重複的資料欄類型:INT64STRINGDATETIMESTAMPBOOLNUMERICGEOGRAPHY。如要進一步瞭解資料類型,請參閱標準 SQL 資料類型一文。
  • 您最多可指定四個叢集資料欄。

建立叢集資料表

目前您只能夠叢集已分區完成的資料表,包含擷取時間分區資料表以及分區資料表 (即由 TIMESTAMPDATE 資料欄分區的資料表)。

您可用以下方式,在 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 中的 IAM 角色和權限,請參閱存取權控管一文。如要深入瞭解資料集層級角色,請參閱資料集的原始角色

透過結構定義建立空白叢集資料表

您在 BigQuery 中建立資料表時,應指定欲叢集的欄位。資料表建立完成後,您將無法修改叢集欄位。目前您僅能針對已分區的資料表指定欲叢集的資料欄。

叢集資料欄必須是頂層、非重複資料欄,且屬於下列任一項簡易資料類型:INTEGERSTRINGDATETIMESTAMPBOOLEANNUMERICGEOGRAPHY

您最多可指定四個叢集資料欄,當您指定多個叢集欄位時,欄位的順序將決定資料的排序方式。舉例來說,若資料表是依欄位 a、b、c 來進行叢集,則資料也會按照相同順序排序,即第一順位是欄位 a、第二順位是欄位 b、第三順位是欄位 c。按照最佳做法,最常用以進行資料篩選或匯總的欄位應排在第一順位。

叢集資料欄順序亦會影響資料查詢的效能與定價。要進一步瞭解叢集資料表查詢的最佳做法,請參閱查詢叢集資料表一文。

如要使用結構定義建立空白叢集資料表:

主控台

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

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

  3. 在視窗右側的詳細資料面板中,按一下 [Create table] (建立資料表)。載入資料的程序與建立空白資料表的程序相同。

    使用新增欄位選項來新增結構定義

  4. 在「Create table」(建立資料表) 頁面的「Source」(來源) 區段中,針對「Create table from」(使用下列資料建立資料表) 選取 [Empty table] (空白資料表)

    使用新增欄位選項來新增結構定義

  5. 在「Create table」(建立資料表) 頁面的「Destination」(目的地) 區段中:

    • 針對「Dataset name」(資料集名稱) 選擇適當的資料集,然後在「Table name」(資料表名稱) 欄位中,輸入您在 BigQuery 建立資料表時使用的名稱。

      使用新增欄位選項來新增結構定義

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

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

    • 使用以下其中一種方式,手動輸入結構定義資訊:

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

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

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

    • 針對「Partitioning」(分區),選取 [Partition by ingestion time] (依擷取時間分區)。

      使用新增欄位選項來新增結構定義

    • 針對「Clustering order」(分群順序),輸入一到四個以半形逗號分隔的欄位名稱。

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

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

傳統版 UI

  1. 使用傳統 BigQuery 網頁版 UI

    前往傳統 BigQuery 網頁版 UI

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

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

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

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

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

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

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

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

    • 針對「Partitioning Type」(分區類型),按一下 [None] (無),接著選擇 [Day] (日)
    • 針對「Partitioning Field」(分區欄位),選擇下列其中一個選項:
      • 選擇 timestamp 建立依「日期」或「時間戳記」資料欄分區的資料表。
      • 選擇 _PARTITIONTIME 建立擷取時間分區資料表。
    • 針對「Clustering columns」(叢集資料欄),輸入一到四個欄位名稱。
    • 在「Encryption Type」(加密類型) 中保留 Default 選項。這個屬性適用於客戶管理的加密金鑰。根據預設,BigQuery 會加密靜態儲存的客戶內容

      分區資料表詳細資料

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

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

指令列

使用 mk 指令搭配下列標記:

  • --table (或是 -t 縮寫)。
  • --schema &mdash:您可利用內嵌方式或以 JSON 結構定義檔提供資料表結構定義。
  • --time_partitioning_type (用於擷取時間分區資料表) 或 --time_partitioning_field (用於分區資料表)。目前 --time_partitioning_type 唯一支援的值是 DAY
  • --clustering_fields 可以指定最多四個叢集欄位。

選用參數包括 --expiration--description--time_partitioning_expiration--destination_kms_key--label

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

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

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

bq mk --table --expiration [INTEGER1] --schema [SCHEMA] --time_partitioning_type=DAY --time_partitioning_field [COLUMN] --clustering_fields [COLUMNS] --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 資料欄名稱。若您建立分區資料表,則無須指定 --time_partitioning_type=DAY 標記。
  • [COLUMNS] 是一種以逗號分隔的列表,最多包含四個叢集欄位。
  • [INTEGER2] 是資料表分區的預設生命週期 (以秒為單位)。這個值沒有下限。有效期限的時間值為分區日期加整數值。分區有效期限與資料表的有效期限無關,並不會覆寫資料表有效期限。如果您設定的分區有效期限比資料表有效期限長,則以資料表有效期限為優先。
  • [DESCRIPTION] 是置於括號中的資料表說明。
  • [KEY:VALUE] 是代表標籤的鍵/值組合。您可以輸入多個標籤,中間以半形逗號隔開。
  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是專案中的資料集。
  • [TABLE] 是您正在建立之分區資料表的名稱。

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

範例:

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

結構定義以內嵌方式指定,例如:timestamp:timestamp,customer_id:string,transaction_amount:float,所指定的叢集欄位 customer_id 用來叢集分區之用。

bq mk -t --expiration 2592000 --schema 'timestamp:timestamp,customer_id:string,transaction_amount:float' --time_partitioning_field timestamp --clustering_fields customer_id --time_partitioning_expiration 86400  --description "This is my clustered table" --label org:dev mydataset.myclusteredtable

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

結構定義在本機 JSON 檔案 /tmp/myschema.json 中指定。customer_id 欄位用於叢集分區。

bq mk -t --expiration 2592000 --schema /tmp/myschema.json --time_partitioning_type=DAY --clustering_fields=customer_id --time_partitioning_expiration 86400  --description "This is my partitioned table" --label org:dev myotherproject:mydataset.myclusteredtable

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

API

使用指定 timePartitioningclustering.fieldsschema 屬性的已定義資料表資源呼叫 tables.insert 方法。

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: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
metaData := &bigquery.TableMetadata{
	Schema: sampleSchema,
	TimePartitioning: &bigquery.TimePartitioning{
		Field:      "timestamp",
		Expiration: 90 * 24 * time.Hour,
	},
	Clustering: &bigquery.Clustering{
		Fields: []string{"origin", "destination"},
	},
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

從查詢結果建立叢集資料表

您可以透過下列兩種方式,從查詢結果建立叢集資料表:

  • 將結果寫入新的目的地資料表並指定叢集資料欄,這個方法將於以下探討。
  • 使用 DDL CREATE TABLE AS SELECT 的陳述式語法。如要進一步瞭解此做法,請參閱「使用資料定義語言陳述式」頁面中的從查詢結果建立叢集資料表一節。

您可以藉由查詢分區資料表或非分區資料表的方式建立叢集資料表,但不能利用查詢結果將現有的資料表變更為叢集資料表。

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

主控台

使用主控台 BigQuery 網頁版 UI 查詢資料時,不能指定目的地資料表的叢集選項,除非您使用 DDL 陳述式。詳情請參閱使用資料定義語言陳述式一文。

傳統版 UI

使用 BigQuery 傳統網頁版 UI 查詢資料時,不能指定目的地資料表的叢集選項,除非您使用 DDL 陳述式。詳情請參閱使用資料定義語言陳述式一文。

CLI 指令列介面

輸入 bq query 指令並指定下列標記:

  • 指定 use_legacy_sql=false 標記以使用標準 SQL 語法。
  • 提供 --location 標記,並將值設為您的位置

輸入下列指令,從查詢結果中建立新的叢集目的地資料表:

    bq --location=[LOCATION] query --use_legacy_sql=false '[QUERY]'

其中:

  • [LOCATION] 是您的位置名稱,--location 是選用標記。例如,如果您在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • [QUERY] 是使用標準 SQL 語法的查詢。目前您無法使用舊版 SQL 查詢叢集資料表或是將查詢結果寫入叢集資料表。該查詢可包含 CREATE TABLE DDL 陳述式,以指定建立叢集資料表的選項。您可以使用 DDL 取代指定個別指令列標記的做法。

範例:

輸入下列指令,將查詢結果寫入 mydataset 中名稱為 myclusteredtable 的叢集目的地資料表,mydataset 位於您的預設專案內。查詢會從非分區資料表 mytable 中擷取資料,資料表的 customer_id 資料欄用於叢集資料表;資料表的 timestamp 資料欄用於建立分區資料表。

bq --location=US query --use_legacy_sql=false 'CREATE TABLE mydataset.myclusteredtable PARTITION BY DATE(timestamp) CLUSTER BY customer_id AS SELECT * FROM mydataset.mytable'

API

如要將查詢結果儲存到叢集資料表,請呼叫 jobs.insert 方法、設定 query 工作,然後加入可建立叢集資料表的 CREATE TABLE DDL 陳述式。

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

於載入資料時建立叢集資料表

您載入資料到一個新的資料表時,可透過指定叢集欄的方式來建立叢集資料表。您不必在資料載入資料表之前,事先建立空白的資料表,因為您可以同時建立叢集資料表並載入資料。

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

如要在定義載入工作時定義叢集,請進行以下操作:

API

如要在透過載入工作建立資料表時定義叢集設定,您可以填入 configuration.load.clustering 訊息,並依照優先順序使用最多四個叢集資料欄填入 configuration.load.clustering.Fields 屬性。

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/sample-transactions/transactions.csv")
gcsRef.SkipLeadingRows = 1
gcsRef.Schema = bigquery.Schema{
	{Name: "timestamp", Type: bigquery.TimestampFieldType},
	{Name: "origin", Type: bigquery.StringFieldType},
	{Name: "destination", Type: bigquery.StringFieldType},
	{Name: "amount", Type: bigquery.NumericFieldType},
}
loader := client.Dataset(destDatasetID).Table(destTableID).LoaderFrom(gcsRef)
loader.TimePartitioning = &bigquery.TimePartitioning{
	Field: "timestamp",
}
loader.Clustering = &bigquery.Clustering{
	Fields: []string{"origin", "destination"},
}
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())
}

控管叢集資料表的存取權

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

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

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

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

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

使用叢集資料表

取得叢集資料表的相關資訊

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

  • 使用 GCP 主控台或傳統 BigQuery 網頁版 UI
  • 使用 bq show CLI 指令
  • 呼叫 tables.get API 方法
  • 查詢 INFORMATION_SCHEMA 檢視表 (Beta 版)

所需權限

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

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

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

獲得叢集資料表資訊

如要查看叢集資料表的相關資訊:

主控台

  1. 在「Resources」(資源) 窗格中,按一下資料集名稱,即可展開該資料集,然後點選您想要檢視的資料表名稱。

  2. 按一下 [Details] (詳細資料)。這個頁面會顯示包含叢集資料欄的資料表詳細資料。

    資料表詳細資料

傳統版 UI

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

  2. 按一下資料表名稱。

  3. 按一下 [Details] (詳細資料)。「Table Details」(資料表詳細資料) 頁面會顯示包含叢集資料欄的資料表詳細資料。

    叢集資料表細節

指令列

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

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

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

其中:

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

範例:

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

bq show --format=prettyjson mydataset.myclusteredtable

輸出應如下所示:

{
  "clustering": {
    "fields": [
      "customer_id"
    ]
  },
...
}

API

呼叫 bigquery.tables.get 方法,並提供所有相關參數。

使用 INFORMATION_SCHEMA 取得叢集資料表資訊 (Beta 版)

INFORMATION_SCHEMA 是一系列檢視畫面,可提供資料集、資料表與檢視畫面相關中繼資料的存取權。

您可以查詢 INFORMATION_SCHEMA.TABLESINFORMATION_SCHEMA.TABLE_OPTIONS 檢視畫面,藉以擷取專案中資料表與檢視畫面的相關中繼資料,也可以查詢 INFORMATION_SCHEMA.COLUMNSINFORMATION_SCHEMA.COLUMN_FIELD_PATHS 檢視畫面,以擷取資料表中資料欄 (欄位) 的相關中繼資料。

對於叢集資料表,您可以在 INFORMATION_SCHEMA.COLUMNS 檢視畫面中查詢 CLUSTERING_ORDINAL_POSITION 資料欄,以擷取叢集資料欄的相關資訊。

TABLES 檢視畫面

當您查詢 INFORMATION_SCHEMA.TABLES 檢視畫面時,系統會針對資料集裡的每個資料表或檢視畫面,分別在查詢結果中列出一個相對應的資料列。

INFORMATION_SCHEMA.TABLES 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表或檢視畫面的資料集。

INFORMATION_SCHEMA.TABLES 檢視畫面具有下列結構定義:

資料欄名稱 資料類型
TABLE_CATALOG STRING 包含資料集的專案名稱
TABLE_SCHEMA STRING 包含資料表或檢視表的資料集名稱 (又稱為 datasetId)
TABLE_NAME STRING 資料表或檢視表的名稱 (又稱為 tableId)
TABLE_TYPE STRING 資料表類型:
IS_INSERTABLE_INTO STRING YESNO,視資料表是否支援 DML INSERT 陳述式而定
IS_TYPED STRING 此值一律為 NO
CREATION_TIME TIMESTAMP 資料表的建立時間

如要進一步瞭解資料集屬性,請參閱 REST API 說明文件中的資料集資源頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的資料表資源頁面。

示例

範例 1:

下列範例從 INFORMATION_SCHEMA.TABLES 檢視畫面擷取了所有資料欄,但保留 is_typed 以供未來使用。系統傳回的是預設專案 myprojectmydataset 之下所有資料表的中繼資料。

mydataset 包含下列資料表:

  • mytable1:標準 BigQuery 資料表
  • myview1:BigQuery 檢視畫面

INFORMATION_SCHEMA.TABLES 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表的資料集。

如要對預設專案以外的專案執行查詢,請使用下列格式將專案 ID 新增到資料集:`[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] ,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES

如要執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP 主控台的預設語法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    
  3. 按一下 [Run] (執行)

指令列

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES'

結果如下所示:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | YES                | 2018-10-29 20:34:44 |
  | myproject      | mydataset     | myview1        | VIEW       | NO                 | 2018-12-29 00:19:20 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

範例 2:

下列範例會從 INFORMATION_SCHEMA.TABLES 檢視畫面擷取 BASE TABLE 類型的所有資料表,但會排除 is_typed 資料欄。系統傳回的是預設專案 (myproject) 中 mydataset 內的所有資料表中繼資料。

INFORMATION_SCHEMA.TABLES 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表的資料集。

如要對預設專案以外的專案執行查詢,請使用下列格式將專案 ID 新增到資料集:`[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] ,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLES

如要執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP 主控台的預設語法。

    SELECT
     * EXCEPT(is_typed)
    FROM
     mydataset.INFORMATION_SCHEMA.TABLES
    WHERE
     table_type="BASE TABLE"
    
  3. 按一下 [Run] (執行)

指令列

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_typed) FROM mydataset.INFORMATION_SCHEMA.TABLES
WHERE table_type="BASE TABLE"'

結果如下所示:

  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | table_catalog  | table_schema  |   table_name   | table_type | is_insertable_into |    creation_time    |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  | myproject      | mydataset     | mytable1       | BASE TABLE | NO                 | 2018-10-31 22:40:05 |
  +----------------+---------------+----------------+------------+--------------------+---------------------+
  

TABLE_OPTIONS 檢視畫面

當您查詢 INFORMATION_SCHEMA.TABLE_OPTIONS 檢視畫面時,系統會針對資料集內每個資料表或檢視畫面,分別列出一個相對應的資料列在查詢結果中。

INFORMATION_SCHEMA.TABLE_OPTIONS 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表或檢視畫面的資料集。

INFORMATION_SCHEMA.TABLE_OPTIONS 檢視畫面具有下列結構定義:

資料欄名稱 資料類型
TABLE_CATALOG STRING 包含資料集的專案名稱
TABLE_SCHEMA STRING 包含資料表或檢視表的資料集名稱 (又稱為 datasetId)
TABLE_NAME STRING 資料表或檢視表的名稱 (又稱為 tableId)
OPTION_NAME STRING 選項表格中的其中一個名稱值
OPTION_TYPE STRING 選項表格中的其中一個資料類型值
OPTION_VALUE STRING 選項表格中的其中一個值選項
選項表格
OPTION_NAME OPTION_TYPE OPTION_VALUE
partition_expiration_days FLOAT64 分區資料表中所有分區的預設生命週期 (以天為單位)
expiration_timestamp FLOAT64 資料表的預設生命週期 (以天為單位)
kms_key_name STRING 用來加密資料表的 Cloud KMS 金鑰名稱
friendly_name STRING 資料表的描述性名稱
description STRING 資料表的說明
labels ARRAY<STRUCT<STRING, STRING>> 代表資料表標籤的 STRUCT 陣列

如要進一步瞭解資料集屬性,請參閱 REST API 說明文件中的資料集資源頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的資料表資源頁面。

示例

示例 1:

以下示例查詢 INFORMATION_SCHEMATA.TABLE_OPTIONS 檢視畫面,以擷取預設專案 (myproject) 的 mydataset 中所有資料表的預設資料表到期時間。

INFORMATION_SCHEMA.TABLE_OPTIONS 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表的資料集。

如要對預設專案以外的專案執行查詢,請使用下列格式將專案 ID 新增到資料集:`[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] ,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS

如要執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP 主控台的預設語法。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="expiration_timestamp"
    
  3. 按一下 [Run] (執行)

指令列

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="expiration_timestamp"'

結果如下所示:

  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | table_catalog  | table_schema  | table_name |     option_name      | option_type |             option_value             |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  | myproject      | mydataset     | mytable1   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2020-01-16T21:12:28.000Z" |
  | myproject      | mydataset     | mytable2   | expiration_timestamp | TIMESTAMP   | TIMESTAMP "2021-01-01T21:12:28.000Z" |
  +----------------+---------------+------------+----------------------+-------------+--------------------------------------+
  

示例 2:

以下示例擷取了 mydataset 中包含測試資料的所有資料表相關中繼資料。此查詢使用 description 選項中的值來尋找在說明中任意位置包含「測試」的資料表。mydataset 在您的預設專案 (myproject) 中。

如要對預設專案以外的專案執行查詢,請使用下列格式將專案 ID 新增到資料集:`[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] ,例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS

如要執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP 主控台的預設語法。

    SELECT
     *
    FROM
     mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
    WHERE
     option_name="description" AND option_value LIKE "%test%"
    
  3. 按一下 [Run] (執行)

指令列

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT * FROM mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS
WHERE option_name="description" AND option_value LIKE "%test%"'

結果如下所示:

  +----------------+---------------+------------+-------------+-------------+--------------+
  | table_catalog  | table_schema  | table_name | option_name | option_type | option_value |
  +----------------+---------------+------------+-------------+-------------+--------------+
  | myproject      | mydataset     | mytable1   | description | STRING      | "test data"  |
  | myproject      | mydataset     | mytable2   | description | STRING      | "test data"  |
  +----------------+---------------+------------+-------------+-------------+--------------+
  

COLUMNS 檢視畫面

當您查詢 INFORMATION_SCHEMA.COLUMNS 檢視畫面時,系統會針對資料表中的每個資料欄 (欄位),分別列出一個相對應的資料列在查詢結果中。

INFORMATION_SCHEMA.COLUMNS 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表的資料集。

INFORMATION_SCHEMA.COLUMNS 檢視畫面具有下列結構定義:

資料欄名稱 資料類型
TABLE_CATALOG STRING 包含資料集的專案名稱
TABLE_SCHEMA STRING 包含資料表的資料集名稱 (又稱為 datasetId)
TABLE_NAME STRING 資料表或檢視表的名稱 (又稱為 tableId)
COLUMN_NAME STRING 資料欄的名稱
ORDINAL_POSITION INT64 資料表中資料欄的 1 索引偏移。如果是虛擬資料欄,例如 _PARTITIONTIME 或 _PARTITIONDATE,則值為 NULL
IS_NULLABLE STRING YESNO,視資料欄的模式是否允許使用 NULL 值而定
DATA_TYPE STRING 資料欄的標準 SQL 資料類型
IS_GENERATED STRING 此值一律為 NEVER
GENERATION_EXPRESSION STRING 此值一律為 NULL
IS_STORED STRING 此值一律為 NULL
IS_HIDDEN STRING YESNO,視資料欄是否為虛擬資料欄 (例如 _PARTITIONTIME 或 _PARTITIONDATE) 而定
IS_UPDATABLE STRING 此值一律為 NULL
IS_SYSTEM_DEFINED STRING YESNO,視資料欄是否為虛擬資料欄 (例如 _PARTITIONTIME 或 _PARTITIONDATE) 而定
IS_PARTITIONING_COLUMN STRING YESNO,視資料欄是否為分區資料欄而定
CLUSTERING_ORDINAL_POSITION STRING 在資料表的叢集資料欄中,資料欄的 1 索引偏移。如果資料表不是叢集資料表,則此值為 NULL

如要進一步瞭解資料集屬性,請參閱 REST API 說明文件中的資料集資源頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的資料表資源頁面。

示例

以下示例會針對 census_bureau_usa 資料集裡的 population_by_zip_2010 資料表,從 INFORMATION_SCHEMA.COLUMNS 檢視表中擷取中繼資料。這個資料集是 BigQuery 公開資料集方案的一部分。

由於這個資料表位於 bigquery-public-data 專案中,您應使用下列格式將專案 ID 新增到資料集:`[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW] ,例如 `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.TABLES

目前以下資料欄會從查詢結果中排除,保留供未來使用:

  • IS_GENERATED
  • GENERATION_EXPRESSION
  • IS_STORED
  • IS_UPDATABLE

INFORMATION_SCHEMA.COLUMNS 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表的資料集。

執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP 主控台的預設語法。

    SELECT
     * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
    FROM
     `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
    WHERE
     table_name="population_by_zip_2010"
    
  3. 按一下 [Run] (執行)

指令列

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT * EXCEPT(is_generated, generation_expression, is_stored, is_updatable)
FROM `bigquery-public-data`.census_bureau_usa.INFORMATION_SCHEMA.COLUMNS
WHERE table_name="population_by_zip_2010"'

結果如下所示。為了讓您容易閱讀,我們已從結果中排除 table_catalogtable_schema

+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
|       table_name       | column_name | ordinal_position | is_nullable | data_type | is_hidden | is_system_defined | is_partitioning_column | clustering_ordinal_position |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
| population_by_zip_2010 | zipcode     |                1 | NO          | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | geo_id      |                2 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | minimum_age |                3 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | maximum_age |                4 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | gender      |                5 | YES         | STRING    | NO        | NO                | NO                     |                        NULL |
| population_by_zip_2010 | population  |                6 | YES         | INT64     | NO        | NO                | NO                     |                        NULL |
+------------------------+-------------+------------------+-------------+-----------+-----------+-------------------+------------------------+-----------------------------+
  

COLUMN_FIELD_PATHS 檢視畫面

當您查詢 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 檢視表時,系統會針對 RECORD (或 STRUCT) 資料欄巢狀結構中的每個資料欄,在查詢結果中列出每個相對應的資料列。

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表的資料集。

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 檢視畫面具有下列結構定義:

資料欄名稱 資料類型
TABLE_CATALOG >STRING 包含資料集的專案名稱
TABLE_SCHEMA STRING 包含資料表的資料集名稱 (又稱為 datasetId)
TABLE_NAME STRING 資料表或檢視表的名稱 (又稱為 tableId)
COLUMN_NAME STRING 資料欄的名稱
FIELD_PATH STRING 「RECORD」(或「STRUCT」) 資料欄巢狀結構中資料欄的路徑
DATA_TYPE STRING 資料欄的標準 SQL 資料類型
DESCRIPTION STRING 資料欄的說明

如要進一步瞭解資料集屬性,請參閱 REST API 說明文件中的資料集資源頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的資料表資源頁面。

示例

以下示例會針對 github_repos 資料集裡的 commits 資料表,從 INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 檢視表中擷取中繼資料。這個資料集是 BigQuery 公開資料集方案的一部分。

由於這個資料表位於 bigquery-public-data 專案中,您應使用下列格式將專案 ID 新增到資料集:`[PROJECT_ID]`.[DATASET].INFORMATION_SCHEMA.[VIEW],例如 `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS

commits 資料表中包含下列巢狀結構與巢狀和重複結構的資料欄:

  • author:巢狀結構 RECORD 資料欄
  • committer:巢狀結構 RECORD 資料欄
  • trailer:巢狀和重複結構 RECORD 資料欄
  • difference:巢狀和重複結構 RECORD 資料欄

您的查詢將會擷取 authordifference 資料欄的相關中繼資料。

INFORMATION_SCHEMA.COLUMN_FIELD_PATHS 檢視畫面的查詢必須具有資料集限定詞。提交查詢的使用者必須能夠存取包含資料表的資料集。

如要執行查詢:

主控台

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

    前往 BigQuery 網頁版 UI

  2. 在「Query editor」(查詢編輯器) 方塊中輸入下列標準 SQL 查詢。 INFORMATION_SCHEMA 需要標準 SQL 語法。標準 SQL 是 GCP 主控台的預設語法。

    SELECT
     *
    FROM
     `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
    WHERE
     table_name="commits"
     AND column_name="author"
     OR column_name="difference"
    
  3. 按一下 [Run] (執行)

指令列

使用 query 指令並透過 --nouse_legacy_sql--use_legacy_sql=false 標記指定標準 SQL 語法。INFORMATION_SCHEMA 查詢需要標準 SQL 語法。

如要執行查詢,請輸入以下指令:

bq query --nouse_legacy_sql \
'SELECT * FROM `bigquery-public-data`.github_repos.INFORMATION_SCHEMA.COLUMN_FIELD_PATHS
WHERE table_name="commits" AND column_name="author" OR column_name="difference"'

結果如下所示。為了讓您容易閱讀,我們已從結果中排除 table_catalogtable_schema

  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | table_name | column_name |     field_path      |                                                                      data_type                                                                      | description |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  | commits    | author      | author              | STRUCT<name STRING, email STRING, time_sec INT64, tz_offset INT64, date TIMESTAMP>                                                                  | NULL        |
  | commits    | author      | author.name         | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.email        | STRING                                                                                                                                              | NULL        |
  | commits    | author      | author.time_sec     | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.tz_offset    | INT64                                                                                                                                               | NULL        |
  | commits    | author      | author.date         | TIMESTAMP                                                                                                                                           | NULL        |
  | commits    | difference  | difference          | ARRAY<STRUCT<old_mode INT64, new_mode INT64, old_path STRING, new_path STRING, old_sha1 STRING, new_sha1 STRING, old_repo STRING, new_repo STRING>> | NULL        |
  | commits    | difference  | difference.old_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.new_mode | INT64                                                                                                                                               | NULL        |
  | commits    | difference  | difference.old_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_path | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_sha1 | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.old_repo | STRING                                                                                                                                              | NULL        |
  | commits    | difference  | difference.new_repo | STRING                                                                                                                                              | NULL        |
  +------------+-------------+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------+-------------+
  

在資料集裡列出叢集資料表

您可以使用 GCP 主控台、傳統 BigQuery 網頁版 UI、bq ls CLI 指令,或是呼叫 tables.list API 方法來列出資料集裡的叢集資料表。

列出叢集資料表須具備的權限以及列出該資料表的步驟與處理分區資料表時相同,如要進一步瞭解如何列出資料表,請參閱在資料集中列出分區資料表一節。

開發中的功能

下列功能正在開發中,目前在 Alpha 版中無法使用:

  • 支援原生資料表 (非分區) 的叢集。
  • 減少在叢集資料欄使用篩選器的特定類型查詢成本。

後續步驟

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

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

這個網頁