使用結構定義自動偵測功能

結構定義自動偵測

無論是要將資料載入至 BigQuery 或是查詢外部資料來源,您都可以使用結構定義自動偵測功能。

自動偵測功能啟用之後,BigQuery 會隨機選擇資料來源中的檔案,並掃描最多 100 個資料列來當做代表性樣本,以便啟動推論程序。BigQuery 接著會檢查每個欄位,並嘗試根據樣本中的值為各欄位指派資料類型。

如何查看資料表的結構定義偵測結果:

  • 使用指令列工具的 bq show 指令
  • 使用 Cloud Console 或傳統網頁版 UI 查看資料表結構定義

當 BigQuery 偵測到結構定義時,可能會在極少數情況下更改欄位名稱,這是為了要配合 BigQuery SQL 的語法。

如要進一步瞭解資料類型轉換,請參閱以下資訊:

使用結構定義自動偵測功能載入資料

如何在載入資料時啟用結構定義自動偵測功能:

  • Cloud Console:在「Schema」(結構定義) 區段中,針對「Auto detect」(自動偵測) 勾選 [Schema and input parameters] (結構定義和輸入參數) 選項。
  • 傳統 BigQuery 網頁版 UI:在「Schema」(結構定義) 區段中,勾選 [Automatically detect] (自動偵測) 選項。
  • CLI:使用 bq load 指令並加入 --autodetect 參數。

啟用這項功能後,BigQuery 會盡可能嘗試自動推導 CSV 和 JSON 檔案的結構定義。

結構定義自動偵測功能不適用於 Avro 檔案、Parquet 檔案、ORC 檔案、Cloud Firestore 匯出檔案或 Datastore 匯出檔案。您將這些檔案載入至 BigQuery 時,系統會透過自述式來源資料自動擷取資料表結構定義。

如何在載入 JSON 和 CSV 資料時使用結構定義自動偵測功能:

主控台

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

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

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

    建立資料表

  4. 在「Create table」(建立資料表) 頁面的「Source」(來源) 區段中:

    • 在「Create table from」(使用下列資料建立資料表) 部分,選取您想要的來源類型。
    • 在「Source」(來源) 欄位中,瀏覽檔案/Cloud Storage 值區,或輸入 Cloud Storage URI。請注意,您無法在 BigQuery 網頁版 UI 中輸入多個 URI,但可以使用萬用字元。Cloud Storage 值區的位置必須與待建立資料表所在的資料集位置相同。

      選取檔案

    • 在「File format」(檔案格式) 部分,選取 [CSV] 或 [JSON]。

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

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

      選取資料集

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

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

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

傳統版 UI

  1. 前往 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

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

    注意:在 UI 中載入資料的程序與建立資料表的程序相同。
  3. 於「Create Table」(建立資料表) 頁面執行下列操作:

    • 在「Source Data」(來源資料) 區段,按一下 [Create from source] (從來源建立)。
    • 在「Destination Table」(目的地資料表) 部分中,選擇您的資料集並在 [Destination table name] (目的地資料表名稱) 欄位中輸入資料表名稱。
    • 在「Schema」(結構定義) 部分中,按一下 [Automatically detect] (自動偵測) 以偵測結構定義。

      自動偵測連結

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

CLI

發出 bq load 指令並搭配使用 --autodetect 參數。

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

下方指令會使用結構定義自動偵測功能載入檔案:

bq --location=location load \
--autodetect \
--source_format=format \
dataset.table \
path_to_source

其中:

  • 「location」是您位置的名稱,--location 是選用旗標。舉例來說,如果您在東京地區使用 BigQuery,就可以將該旗標的值設定為 asia-northeast1。您可以使用 .bigqueryrc 檔案設定位置的預設值。
  • 「format」NEWLINE_DELIMITED_JSONCSV
  • 「dataset」是指您載入資料的目標資料表所屬的資料集。
  • 「table」是您要載入資料的目標資料表的名稱。
  • 「path_to_source」 是 CSV 或 JSON 檔案的位置。

範例:

輸入下列指令,可將 myfile.csv 從您的本機載入至儲存於 mydataset 資料集內名稱為 mytable 的資料表。

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

輸入下列指令,可將 myfile.csv 從您的本機載入至儲存於 mydataset 資料集內名稱為 mytable 的資料表。

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

API

  1. 建立指向來源資料的 load 工作。如要瞭解如何建立工作,請參閱透過程式執行 BigQuery 工作。在 jobReference 區段的 location 屬性中指定您的位置。

  2. 設定 sourceFormat 屬性來指定資料格式。如要使用結構定義自動偵測功能,這個值必須設為 NEWLINE_DELIMITED_JSONCSV

  3. 使用 autodetect 屬性將結構定義自動偵測功能設為 true

Go

在嘗試這個範例之前,請先按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Go 設定操作說明來進行。詳情請參閱 BigQuery Go API 參考資料說明文件

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

Node.js

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

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

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

/**
 * This sample loads the JSON file at
 * https://storage.googleapis.com/cloud-samples-data/bigquery/us-states/us-states.json
 *
 * TODO(developer): Replace the following lines with the path to your file.
 */
const bucketName = 'cloud-samples-data';
const filename = 'bigquery/us-states/us-states.json';

async function loadJSONFromGCSAutodetect() {
  // Imports a GCS file into a table with autodetected schema.

  // Instantiate clients
  const bigquery = new BigQuery();
  const storage = new Storage();

  // Configure the load job. For full list of options, see:
  // https://cloud.google.com/bigquery/docs/reference/rest/v2/Job#JobConfigurationLoad
  const metadata = {
    sourceFormat: 'NEWLINE_DELIMITED_JSON',
    autodetect: true,
    location: 'US',
  };

  // Load data from a Google Cloud Storage file into the table
  const [job] = await bigquery
    .dataset(datasetId)
    .table(tableId)
    .load(storage.bucket(bucketName).file(filename), metadata);
  // load() waits for the job to finish
  console.log(`Job ${job.id} completed.`);

  // Check the job's status for errors
  const errors = job.status.errors;
  if (errors && errors.length > 0) {
    throw errors;
  }
}
loadJSONFromGCSAutodetect();

Python

在嘗試這個範例之前,請先按照 BigQuery 快速入門導覽課程:使用用戶端程式庫中的 Python 設定操作說明來進行。詳情請參閱 BigQuery Python API 參考資料說明文件

如要啟用結構定義自動偵測功能,請將 LoadJobConfig.autodetect 屬性設為 True

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

dataset_ref = client.dataset(dataset_id)
job_config = bigquery.LoadJobConfig()
job_config.autodetect = True
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"), 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))

自動偵測外部資料來源的結構定義

在建立連結到外部資料來源的資料表時,如何啟用結構定義自動偵測功能:

  • 在 Cloud Console 中,針對「Auto detect」(自動偵測) 勾選 [Schema and input parameters] (結構定義和輸入參數)。
  • 在傳統 BigQuery 網頁版 UI 中,勾選 [Automatically detect] (自動偵測) 選項。

啟用後,BigQuery 會盡量嘗試自動推斷 CSV 和 JSON 外部資料來源的結構定義。

目前,您無法使用 Cloud Console 或傳統網頁版 UI 為 Google 試算表外部資料來源啟用結構定義自動偵測功能。此外,結構定義自動偵測功能也無法與外部 Avro 檔案、Cloud Firestore 匯出檔案或 Datastore 匯出檔案搭配使用。當您建立連結到其中一種檔案類型的資料表時,BigQuery 會自動從自述來源資料中擷取結構定義。

如果您使用 CLI,就可以在為 CSV、JSON 或 Google 試算表資料建立資料表定義檔時,啟用結構定義自動偵測功能。在使用 CLI 建立資料表定義檔案時,您可以將 --autodetect 旗標傳送至 mkdef 指令以啟用結構定義自動偵測功能,或是傳送 --noautodetect 旗標來停用自動偵測。

當您使用 --autodetect 旗標時,資料表定義檔中的 autodetect 設定會設為 true。當您使用 --noautodetect 旗標時,資料表定義檔中的 "autodetect" 設定會設為 false。如果您在建立資料表定義時,沒有提供外部資料來源的結構定義,而且未使用 --noautodetect--autodetect 旗標,則 "autodetect" 設定會預設為 true

當您使用 API 建立資料表定義檔時,請將 autodetect 屬性值設為 truefalse。將 autodetect 設為 true 可啟用自動偵測功能;將 autodetect 設為 false 則可停用。

自動偵測詳細資料

除了偵測結構定義詳細資料外,自動偵測功能還可識別以下內容:

壓縮

BigQuery 在開啟檔案時,可識別與 gzip 相容的檔案壓縮格式。

CSV 分隔符號

BigQuery 可偵測以下分隔符號:

  • 半形逗號 ( , )
  • 管線符號 ( | )
  • 定位點 ( \t )

CSV 標頭

BigQuery 會將檔案的第一個資料列與資料集中的其他資料列做比較,藉此推測出標題。假如第一行只包含字串,但其他行沒有,BigQuery 會假設第一個資料列是標題資料列。

CSV 引用的新行

BigQuery 會偵測 CSV 欄位內引用的新行字元,但不會將引用的新行字元解讀為資料列邊界。

日期

對 JSON 或 CSV 資料使用結構定義偵測功能時,DATE 資料欄中的值必須使用連字號 (-) 分隔符且必須採用以下格式:YYYY-MM-DD (年-月-日)。

時間戳記

BigQuery 可偵測多種時間戳記格式,包括但不限於以下格式:

  • yyyy-mm-dd
  • yyyy-mm-dd hh:mm:ss
  • yyyy-mm-dd hh:mm:ss.mmm

時間戳記也可包含 UTC 偏移和 UTC 區域指定元 Z,也支援使用整數型時間戳記值。

對 JSON 或 CSV 資料使用結構定義偵測功能時,TIMESTAMP 資料欄中的值必須使用連字號 (-) 分隔符來區隔時間戳記的日期部分,且日期必須採用以下格式:YYYY-MM-DD (年-月-日)。時間戳記的 hh:mm:ss (時-分-秒) 部分必須使用冒號 (:) 分隔符。

時間戳記範例

以下是 BigQuery 自動偵測到的時間戳記格式範例:

  • 253402300799
  • 2018-07-05 12:54:00 UTC
  • 2018-08-19 07:11:35.220 -05:00
  • 2018-08-19 12:11:35.220 UTC
  • 2018-08-19T12:11:35.220Z
  • 2.53402300799e11
  • 2018-08-19 12:11:35.220000
  • 2018-08-19 12:11:35.220