BigQuery - 填充表

初學 BigQuery 時，立即使用其函式並編寫SQL 查詢 的最快方法之一是提供現有的資料來源。最容易上傳和開始使用的其中一個數據源是靜態檔案。

BigQuery 接受的檔案型別

BigQuery 將從檔案建立的任何表都視為外部表。

BigQuery 接受來自GCP 的 Google Cloud Storage 中的本地上傳或雲端儲存的檔案輸入。

接受的檔案型別包括：

就像建立空表一樣，要填充資料，需要指定模式。提醒一下，模式可以是推斷的（自動生成的），也可以在 UI 中或作為 JSON 檔案提供。

使用外部檔案（尤其是 CSV）的挑戰之一是 BigQuery 對某些資料型別特別講究。

在上傳檔案之前，最好檢查 BigQuery 是否支援資料型別並相應地調整輸入。例如，BigQuery 對其攝取時間戳資料的方式非常挑剔，即使對於縮寫日期，也更傾向於每個值有兩個整數而不是一個。

開發人員在將檔案載入到任何 SQL 表時遇到的另一個挑戰是存在特殊字元，例如換行符“\n”。雖然始終建議以程式設計方式刪除這些字元並充分清理資料，但 BigQuery 提供了過濾掉或完全忽略可能導致檔案上傳錯誤的行的方法。

不幸的是，即使使用這些選項，確定檔案是否可以上傳的唯一方法是一些嘗試、錯誤和迭代。

根據情況的不同，有兩種方法可以從“頭開始”（即簡單的 SQL 語句）填充表。

第一個，CREATE OR REPLACE，已經介紹過了。在這種情況下，開發人員正在建立一個全新的表，並且必須定義表模式以及表內容。通常，當彙總或擴充套件 SQL 表中已存在的資料時，最好使用 CREATE OR REPLACE。

如果您發現自己有一個表的“外殼”，包括一個定義良好的模式但還沒有新增資料，並且您沒有像外部檔案或 API 負載這樣的來源，那麼新增資料的方法是使用 INSERT 命令。

在這種情況下，要正確使用 INSERT，不僅需要定義列、型別和模式，還需要提供要插入的資料。

在 SQL 的範圍內定義資料涉及提供值和列別名。例如：

要新增多行，請使用 UNION 命令。對於不熟悉 UNION 的人來說，它類似於 Pandas 的concat，它實際上是將行條目“堆疊”在一起，而不是根據公共鍵進行連線。

UNION 有兩種：

要確保使用 UNION 正確插入資料，必須確保資料型別彼此一致。例如，由於我為 station_id 提供了一個 STRING 值，因此我無法將後續行的型別更改為 INTEGER。

在專業環境中，INSERT 不用於一次插入一行。相反，INSERT 通常用於插入資料分割槽，通常按日期，即最近一天的資料。

正如現在所看到的那樣，BigQuery 中的查詢與在本地環境中編寫 SQL 不同，部分原因是開發人員能夠將 BigQuery 資料與 GCP 的基於雲的工具同步。最強大和最直觀的整合之一是將 BigQuery 與 Google Sheets 結合使用。

瀏覽外部表源的下拉選單將顯示“表格”作為開發人員可以使用的資料來源來使用資料填充表。

使用 Google 表格作為主要資料來源的原因非常有用，因為與靜態 CSV 不同，表格是動態實體。這意味著對連線的表格中的行所做的任何更改都將即時反映在關聯的 BigQuery 表中。

要將 Google 表格連線到 BigQuery 表，請遵循原始建立表的過程。您無需從上傳中選擇建立表，而是需要選擇“驅動器”作為源。即使 Google 表格是在表格 UI 中建立的，它們也位於 Google Drive 中。

與 CSV 一樣，連線的表格必須具有已定義的標題。但是，與 CSV 不同的是，開發人員可以指定要包含和省略的列。

語法類似於建立和執行 Excel 公式。只需編寫列字母和行號即可。要選擇給定行之後的全部行，請在列前加上感嘆號。

Like: !A2:M

使用相同的語法，您甚至可以選擇提供的表格中的不同選項卡。例如：

"Sheet2 !A2:M"

與使用 CSV 一樣，您可以指定要忽略的行和標題，以及是否允許帶引號的換行符或參差不齊的行。

INSERT 或 DROP 等 DML 語句不適用於連線的表格。要省略列，您需要在表格中隱藏它或在初始配置中指定它。

列印頁面