【東東老師 X 思想科技】資料「不用」上傳到 BigQuery 的方法介紹

沒錯，資料不一定要上傳到 Google Cloud BigQuery 才能做分析，你可以讓資料就在原來的地方，讓 BigQuery 自己過去抓資料來分析，以下介紹資料「不用」上傳到 BigQuery 的方法：

(一) 外部表  (External Tables)

外部表就像是在 BigQuery 中設定一個連結，指向存放在別處的資料。連結建立後，你就一樣在 BigQuery 的介面，使用一般的 SQL 語法來查詢，就跟查詢 BigQuery 本身的表格一樣方便。

適用的資料來源包含：

• Cloud Storage 中的 CSV、JSON、Avro、Parquet、ORC 等檔案
• Google 試算表
• Bigtable

像是從雲端硬碟匯入 Google 試算表，我們在最前面已經示範過了，匯入之後，就會呈現表格的詳細資訊，有一個來源 URI 代表真的是外部表，這裡就不再贅述。

使用外部表有一些注意事項如下：

1. 查詢效能會比較慢

因為每次都要讀取外部資料，效能取決於資料來源本身，就不像在 BigQuery 身上那麼快。

2. 資料格式要一致

尤其是 CSV 的欄位順序和型態，例如 Schema 設定 3 個欄位，但某些 CSV 檔只有 2 個欄位，就會報錯「Invalid field count」；或是​​數字欄位出現文字，會造成「Invalid integer value」錯誤。

3. 檔案路徑支援萬用字元 「*」

代表你可以一口氣匯入「多個」檔案成為「一個」外部表，很適合定期新增的資料檔案，例如每天的 Log 檔，每次執行查詢時，BigQuery 都會重新掃描符合萬用字元的檔案，非常方便。

4. 權限要設定正確

你設定適當的權限給 BigQuery Service Account，例如 storage.objectViewer，確保 BigQuery 能存取Cloud Storage 的檔案。

(二) 同步查詢 (Federated Query)

Federated Query 是直接發送 SQL 查詢語法到其他資料來源，即時取得結果。這種方式更有彈性，可以直接查詢原始資料庫，尤其來源資料經常變動的話，適合用這種方式查詢。

適用的資料來源包含：

• Cloud SQL (MySQL/PostgreSQL)
• Cloud Spanner
• Alloy DB

我們以網路上示範的影片為例子呈現如下，我們現在 Spanner 準備好一個 demo_db 資料庫，點擊進入看到 emp 表格，再點進去：

我們再點擊「Data」，看到裡面已經準備好一筆資料。 

我們在回到 BigQuery 去新增外部的資料來源，點擊「ADD DATA」裡面的「External data source」： 

選擇 Spanner 之後，填上資料庫的相關資訊，如果相關內容都輸入正確，可以直接按下「Create Connection」：

接下來它秀出連線資訊，我們可以直接點擊「Query」來試著查詢資料：

它預設的語法是讓你看到整個資料庫的 Metadata，例如資料庫裡有哪些表格和欄位。 

它預設的語法是讓你看到整個資料庫的 Metadata，例如資料庫裡有哪些表格和欄位。 

我們來調整一下語法，讓它直接查詢「emp」這個表格，最後就看到原本儲存 Spanner 的裡面的資料內容，代表我們的確是連線到 Spanner 做查詢。 

使用 Federated Query 的注意事項：

1. 需要額外設定連線和授權

因為我們直接連線到來源資料庫，查詢時間可能較長。光是連線成功就需要時間等待。

2. 要注意資料量

避免查詢太大量資料，影響原始資料庫的運作，如果要大量還是把資料轉入 BigQuery 比較好。

3. SQL 語法會因資料來源而異

要配合來源資料，例如 MySQL、PostgreSQL 和 Spanner 的查詢語法可能有所不同。

4. 計費方式與一般查詢不同

它不會佔用 BigQuery 的儲存費用，但仍然會有查詢費用。如果頻繁查詢外部資料庫，也要考慮提高規格，相對也造成較高成本。

(三) 如何判斷要用原生、外部表或同步查詢？

我們已經看了各種匯入資料到 BigQuery 的方法，那要選原生或外部表或同步查詢的決策標準是什麼？這裡整理各種方法優缺點和適用的場景給你參考：

建議策略

1. 從資料更新頻率來看

(1) 如果資料每天更新 1-2 次，建議使用原生表，查詢效能最好。

(2) 如果資料持續變動 (如每分鐘)，考慮外部表或 Federated Query。

(3) 如果是即時分析需求，一定使用 Federated Query。

2. 從查詢效能來看

(1) 需要毫秒級回應，必須使用原生表。

(2) 可接受秒級延遲，使用外部表。

(3) 可接受較高延遲，使用 Federated Query 。

3. 以成本為考量

(1) 資料量大但查詢頻率低，選擇外部表或 Federated Query，不用花費 BigQuery 儲存成本。

(2) 查詢頻率高，原生表有 24 小時快取，重複讀取外部資料成本較高。

(3) 預算有限，可先用 Federated Query，再根據使用情況調整。

4. 實務建議

(1) 可採用混合策略，不同的資料採用不同的查詢方式。

• 熱門資料 (經常查詢) 使用原生表
• 冷資料 (不常查詢) 使用外部表
• 特殊即時需求用 Federated Query

(2) 建議先小規模測試：

• 先用小部分資料評估性能
• 測試實際查詢場景
• 監控成本和效能數據再決定

上傳到 BigQuery 之前的注意事項

前面看完各種上傳或不上傳到 BigQuery 的方法，除了針對採取的方法提共建議之外，這裡也建議上傳之前要確認以下注意事項：

(一) 資料品質與準備工作

你必須確保所有資料的格式是一致的，包括日期格式和數值類型都要統一，異常值和空值也要先處理好。編碼格式最好用 UTF-8，這樣比較不會出現亂碼。另外欄位名稱要注意，不能用特殊字元，不然 BigQuery 會報錯。

針對批次載入，你沒有辦法在 BigQuery 傳到一半給它按暫停，調整後再繼續傳，它錯了就是要全部重來，所以請務必謹慎處理，避免重工。

(二) 成本考量

如果資料需要整理，可以先用臨時表，等到都整理好了再存到永久表。這樣中間處理的表隔天會自動刪除，不會佔用空間也不用付費，最後的結果才存在永久表裡給大家查詢使用。

分區 (Partitioned) 策略也要規劃好，確保查詢只針對部分資料而不是整張表格，這樣可以省下不少查詢費用。要是預算有限，最好設個配額上限，免得花太多錢。

(三) 效能優化

表格結構要設計得合理，別搞得太複雜，例如盡量使用巢狀表格而非 Join 太多表格。選擇分區欄位的時候，通常用時間欄位或是基數 (Cardinality：一個欄位中不重複值的數量) 比較高的欄位會比較好。如果需要的話，也可以考慮加上叢集索引。

(四) 權限與安全性

如果上傳有使用特定工具，要遵守「最小權限原則」，給予剛好且必要的權限。資料存取的控管策略也要想清楚，特別是有敏感資料的話，可能還需要設定資料遮罩或加密。

(五) 上傳方式選擇

大部分情況都建議先傳到 Cloud Storage 再導入 BigQuery，至少資料已經先進來了，也不受檔案大小限制。記得要設定合理的 Time-Out 時間，讓傳輸工作多等待一段時間，萬一上傳失敗也要有因應的處理方式。

(六) 監控與維護

上傳過程中要做好監控，隨時掌握進度。要是上傳失敗了，得要有辦法快速恢復。定期維護和檢查資料品質也別忘了。

(七) 文件與溝通

透過文件把所有細節都記錄下來，每個欄位都要寫清楚說明，資料從哪裡來、怎麼處理的都要記錄好。跟其他團隊也要講清楚什麼時候上傳、會影響到誰。要是碰到問題，大家也才知道該怎麼處理。

五、結論

我們總共看了手動上傳、Data Transfer Service、Datastream 和串流各種方法，以及「不上傳」到 BigQuery 的外部表和 Federated Query。可以看到 BigQuery 提供的方法真的非常多，讓你可以因應各種情境，來評估和選擇最適合的方法。

至於到底要用哪一種方法，除了參考上面的整理表格之外，最重要還是建議你先以小量資料試過一遍，才會發現到更多沒提到的小細節，來幫助你做出更好的判斷。只要資料成功進來了，BigQuery 就不只能夠幫你做好分析，還能做為開發 AI 模型的基礎，幫企業產生更多價值。

文章轉載自《東東GCP 教學》網站