指定結構定義

BigQuery 可讓您在將資料載入資料表,以及建立空白資料表時指定資料表結構定義。此外,您也可以針對支援的資料格式,使用結構定義自動偵測功能。

載入 Avro、Parquet、ORC、Cloud Firestore 匯出檔案或 Cloud Datastore 匯出檔案時,系統會自動從自述式來源資料擷取結構定義。

您可以透過下列任一方式指定資料表的結構定義:

  • 手動指定結構定義:
    • 使用 GCP 主控台
    • 使用傳統 BigQuery 網頁版 UI
    • 使用 CLI 內嵌方式。
  • 以 JSON 格式建立結構定義檔案。
  • 呼叫 jobs.insert 方法,並設定 load 工作設定中的 schema 屬性。
  • 呼叫 tables.insert 方法,並使用 schema 屬性,設定資料表資源中的結構定義。

載入資料或建立空白資料表之後,您可以修改資料表結構定義

結構定義元件

當您指定資料表結構定義時,您必須提供每個資料欄的名稱和資料類型。您可以視需要提供資料欄的說明和模式。

資料欄名稱

資料欄名稱只能包含英文字母 (a-z、A-Z)、數字 (0-9) 或底線 (_),且開頭必須為英文字母或底線。資料欄名稱的長度上限為 128 個字元。資料欄名稱不得使用以下任何一個前置字元:

  • _TABLE_
  • _FILE_
  • _PARTITION

資料欄名稱不得重複,即便其情況不同也是如此。舉例來說,系統會將名為 Column1 的資料欄視為與 column1 資料欄的名稱相同。

資料欄說明

每個資料欄可視需要納入說明。說明的長度上限為 1024 個字元。

標準 SQL 資料類型

BigQuery 標準 SQL 允許您在結構定義中指定以下資料類型;資料類型為必要項目。

資料類型 說明
整數 不包含小數的數值
浮點 具有小數的概略數值
數字 具有小數的精確數值
布林值 TRUE 或 FALSE (有大小寫之分)
字串 變數長度字元 (Unicode) 資料
位元組 變數長度二進位資料
日期 邏輯日曆日期
日期時間 年、月、日、時、分、秒和亞秒
時間 時間;與特定日期無關
時間戳記 絕對時間點 (精確度高達微秒)
結構 (記錄) 已排序欄位的容器,每個容器都有一個類型 (必填) 和欄位名稱 (選填)
地理位置 地表上的地理資訊點集合 (WGS84 參考橢球體上的點、線與多邊形集合,含測地線)

如要進一步瞭解標準 SQL 中的資料類型,請參閱標準 SQL 資料類型

查詢資料時,您也可以聲明陣列類型。詳情請參閱使用陣列

模式

BigQuery 支援下列資料欄模式;模式為選用項目。假如未指定模式,資料欄會預設為 NULLABLE

模式 說明
可以為空值 資料欄允許 NULL 值 (預設)
必要 不得輸入 NULL
重複 資料欄包含指定類型的值陣列

如要進一步瞭解模式,請參閱 TableFieldSchema 中的 mode

手動指定結構定義

載入資料或建立空白資料表時,您可以使用 GCP 主控台、傳統 BigQuery 網頁版 UI 或指令列工具手動指定資料表的結構定義。載入 CSV 與 JSON (以換行符號分隔) 檔時,系統支援手動指定結構定義。載入 Avro、Parquet、ORC、Cloud Firestore 匯出資料或 Cloud Datastore 匯出資料時,系統會自動從自述式來源資料擷取結構定義。

如要手動指定資料表結構定義:

主控台

在 GCP Console 中,您可以使用 [Add field] (新增欄位) 選項或 [Edit as text] (以文字形式編輯) 選項,指定結構定義。

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

  2. 從導覽面板的「Resources」(資源) 區段選取資料集。

  3. 按一下視窗右側的 [Create table] (建立資料表)

    建立資料表

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

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

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

      選取資料集

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

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

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

    • 選項 1:使用 [Add Field] (新增欄位) 並指定每個欄位的「Name」(名稱)、「Type」(類型) 和「Mode」(模式)。在 GCP Console 中,如果您使用 [Add Field] (新增欄位) 選項,將無法新增欄位說明。不過您可以在載入資料後,於 UI 中手動新增欄位說明。
    • 選項 2:按一下 [Edit as Text] (以文字形式編輯),然後以 JSON 陣列的形式貼上結構定義。如果您使用 JSON 陣列,可透過與建立 JSON 結構定義檔一樣的程序產生結構定義。
  7. 按一下 [Create table] (建立資料表)

傳統版 UI

在傳統 BigQuery 網頁版 UI 中,您可使用 [Add Field] (新增欄位) 選項或 [Edit as Text] (以文字形式編輯) 選項,指定結構定義。

  1. 前往 BigQuery 網頁版 UI。

    前往 BigQuery 網頁版 UI

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

  3. 於「Create Table」(建立資料表) 頁面執行下列操作:

    • 如果您要載入資料,請在「Source Data」(來源資料) 區段按一下 [Create from source] (透過來源建立),或點選 [Create empty table] (建立空白資料表)
    • 在「Destination Table」(目的地資料表) 區段選擇所需資料集,然後在 [Destination table name] (目的地資料表名稱) 欄位輸入資料表名稱。
    • 在「Schema」(結構定義) 區段,選擇下列任一選項,手動指定結構定義:

      • 選項 1:使用 [Add Field] (新增欄位) 並指定每個欄位的「Name」(名稱)、「Type」(類型) 和「Mode」(模式)。在 BigQuery 網頁版 UI 中,如果您使用 [Add Field] (新增欄位) 選項,將無法新增欄位說明。不過您可以在載入資料後,於 UI 中手動新增欄位說明。

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

      • 選項 2:按一下 [Edit as Text] (以文字形式編輯),然後以 JSON 陣列的形式貼上結構定義。如果您使用 JSON 陣列,可透過與建立 JSON 結構定義檔一樣的程序產生結構定義。

        以 JSON 陣列新增結構定義

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

CLI

使用下列指令,以內嵌方式手動提供 field:data_type,field:data_type 格式的結構定義:

  • load 指令 (假如您要載入資料)
  • mk 指令 (假如您要建立空白資料表)

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

如要使用內嵌結構定義將資料載入資料表,請輸入 load 指令並使用 --source_format 標記指定資料格式。如要將資料載入預設專案以外的專案中,請使用下列格式加上專案 ID:project_id:dataset.table_name

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

bq --location=location load \
--source_format=format \
project_id:dataset.table_name \
path_to_source \
schema

其中:

  • 「location」是您位置的名稱,--location 是選用標記。舉例來說,如果您在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • 「format」NEWLINE_DELIMITED_JSONCSV
  • 「project_id:dataset.table_name」是您的專案 ID。
  • 「project_id:dataset.table_name」是您載入資料的目標資料表所屬的資料集。
  • 「project_id:dataset.table_name」是您要載入資料的目標資料表的名稱。
  • 「path_to_source」是您本機或 Cloud Storage 上的 CSV 或 JSON 資料檔案的位置。
  • 「schema」是內嵌結構定義。

範例:

輸入下列指令,將資料從名為 myfile.csv 的本機 CSV 檔案載入預設專案中的 mydataset.mytable。您必須以手動方式將結構定義指定為內嵌。

bq load \
--source_format=CSV \
mydataset.mytable \
./myfile.csv \
qtr:STRING,sales:FLOAT,year:STRING

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

如要在建立空白資料表時指定內嵌結構定義,請搭配 --table-t 標記輸入 mk 指令。如要在預設專案以外的專案中建立資料表,請使用下列格式在指令中加入專案 ID:project_id:dataset.table

bq mk --table project_id:dataset.table schema

其中:

  • 「project_id」是您的專案 ID。
  • 「dataset」是專案中的資料集。
  • 「table」是您建立的資料表名稱。
  • 「schema」是內嵌結構定義。

舉例來說,下列指令會在預設專案中建立名為 mytable 的空白資料表。您必須以手動方式將結構定義指定為內嵌。

bq mk --table mydataset.mytable qtr:STRING,sales:FLOAT,year:STRING

如要進一步瞭解如何建立空白資料表,請參閱建立含有結構定義的空白資料表

C#

將資料載入資料表時,如何指定資料表的結構定義:


using Google.Apis.Bigquery.v2.Data;
using Google.Cloud.BigQuery.V2;
using System;

public class BigQueryLoadTableGcsJson
{
    public void LoadTableGcsJson(
        string projectId = "your-project-id",
        string datasetId = "your_dataset_id"
    )
    {
        BigQueryClient client = BigQueryClient.Create(projectId);
        var gcsURI = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
        var dataset = client.GetDataset(datasetId);
        var schema = new TableSchemaBuilder {
            { "name", BigQueryDbType.String },
            { "post_abbr", BigQueryDbType.String }
        }.Build();
        TableReference destinationTableRef = dataset.GetTableReference(
            tableId: "us_states");
        // Create job configuration
        var jobOptions = new CreateLoadJobOptions()
        {
            SourceFormat = FileFormat.NewlineDelimitedJson
        };
        // Create and run job
        BigQueryJob loadJob = client.CreateLoadJob(
            sourceUri: gcsURI, destination: destinationTableRef,
            schema: schema, options: jobOptions);
        loadJob.PollUntilCompleted();  // Waits for the job to complete.
        // Display the number of rows uploaded
        BigQueryTable table = client.GetTable(destinationTableRef);
        Console.WriteLine(
            $"Loaded {table.Resource.NumRows} rows to {table.FullyQualifiedId}");
    }
}

建立空白資料表時,如何指定結構定義:


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

將資料載入資料表時,如何指定資料表的結構定義:

// To run this sample, you will need to create (or reuse) a context and
// an instance of the bigquery client.  For example:
// import "cloud.google.com/go/bigquery"
// ctx := context.Background()
// client, err := bigquery.NewClient(ctx, "your-project-id")
gcsRef := bigquery.NewGCSReference("gs://cloud-samples-data/bigquery/us-states/us-states.json")
gcsRef.SourceFormat = bigquery.JSON
gcsRef.Schema = bigquery.Schema{
	{Name: "name", Type: bigquery.StringFieldType},
	{Name: "post_abbr", Type: bigquery.StringFieldType},
}
loader := client.Dataset(datasetID).Table(tableID).LoaderFrom(gcsRef)
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())
}

建立空白資料表時,如何指定結構定義:

// 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: "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
}

Python

將資料載入資料表時,如要指定資料表的結構定義,請設定 LoadJobConfig.schema 屬性。

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

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON
uri = "gs://cloud-samples-data/bigquery/us-states/us-states.json"

load_job = client.load_table_from_uri(
    uri,
    dataset_ref.table("us_states"),
    location="US",  # Location must match that of the destination dataset.
    job_config=job_config,
)  # API request
print("Starting job {}".format(load_job.job_id))

load_job.result()  # Waits for table load to complete.
print("Job finished.")

destination_table = client.get_table(dataset_ref.table("us_states"))
print("Loaded {} rows.".format(destination_table.num_rows))

如要在建立空白資料表時指定結構定義,請設定 Table.schema 屬性。

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

指定 JSON 結構定義檔

假如不想手動指定結構定義,您可以建立 JSON 結構定義檔,以便與 CLI 搭配使用。JSON 結構定義檔是由包含以下項目的 JSON 陣列所組成:

建立 JSON 結構定義檔

如要建立 JSON 結構定義檔,請使用相關的文字編輯器輸入下列指令:

[
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 },
 {
   "description": "[DESCRIPTION]",
   "name": "[NAME]",
   "type": "[TYPE]",
   "mode": "[MODE]"
 }
]

JSON 陣列會包含在括號 [] 中。每個資料欄都必須以半形逗號分隔 (},)。

您可以輸入以下指令將現有資料表結構定義寫入本機檔案:

bq show \
--schema \
--format=prettyjson \
project_id:dataset.table > path_to_file

您可以使用輸出檔案做為 JSON 結構定義檔的起點。假如您使用此方法,請確定該檔案僅包含代表資料表結構定義的 JSON 陣列。

舉例來說,下列 JSON 陣列代表基本的資料表結構定義。此結構定義有 3 個資料欄:qtr (REQUIRED STRING)、rep (NULLABLE STRING) 以及 sales (NULLABLE FLOAT)。

[
  {
    "description": "quarter",
    "mode": "REQUIRED",
    "name": "qtr",
    "type": "STRING"
  },
  {
    "description": "sales representative",
    "mode": "NULLABLE",
    "name": "rep",
    "type": "STRING"
  },
  {
    "description": "total sales",
    "mode": "NULLABLE",
    "name": "sales",
    "type": "FLOAT"
  }
]

使用 JSON 結構定義檔

建立 JSON 結構定義檔後,您可以在指令列指定結構定義。您無法透過 GCP 主控台、傳統 BigQuery 網頁版 UI 或 API 使用結構定義檔。

使用以下指令手動提供結構定義:

  • load 指令 (假如您要載入資料)
  • mk 指令 (假如您要建立空白資料表)

您提供的 JSON 結構定義檔必須儲存在可讀取的本機位置。您無法指定儲存在 Cloud Storage 或 Google 雲端硬碟中的 JSON 結構定義檔。

在載入資料時指定結構定義檔

下列指令會使用 JSON 檔案的結構定義將資料載入資料表:

bq --location=location load \
--source_format=format \
project_id:dataset.table \
path_to_data_file \
path_to_schema_file

其中:

  • 「location」是您位置的名稱,--location 是選用標記。舉例來說,如果您在東京地區使用 BigQuery,請將標記的值設為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • 「format」NEWLINE_DELIMITED_JSONCSV
  • 「project_id」是您的專案 ID。
  • 「dataset」是特定資料集,該資料集包含您要在當中載入資料的資料表。
  • 「table」是您要載入資料的目標資料表名稱。
  • 「path_to_data_file」是您本機或 Cloud Storage 上的 CSV 或 JSON 資料檔案位置。
  • 「path_to_schema_file」是本機上結構定義檔的路徑。

範例:

輸入下列指令,將資料從名為 myfile.csv 的本機 CSV 檔案載入預設專案中的 mydataset.mytable。結構定義在 myschema.json 中指定。

bq load --source_format=CSV mydataset.mytable ./myfile.csv ./myschema.json

在建立資料表時指定結構定義檔

下列指令會使用 JSON 檔案的結構定義,在現有的資料集中建立空白資料表:

bq mk --table project_id:dataset.table path_to_schema_file

其中:

  • 「project_id」是您的專案 ID。
  • 「dataset」是專案中的資料集。
  • 「table」是您建立的資料表名稱。
  • 「path_to_schema_file」是本機上結構定義檔的路徑。

舉例來說,以下指令會在預設專案的 mydataset 中建立名為 mytable 的資料表。結構定義是在 myschema.json 中指定:

bq mk --table mydataset.mytable ./myschema.json

在 API 中指定結構定義

如要使用 API 指定資料表結構定義:

  • 如要在載入資料時指定結構定義,請呼叫 jobs.insert 方法,並設定 load 工作設定中的 schema 屬性。
  • 如要在建立資料表時指定結構定義,請呼叫 tables.insert 方法,並設定資料表資源中的 schema 屬性。

使用 API 指定結構定義的程序與建立 JSON 結構定義檔的程序十分相似。

後續步驟

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

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

這個網頁