建立與使用資料表

本文件說明如何在 BigQuery 中建立及使用資料表。建立資料表之後,您可以執行下列作業:

  • 控管資料表資料的存取權
  • 取得有關資料表的資訊
  • 列出資料集中的資料表
  • 取得資料表中繼資料

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

資料表限制

BigQuery 資料表有以下限制:

  • 資料表名稱不得在資料集中重複。
  • 主控台與傳統 BigQuery 網頁版 UI 僅支援一次複製一個資料表。
  • 複製資料表時,目標資料集必須和正在複製的資料表位於同一個位置。例如,您無法將位於歐盟資料集的資料表複製到位於美國的資料集。
  • 使用 CLI 或 API 複製多個來源資料表到目標資料表時,所有來源資料表都必須具有相同的結構定義。
  • 使用主控台、傳統 BigQuery 網頁版 UI、指令列工具或 API 時,一次只能刪除一個資料表。
  • 匯出資料表資料時,唯一支援的目的地是 Cloud Storage。
  • 當資料集中的資料表數目接近 50,000 或更多數目時,列舉資料表的速度會變慢。無論您使用 API 呼叫還是傳統 BigQuery 網頁版 UI,列舉效能都會受到影響。GCP 主控台中的 BigQuery 網頁版 UI 目前僅允許您每個資料集顯示 50,000 個資料表。

    如要改善傳統 BigQuery 網頁版 UI 效能,您可以使用 ?minimal 參數,將顯示的資料表數量限制為每個專案 30,000 個資料表。您可以按照以下格式,將參數新增至傳統 BigQuery 網頁版 UI 網址:https://bigquery.cloud.google.com/queries/[PROJECT_NAME]?minimal

建立資料表

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

  • 手動使用 GCP 主控台、傳統 BigQuery 網頁版 UI 或指令列工具的 bq mk 指令建立資料表
  • 呼叫 tables.insert API 方法,透過程式建立資料表
  • 從查詢結果建立資料表
  • 透過定義參照外部資料來源的資料表
  • 載入資料時建立資料表

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

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

所需權限

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

此外,因為 bigquery.user 角色具備 bigquery.datasets.create 權限,所以經指派為 bigquery.user 角色的使用者可以在自己建立的任何資料集中建立資料表。當取得 bigquery.user 角色的使用者建立資料集時,該使用者即被授予該資料集的 OWNER 存取權。 OWNER 存取權可讓使用者完全控管該資料集。

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

使用結構定義建立空白資料表

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

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

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

您可以在建立資料表後,將資料載入資料表,或透過寫入查詢結果填入資料。

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

主控台

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

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

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

    建立資料表

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

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

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

      選取資料集

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

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

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

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

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

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

  7. 在「Advanced options」(進階選項) 區段中,保留預設值。

  8. 在「Advanced options」(進階選項) 區段中選取適合的項目,然後按一下 [Create table] (建立資料表)。如要瞭解可用的選項,請參閱 JSON 選項一節。

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

傳統版 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] (建立資料表)

指令列

使用 mk 指令並搭配使用 --table-t 標記。您可以透過內嵌方式或透過 JSON 結構定義檔提供資料表結構定義資訊。選用參數包括 --expiration--description--time_partitioning_type--destination_kms_key--label。如果您要在預設專案以外的專案中建立資料表,請使用下列格式將專案 ID 新增至資料集:[PROJECT_ID]:[DATASET]

本文不示範 --time_partitioning_type--destination_kms_key。如要深入瞭解 --time_partitioning_type,請參閱擷取時間分區資料表分區資料表。如要深入瞭解 --destination_kms_key,請參閱客戶代管加密金鑰

如要在含結構定義的現有資料集中建立空白的資料表,請輸入:

bq mk --table --expiration [INTEGER] --description [DESCRIPTION] --label [KEY:VALUE, KEY:VALUE] [PROJECT_ID]:[DATASET].[TABLE] [SCHEMA]

其中:

  • [INTEGER] 是資料表的預設生命週期 (以秒為單位)。最小值是 3600 秒 (1 小時)。到期時間為目前時間加整數值。如果您在建立資料表時設定了資料表的到期時間,則系統會忽略資料集的預設資料表到期時間設定。
  • [DESCRIPTION] 是置於括號中的資料表說明。
  • [KEY:VALUE] 是代表標籤的鍵/值組合。您可以輸入多個標籤,中間以半形逗號隔開。
  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是專案中的資料集。
  • [TABLE] 是您正在建立之資料表的名稱。
  • [SCHEMA] 是格式為 [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE] 的內嵌結構定義,或本機上 JSON 結構定義檔的路徑。

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

示例:

輸入以下指令,建立使用內嵌結構定義的資料表。這個指令會在您預設專案的 mydataset 中建立一個名為 mytable 的資料表。資料表到期時間設為 3600 秒 (1 小時)、說明設為 This is my table,並且標籤設為 organization:development。指令使用 -t 捷徑而非 --table。結構定義以內嵌方式指定,如:qtr:STRING,sales:FLOAT,year:STRING

bq mk -t --expiration 3600 --description "This is my table" --label organization:development mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

輸入下列指令,建立使用 JSON 結構定義檔的資料表。這個指令會在您預設專案的 mydataset 中建立一個名為 mytable 的資料表。資料表到期時間設為 3600 秒 (1 小時)、說明設為 This is my table,並且標籤設為 organization:development。結構定義檔的路徑為 /tmp/myschema.json

bq mk --table --expiration 3600 --description "This is my table" --label organization:development mydataset.mytable /tmp/myschema.json

輸入以下指令,建立使用 JSON 結構定義檔的資料表。這個指令會在 myotherprojectmydataset 中建立一個名為 mytable 的資料表。資料表到期時間設為 3600 秒 (1 小時)、說明設為 This is my table,並且標籤設為 organization:development。結構定義檔的路徑為 /tmp/myschema.json

bq mk --table --expiration 3600 --description "This is my table" --label organization:development myotherproject:mydataset.mytable /tmp/myschema.json

您可以在建立資料表後,更新資料表的到期時間說明標籤。您也可以修改結構定義

API

使用已定義的資料表資源呼叫 tables.insert 方法。

C#

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

using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryCreateTable
{
    public BigQueryTable CreateTable(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var dataset = client.GetDataset(datasetId);
        // Create schema for new table.
        var schema = new TableSchemaBuilder
        {
            { "full_name", BigQueryDbType.String },
            { "age", BigQueryDbType.Int64 }
        }.Build();
        // Create the table
        return dataset.CreateTable(tableId: "your_table_id", schema: schema);
    }
}

Go

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

sampleSchema := bigquery.Schema{
	{Name: "full_name", Type: bigquery.StringFieldType},
	{Name: "age", Type: bigquery.IntegerFieldType},
}

metaData := &bigquery.TableMetadata{
	Schema:         sampleSchema,
	ExpirationTime: time.Now().AddDate(1, 0, 0), // Table will be automatically deleted in 1 year.
}
tableRef := client.Dataset(datasetID).Table(tableID)
if err := tableRef.Create(ctx, metaData); err != nil {
	return err
}

Java

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

TableId tableId = TableId.of(datasetName, tableName);
// Table field definition
Field field = Field.of(fieldName, LegacySQLTypeName.STRING);
// Table schema definition
Schema schema = Schema.of(field);
TableDefinition tableDefinition = StandardTableDefinition.of(schema);
TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
Table table = bigquery.create(tableInfo);

Node.js

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

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');

async function createTable() {
  // Creates a new table named "my_table" in "my_dataset".

  /**
   * TODO(developer): Uncomment the following lines before running the sample
   */
  // const datasetId = "my_new_dataset";
  // const tableId = "my_new_table";
  // const schema = "Name:string, Age:integer, Weight:float, IsMagic:boolean";

  // Create a client
  const bigqueryClient = new BigQuery();

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
    location: 'US',
  };

  // Create a new table in the dataset
  const [table] = await bigqueryClient
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);
}
createTable();

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId = 'The Google project ID';
// $datasetId = 'The BigQuery dataset ID';
// $tableId = 'The BigQuery table ID';
// $fields = [
//    [
//        'name' => 'field1',
//        'type' => 'string',
//        'mode' => 'required'
//    ],
//    [
//        'name' => 'field2',
//        'type' => 'integer'
//    ],
//];

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$schema = ['fields' => $fields];
$table = $dataset->createTable($tableId, ['schema' => $schema]);
printf('Created table %s' . PHP_EOL, $tableId);

Python

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

from google.cloud import bigquery

schema = [
    bigquery.SchemaField("full_name", "STRING", mode="REQUIRED"),
    bigquery.SchemaField("age", "INTEGER", mode="REQUIRED"),
]

# TODO(developer): Construct a BigQuery client object.
# client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the table to create
# table_id = "your-project.your_dataset.your_table_name"

table = bigquery.Table(table_id, schema=schema)
table = client.create_table(table)  # API request
print(
    "Created table {}.{}.{}".format(table.project, table.dataset_id, table.table_id)
)

Ruby

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

require "google/cloud/bigquery"

def create_table dataset_id = "my_dataset"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id
  table_id = "my_table"

  table = dataset.create_table table_id do |updater|
    updater.string  "full_name", mode: :required
    updater.integer "age",       mode: :required
  end

  puts "Created table: #{table_id}"
end

從查詢結果建立資料表

如要從查詢結果建立資料表,請將結果寫入目標資料表。

主控台

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

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

  3. 如果查詢編輯器處於隱藏狀態,請按一下視窗右上方的 [Show editor] (顯示編輯器)

  4. 在「Query editor」(查詢編輯器) 文字區域中輸入有效的 SQL 查詢。

  5. 按一下編輯器下方的 [More] (更多),然後選取 [Query settings] (查詢設定)

    查詢設定

  6. 勾選 [Set a destination table for query results] (為查詢結果設定目標資料表) 方塊。

    設定目的地

  7. 在「Destination」(目的地) 部分,選取建立資料表所在的適當 [Project name] (專案名稱) 與 [Dataset name] (資料集名稱),並選擇 [Table name] (資料表名稱)

  8. 在「Destination table write preference」(目標資料表寫入偏好設定) 部分,選擇下列其中一項:

    • Write if empty (空白時寫入) — 僅在資料表為空白時將查詢結果寫入資料表。
    • Append to table (附加到資料表中) — 將查詢結果附加到現有的資料表。
    • Overwrite table (覆寫資料表) — 使用查詢結果覆寫名稱相同的現有資料表。
  9. (選用) 在「Processing location」(處理位置) 中,按一下 [Auto-select] (自動選取),然後選擇您資料的位置

  10. 按一下 [Run query] (執行查詢),這會建立一個查詢工作,將查詢結果寫入您指定的資料表。

如果您在執行查詢前忘記指定目的地資料表,也可以按一下編輯器下方的 [Save View] (儲存為資料檢視) 按鈕,將暫時性資料表複製到永久性資料表。

傳統版 UI

選項 1:使用 DDL 陳述式

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

詳情請參閱 CREATE TABLE 陳述式頁面與 CREATE TABLE 範例:從現有資料表建立新資料表

選項 2:使用傳統網頁版 UI

  1. 使用傳統 BigQuery 網頁版 UI
    前往傳統 BigQuery 網頁版 UI

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

  3. 在「New Query」(新查詢) 文字區域中輸入有效的 SQL 查詢。

  4. 按一下 [Show Options] (顯示選項)

  5. 在「Destination Table」(目標資料表) 部分按一下 [Select Table] (選取資料表)

  6. 在「Select Destination Table」(選取目標資料表) 對話方塊中:

    1. 在「Project」(專案) 部分,選擇要在其中建立目標資料表的專案。

    2. 在「Dataset」(資料集) 部分,選擇要儲存資料表的資料集。

    3. 在「Table ID」(資料表 ID)欄位,輸入資料表名稱。目標資料集中的資料表名稱不得重複。資料表名稱不得超過 1,024 個字元,並且只能包含 a-z、A-Z、0-9 或 _ (底線字元)。

    4. 按一下 [OK] (確定)

  7. 在「Destination Table」(目標資料表) 區段中的「Write Preference」(寫入偏好設定) 部分,選擇以下其中一項:

    • Write if empty (空白時寫入) — 僅在資料表為空白時將查詢結果寫入資料表。
    • Append to table (附加到資料表中) — 將查詢結果附加到現有的資料表。
    • Overwrite table (覆寫資料表) — 使用查詢結果覆寫名稱相同的現有資料表。
  8. (選用) 在「Processing Location」(處理位置) 中,按一下 [Unspecified] (未指定),然後選擇您資料的位置

  9. 按一下 [Run query] (執行查詢),這會建立一個查詢工作,將查詢結果寫入您指定的資料表。

如果您在執行查詢前忘記指定目的地資料表,也可以按一下結果視窗中的 [Save as Table] (另存為資料表) 按鈕,將臨時資料表複製到永久性資料表。

CLI

輸入 bq query 指令並指定 --destination_table 標記,根據查詢結果建立永久性資料表。指定 use_legacy_sql=false 標記以使用標準 SQL 語法。如要將查詢結果寫入不在預設專案內的資料表,請使用下列格式將專案 ID 新增至資料集名稱:[PROJECT_ID]:[DATASET]

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

如要控管現有目標資料表的寫入配置,請指定以下其中一種選用標記:

  • --append_table — 如果目標資料表已存在,會將查詢結果附加至該資料表。
  • --replace — 如果目標資料表已存在,會以查詢結果覆寫該資料表。

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

其中:

  • [LOCATION] 是用於處理查詢的位置名稱。--location 是選用標記。舉例來說,假設您是在東京地區使用 BigQuery,可將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • [PROJECT_ID] 是您的專案 ID。
  • [DATASET] 是包含您要寫入查詢結果之資料表的資料集名稱。
  • [TABLE] 是您要寫入查詢結果的資料表名稱。
  • [QUERY] 是採用標準 SQL 語法的查詢。

如未指定任何寫入配置標記,預設動作是僅在資料表為空白時將結果寫入資料表。如已存在非空白的資料表,系統會傳回以下錯誤:BigQuery error in query operation: Error processing job '[PROJECT_ID]:bqjob_123abc456789_00000e1234f_1': Already Exists: Table [PROJECT_ID]:[DATASET].[TABLE]

範例:

輸入下列指令,將查詢結果寫入 mydataset 中名為 mytable 的目標資料表。該資料集位於預設專案中。由於您未在指令中指定任何寫入配置標記,資料表必須是新的或空白的。否則,系統會傳回 Already exists 錯誤。查詢會從美國名稱資料公開資料集中擷取資料。

bq --location=US query --destination_table mydataset.mytable --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 的目標資料表。該資料集位於預設專案中。指令使用 --replace 標記覆寫目標資料表。

bq --location=US query --destination_table mydataset.mytable --replace --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 而非預設專案中。指令使用 --append 標記將查詢結果附加至目標資料表。

bq --location=US query --destination_table myotherproject:mydataset.mytable --append --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.destinationTable 屬性的值。如要控管現有目的地資料表的寫入配置,請設定 configuration.query.writeDisposition 屬性。

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

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")

q := client.Query("SELECT 17 as my_col")
q.Location = "US" // Location must match the dataset(s) referenced in query.
q.QueryConfig.Dst = client.Dataset(destDatasetID).Table(destTableID)
job, err := q.Run(ctx)
if err != nil {
	return err
}
status, err := job.Wait(ctx)
if err != nil {
	return err
}
if err := status.Err(); err != nil {
	return err
}
it, err := job.Read(ctx)
for {
	var row []bigquery.Value
	err := it.Next(&row)
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Println(row)
}

Java

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

如要將查詢結果儲存到永久性資料表,請在 QueryJobConfiguration 中將目的地資料表設為所需的 TableId

// BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// String destinationDataset = 'my_destination_dataset';
// String destinationTable = 'my_destination_table';
String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
QueryJobConfiguration queryConfig =
    // Note that setUseLegacySql is set to false by default
    QueryJobConfiguration.newBuilder(query)
        // Save the results of the query to a permanent table.
        .setDestinationTable(TableId.of(destinationDataset, destinationTable))
        .build();

// Print the results.
for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
  for (FieldValue val : row) {
    System.out.printf("%s,", val.toString());
  }
  System.out.printf("\n");
}

Python

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

如要將查詢結果儲存到永久性資料表,請建立 QueryJobConfig,並將目的地設為所需的 TableReference。將工作設定傳送至查詢方法

# from google.cloud import bigquery
# client = bigquery.Client()
# dataset_id = 'your_dataset_id'

job_config = bigquery.QueryJobConfig()
# Set the destination table
table_ref = client.dataset(dataset_id).table('your_table_id')
job_config.destination = table_ref
sql = """
    SELECT corpus
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY corpus;
"""

# Start the query, passing in the extra configuration.
query_job = client.query(
    sql,
    # Location must match that of the dataset(s) referenced in the query
    # and of the destination table.
    location='US',
    job_config=job_config)  # API request - starts the query

query_job.result()  # Waits for the query to finish
print('Query results loaded to table {}'.format(table_ref.path))

建立參照外部資料來源的資料表

外部資料來源 (也稱為聯合資料來源) 是一種您可以直接查詢的資料來源,即使資料不是儲存在 BigQuery 中也一樣可以。您不用載入或串流資料,只須建立一個參照外部資料來源的資料表即可。

BigQuery 支援直接從下列項目查詢資料:

您可以在支援的外部資料來源中查詢資料,方法是建立一個參照儲存在外部資料來源中的資料的臨時或永久資料表。如要深入瞭解如何使用外部資料來源,請參閱下列說明文章:

在載入資料時建立資料表

將資料載入 BigQuery 時,您可將資料載入新的資料表或分區、將資料附加至現有的資料表或分區,或是覆寫資料表或分區。您不必在載入資料前先建立空白的資料表。您可以同時建立新的資料表並載入資料。

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

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

控管資料表的存取權

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

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

  3. 在編輯器下方,按一下 [Details] (詳細資料)。這個頁面顯示資料表的說明與資料表資訊。

  4. 按一下「Schema」(結構定義) 分頁標籤,查看資料表的結構定義。

傳統版 UI

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

  2. 按一下資料表名稱。

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

    查看資料表詳細資料

  4. 按一下「Schema」(結構定義) 分頁標籤,查看資料表的結構定義。

CLI

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

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

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

其中:

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

示例:

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

bq show --format=prettyjson mydataset.mytable

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

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

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

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

API

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

Go

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

meta, err := client.Dataset(datasetID).Table(tableID).Metadata(ctx)
if err != nil {
	return err
}
// Print basic information about the table.
fmt.Printf("Schema has %d top-level fields\n", len(meta.Schema))
fmt.Printf("Description: %s\n", meta.Description)
fmt.Printf("Row in managed storage: %d\n", meta.NumRows)

Java

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

TableId tableId = TableId.of(projectId, datasetName, tableName);
Table table = bigquery.getTable(tableId);

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
//$projectId = 'The Google project ID';
//$datasetId = 'The BigQuery dataset ID';
//$tableId   = 'The BigQuery table ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$table = $dataset->table($tableId);

Python

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

from google.cloud import bigquery

# TODO(developer): Construct a BigQuery client object.
# client = bigquery.Client()

# TODO(developer): Set table_id to the ID of the model to fetch.
# table_id = 'your-project.your_dataset.your_table'

table = client.get_table(table_id)

print(
    "Got table '{}.{}.{}'.".format(table.project, table.dataset_id, table.table_id)
)

# View table properties
print("Table schema: {}".format(table.schema))
print("Table description: {}".format(table.description))
print("Table has {} rows".format(table.num_rows))

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

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

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

TABLESTABLE_OPTIONS 資料檢視也包含資料檢視的相關高階資訊。如需詳細資訊,請改為查詢 INFORMATION_SCHEMA.VIEWS 資料檢視。

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 資料表的建立時間

示例

示例 1:

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

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 陣列

示例

示例 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

示例

以下範例會針對 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 資料欄的說明

示例

以下範例會針對 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 方法

所需權限

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

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

列出資料表

如何列出資料集中的資料表:

主控台

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

  2. 捲動清單,直到看到資料集中的資料表為止。資料表和資料檢視會分別以不同的圖示呈現。

傳統版 UI

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

  2. 捲動清單,直到看到資料集中的資料表為止。資料表和資料檢視會分別以不同的圖示呈現。

    查看資料表

指令列

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

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

其中:

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

執行指令時,Type 欄位會顯示 TABLEVIEW。例如:

+-------------------------+-------+----------------------+-------------------+
|         tableId         | Type  |        Labels        | Time Partitioning |
+-------------------------+-------+----------------------+-------------------+
| mytable                 | TABLE | department:shipping  |                   |
| myview                  | VIEW  |                      |                   |
+-------------------------+-------+----------------------+-------------------+

示例:

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

bq ls --format=pretty mydataset

輸入下列指令,列出 myotherprojectmydataset 資料集中的資料表。

bq ls --format=pretty myotherproject:mydataset

API

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

C#

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

using Google.Cloud.BigQuery.V2;
using System;
using System.Collections.Generic;
using System.Linq;

public class BigQueryListTables
{
    public void ListTables(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        // Retrieve list of tables in the dataset
        List<BigQueryTable> tables = client.ListTables(datasetId).ToList();
        // Display the results
        if (tables.Count > 0)
        {
            Console.WriteLine($"Tables in dataset {datasetId}:");
            foreach (var table in tables)
            {
                Console.WriteLine($"\t{table.Reference.TableId}");
            }
        }
        else
        {
            Console.WriteLine($"{datasetId} does not contain any tables.");
        }
    }
}

Go

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

ts := client.Dataset(datasetID).Tables(ctx)
for {
	t, err := ts.Next()
	if err == iterator.Done {
		break
	}
	if err != nil {
		return err
	}
	fmt.Fprintf(w, "Table: %q\n", t.TableID)
}

Java

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

DatasetId datasetId = DatasetId.of(projectId, datasetName);
Page<Table> tables = bigquery.listTables(datasetId, TableListOption.pageSize(100));
for (Table table : tables.iterateAll()) {
  // do something with the table
}

Node.js

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

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const datasetId = "my_dataset";

// Create a client
const bigqueryClient = new BigQuery();

async function listTables() {
  // Lists tables in "my_dataset".

  // List all tables in the dataset
  const [tables] = await bigqueryClient.dataset(datasetId).getTables();

  console.log('Tables:');
  tables.forEach(table => console.log(table.id));
}
listTables();

PHP

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

use Google\Cloud\BigQuery\BigQueryClient;

/** Uncomment and populate these variables in your code */
// $projectId  = 'The Google project ID';
// $datasetId  = 'The BigQuery dataset ID';

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$dataset = $bigQuery->dataset($datasetId);
$tables = $dataset->tables();
foreach ($tables as $table) {
    print($table->id() . PHP_EOL);
}

Python

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

from google.cloud import bigquery

# TODO(developer): Construct a BigQuery client object.
# client = bigquery.Client()

# TODO(developer): Set dataset_id to the ID of the dataset that contains
#                  the tables you are listing.
# dataset_id = 'your-project.your_dataset'

tables = client.list_tables(dataset_id)

print("Tables contained in '{}':".format(dataset_id))
for table in tables:
    print("{}.{}.{}".format(table.project, table.dataset_id, table.table_id))

Ruby

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

require "google/cloud/bigquery"

def list_tables dataset_id = "your_dataset_id"
  bigquery = Google::Cloud::Bigquery.new
  dataset  = bigquery.dataset dataset_id

  puts "Tables in dataset #{dataset_id}:"
  dataset.tables.each do |table|
    puts "\t#{table.table_id}"
  end
end

相關資源

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

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

這個網頁