Google BigQuery 初試

Google Cloud Platform (GCP) 於 2017 年 4 月次第一次在臺灣舉行大型公開活動,剛好也碰到一些對於資料庫查詢回應時間的問題,對於 Google 演示Big Query 對一個將近 1T 的 table 送出包含萬用字元(*)查詢後,不到三十秒即可收到結果感覺印象深刻。

測試的衝動也油然由生,但事情不如預期容易,本來想著將 csv (comma seperated values) 檔單純匯入 Big Query 並不會太困難,挑戰才開始不斷出現,其中遇到的狀況確實也反映出 GCP 確實還有一段路要走

Big Query Schema設定需要人為介入
目前 Big Query 匯入的方式主要透過匯入檔案(csv, json, avro)或 API 上傳(https://cloud.google.com/bigquery/loading-data),在使用 csv 匯入時,Big Query 支援依資料自動偵測,但僅限用讀入的第一筆資料,若判斷的格式在之後的資料不合,一樣產生匯入失敗的情況,故轉用自定 schema 的方式。

google big query 在 detect 資料格式時錯誤

已有開發者貢獻 shell script (https://gist.github.com/shantanuo/4466298) 適合小量的表格上傳,由於測試用的表格單檔大於 5GB,故改上傳 csv 到Google Storage 後再匯入 Big Query 的方式,在 schema 的轉換則自製一支python 程式(https://gitlab.com/Maratyz/Python_for_fun/blob/master/mysql_to_bigquery_schema.py) ,將 mysql create table 的 query 轉成 big query 需要的 json格式如下,每欄需要 name, type, mode 的參數。

google big query schema in json format

Google 若能支援不同資料庫直接匯入,減少開發者們煩惱 schema 轉移的問題,相信能降低使用 Big Query 的門檻

另外,mysql 匯出 csv 的檔案中 null 值可能以 \N 呈現,這點在將 csv 匯入big query 時也會產生 parsing 錯誤,可以考慮開一個 GCP Compute Engine將 Google Storage 用 gcsfuse 掛載後做 sed 將 \N 取代,然而,大檔案的讀寫可能會讓掛載錯誤,恢復方式是直接 sudo umount -l [mount point]還原該目錄狀態。

直接對在google storage底下檔案做大量操作失敗結果 (第一次搞壞GCP就上手)

繞路將 csv 檔先複製一份到 Compute Engine 上做完大量取代的操作後再傳回 Google Storage。經過大約三十次的嘗試後,終於成功將表格放入 Big Query。

錯誤訊息不友善

錯誤訊息以字元位置表現

匯入 csv 時,GCP 是以字元位置的方式提供錯誤點給使用者,而非列數與欄數,在除錯時相對不直覺,雖然可用 head -c [location] 的方式搭配其它字元工具來查找,畢竟不如行數與該行完整資料直覺

驚人的查詢速度
即便有以上的小缺點,big query 在查詢速度上無庸置疑的驚人,對一個有180以上欄位,兩千多萬行的表格,下了一個包括萬用字完,數值大小,及group by 三個欄位的查詢,不到三秒即能取得結果。

值得注意的是 big query 似乎也支援 UDF (user-defined function),而且也能將 query 及其結果儲存,對於使用上來說蠻友善。

測試流程圖

後記
若 csv 檔已預先處理過,也準備好 schema 的話,可以直接以 gzip 的格式上傳至 Google Storage 或匯入 Big Query,減少網路傳輸用量及時間 (https://cloud.google.com/bigquery/preparing-data-for-loading)