針對外部資料來源建立資料表定義檔

本頁面說明如何針對外部資料來源建立資料表定義檔。外部資料來源是指可以直接查詢的資料來源，即使資料未儲存在 BigQuery 中也一樣。

資料表定義檔內含外部資料表的結構定義和中繼資料，例如資料表的資料格式和相關屬性。建立資料表定義檔時，您可以使用結構定義自動偵測功能，針對外部資料來源定義結構定義。您可以內嵌提供結構定義，也可以提供含結構定義的 JSON 檔案。

資料表定義檔與 bq 指令列工具搭配使用。使用 REST API 時，資料表定義檔中的屬性也可用於建立 ExternalDataConfiguration。使用 Google Cloud 主控台建立外部資料表時，不會使用資料表定義檔。

您可以建立資料表定義檔，說明下列外部資料來源的永久或臨時外部資料表：

  • Cloud Storage

    • 逗號分隔值 (CSV)
    • 換行符號分隔的 JSON
    • Avro 檔案
    • Datastore 匯出檔案
    • ORC 檔案
    • Parquet 檔案
    • Firestore 匯出檔案

  • Google 雲端硬碟

    • 逗號分隔值 (CSV)
    • 換行符號分隔的 JSON
    • Avro 檔案
    • Google 試算表

  • Bigtable

事前準備

如要建立資料表定義檔，您必須備妥資料來源的 URI：

為 CSV、JSON 或 Google 試算表檔案建立定義檔

如要為 Cloud Storage 或雲端硬碟中的 CSV、JSON 或 Google 試算表檔案建立資料表定義檔，請使用下列其中一種方法：

使用 autodetect 旗標

如果您指定了 CSV、JSON 或 Google 試算表檔案，但未加入內嵌結構定義描述或結構定義檔，可以在資料表定義檔中使用 --autodetect 旗標將 "autodetect"選項設為 true。啟用自動偵測時，BigQuery 會儘可能嘗試自動推測結構定義。詳情請參閱自動偵測外部資料來源的結構定義。

搭配 Cloud Storage 資料來源使用自動偵測功能

建立 Cloud Storage 資料來源的資料表定義檔：

  1. 使用 bq mkdef 指令與 --autodetect 旗標建立資料表定義檔。mkdef 指令會產生 JSON 格式的資料表定義檔。以下範例建立了資料表定義，並且將輸出內容寫入 /tmp/file_name 檔案。

    bq mkdef \
  --autodetect \
  --source_format=SOURCE_FORMAT \
  "URI" > /tmp/FILE_NAME

    更改下列內容：

    • SOURCE_FORMAT：檔案格式
    • FILE_NAME：資料表定義檔的名稱

    • URI：Cloud Storage URI

      例如 gs://mybucket/myfile。

  2. (選用) 在文字編輯器中開啟資料表定義檔。舉例來說，指令 nano /tmp/file_name 會在 nano 中開啟檔案。CSV 外部資料來源的檔案應會如下所示。請注意 "autodetect" 設為 true。

    {
"autodetect": true,
"csvOptions": {
  "allowJaggedRows": false,
  "allowQuotedNewlines": false,
  "encoding": "UTF-8",
  "fieldDelimiter": ",",
  "quote": "\"",
  "skipLeadingRows": 0
},
"sourceFormat": "CSV",
"sourceUris": [
  "URI"
]
}

  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定，如 maxBadRecords 和 ignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定，但有設定會套用至 CSV 和 Google 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration。

搭配雲端硬碟資料來源使用自動偵測功能

為雲端硬碟資料來源建立資料表定義檔：

  1. 使用 bq mkdef 指令搭配 --autodetect 旗標建立資料表定義。mkdef 指令會產生 JSON 格式的資料表定義檔。以下範例建立了資料表定義，並且將輸出內容寫入 /tmp/file_name 檔案。

    bq mkdef \
   --autodetect \
   --source_format=SOURCE_FORMAT \
   "URI" > /tmp/FILE_NAME

    更改下列內容：

    • SOURCE_FORMAT：檔案格式
    • FILE_NAME：資料表定義檔的名稱

    • URI：雲端硬碟 URI

      例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。

  2. 在文字編輯器中開啟資料表定義檔。舉例來說，指令 nano /tmp/file_name 會在 nano 中開啟檔案。Google 試算表外部資料來源的檔案應會如下所示。請注意 "autodetect" 設為 true。

    {
"autodetect": true,
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定，如 maxBadRecords 和 ignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定，但有設定會套用至 CSV 和 Google 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration。

  4. 如要在 Google 試算表檔案中指定特定的工作表或儲存格範圍，請將 range 屬性新增至資料表定義檔中的 GoogleSheetsOptions 物件。如要查詢特定工作表，請指定工作表名稱。如要查詢儲存格範圍，請使用 sheet_name!top_left_cell_id:bottom_right_cell_id 格式指定範圍，例如 "Sheet1!A1:B20"。如果未指定 range 參數，則會使用檔案中的第一個工作表。

使用內嵌結構定義

如果您不要使用結構定義自動偵測，則可透過提供內嵌結構定義的方式建立資料表定義檔。如要提供內嵌結構定義，請在指令列中列出欄位和資料類型，格式如下：FIELD:DATA_TYPE,FIELD:DATA_TYPE。

搭配 Cloud Storage 或 Google 雲端硬碟資料來源使用內嵌結構定義

使用內嵌結構定義，為 Cloud Storage 或雲端硬碟資料來源建立資料表定義：

  1. 使用 bq mkdef 指令搭配 --noautodetect 旗標建立資料表定義。mkdef 指令會產生 JSON 格式的資料表定義檔。以下範例建立了資料表定義，並且將輸出內容寫入 /tmp/file_name 檔案。

    bq mkdef \
  --noautodetect \
  --source_format=SOURCE_FORMAT \
  "URI" \
  FIELD:DATA_TYPE,FIELD:DATA_TYPE > /tmp/FILE_NAME

    取代下列項目

    • SOURCE_FORMAT：來源檔案格式

    • URI：Cloud Storage URI 或 Google 雲端硬碟 URI

      例如 Cloud Storage 的 gs://mybucket/myfile 或 Google 雲端硬碟的 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。

    • FIELD:DATA_TYPE,FIELD:DATA_TYPE：結構定義

      例如 Name:STRING,Address:STRING, ...。

    • FILE_NAME：資料表定義檔的名稱

  2. (選用) 在文字編輯器中開啟資料表定義檔。舉例來說，指令 nano /tmp/file_name 會在 nano 中開啟檔案。檔案內容大致如下。請注意，"autodetect" 未啟用，且結構定義資訊寫入了資料表定義檔。

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定，如 maxBadRecords 和 ignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定，但有設定會套用至 CSV 和 Google 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration。

使用 JSON 結構定義檔

如果您不要使用自動偵測或提供內嵌結構定義，則可建立 JSON 結構定義檔，並於建立資料表定義檔時參照 JSON 結構定義檔。在本機上，手動建立 JSON 結構定義檔。如果 JSON 結構定義檔儲存在 Cloud Storage 或雲端硬碟中，則無法參照。

搭配 Cloud Storage 資料來源使用結構定義檔案

使用 JSON 結構定義檔建立 Cloud Storage 資料來源的資料表定義：

  1. 使用 bq mkdef 指令搭配 --noautodetect 旗標建立資料表定義。mkdef 指令會產生 JSON 格式的資料表定義檔。以下範例建立了資料表定義，並且將輸出內容寫入 /tmp/file_name 檔案。

    bq mkdef \
   --noautodetect \
   --source_format=SOURCE_FORMAT \
   "URI" \
  PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    更改下列內容：

    • SOURCE_FORMAT：檔案格式
    • FILE_NAME：資料表定義檔的名稱

    • URI：Cloud Storage URI

      例如 gs://mybucket/myfile。

    • PATH_TO_SCHEMA_FILE：本機上 JSON 結構定義檔案的位置

  2. (選用) 在文字編輯器中開啟資料表定義檔。舉例來說，指令 nano /tmp/file_name 會在
    nano 中開啟檔案。檔案內容大致如下。請注意，"autodetect" 未啟用，且結構定義資訊寫入了資料表定義檔。

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "NEWLINE_DELIMITED_JSON",
"sourceUris": [
  "URI"
]
}

  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定，如 maxBadRecords 和 ignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定，但有設定會套用至 CSV 和 Google 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration。

搭配雲端硬碟資料來源使用結構定義檔案

使用 JSON 結構定義檔建立 Google 雲端硬碟資料來源的資料表定義：

  1. 使用 bq mkdef 指令搭配 --noautodetect 旗標建立資料表定義。mkdef 指令會產生 JSON 格式的資料表定義檔。以下範例建立了資料表定義，並且將輸出內容寫入 /tmp/file_name 檔案。

    bq mkdef \
   --noautodetect \
   --source_format=source_format \
   "URI" \
   PATH_TO_SCHEMA_FILE > /tmp/FILE_NAME

    更改下列內容：

    • SOURCE_FORMAT：來源檔案格式

    • URI：雲端硬碟 URI

      例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。

    • PATH_TO_SCHEMA_FILE：本機上 JSON 結構定義檔案的位置

    • FILE_NAME：資料表定義檔的名稱

  2. 在文字編輯器中開啟資料表定義檔。舉例來說，指令 nano /tmp/file_name 會在 nano 中開啟檔案。檔案內容大致如下。請注意，"autodetect" 未啟用，且結構定義資訊寫入了資料表定義檔。

    {
"schema": {
  "fields": [
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    },
    {
      "name": "FIELD",
      "type": "DATA_TYPE"
    }
    ...
  ]
},
"sourceFormat": "GOOGLE_SHEETS",
"sourceUris": [
  "URI"
]
}

  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定，如 maxBadRecords 和 ignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定，但有設定會套用至 CSV 和 Google 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration。

  4. 如要在 Google 試算表檔案中指定特定的工作表或儲存格範圍，請將 range 屬性新增至資料表定義檔中的 GoogleSheetsOptions 物件。如要查詢特定工作表，請指定工作表名稱。如要查詢儲存格範圍，請使用 sheet_name!top_left_cell_id:bottom_right_cell_id 格式指定範圍，例如 "Sheet1!A1:B20"。如果未指定 range 參數，則會使用檔案中的第一個工作表。

為自述式格式建立定義檔

Avro、Parquet 和 ORC 都是自述式格式。這些格式的資料檔案包含自己的結構定義資訊。如果您使用這些格式做為外部資料來源，BigQuery 會自動使用來源資料擷取結構定義。建立資料表定義時，不必使用結構定義自動偵測，也不須提供內嵌結構定義或結構定義檔。

對於儲存在 Cloud Storage 或雲端硬碟的 Avro、Parquet 或 ORC 資料，您可以建立資料表定義檔：

  1. 使用 bq mkdef 指令建立資料表定義。

    bq mkdef \
    --source_format=FORMAT \
    "URI" > FILE_NAME

    更改下列內容：

    • FORMAT：來源格式

    • URI：Cloud Storage URI 或 Google 雲端硬碟 URI

      例如 Cloud Storage 的 gs://mybucket/myfile 或 Google 雲端硬碟的 https://drive.google.com/open?id=123ABCD123AbcD123Abcd。

    • FILE_NAME：資料表定義檔的名稱

  2. 選用：在文字編輯器中開啟資料表定義檔。檔案內容大致如下：

    {
   "sourceFormat": "AVRO",
   "sourceUris": [
   "URI"
    ]
}

  3. 選用：手動編輯資料表定義檔，修改、新增或刪除一般設定，如 maxBadRecords 和 ignoreUnknownValues。 詳情請參閱 API 參考資料中的 ExternalDataConfiguration。

為 Hive 分區資料建立定義檔

使用 bq mkdef 指令搭配 hive_partitioning_mode 和 hive_partitioning_source_uri_prefix 旗標，為儲存在 Cloud Storage、Amazon Simple Storage Service (Amazon S3) 或 Azure Blob 儲存體中的 Hive 分割資料建立定義檔。

為 Datastore 和 Firestore 建立定義檔

如果您使用 Datastore 或 Firestore 匯出做為外部資料來源，BigQuery 會自動使用該自述式來源資料擷取結構定義。建立資料表定義時，不必提供內嵌結構定義或結構定義檔。

對於儲存在 Cloud Storage 的 Datastore 和 Firestore 匯出資料，您可以建立資料表定義檔：

  1. 使用 bq mkdef 指令建立資料表定義。您不必針對 Datastore 或 Firestore 備份檔案使用 --noautodetect 旗標，對於這些檔案類型，系統不會啟用結構定義自動偵測。mkdef 指令會產生 JSON 格式的資料表定義檔。以下範例建立了資料表定義，並且將輸出內容寫入 /tmp/file_name 檔案。

    bq mkdef \
--source_format=DATASTORE_BACKUP \
"URI" > /tmp/FILE_NAME

    更改下列內容：

    Datastore 和 Firestore 皆使用 DATASTORE_BACKUP 來源格式。

  2. (選用) 在文字編輯器中開啟資料表定義檔。舉例來說，指令 nano /tmp/file_name 會在 nano 中開啟檔案。檔案內容大致如下。請注意，這裡不必使用 "autodetect" 設定。

    {
"sourceFormat": "DATASTORE_BACKUP",
"sourceUris": [
  "gs://URI"
]
}

  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除設定，如 maxBadRecords 和 ignoreUnknownValues。Datastore 和 Firestore 匯出檔案沒有專屬的配置設定。詳情請參閱 API 參考資料中的 ExternalDataConfiguration。

建立 Bigtable 的定義檔

針對 Bigtable 建立資料表定義檔時，請手動產生 JSON 格式的檔案。目前無法針對 Bigtable 資料來源使用 mkdef 指令建立資料表定義。結構定義自動偵測也不適用於 Bigtable。如需 Bigtable 資料表定義選項清單，請參閱 REST API 參考資料中的 BigtableOptions 說明。

Bigtable 的 JSON 資料表定義檔內容大致如下。 使用此資料表定義檔時，BigQuery 會從單一資料欄系列讀取資料，將值解讀為二進位編碼整數。

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/PROJECT_ID/instances/INSTANCE_ID[/appProfiles/APP_PROFILE_ID]/tables/TABLE_NAME"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "FAMILY_ID",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ]
    }
}

更改下列內容：

  • PROJECT_ID：包含 Bigtable 叢集的專案
  • INSTANCE_ID：Bigtable 執行個體 ID
  • APP_PROFILE_ID (選用)：您要用來讀取 Bigtable 資料的應用程式設定檔 ID。應用程式設定檔設定會指出外部資料表是否使用 Data Boost 或佈建節點進行運算。
  • TABLE_NAME：您要查詢的資料表名稱
  • FAMILY_ID：資料欄系列 ID

詳情請參閱「擷取 Bigtable URI」。

資料表定義檔的萬用字元支援

如果資料分成多個檔案，可以使用星號 (*) 萬用字元選取多個檔案。使用星號萬用字元時，必須遵守下列規則：

  • 星號可以出現在物件名稱內或物件名稱的末端。
  • 系統不支援使用多個星號。例如，gs://mybucket/fed-*/temp/*.csv 是無效的路徑。
  • 系統不支援在 bucket 名稱中使用星號。

範例：

  • 以下範例說明如何選取所有資料夾中以 gs://mybucket/fed-samples/fed-sample 前置字元開頭的所有檔案：

    gs://mybucket/fed-samples/fed-sample*

  • 以下範例說明如何只選取名為 fed-samples 的資料夾和 fed-samples 的任何子資料夾中，副檔名為 .csv 的檔案：

    gs://mybucket/fed-samples/*.csv

  • 以下範例說明如何在名為 fed-samples 的資料夾中，選取命名模式為 fed-sample*.csv 的檔案。這個範例不會選取 fed-samples 子資料夾中的檔案。

    gs://mybucket/fed-samples/fed-sample*.csv

使用 bq 指令列工具時，您可能需要在某些平台上逸出星號。

如果使用星號萬用字元，請以引號括住值區和檔案名稱。舉例來說，如果您有兩個名為 fed-sample000001.csv 和 fed-sample000002.csv 的檔案，並想使用星號選取這兩個檔案，則值區 URI 會是 "gs://mybucket/fed-sample*"。

針對下列資料來源建立資料表定義檔時，無法使用 * 萬用字元：

  • Bigtable。對於 Bigtable 資料，您只能指定一個資料來源。URI 值必須是 Bigtable 資料表的有效 HTTPS 網址。
  • Datastore 或 Firestore。儲存在 Cloud Storage 中的 Datastore 或 Firestore 匯出資料。對於 Datastore 備份，您只能指定一個資料來源。URI 值的結尾必須為 .backup_info 或 .export_metadata。
  • 雲端硬碟。儲存在雲端硬碟中的資料。

後續步驟