建立及使用資料表

本文件說明如何在 BigQuery 中建立及使用標準或「原生」資料表。如要瞭解如何建立其他類型的資料表,請參閱:

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

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

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

資料表限制

BigQuery 資料表有以下限制:

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

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

建立資料表

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

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

資料表命名

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

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

所需權限

如要建立資料表,您至少要具備下列權限:

  • 建立資料表的 bigquery.tables.create 權限
  • 使用載入工作、查詢工作或複製工作將資料寫入資料表的 bigquery.tables.updateData 權限
  • 執行查詢工作、載入工作或複製工作以將資料寫入資料表的 bigquery.jobs.create 權限

您可能還需要其他權限 (例如 bigquery.tables.getData),才能存取您要寫入資料表的資料。

以下是具有 bigquery.tables.createbigquery.tables.updateData 權限的預先定義 Cloud IAM 角色:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

以下是具有 bigquery.jobs.create 權限的預先定義 Cloud IAM 角色:

  • bigquery.user
  • bigquery.jobUser
  • bigquery.admin

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

如要深入瞭解 BigQuery 中的 Cloud IAM 角色和權限,請參閱存取權控管

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

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

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

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

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

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

主控台

  1. 在 GCP Console 中開啟 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. 保留「Partition and cluster settings」(分區與叢集設定) 中的預設值:No partitioning

  8. 在「Advanced options」(進階選項) 區段的「Encryption」(加密) 中,請保留預設值:Google-managed key。根據預設,BigQuery 會加密靜態儲存的客戶內容

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

DDL

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

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

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

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

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

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

    下列查詢會建立名為 newtable 且到期時間為 2020 年 1 月 1 日的資料表。資料表的說明是「a table that expires in 2020」(於 2020 年到期的資料表),資料表的標籤為 org_unit:development

     CREATE TABLE mydataset.newtable
     (
       x INT64 OPTIONS(description="An optional INTEGER field"),
       y STRUCT<
         a ARRAY<STRING> OPTIONS(description="A repeated STRING field"),
         b BOOL
       >
     )
     OPTIONS(
       expiration_timestamp=TIMESTAMP "2020-01-01 00:00:00 UTC",
       description="a table that expires in 2020",
       labels=[("org_unit", "development")]
     )

  4. (選用) 按一下 [More] (其他選項) 並選取 [Query settings] (查詢設定)查詢設定

  5. (選用) 在「Processing location」(處理位置),按一下 [Auto-select] (自動選取),然後選擇資料的位置。如果處理位置設為為指定,系統便會自動偵測處理位置。 查詢處理位置

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

傳統版 UI

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

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

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

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

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

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

        以 JSON 陣列新增結構定義

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

        使用新增欄位新增結構定義

  5. 保留「Options」(選項) 區段的預設值。

    • 「Partitioning Type」(分區類型)None
    • 「Partitioning Field」(分區欄位):應為不可用
    • 「Require partition filter」(需要分區篩選器):應為不可用
    • 「Clustering Fields」(叢集欄位):應為不可用
    • 「Destination Encryption」(目的地加密)Default 根據預設,BigQuery 會使用 Google 代管的金鑰加密靜態儲存的客戶內容
  6. 按一下 [Create Table] (建立資料表)。

CLI

使用 mk 指令並搭配使用 --table-t 標記。您可以透過內嵌方式或 JSON 結構定義檔提供資料表結構定義資訊。選用參數包括:

  • --expiration
  • --description
  • --time_partitioning_type
  • --destination_kms_key
  • --label

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

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

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

bq mk \
--table \
--expiration integer \
--description description \
--label key:value, key:value \
project_id:dataset.table \
schema

其中:

  • integer 是資料表的預設生命週期 (以秒為單位),最小值是 3600 秒 (1 小時)。到期時間為目前世界標準時間加上 [INTEGER] 中的整數值。如果您在建立資料表時設定了資料表的到期時間,則系統會忽略資料集的預設資料表到期時間設定。
  • 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 and create a client
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new 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_dataset";
  // const tableId = "my_table";
  // const schema = 'Name:string, Age:integer, Weight:float, IsMagic:boolean';

  // 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 bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

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

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 Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

  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 Results] (儲存結果) 按鈕,將快取結果資料表複製至永久性資料表。

DDL

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

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

傳統版 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 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 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 的目標資料表。資料集位於 my-other-project,而非預設專案中。指令使用 --append 標記將查詢結果附加至目標資料表。

bq query \
--append \
--use_legacy_sql=false \
--destination_table my-other-project:mydataset.mytable \
'SELECT
   name,
   number
 FROM
   `bigquery-public-data`.usa_names.usa_1910_current
 WHERE
   gender = "M"
 ORDER BY
   number DESC'
 

各範例的輸出內容如下。為了方便閱讀,部分輸出內容會被截斷。

Waiting on bqjob_r123abc456_000001234567_1 ... (2s) Current status: DONE
+---------+--------+
|  name   | number |
+---------+--------+
| Robert  |  10021 |
| John    |   9636 |
| Robert  |   9297 |
| ...              |
+---------+--------+

API

如要將查詢結果儲存至永久性資料表,請呼叫 jobs.insert 方法,設定 query 工作,然後加入 destinationTable 屬性的值。如要控管現有目標資料表的寫入配置,請設定 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 資源存取層級是資料集層級。如要設定資料表和檢視表的存取權,請在資料集層級或更高層級授予 Cloud IAM 角色。

在資料集層級授予角色,就能讓實體對特定資料集中的資料表和檢視表執行指定作業。如需設定資料集存取權控管權限的詳細資訊,請參閱控管資料集存取權

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

您也可以建立 Cloud IAM 自訂角色。如果您建立自訂角色,就可依據要讓實體執行的資料表作業來授予權限。

如要深入瞭解角色和授權,請參閱:

使用資料表

取得資料表相關資訊

您可以透過以下方式,取得有關資料表的資訊或中繼資料:

  • 使用 GCP Console 或傳統 BigQuery 網頁版 UI
  • 使用 bq show CLI 指令
  • 呼叫 tables.get API 方法
  • 使用用戶端程式庫
  • 查詢 INFORMATION_SCHEMA 檢視表 (Beta 版)

所需權限

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

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

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

如要深入瞭解 BigQuery 中的 Cloud IAM 角色和權限,請參閱存取權控管

取得資料表資訊

如何取得資料表的相關資訊:

主控台

  1. 在導覽面板的「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 資料表的建立時間

如要進一步瞭解資料集屬性,請參閱 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 Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

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

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

CLI

使用 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 Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

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

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

CLI

使用 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 說明文件中的「dataset resource」(資料集資源) 頁面。如要進一步瞭解資料表和檢視表屬性,請參閱 REST API 說明文件中的資料表資源頁面。

範例

範例 1:

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

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

如要對預設專案以外的專案執行查詢,請使用下列格式將專案 ID 新增至資料集:`project_id`.dataset.INFORMATION_SCHEMA.view (例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS)。

執行查詢:

主控台

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

    前往 GCP Console

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

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

CLI

使用 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 選項中的值來尋找在說明中的任意位置包含「test」的資料表。mydataset 是您的預設專案 (myproject)。

如要對預設專案以外的專案執行查詢,請使用下列格式將專案 ID 新增至資料集:`project_id`.dataset.INFORMATION_SCHEMA.view (例如 `myproject`.mydataset.INFORMATION_SCHEMA.TABLE_OPTIONS)。

執行查詢:

主控台

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

    前往 GCP Console

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

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

CLI

使用 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 Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

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

    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] (執行)

CLI

使用 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 Console 中開啟 BigQuery 網頁版 UI。

    前往 GCP Console

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

    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] (執行)

CLI

使用 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 Console 或傳統 BigQuery 網頁版 UI
  • 使用 bq ls CLI 指令
  • 呼叫 tables.list API 方法
  • 使用用戶端程式庫

所需權限

您至少要具備 bigquery.tables.list 權限,才能列出資料集裡的資料表。以下是具有 bigquery.tables.list 權限的預先定義 Cloud IAM 角色:

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

如要深入瞭解 BigQuery 中的 Cloud IAM 角色和權限,請參閱存取權控管

列出資料表

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

主控台

  1. 在 GCP Console 的導覽窗格中,按一下資料集以將其展開。如此即會顯示資料集裡的資料表和檢視表。

  2. 捲動清單來檢視該資料集中的資料表。資料表和檢視表會分別以不同的圖示呈現。

傳統版 UI

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

  2. 捲動清單來檢視該資料集中的資料表。資料表和檢視表會分別以不同的圖示呈現。

    查看資料表

CLI

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

其他標記包括:

  • --max_results-n:用來表示結果數量上限的整數。預設值為 50
bq ls \
--format=pretty \
--max_results integer \
project_id:dataset

其中:

  • integer 是整數,代表要列出的資料表數量。
  • project_id 是您的專案 ID。
  • dataset 是資料集名稱。

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

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

範例:

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

bq ls --format=pretty mydataset

輸入下列指令,以讓 mydataset 傳回超過 50 個 (預設值) 輸出資料表。mydataset 位於預設專案中。

bq ls --format=pretty --max_results 60 mydataset

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

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');
const bigquery = new BigQuery();

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

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

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

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

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

相關資源

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

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

這個網頁
需要協助嗎?請前往我們的支援網頁