使用結構定義自動偵測功能
結構定義自動偵測
啟用結構定義自動偵測功能後,BigQuery 就能推導 CSV、JSON 或 Google 試算表資料的結構定義。無論是要將資料載入至 BigQuery 或是查詢外部資料來源,您都可以使用結構定義自動偵測功能。
啟用自動偵測功能後,BigQuery 會推斷每個資料欄的資料類型。BigQuery 會在資料來源中隨機選取檔案,並掃描最多前 500 列的資料,做為代表性樣本。BigQuery 接著會檢查每個欄位,並嘗試根據樣本中的值為各欄位指派資料類型。如果資料欄中的所有資料列都為空白,自動偵測功能會將資料欄的預設值設為 STRING
資料類型。
如果您未針對 CSV、JSON 或 Google 試算表資料啟用結構定義自動偵測功能,則必須在建立資料表時手動提供結構定義。
您不需要為 Avro、Parquet、ORC、Firestore 匯出或 Datastore 匯出檔案啟用結構定義自動偵測功能。這些檔案格式是自述式,因此 BigQuery 會自動根據來源資料推論資料表結構定義。針對 Parquet、Avro 和 Orc 檔案,您可以選擇提供明確的結構定義來覆寫推斷的結構定義。
您可以透過下列方式查看資料表的結構定義偵測結果:
- 使用 Google Cloud 控制台。
- 使用 bq 指令列工具的
bq show
指令。
當 BigQuery 偵測到結構定義時,可能會在極少數情況下更改欄位名稱,這是為了要配合 GoogleSQL 的語法。
如需資料類型轉換的資訊,請參閱以下內容:
- 在從 Datastore 載入資料時進行資料類型轉換
- 在從 Firestore 載入資料時進行資料類型轉換
- Avro 轉換
- Parquet 轉換
- ORC 轉換
使用結構定義自動偵測功能載入資料
如要在載入資料時啟用結構定義自動偵測功能,請使用下列任一方法:
- 在 Google Cloud 主控台的「Schema」(結構定義) 區段中,針對「Auto detect」(自動偵測),勾選「Schema and input parameters」(結構定義和輸入參數) 選項。
- 在 bq 指令列工具中,使用
bq load
指令搭配--autodetect
參數。
啟用結構定義自動偵測功能後,BigQuery 會盡可能嘗試自動推導 CSV 和 JSON 檔案的結構定義。自動偵測邏輯會讀取前 500 列資料,推論結構定義欄位類型。如果有 --skip_leading_rows
標記,系統會略過前導線。欄位類型會根據擁有最多欄位的資料列。因此,只要每個資料欄/欄位至少有一列資料含有值,自動偵測功能就會正常運作。
結構定義自動偵測功能不適用於 Avro 檔案、Parquet 檔案、ORC 檔案、Firestore 匯出檔案或 Datastore 匯出檔案。您將這些檔案載入至 BigQuery 時,系統會透過自述式來源資料自動擷取資料表結構定義。
如何在載入 JSON 和 CSV 資料時使用結構定義自動偵測功能:
主控台
前往 Google Cloud 控制台的「BigQuery」頁面。
在「Explorer」面板中展開專案並選取資料集。
展開
「動作」選項,然後按一下「開啟」。在詳細資料面板中,按一下「Create table」(建立資料表) 圖示
。在「Create table」(建立資料表) 頁面的「Source」(來源) 區段中:
- 在「Create table from」(使用下列資料建立資料表) 部分,選取您想要的來源類型。
在「Source」(來源) 欄位中,瀏覽檔案/Cloud Storage 值區,或輸入 Cloud Storage URI。請注意, Google Cloud 主控台中無法加入多個 URI,但支援使用萬用字元。Cloud Storage 值區的位置必須與待建立資料表所在的資料集位置相同。
在「File format」(檔案格式) 部分,選取 [CSV] 或 [JSON]。
在「Create table」(建立資料表) 頁面的「Destination」(目的地) 區段中:
在「Dataset name」(資料集名稱) 部分選擇適當的資料集。
在「Table name」(資料表名稱) 欄位中,輸入您建立資料表時所使用的名稱。
確認「Table type」(資料表類型) 已設為「Native table」(原生資料表)。
點選「建立資料表」。
bq
發出 bq load
指令並搭配使用 --autodetect
參數。
(選用) 提供 --location
旗標,並將值設為您的位置。
下方指令會使用結構定義自動偵測功能載入檔案:
bq --location=LOCATION load \ --autodetect \ --source_format=FORMAT \ DATASET.TABLE \ PATH_TO_SOURCE
更改下列內容:
LOCATION
:您所在位置的名稱。--location
是選用旗標。舉例來說,如果您在東京地區使用 BigQuery,請將該旗標的值設為asia-northeast1
。您可以使用 .bigqueryrc 檔案設定位置的預設值。FORMAT
:NEWLINE_DELIMITED_JSON
或CSV
。DATASET
:資料集包含您要載入資料的資料表。TABLE
:您要載入資料的資料表名稱。PATH_TO_SOURCE
:CSV 或 JSON 檔案的位置。
範例:
輸入下列指令,可將 myfile.csv
從您的本機載入至儲存於 mydataset
資料集內名稱為 mytable
的資料表。
bq load --autodetect --source_format=CSV mydataset.mytable ./myfile.csv
輸入下列指令,可將 myfile.json
從您的本機載入至儲存於 mydataset
資料集內名稱為 mytable
的資料表。
bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON \
mydataset.mytable ./myfile.json
API
建立指向來源資料的
load
工作。如要瞭解如何建立工作,請參閱「透過程式執行 BigQuery 工作」。在jobReference
區段的location
屬性中指定您的位置。設定
sourceFormat
屬性來指定資料格式。如要使用結構定義自動偵測功能,這個值必須設為NEWLINE_DELIMITED_JSON
或CSV
。使用
autodetect
屬性將結構定義自動偵測功能設為true
。
Go
在嘗試這個範例之前,請先按照 BigQuery 快速入門:使用用戶端程式庫中的 Go 設定說明進行操作。詳情請參閱 BigQuery Go API 參考說明文件。
如要向 BigQuery 進行驗證,請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。
Java
在嘗試這個範例之前,請先按照 BigQuery 快速入門:使用用戶端程式庫中的 Java 設定說明進行操作。詳情請參閱 BigQuery Java API 參考說明文件。
如要向 BigQuery 進行驗證,請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。
Node.js
在嘗試這個範例之前,請先按照 BigQuery 快速入門:使用用戶端程式庫中的 Node.js 設定說明進行操作。詳情請參閱 BigQuery Node.js API 參考說明文件。
如要向 BigQuery 進行驗證,請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。
PHP
在嘗試這個範例之前,請先按照 BigQuery 快速入門:使用用戶端程式庫中的 PHP 設定說明進行操作。詳情請參閱 BigQuery PHP API 參考說明文件。
如要向 BigQuery 進行驗證,請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。
Python
如要啟用結構定義自動偵測功能,請將 LoadJobConfig.autodetect 屬性設為 True
。
在嘗試這個範例之前,請先按照 BigQuery 快速入門:使用用戶端程式庫中的 Python 設定說明進行操作。詳情請參閱 BigQuery Python API 參考說明文件。
如要向 BigQuery 進行驗證,請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。
Ruby
在嘗試這個範例之前,請先按照 BigQuery 快速入門:使用用戶端程式庫中的 Ruby 設定說明進行操作。詳情請參閱 BigQuery Ruby API 參考說明文件。
如要向 BigQuery 進行驗證,請設定應用程式預設憑證。詳情請參閱「設定用戶端程式庫的驗證機制」。
自動偵測外部資料來源的結構定義
結構定義自動偵測功能可搭配 CSV、JSON 和 Google 試算表外部資料來源使用。啟用結構定義自動偵測功能後,BigQuery 會盡可能嘗試從來源資料自動推測結構定義。如果您未為這些來源啟用結構定義自動偵測功能,則必須提供明確的結構定義。
查詢外部 Avro、Parquet、ORC、Firestore 匯出檔案或 Datastore 匯出檔案時,您不需要啟用結構定義自動偵測功能。這些檔案格式是自述式,因此 BigQuery 會自動根據來源資料推論資料表結構定義。針對 Parquet、Avro 和 Orc 檔案,您可以選擇提供明確的結構定義來覆寫推斷的結構定義。
您可以使用 Google Cloud 主控台,勾選「Auto detect」的「Schema and input parameters」選項,啟用結構定義自動偵測功能。
使用 bq 指令列工具,您可以在為 CSV、JSON 或 Google 試算表資料建立資料表定義檔時,啟用結構定義自動偵測功能。使用 bq 工具建立資料表定義檔案時,您可以將 --autodetect
旗標傳送至 mkdef
指令以啟用結構定義自動偵測功能,或是傳送 --noautodetect
旗標來停用自動偵測。
當您使用 --autodetect
旗標時,資料表定義檔中的 autodetect
設定會設為 true
。當您使用 --noautodetect
旗標時,資料表定義檔中的 autodetect
設定會設為 false
。如果您在建立資料表定義時,沒有提供外部資料來源的結構定義,而且未使用 --noautodetect
或 --autodetect
旗標,則 autodetect
設定會預設為 true
。
當您使用 API 建立資料表定義檔時,請將 autodetect
屬性值設為 true
或 false
。將 autodetect
設為 true
可啟用自動偵測功能;將 autodetect
設為 false
則可停用自動偵測功能。
自動偵測詳細資料
除了偵測結構定義詳細資料外,自動偵測功能還可識別以下內容:
壓縮
BigQuery 在開啟檔案時,可識別與 gzip 相容的檔案壓縮格式。
日期和時間值
BigQuery 會根據來源資料的格式偵測日期和時間值。
DATE
欄中的值必須採用以下格式:YYYY-MM-DD
。
TIME
欄中的值必須採用以下格式:HH:MM:SS[.SSSSSS]
(秒的部分可省略)。
針對 TIMESTAMP
欄,BigQuery 可偵測多種時間戳記格式,包括但不限於以下格式:
YYYY-MM-DD HH:MM
YYYY-MM-DD HH:MM:SS
YYYY-MM-DD HH:MM:SS.SSSSSS
YYYY/MM/DD HH:MM
時間戳記也可包含 UTC 偏移或 UTC 區域指定元 ('Z')。
以下是 BigQuery 會自動偵測為時間戳記值的值範例:
- 2018-08-19 12:11
- 2018-08-19 12:11:35.22
- 2018/08/19 12:11
- 2018-08-19 07:11:35.220 -05:00
如果 BigQuery 無法辨識格式,就會將該欄載入為字串資料類型。在這種情況下,您可能需要先對來源資料進行預處理,再載入資料。舉例來說,如果您要從試算表匯出 CSV 資料,請將日期格式設為與這裡顯示的其中一個範例相符。或者,您也可以在將資料載入 BigQuery 後進行轉換。
針對 CSV 資料自動偵測結構定義
CSV 分隔符號
BigQuery 可偵測以下分隔符號:
- 半形逗號 ( , )
- 管線符號 ( | )
- 定位點 ( \t )
CSV 標頭
BigQuery 會將檔案的第一個資料列與檔案中的其他資料列做比較,藉此推測出標題。如果第一行只包含字串,而其他行包含其他資料類型,BigQuery 會假設第一行是標題資料列。BigQuery 會根據標題列中的欄位名稱指派資料欄名稱。名稱可能會經過修改,以符合 BigQuery 資料欄的命名規則。例如,空格會替換為底線。
否則,BigQuery 會假設第一列是資料列,並指派 string_field_1
等通用欄名稱。請注意,資料表建立後,雖然您可以在資料表建立後手動變更名稱,但無法在結構定義中更新資料欄名稱。另一個選項是提供明確的結構定義,而非使用自動偵測功能。
您可能會使用含有標題列的 CSV 檔案,其中所有資料欄位都是字串。在這種情況下,BigQuery 不會自動偵測第一列是標題。使用 --skip_leading_rows
選項略過標題列。否則,系統會將標頭匯入為資料。在這種情況下,您也可以考慮提供明確的結構定義,以便指派資料欄名稱。
CSV 引用的新行
BigQuery 會偵測 CSV 欄位內引用的新行字元,但不會將引用的新行字元解讀為資料列邊界。
自動偵測 JSON 資料的結構定義
JSON 巢狀和重複欄位
BigQuery 會推測 JSON 檔案中的巢狀與重複欄位。如果欄位值是 JSON 物件,BigQuery 會將資料欄載入為 RECORD
類型。如果欄位值是陣列,BigQuery 會將資料欄載入為重複欄。如需包含巢狀和重複資料的 JSON 資料範例,請參閱「載入巢狀和重複的 JSON 資料」。
字串轉換
如果您啟用結構定義自動偵測功能,BigQuery 會盡可能將字串轉換為布林值、數值或日期/時間類型。舉例來說,如果使用下列 JSON 資料,結構定義自動偵測功能會將 id
欄位轉換為 INTEGER
欄:
{ "name":"Alice","id":"12"}
{ "name":"Bob","id":"34"}
{ "name":"Charles","id":"45"}
詳情請參閱「從 Cloud Storage 載入 JSON 資料」。
Google 試算表的結構定義自動偵測功能
對於試算表,BigQuery 會自動偵測第一列是否為標題列,這與 CSV 檔案的自動偵測功能類似。如果第一行被視為標題,BigQuery 會根據標題列中的欄位名稱指派資料欄名稱,並略過該列。系統可能會修改名稱,以符合 BigQuery 資料欄的命名規則。例如,空格會替換為底線。
表格安全性
如要控管 BigQuery 中資料表的存取權,請參閱「使用 IAM 控管資源存取權」。