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

本頁面說明如何針對外部資料來源建立資料表定義檔。外部資料來源 (也稱為聯合資料來源) 是一種您可以直接查詢的資料來源,即使資料不是儲存在 BigQuery 中也一樣可以。

資料表定義檔

資料表定義檔內含外部資料表的結構定義和中繼資料,例如資料表的資料格式和相關屬性。建立資料表定義檔時,您可以使用結構定義自動偵測針對外部資料來源定義結構定義、以內嵌方式提供結構定義 (使用指令列),或提供含結構定義的 JSON 檔案。

資料表定義檔與 BigQuery CLI 搭配使用。使用 REST API 時,資料表定義檔中的屬性也可用於建立 ExternalDataConfiguration。當您使用 GCP Console 或傳統 BigQuery 網頁介面建立外部資料表時,則不會使用資料表定義檔。

您可以針對下列外部資料來源建立資料表定義檔:

  • Cloud Storage

    • 逗號分隔值 (CSV)
    • 換行符號分隔的 JSON
    • Avro 檔案
    • Cloud Datastore 匯出檔案
    • Cloud Firestore 匯出檔案
  • Google 雲端硬碟

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

事前準備

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

永久與臨時外部資料表

您可以在 BigQuery 中使用永久資料表或臨時資料表查詢外部資料來源。永久資料表是在資料集中建立並連結至外部資料來源的資料表。由於資料表為永久性,因此您可以使用資料集層級存取權控管機制,將資料表分享給其他同樣具備基礎外部資料來源存取權的使用者,而且您可以隨時查詢此資料表。

使用臨時資料表查詢外部資料來源時,您必須提交一個指令,該指令必須內含查詢並建立連結至外部資料來源的非永久性資料表。使用臨時資料表時,不用在 BigQuery 資料集內建立資料表。因為資料表不會永久儲存在資料集中,所以無法與其他使用者分享。使用臨時資料表查詢外部資料來源,對於一次性、臨時查詢外部資料,或對擷取、轉換和載入 (ETL) 處理程序而言非常有用。

您可以使用資料表定義檔描述永久或臨時外部資料表。

使用結構定義自動偵測建立資料表定義

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

針對下列資料來源建立資料表定義時,您可以使用結構定義自動偵測:

  • 儲存在 Cloud Storage 或 Google 雲端硬碟中的 JSON 檔案
  • 儲存在 Cloud Storage 或 Google 雲端硬碟中的 CSV 檔案
  • 儲存在 Google 雲端硬碟中的 Google 試算表檔案

Cloud Storage

如要使用 CLI 建立 Cloud Storage 資料來源的資料表定義,請執行以下操作:

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

    在指令中,請按照以下方式替換:

    • 將「source_format」替換為您的檔案格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_SHEETS
    • 將「file_name」替換為您的資料表定義檔名稱。
    • 將「bucket_uri」替換為您的 Cloud Storage URI,例如 gs://mybucket/myfile
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "bucket_uri" > /tmp/file_name
    
  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": [
      "bucket_uri"
    ]
    }
    
  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定,如 maxBadRecordsignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定,但有設定會套用至 CSVGoogle 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration

Google 雲端硬碟

如要使用 CLI 建立 Google 雲端硬碟資料來源的資料表定義,請執行以下操作:

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

    在指令中,請按照以下方式替換:

    • 將「source_format」替換為您的檔案格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_SHEETS
    • 將「file_name」替換為您的資料表定義檔名稱。
    • 將「drive_uri」替換為您的 Google 雲端硬碟 URI,例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd
    bq mkdef \
    --autodetect \
    --source_format=source_format \
    "drive_uri" > /tmp/file_name
    
  2. 在文字編輯器中開啟資料表定義檔。舉例來說,指令 nano /tmp/file_name 會在 nano 中開啟檔案。Google 試算表外部資料來源的檔案應會如下所示。請注意,"autodetect" 設為 true

    {
    "autodetect": true,
    "sourceFormat": "GOOGLE_SHEETS",
    "sourceUris": [
      "drive_uri"
    ]
    }
    
  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定,如 maxBadRecordsignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定,但有設定會套用至 CSVGoogle 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration

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

使用內嵌結構定義建立資料表定義

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

針對下列資料來源建立資料表定義檔時,您可以使用內嵌結構定義:

  • 儲存在 Cloud Storage 或 Google 雲端硬碟中的 JSON 檔案
  • 儲存在 Cloud Storage 或 Google 雲端硬碟中的 CSV 檔案
  • 儲存在 Google 雲端硬碟中的 Google 試算表檔案

如要使用內嵌結構定義,以便使用 CLI 建立 Cloud Storage 資料來源的資料表定義,請執行以下操作:

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

    在指令中,請按照以下方式替換:

    • 將「source_format」替換為您的檔案格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_SHEETS
    • 將「uri」替換為您的 Cloud Storage URIGoogle 雲端硬碟 URI。例如,https://drive.google.com/open?id=123ABCD123AbcD123Abcd (Google 雲端硬碟) 或 gs://mybucket/myfile (Cloud Storage)。
    • field:data_type,field:data_type 替換為您的結構定義,例如 Name:STRING,Address:STRING, ...
    • 將「file_name」替換為您的資料表定義檔名稱。
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "uri" \
    field:data_type,field:data_type > /tmp/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. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定,如 maxBadRecordsignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定,但有設定會套用至 CSVGoogle 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration

使用 JSON 結構定義檔建立資料表定義

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

針對下列資料來源建立資料表定義時,您可以使用 JSON 結構定義檔:

  • 儲存在 Cloud Storage 或 Google 雲端硬碟中的 JSON 檔案
  • 儲存在 Cloud Storage 或 Google 雲端硬碟中的 CSV 檔案
  • 儲存在 Google 雲端硬碟中的 Google 試算表檔案

Cloud Storage

如要使用 JSON 結構定義檔,以便使用 CLI 建立 Cloud Storage 資料來源的資料表定義,請執行以下操作:

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

    在指令中,請按照以下方式替換:

    • 將「source_format」替換為您的檔案格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_SHEETS
    • 將「file_name」替換為您的資料表定義檔名稱。
    • 將「bucket_uri」替換為您的 Cloud Storage URI,例如 gs://mybucket/myfile
    • 將「path_to_schema_file」替換為您本機上 JSON 結構定義檔的所在位置。
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "bucket_uri" \
    path_to_schema_file > /tmp/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": [
      "bucket_uri"
    ]
    }
    
  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定,如 maxBadRecordsignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定,但有設定會套用至 CSVGoogle 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration

Google 雲端硬碟

如要使用 JSON 結構定義檔,以便使用 CLI 建立 Google 雲端硬碟資料來源的資料表定義,請執行以下操作:

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

    在指令中,請按照以下方式替換:

    • 將「source_format」替換為您的檔案格式:NEWLINE_DELIMITED_JSONCSVGOOGLE_SHEETS
    • 將「drive_uri」替換為您的 Google 雲端硬碟 URI,例如 https://drive.google.com/open?id=123ABCD123AbcD123Abcd
    • 將「path_to_schema_file」替換為您本機上 JSON 結構定義檔的所在位置。
    • 將「file_name」替換為您的資料表定義檔名稱。
    bq mkdef \
    --noautodetect \
    --source_format=source_format \
    "drive_uri" \
    path_to_schema_file > /tmp/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": [
      "drive_uri"
    ]
    }
    
  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定,如 maxBadRecordsignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定,但有設定會套用至 CSVGoogle 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration

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

建立 Avro 資料表定義

如果您使用 Avro 檔案做為外部資料來源,BigQuery 會自動使用該來源資料擷取結構定義。您在為 Avro 檔案建立資料表定義時,不必使用結構定義自動偵測,也不須提供內嵌結構定義或結構定義檔。

對於儲存在 Cloud Storage 或 Google 雲端硬碟的 Avro 資料,您可以建立資料表定義檔。

如何使用 CLI 建立 Avro 資料的資料表定義檔:

  1. 使用指令列工具的 mkdef 指令建立資料表定義。您不必針對 Avro 檔案使用 --noautodetect 標記。對於 Avro 檔案,系統不會啟用結構定義自動偵測。mkdef 指令會產生 JSON 格式的資料表定義檔。以下範例建立了資料表定義,並且將輸出內容寫入 /tmp/file_name 檔案。

    在指令中,請按照以下方式替換:

    • 將「uri」替換為您的 Cloud Storage URIGoogle 雲端硬碟 URI。例如,https://drive.google.com/open?id=123ABCD123AbcD123Abcd (Google 雲端硬碟) 或 gs://mybucket/myfile (Cloud Storage)。
    • 將「file_name」替換為您的資料表定義檔名稱。
    bq mkdef \
    --source_format=AVRO \
    "uri" > /tmp/file_name
    
  2. (選用) 在文字編輯器中開啟資料表定義檔。舉例來說,指令 nano /tmp/file_name 會在 nano 中開啟檔案。檔案內容大致如下。請注意,這裡不必使用 "autodetect" 設定。

    {
    "sourceFormat": "AVRO",
    "sourceUris": [
      "uri"
    ]
    }
    
  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除一般設定,如 maxBadRecordsignoreUnknownValues。JSON 來源檔案沒有專屬的配置設定,但有設定會套用至 CSVGoogle 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration

建立 Cloud Datastore 和 Cloud Firestore 匯出資料表定義

如果您使用 Cloud Datastore 或 Cloud Firestore 匯出做為外部資料來源,BigQuery 會自動使用該自述式來源資料擷取結構定義。為 Cloud Datastore 和 Cloud Firestore 備份檔案建立資料表定義時,不必使用結構定義自動偵測,也不須提供內嵌結構定義或結構定義檔。

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

如要使用 CLI,建立 Cloud Datastore 或 Cloud Firestore 匯出資料的資料表定義檔,請執行以下操作:

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

    在指令中,請按照以下方式替換:

    • 將「bucket_uri」替換為您的 Cloud Storage URI
    • 將「file_name」替換為您的資料表定義檔名稱。

    請注意,Cloud Datastore 和 Cloud Firestore 皆使用 DATASTORE_BACKUP 來源格式。

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

    {
    "sourceFormat": "DATASTORE_BACKUP",
    "sourceUris": [
      "gs://bucket_uri"
    ]
    }
    
  3. (選用) 手動編輯資料表定義檔以修改、新增或刪除設定,如 maxBadRecordsignoreUnknownValues。Cloud Datastore 和 Cloud Firestore 匯出檔案沒有專屬的配置設定,但有設定會套用至 CSVGoogle 試算表檔案。詳情請參閱 API 參考資料中的 ExternalDataConfiguration

建立 Cloud BigTable 資料表定義

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

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

{
    "sourceFormat": "BIGTABLE",
    "sourceUris": [
        "https://googleapis.com/bigtable/projects/project_id/instances/instance_id/tables/table_name"
    ],
    "bigtableOptions": {
        "columnFamilies" : [
            {
                "familyId": "family_int",
                "type": "INTEGER",
                "encoding": "BINARY"
            }
        ],
    }
}

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

如果您的 Cloud Storage 資料分成多個共用通用基礎名稱的檔案,您可以在資料表定義檔的 URI 中使用萬用字元。請為基礎名稱加上星號 (*),並以引號括住值區和檔案名稱。例如,如果您有兩個名為 fed- sample000001.csvfed-sample000002.csv 的檔案,則值區 URI 會是 "gs://mybucket/fed-sample*"

對於值區內的物件 (檔案名稱),您只能使用一個萬用字元。萬用字元可以出現在物件名稱內或物件名稱的尾端。系統不支援為值區名稱加上萬用字元。

對於 Cloud BigTable 資料,您只能指定一個 URI,且該 URI 對 Cloud BigTable 資料表來說必須是完整且有效的 HTTPS 網址。對於 Cloud Datastore 備份,您只能指定一個 URI,且必須以 .backup_info 結尾。

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

  • Cloud BigTable 資料來源
  • 儲存在 Cloud Storage 中的 Cloud Datastore 匯出資料
  • 儲存在 Cloud Storage 中的 Cloud Firestore 匯出資料
  • 儲存在 Google 雲端硬碟中的資料
本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁