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

結構定義自動偵測

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

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

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

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

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

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

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

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

  • GCP 主控台:在「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 主控台中開啟 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI

  2. 從導覽面板的「Resources」(資源) 區段選取資料集。按一下視窗右側的 [Create table] (建立資料表)

    建立資料表

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

    • 在「Create table from」(使用下列資料建立資料表) 部分,選取您想要的來源類型。

      建立資料表來源

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

      選取檔案

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

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

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

      選取資料集

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

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

  5. 按一下 [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 從本機電腦載入名為 mytable 的資料表 (儲存在名為 mydataset 的資料集中)。mydataset 建立於 US 多區域位置。

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

輸入下列指令將 myfile.csv 從本機電腦載入名為 mytable 的資料表 (儲存在名為 mydataset 的資料集中)。mydataset 建立於 asia-northeast1 位置。

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

API

  1. 建立指向來源資料的載入工作。如要瞭解如何建立工作,請參閱透過程式執行 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 參考說明文件

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

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

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

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

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

目前,您無法使用 GCP 主控台或傳統網頁版 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
本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁