指定結構定義

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

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

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

  • 手動指定結構定義:
    • 使用 Cloud Console
    • 使用傳統 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

手動指定結構定義

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

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

主控台

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

  1. 在 Cloud Console 中開啟 BigQuery 網頁版 UI。
    前往 Cloud 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」(模式)。在 Cloud 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 結構定義檔後,您可以在指令列指定結構定義。您無法透過 Cloud Console、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 結構定義檔的程序十分相似。

後續步驟