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

結構定義自動偵測

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

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

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

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

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

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

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

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

  • GCP 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 匯出檔案或 Cloud Datastore 匯出檔案。您將這些檔案載入至 BigQuery 時,系統會透過自述式來源資料自動擷取資料表結構定義。

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

主控台

  1. 在 GCP Console 中開啟 BigQuery 網頁版 UI。
    前往 GCP 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」(資料表名稱) 欄位中,輸入您在 BigQuery 中建立資料表時使用的資料表名稱。

    • 確認「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

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

(選用) 提供 --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())
}

Python

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

如要啟用結構定義自動偵測功能,請將 屬性設為 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))

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

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

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

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

目前,您無法使用 GCP Console 或傳統網頁版 UI 為 Google 試算表外部資料來源啟用結構定義自動偵測功能。此外,結構定義自動偵測功能也無法與 Avro 檔案、Cloud Firestore 匯出檔案或 Cloud 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
本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁