查詢 Cloud Storage 資料

BigQuery 支援查詢下列格式的 Cloud Storage 資料:

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

BigQuery 支援在下列儲存空間級別查詢 Cloud Storage 資料:

  • 多地區
  • 地區
  • Nearline
  • Coldline

如要直接查詢 Cloud Storage 外部資料來源,請提供資料的 Cloud Storage URI 路徑,並建立參照資料來源的資料表。用於參照 Cloud Storage 資料來源的資料表可以是永久性資料表臨時資料表

查詢儲存在 Cloud Storage 中的資料時,請務必考慮資料集和 Cloud Storage 值區的位置

擷取 Cloud Storage URI

如要使用 Cloud Storage 資料來源建立外部資料表,您必須提供 Cloud Storage URI。

Cloud Storage URI 包含您的值區名稱和物件 (檔案名稱)。舉例來說,如果 Cloud Storage 值區的名稱為 mybucket 且資料檔案的名稱為 myfile.csv,則值區 URI 會是 gs://mybucket/myfile.csv。如果資料分成多個檔案,您可以在 URI 中使用萬用字元。詳情請參閱 Cloud Storage 要求 URI 的說明。

BigQuery 並不支援來源 URI 在初始的雙斜線後面具有多個連續斜線。Cloud Storage 物件名稱可以包含多個連續的斜線 (「/」) 字元,但 BigQuery 會將多個連續斜線轉換成一個斜線。舉例來說,下列來源 URI 在 Cloud Storage 中有效,但在 BigQuery 中則為無效:gs://[BUCKET]/my//object//name

如要擷取 Cloud Storage URI:

  1. 開啟 Cloud Storage 主控台。

    Cloud Storage 主控台

  2. 瀏覽至含有來源資料的物件 (檔案) 位置。

  3. 在 Cloud Storage 主控台的頂端,您會看到物件路徑。如要撰寫 URI,請將 gs://[BUCKET]/[FILE] 替換為適當路徑,例如 gs://mybucket/myfile.json[BUCKET] 是 Cloud Storage 值區名稱,而 [FILE] 是含有資料的物件 (檔案) 名稱。

存取權控管和範圍

永久性外部資料表的存取權控管

您可以分享連結至 Cloud Storage 資料來源的永久性外部資料表的存取權。您可以和使用者 (包括服務帳戶) 或群組分享存取權。如要查詢外部資料表,您的使用者或群組 (至少) 需要擁有:

Compute Engine 執行個體的範圍

建立 Compute Engine 執行個體時,您可以指定執行個體的範圍清單。那些範圍可以控管執行個體對 GCP 產品 (包括 Cloud Storage) 的存取權。在 VM 上執行的應用程式會使用附加到執行個體的服務帳戶來呼叫 Google Cloud API。

如果將 Compute Engine 執行個體設為以預設的 Compute Engine 服務帳戶執行,且該服務帳戶會存取連結至 Cloud Storage 資料來源的外部資料表,則該執行個體需要 Cloud Storage 的唯讀權限。系統會自動將 https://www.googleapis.com/auth/devstorage.read_only 範圍授予預設的 Compute Engine 服務帳戶。如果您建立自己的服務帳戶,請將 Cloud Storage 唯讀範圍套用於執行個體。

如要瞭解 Compute Engine 執行個體的套用範圍,請參閱變更執行個體的服務帳戶與存取範圍一節。如要深入瞭解 Compute Engine 服務帳戶,請參閱服務帳戶一文。

永久與臨時外部資料表

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

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

使用永久性外部資料表查詢 Cloud Storage 資料

如要使用永久性資料表查詢外部資料來源,可在 BigQuery 資料集中建立連接外部資料來源的資料表。資料不會儲存在 BigQuery 資料表中。由於資料表為永久性,因此您可以使用資料集層級存取權控管,將資料表分享給其他同樣具備基礎外部資料來源存取權的使用者。

在 BigQuery 中建立永久外部資料表時,您可以透過三種方式指定結構定義資訊:

  • 如果您是使用 API 來建立永久外部資料表,請先從建立資料表定義檔開始;這個檔案的用途是定義外部資料來源的結構定義和中繼資料。建立資料表定義檔時,您可以啟用結構定義自動偵測來找出支援的資料來源。
  • 如果您是使用 CLI 來建立永久外部資料表,可以使用資料表定義檔、建立及使用自己的結構定義檔,或是在指令列以內嵌方式輸入結構定義。
  • 如果您是使用主控台或傳統 BigQuery 網頁版 UI 來建立永久外部資料表,可以手動輸入資料表結構定義,或使用結構定義自動偵測來找出支援的資料來源。

如要使用永久性外部資料表查詢 Cloud Storage 資料,您必須:

  • 建立資料表定義檔 (適用於 API,以及有條件地適用 CLI)
  • 在 BigQuery 中建立連結至外部資料來源的資料表
  • 查詢已連結至外部資料來源的資料表

建立永久外部資料表

您可以透過下列方式建立已連結至外部資料來源的永久資料表:

主控台

  1. 在 GCP 主控台中開啟 BigQuery 網頁版 UI。
    前往 BigQuery 網頁版 UI
  2. 在導覽面板的「Resources」(資源) 部分,展開您的專案並選取資料集。按一下視窗右側的 [Create table] (建立資料表)建立資料表
  3. 在「Create table」(建立資料表) 頁面的「Source」(來源) 區段中:

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

      建立資料表來源

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

      選取檔案

    • 針對「File format」(檔案格式) 選取資料格式。Cloud Storage 資料的有效格式包括:

      • 逗號分隔值 (CSV)
      • JSON (以換行符號分隔)
      • Avro
      • Cloud Datastore 備份 (也能用在 Cloud Firestore)
  4. 在「Create table」(建立資料表) 頁面的「Destination」(目的地) 區段中:

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

      選取資料集

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

    • 確認「Table type」(資料表類型) 已設為 [External table] (外部資料表)。

  5. 在「Schema」(結構定義) 區段中,輸入結構定義

    • 如果是 JSON 或 CSV 檔案,您可以勾選 [Auto-detect] (自動偵測) 選項來啟用結構定義自動偵測功能。[Auto-detect] (自動偵測) 不適用於 Cloud Datastore 匯出、Cloud Firestore 匯出和 Avro 檔案。系統會自動從自述來源資料中擷取結構定義資訊。
    • 透過以下方式,手動輸入結構定義資訊:
      • 按一下 [Edit as text] (以文字形式編輯),然後以 JSON 陣列的形式輸入資料表結構定義。附註:您可以輸入下列指令,查看現有資料表的 JSON 格式結構定義:bq show --format=prettyjson [DATASET].[TABLE]
      • 使用 [Add Field] (新增欄位) 手動輸入結構定義
  6. 按一下 [Create table] (建立資料表)

建立永久性資料表後,您就可以在外部資料來源的限制下,把這個資料表當做原生 BigQuery 資料表一樣進行查詢。

查詢完成後,可以將結果匯出為 CSV 或 JSON 檔案、將結果儲存為資料表,或將結果儲存至 Google 試算表。詳情請參閱下載、儲存及匯出資料一文。

傳統版 UI

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

  2. 在導覽面板中,將游標懸停在資料集上,然後按一下向下箭號圖示 向下箭號圖示圖片 並點選 [Create new table] (建立新資料表)

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

    • 針對「Location」(位置) 選取 [Cloud Storage],然後在來源欄位中輸入 Cloud Storage URI。請注意,Cloud Storage URI 支援使用萬用字元
    • 針對「File format」(檔案格式) 選取資料格式。Cloud Storage 資料的有效格式包括:

      • 逗號分隔值 (CSV)
      • JSON (以換行符號分隔)
      • Avro
      • Cloud Datastore 備份 (也能用在 Cloud Firestore)
  4. 在「Create Table」(建立資料表) 頁面的「Destination Table」(目標資料表) 區段中:

    • 針對「Table name」(資料表名稱) 選擇適當的資料集,然後在資料表名稱欄位中,輸入要在 BigQuery 中建立的永久資料表名稱。
    • 確認「Table type」(資料表類型) 已設為 [External table] (外部資料表)。
  5. 在「Schema」(結構定義) 部分中,輸入結構定義資訊。

    • 如果是 JSON 或 CSV 檔案,您可以勾選 [Auto-detect] (自動偵測) 選項來啟用結構定義自動偵測功能。[Auto-detect] (自動偵測) 不適用於 Cloud Datastore 匯出、Cloud Firestore 匯出和 Avro 檔案。系統會自動從自述來源資料中擷取結構定義資訊。

    • 您也可以使用下列方式,手動輸入 CSV 或 JSON 結構定義資訊:

      • 按一下 [Edit as text] (以文字形式編輯),然後以 JSON 格式輸入資料表結構定義
      • 使用 [Add Field] (新增欄位) 手動輸入結構定義
  6. 在「Options」(選項) 區段選取適用的項目,然後按一下 [Create Table] (建立資料表)

建立永久性資料表後,您就可以在外部資料來源的限制下,把這個資料表當做原生 BigQuery 資料表一樣進行查詢。

查詢完成後,可以將結果匯出為 CSV 或 JSON 檔案、將結果儲存為資料表,或將結果儲存至 Google 試算表。詳情請參閱下載、儲存及匯出資料一文。

CLI

您可以在 BigQuery 指令列工具中使用 bq mk 指令建立資料表。使用 CLI 建立已連結至外部資料來源的資料表時,您可以透過以下項目識別資料表的結構定義:

  • 資料表定義檔 (儲存在本機)
  • 內嵌結構定義
  • JSON 結構定義檔 (儲存在本機電腦)

如要使用資料表定義檔建立連結至 Cloud Storage 資料來源的永久性資料表,請輸入以下指令。

bq mk --external_table_definition=[DEFINITION_FILE] [DATASET_ID].[TABLE_NAME]

其中:

  • [DEFINITION_FILE] 是本機電腦上的資料表定義檔路徑
  • [DATASET_ID] 是資料表所屬的資料集名稱
  • [TABLE_NAME] 是您要建立的資料表名稱

舉例來說,以下指令會使用名稱為 mytable_def 的資料表定義檔,建立名為 mytable 的永久資料表。

bq mk --external_table_definition=/tmp/mytable_def mydataset.mytable

如要使用內嵌結構定義建立已連結至外部資料來源的永久資料表,請輸入下列指令。

bq mk --external_table_definition=[SCHEMA]@[SOURCE_FORMAT]=[CLOUD_STORAGE_URI] [DATASET_ID].[TABLE_NAME]

其中:

  • [SCHEMA] 是結構定義 (格式為 [FIELD]:[DATA_TYPE], [FIELD]:[DATA_TYPE])
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUP (DATASTORE_BACKUP 也可被使用在 Cloud Firestore)
  • [CLOUD_STORAGE_URI] 是您的 Cloud Storage URI
  • [DATASET_ID] 是含有資料表的資料集名稱
  • [TABLE_NAME] 是您要建立的資料表名稱

舉例來說,以下指令會使用下列結構定義,建立名稱為 sales 且連結至儲存在 Cloud Storage 中的 CSV 檔案的永久性資料表:Region:STRING,Quarter:STRING,Total_sales:INTEGER

bq mk --external_table_definition=Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv mydataset.sales

如要使用 JSON 結構定義檔來建立連結至外部資料來源的永久性資料表,請輸入以下指令。

bq mk --external_table_definition=[SCHEMA_FILE]@[SOURCE_FORMAT]=[CLOUD_STORAGE_URI] [DATASET_ID].[TABLE_NAME]

其中:

  • [SCHEMA_FILE] 是本機上 JSON 結構定義檔的路徑
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUP (DATASTORE_BACKUP 也可被使用在 Cloud Firestore)
  • [CLOUD_STORAGE_URI] 是您的 Cloud Storage URI
  • [DATASET_ID] 是含有資料表的資料集名稱
  • [TABLE_NAME] 是您要建立的資料表名稱

舉例來說,以下指令會使用 /tmp/sales_schema.json 結構定義檔,建立名稱為 sales 且連結至儲存在 Cloud Storage 中的 CSV 檔案的資料表。

bq mk --external_table_definition=/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv mydataset.sales

建立永久性資料表後,您就可以在外部資料來源的限制下,把這個資料表當做原生 BigQuery 資料表一樣進行查詢。

查詢完成後,可以將結果下載為 CSV 或 JSON、將結果儲存為資料表,或將結果儲存至 Google 試算表。詳情請參閱下載、儲存及匯出資料一文。

API

Python

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

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

# Configure the external data source
dataset_ref = client.dataset(dataset_id)
table_id = 'us_states'
schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING')
]
table = bigquery.Table(dataset_ref.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig('CSV')
external_config.source_uris = [
    'gs://cloud-samples-data/bigquery/us-states/us-states.csv',
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table.external_data_configuration = external_config

# Create a permanent table linked to the GCS file
table = client.create_table(table)  # API request

# Example query to find states starting with 'W'
sql = 'SELECT * FROM `{}.{}` WHERE name LIKE "W%"'.format(
    dataset_id, table_id)

query_job = client.query(sql)  # API request

w_states = list(query_job)  # Waits for query to finish
print('There are {} states with names starting with W.'.format(
    len(w_states)))

使用臨時資料表查詢 Cloud Storage 資料

如要查詢外部資料來源而不建立永久性資料表,請執行合併下列的指令:

系統會使用資料表定義檔或提供的結構定義來建立臨時外部資料表,然後對臨時外部資料表執行查詢。BigQuery CLI 及 API 支援使用臨時資料表查詢外部資料來源。

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

建立及查詢臨時資料表

您可以使用 CLI 或 API,建立及查詢已連結至外部資料來源的臨時資料表。

CLI

您可以搭配 --external_table_definition 標記使用 bq query 指令,查詢已連結至外部資料來源的臨時資料表。使用 CLI 查詢已連結至外部資料來源的臨時資料表時,可以透過以下項目識別資料表的結構定義:

  • 資料表定義檔 (儲存在本機)
  • 內嵌結構定義
  • JSON 結構定義檔 (儲存在本機電腦)

提供 --location 標記,並將值設為您的位置

如要使用資料表定義檔查詢連接外部資料來源的臨時資料表,請輸入下列指令。

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[DEFINITION_FILE] '[QUERY]'

其中:

  • [LOCATION] 是您的位置名稱。如果您的資料位於 USEU 多區域位置,則 --location 標記為選用。
  • [TABLE_NAME] 是要建立的臨時資料表名稱
  • [DEFINITION_FILE] 是本機電腦上的資料表定義檔路徑
  • [QUERY] 是要提交至臨時資料表的查詢

舉例來說,下列指令會使用名為 sales_def 的資料表定義檔,建立及查詢名為 sales 的臨時資料表。

bq --location=US query --external_table_definition=sales::sales_def 'SELECT Region,Total_sales FROM sales;'

如要使用內嵌結構定義來查詢已連結至外部資料來源的臨時資料表,請輸入下列指令。

bq --location=[LOCATION] query --external_table_definition=[TABLE_NAME]::[SCHEMA]@[SOURCE_FORMAT]=[CLOUD_STORAGE_URI] '[QUERY]'

其中:

  • [LOCATION] 是您的位置名稱。如果您的資料位於 USEU 多區域位置,則 --location 標記為選用。
  • [TABLE_NAME] 是要建立的臨時資料表名稱
  • [SCHEMA] 是內嵌結構定義 (格式為 [FIELD]:[DATA_TYPE],[FIELD]:[DATA_TYPE])
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUP (DATASTORE_BACKUP 也可被使用在 Cloud Firestore)
  • [CLOUD_STORAGE_URI] 是您的 Cloud Storage URI
  • [QUERY] 是要提交至臨時資料表的查詢

舉例來說,以下指令會使用下列結構定義,建立並查詢名稱為 sales 且連結至儲存在 Cloud Storage 中的 CSV 檔案的暫時性資料表:Region:STRING,Quarter:STRING,Total_sales:INTEGER

bq --location=US query --external_table_definition=sales::Region:STRING,Quarter:STRING,Total_sales:INTEGER@CSV=gs://mybucket/sales.csv 'SELECT Region,Total_sales FROM sales;'

如要使用 JSON 結構定義檔來查詢連結至外部資料來源的暫時性資料表,請輸入以下指令。

bq --location=[LOCATION] query --external_table_definition=[SCHEMA_FILE]@[SOURCE_FORMAT]=[CLOUD_STORAGE_URI] '[QUERY]'

其中:

  • [LOCATION] 是您的位置名稱。如果您的資料位於 USEU 多區域位置,則 --location 標記為選用。
  • [SCHEMA_FILE] 是本機上 JSON 結構定義檔的路徑
  • [SOURCE_FORMAT]CSVNEWLINE_DELIMITED_JSONAVRODATASTORE_BACKUP (DATASTORE_BACKUP 也可被使用在 Cloud Firestore)
  • [CLOUD_STORAGE_URI] 是您的 Cloud Storage URI
  • [QUERY] 是要提交至臨時資料表的查詢

    舉例來說,下列指令會使用 /tmp/sales_schema.json 結構定義檔來建立和查詢名稱為 sales 且與 Cloud Storage 中儲存的 CSV 檔連結的臨時資料表。

    bq --location=US query --external_table_definition=sales::/tmp/sales_schema.json@CSV=gs://mybucket/sales.csv 'SELECT Region,Total_sales FROM sales;'

API

Python

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

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

# Configure the external data source and query job
external_config = bigquery.ExternalConfig('CSV')
external_config.source_uris = [
    'gs://cloud-samples-data/bigquery/us-states/us-states.csv',
]
external_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING')
]
external_config.options.skip_leading_rows = 1  # optionally skip header row
table_id = 'us_states'
job_config = bigquery.QueryJobConfig()
job_config.table_definitions = {table_id: external_config}

# Example query to find states starting with 'W'
sql = 'SELECT * FROM `{}` WHERE name LIKE "W%"'.format(table_id)

query_job = client.query(sql, job_config=job_config)  # API request

w_states = list(query_job)  # Waits for query to finish
print('There are {} states with names starting with W.'.format(
    len(w_states)))

Cloud Storage URI 的萬用字元支援

如果您的 Cloud Storage 資料分成多個共用通用基礎名稱的檔案,您可以在資料表定義檔的 URI 中使用萬用字元。您也可以在不使用資料表定義檔來建立外部資料表時使用萬用字元。

如要新增萬用字元至 Cloud Storage URI,請附加星號 (*) 到基礎名稱尾端。舉例來說,如果您有兩個名為 fed-sample000001.csvfed-sample000002.csv 的檔案,則值區 URI 會是 gs://mybucket/fed-sample*。這個萬用字元 URI 之後可以在主控台、傳統版 UI、CLI 或 API 中使用。

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

對於 Google Cloud Datastore 匯出檔案,只能指定一個 URI,而且必須以 .backup_info.export_metadata 結尾。

下列情況「不」允許使用 * 萬用字元

  • 建立外部資料表,並將其連結至 Cloud Datastore 或 Cloud Firestore 匯出檔案
  • 載入 Cloud Datastore 或 Cloud Firestore 自 Cloud Storage 匯出資料

_FILE_NAME 虛擬資料欄

以外部資料來源為基礎的資料表可提供名為 _FILE_NAME 的虛擬資料欄。這個資料欄含有該列所屬檔案的完整路徑。此資料欄僅適用於參照儲存在 Google Cloud StorageGoogle 雲端硬碟中的外部資料的資料表。

系統會保留 _FILE_NAME 資料欄名稱,這表示您無法在任何資料表中使用該名稱建立資料欄。如要選取 _FILE_NAME 的值,您必須使用別名。下方範例查詢示範如何透過指派別名 fn 給虛擬資料欄的方式來選取 _FILE_NAME

bq query --project_id=[PROJECT_ID] --use_legacy_sql=false 'SELECT name, _FILE_NAME AS fn from [DATASET].[TABLE_NAME] where name contains "Alex";' 

其中:

  • [PROJECT_ID] 是有效的專案 ID (如果您使用 Cloud Shell,或是在 Cloud SDK 中設定預設專案,則此為選用標記)
  • [DATASET] 是儲存永久外部資料表的資料集名稱
  • [TABLE_NAME] 是永久外部資料表的名稱

本頁內容對您是否有任何幫助?請提供意見:

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

這個網頁