由阿賈伊·拉克什米納拉亞納奧和艾倫·馮

谷歌比奎里是一個強大的工具。具有 SQL 極簡主義理念的軟體工程師可以輕鬆使用它。您可以在幾秒鐘內破解大量數據以獲得見解。在 ShareThis 我們每天調查數 TB 的可操作社交數據,它已成為我們分析、檢查和處理工作不可或缺的一部分。

我們最近面臨的問題之一是根據國家/地區在 Bigquery (BQ) 表中分隔行。BQ 基於列存儲。BQ 不是將整個記錄存儲在單個卷上,而是根據列值劃分行,並將每個值存儲在自己的存儲卷中。為了利用這一方案,我們設計了一個高效的低成本查詢解決方案來檢索每個國家/地區的值,如下所述。

問題:按國家/地區分隔 BQ 表
直截了當的方法

要按國家/地區分隔 BQ 表,我們需要查詢該表,這是國家/地區數的多次。

假設我們需要查詢列 A、B、C、D 和 E。每列的大小為 20GB。我們的表的可計費位元組數將為100GB。

如果我們需要執行查詢的次數與國家/地區(即 240)多一倍,我們的可計費位元組數將為 24,000 GB 或近 24 TB。假設 1 TB 的費用為 5 美元,我們的總成本將為一個 BQ 表 120 美元。

優化方式

此處的訣竅是將所需的欄位陣列為單個列。僅此一項無法保存計費的位元組數。第二個技巧是按隔離列進行透視;在這種情況下,這將是按國家。

在上述步驟之後,結果表將包含與國家/地區相同的列。每列表示一個國家/地區的數據,並且將僅具有該國/地區的以前分組數據。

在我們的 100GB BQ 表中,我們可以假設一個可能的結果,因為代表美國的列是 20GB,10 個主要國家的平均約為 3GB,其餘 230 個國家/地區的平均溫度為 0.5 GB。

現在,我們的計費位元組基於每個國家/地區的資料大小。查詢美國的可計費成本僅為0.1美元,主要國家的成本為0.015美元,其他國家的成本比這小得多。

分組表的總計費位元組為100GB,所有國家/地區查詢的總計費位元組為165GB。總成本約為1.5美元!

生成分組表

我們可以使用 CONCAT 將所需的列分組到單列中。我們可以創建一個格式的行,表示 CSV 檔中的行或有效的 JSON 物件。

對於第二步,查詢將由 240 個 CASE 語句組成,每個國家/ 地區都有一個 CASE 語句,用於透視到特定列。查詢將很大,但 BQ 支援高達 256KB 的查詢大小。我們可以將這兩個步驟合併到單個查詢中,在每個情況下語句中 Concat。

查詢範例:

選擇
(地理時CASE。ISO = "美國"我們
(地理時CASE。ISO = "CA" THEN CONCAT( A,B,C,D,E ) AS 國家約。。。
從
mergelog_20160101;