上篇介紹 bq load 或介面匯入資料的機制及參數用法，本篇介紹各種錯誤因應方式及最佳作法

最標準的 CSV 檔案匯入 bigquery 格式

• 編碼(encoding)格式需為UTF-8
• CSV 內的欄位名稱需為英文，不可包含特殊符號及空格(只允許下底線_)，不可使用數字開頭
• CSV 內的資料需要以雙引號quote起來
• 單一CSV檔案限制容量為4TB以下，若超過4TB需要拆分多個檔案(但你若有這麼大的檔案匯入應該要用其他方式)
• csv 內的資料需要以逗點分隔，或用其他符號分隔，e.g. tab(tab 分隔), | (pipe)
• 若資料內包含雙引號，則雙引號前面需再加一個雙引號
• 範例

下面紅匡處匯入的資料會被判定失敗，因為資料內含雙引號

資料內含雙引號要在前面再加雙引號跳脫才會匯入成功，匯入後會正確顯示 Mens”s淨嫩沐浴乳

匯入 schema 的另一個有效率做法

若你的 CSV 資料需要清理空格, parse 時間格式…等清理流程，建議你在 bigquery 上用 view 或 procedure 處理比較有效率，而要處理之前可以先把所有欄位匯入成 STRING 格式，你的清理過程統一放在 view 或 procedure 比較方便管理

所以你的匯入 schema 參數可以簡化成

--schema=Member_ID:STRING, Line_ID :STRING,TEL: STRING, Birthday:STRING

另一個隱藏版寫法如下，甚至不用每個欄位寫 STRING，只要括弧起來你的欄位名字，bigquery 會統一幫你轉成 STRING 型態喔!

--schema 'Member_ID,Line_ID,TEL,Birthday'

錯誤訊息如何處理

匯入資料遇到錯誤的優先處理步驟如下

1. 根據錯誤訊息，確認 bq load 有沒有寫錯的地方

若確認沒打錯指令

2.1 根據回傳錯誤訊息，打開 CSV 確認是否為資料錯誤

2.2 若想快速印出指定 CSV 錯誤位置，資料在 GCS 上可輸入以下指令印出對應 position

gsutil cat -r 1-300(根據你的錯誤訊息列數) gs://{gcs_path}/{file_name}.csv

1. 根據資料確認該錯誤訊息的原因，修正原始資料
2. 若無法修正原始資料，且可容忍遺失部分資料，可參考以下 workaround 作法

• 資料比設定 schema 還多欄位

BigQuery error in load operation: Error processing job 'bishare-1606:bqjob_r30f35e153a69e981_0000018b05a1d07a_1': Error
while reading data, error message: Too many values in row starting at position: 22. Found 4 column(s) while expected 3.

→ 上面顯示錯誤是在 22 的位置，所以 gsutil cat -r 從 22 開始印出，e.g. gsutil cat -r 22-300

• workaround 作法

  bq load 加上 --ignore_unknown_values 參數(GUI 就是把改選項勾起來)，bigquery 只會把有列 schema 的資料匯入，後面多的都忽略不匯入

• 資料比設定 schema 還少欄位

BigQuery error in load operation: Error processing job 'bishare-1606:bqjob_r701f41bea77ba282_0000018b05a51272_1': Error
while reading data, error message: CSV table references column position 2, but line starting at position:46 contains only 2 columns. 

• workaround 作法

  bq load 加上 --allow_jagged_rows 參數(GUI 就是把改選項勾起來)，bigquery 會把少欄位的資料設定為 NULL 值

• quote 內有 quote 分隔符號，例如雙引號 quote 的資料內有雙引號

BigQuery error in load operation: Error processing job 'bishare-1606:bqjob_r3358f0602ee2f211_0000018b05abf778_1': Error
while reading data, error message: Error detected while parsing row starting at position: 77. Error: Data between close
quote character (") and field separator. 

• workaround 作法

遇到這問題無法只忽略有錯的那欄，只能用 bq load 加上 --max_bad_records={你設定忽略的筆數} 參數(GUI 就是輸入 number of errors allowed 那格)，bigquery 會把忽略有錯的那列

• 建議解法

  請照本文開頭[最標準的 CSV 檔案匯入 bigquery 格式]匯入

使用上述 workaround 作法前最好先知道這些參數的意思，請參考前一篇使用 bigquery 或 bq load 創 table 有什麼技巧? 每個參數的意思?