什麼時候使用 Google BigQuery 才有意義？

在我的職業生涯中，我不得不為不同類型的報告和圖表編寫複雜的分析查詢。 大多數情況下，它是一些圖表，顯示按日期、週、季度等聚合的數據。 通常，創建此類報告是為了幫助客戶識別趨勢並說明他們的業務在高水平上的表現。 但是，當數據科學家和工程師需要基於大數據集創建更廣泛的報告時會發生什麼？

如果報表是基於少量數據的，則可以通過在關係數據庫下編寫 SQL 查詢來解決該任務。 在這一步中，重要的是要了解編寫查詢的基礎知識以及如何使它們更快、更高效。 但是，有時報表依賴於更大的數據集（例如，表格中的數百萬行和更多行），但報表不依賴於輸入變量（參數），或者您可能會發現值的數量非常少。 在這種情況下，SQL 查詢可能會很慢，因此用戶等待查詢執行並不是最佳選擇。 在這種情況下，最常見的做法是在客戶請求報告之前提前運行查詢。

此外，它需要實現一些緩存功能，因此客戶端可以從緩存中獲取數據，而不是實時運行查詢。 如果您不需要顯示實時數據，這種方法非常有效。 它可以顯示一個小時甚至一天前計算的數據。 因此，實際報告/圖表使用緩存數據顯示，而不是基於實時數據。

轉向 Google BigQuery

當我在製藥行業從事分析項目時，我需要將郵政編碼和藥物名稱作為輸入參數的圖表。 我還需要展示美國特定地區的藥物之間的一些比較。

分析查詢非常複雜，最終在我們的 Postgres 服務器（具有 16 GB RAM 的四核 CPU）上運行了大約 50 分鐘。 我無法提前運行它並緩存結果，因為查詢將郵政編碼和藥物作為輸入參數，所以有數千種組合，無法預測哪個客戶會選擇。

即使我想嘗試執行所有輸入參數組合，我的數據庫也很可能崩潰。 所以是時候選擇一種不同的方法並選擇一些易於使用的解決方案了。 該圖表對客戶很重要，但是，客戶還沒有準備好對架構進行重大更改或完全遷移到另一個數據庫。

在那個特定的項目中，我們嘗試了幾種不同的方法：

• 服務器的垂直擴展（向 Postgres 服務器添加 RAM 和 CPU）
• 使用 Amazon Redshift 等替代數據庫。
• 我們還研究了一個 NoSQL 解決方案，但它們中的大多數都非常複雜，需要對架構進行大量更改，其中許多更改對於客戶端來說太大了。

最後，我們嘗試了 Google BigQuery。 它滿足了我們的期望，使我們能夠完成工作，而無需進行客戶不願批准的巨大更改。 但是什麼是 Google BigQuery 以及它的表現如何？

BigQuery 是一種基於 REST 的網絡服務，可讓您在大量數據下運行基於 SQL 的複雜分析查詢。 在我們將數據上傳到 BigQuery 並執行與 Postgres 相同的查詢後（語法非常相似），我們的查詢運行得更快，大約需要一分鐘才能完成。 最終，僅通過使用不同的服務，我們就獲得了 50 倍的性能提升。 值得注意的是，其他數據庫並沒有提供相同的性能提升，讓我們大方一點，只是說它們甚至不接近。 老實說，BigQuery 提供的性能提升給我留下了深刻的印象，因為這些數據比我們任何人都希望的要好。

儘管如此，我不會將 BigQuery 宣傳為世界上最好的數據庫解決方案。 雖然它對我們的項目運行良好，但它仍然有很多限制，例如每天表中的更新數量有限，每個請求的數據大小限制等等。 您需要了解 BigQuery 不能用於替代關係數據庫，它面向的是運行分析查詢，而不是簡單的 CRUD 操作和查詢。

在本文中，我將嘗試比較在實際用例場景中使用 Postgres（我最喜歡的關係數據庫）和 BigQuery。 此外，我會在此過程中提供一些建議，即我對何時使用 BigQuery 真正有意義的看法。

樣本數據

為了比較 Postgres 和 Google BigQuery，我獲取了按國家、年齡、年份和性別分組的每個國家的公共人口統計信息（您可以從此鏈接下載相同的數據）。

我將數據添加到四個表中：

1. populations
2. locations
3. age_groups
4. populations_aggregated

最後一張表只是前三個表的匯總數據。 這是數據庫架構：

我最終得到的populations表包含超過 690 萬行。 這不是太多，但對於我的測試來說已經足夠了。

基於示例數據，我嘗試構建可用於構建現實分析報告和圖表的查詢。 所以我為下一份報告準備了查詢：

1. 美國人口按年匯總。
2. 2019 年所有國家的人口，從最大的國家開始。
3. 每年排名前五的“最古老”國家。 “最老”是指60歲以上人口占總人口比例最高的國家。 該查詢每年應給出五個結果。
4. 按年份匯總的前五個國家，其中男性和女性人口之間的差異最大。
5. 從“最古老”到“最年輕”的國家/地區，每年獲取每個國家/地區的中位（平均）年齡。
6. 每年找出前五個“垂死”的國家。 “垂死”是指人口正在減少（人口減少最多）的國家。

查詢 #1、#2 和 #6 非常簡單直接，但查詢 #3、#4 和 #5 並不那麼容易編寫——至少對我來說是這樣。 請注意，我是一名後端工程師，編寫複雜的 SQL 查詢並不是我的專長，因此具有更多 SQL 經驗的人可能會構建更智能的查詢。 但是，目前我們需要檢查 Postgres 和 BigQuery 如何使用相同的數據處理相同的查詢。

我總共構建了 24 個查詢：

• 6 用於 Postgres DB，它使用非聚合表（ populations 、 locations 、 age_groups ）
• 6 用於 Postgres DB，它使用populations_aggregated表
• BigQuery 的 6+6 個查詢，使用聚合表和非聚合表。

讓我分享聚合數據的 BigQuery 查詢 #1 和 #5，以便您了解簡單 (#1) 和復雜 #5 查詢的複雜性。

按年查詢匯總的美國人口：

 select sum (value), year from world_population.populations_aggregated where location_name = 'United States of America' group by 2 order by year asc

查詢每個國家/地區每年的年齡中位數，按從大到小排序：

 --converts age to number with population_by_age_year_and_location as( select sum (value) as value, cast (regexp_replace(age_group_name, '\\+', '') as int64) as age, year, location_name from world_population.populations_aggregated where location_type = 'COUNTRY' group by 2,3,4), --calculates total population per country per year total_population_by_year_and_locations as ( select sum(value) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), --calculates total number of people in country per year age_multiplied_by_population_temp as ( select sum(value * age) as value, year, location_name from population_by_age_year_and_location group by 2,3 ), median_per_year_country as ( select a.value / b.value as median, a.year, a.location_name from age_multiplied_by_population_temp a inner join total_population_by_year_and_locations b on a.location_name = b.location_name and a.year = b.year ) select * from median_per_year_country order by year desc, median desc

注意：您可以在我的 bitbucket 存儲庫中找到所有查詢（鏈接在文章末尾）。

測試結果

對於運行查詢，我使用了兩個不同的 Postgres 服務器。 第一個有 1 個 CPU 內核和 4GB RAM，由 SSD 驅動器支持。 第二個服務器有 16 個 CPU 內核、64GB RAM，並且還使用 SSD 驅動器（第二個服務器具有 16 倍的 CPU 和 RAM 潛力）。

另外，請注意在運行測試期間數據庫上沒有負載。 我創建它們只是為了運行查詢。 在現實生活中，查詢會花費更長的時間，因為其他查詢可能會同時運行，此外，那些並行運行的查詢可能會鎖定表。 為了檢查查詢速度，我使用了 pgAdmin3 和 BigQuery Web 界面。

在我的測試中，我得到了這些結果：

讓我在查詢 #1 和查詢 #5 的條形圖中顯示這些結果。

注意： Postgres 數據庫位於美國服務器上，而我位於歐洲，因此 Postgres 傳輸數據存在額外延遲。

BigQuery 性能和結論

根據我得到的結果，我得出以下結論：

• 在垂直擴展 Postgres 的情況下，即使是 16 倍，它在運行單個查詢時也只能提供 10-25% 的性能。 換句話說，只有一個 CPU 內核和 4GB RAM 的 Postgres 服務器運行查詢的時間與具有 16 個 CPU 內核和 64GB RAM 的服務器所需的時間非常相似。 當然，更大的服務器可以處理更大的數據集，但是，這並不能大大改善查詢執行時間。
• 對於帶有小表的 Postgres 連接（ locations表大約有 400 行， age_groups有 100 行）與在一個表中的聚合數據下運行查詢相比並沒有產生巨大的差異。 另外，我發現對於運行一到兩秒的查詢，使用內連接的查詢更快，但對於長時間運行的查詢，情況就不同了。
• 在 BigQuery 中，連接的情況完全不同。 BigQuery 不喜歡連接。 使用聚合數據和非聚合數據的查詢之間的時間差異相當大（對於查詢 #3 和 $5，它大約是兩秒）。 這意味著，對於 BigQuery，您可以根據需要執行任意數量的子查詢，但為了獲得良好的性能，查詢應該使用一個表。
• 對於使用簡單聚合或過濾或使用小型數據集的查詢，Postgres 更快。 我發現在 Postgres 中耗時不到 5 秒的查詢在 BigQuery 中運行速度較慢。
• 對於長時間運行的查詢，BigQuery 的性能要好得多。 隨著數據集大小差異的增加，這些查詢完成時間的差異也會增加。

何時使用 BigQuery 有意義

現在，讓我們回到本文討論的核心問題：什麼時候應該真正使用 Google BigQuery？ 根據我的結論，我建議在滿足以下條件時使用 BigQuery：

• 當您有在關係數據庫中運行超過 5 秒的查詢時使用它。 BigQuery 的想法是運行複雜的分析查詢，這意味著運行進行簡單聚合或過濾的查詢毫無意義。 BigQuery 適用於“繁重”查詢，即使用大量數據進行操作的查詢。 數據集越大，使用 BigQuery 獲得性能的可能性就越大。 我使用的數據集只有 330 MB（兆字節，甚至不是千兆字節）。
• BigQuery 不喜歡連接，因此您應該將數據合併到一個表中以獲得更好的執行時間。 BigQuery 允許將查詢結果保存在新表中，因此要創建新的聚合表，只需將所有數據上傳到 BigQuery，運行將合併所有數據的查詢，然後將其保存在新表中即可。
• BigQuery 適用於數據不經常更改並且您希望使用緩存的場景，因為它具有內置緩存。 這是什麼意思？ 如果您運行相同的查詢並且表中的數據沒有更改（更新），BigQuery 將只使用緩存的結果，並且不會嘗試再次執行查詢。 此外，BigQuery 不會對緩存查詢收費。 注意：即使是緩存查詢也需要 1-1.2 秒才能返回結果。
• 當您想減少關係數據庫的負載時，也可以使用 BigQuery。 分析查詢是“繁重的”，在關係數據庫下過度使用它們會導致性能問題。 因此，您最終可能會被迫考慮擴展您的服務器。 但是，使用 BigQuery，您可以將這些正在運行的查詢移至第三方服務，因此它們不會影響您的主關係數據庫。

最後，再說幾句關於在現實生活中使用 BigQuery 的內容。 在我們的實際項目中，報告的數據每週或每月都會發生變化，因此我們可以手動將數據上傳到 BigQuery。 但是，如果您的數據經常更改，那麼在關係數據庫和 BigQuery 之間同步數據可能並不那麼簡單，這是一個值得牢記的警告。

鏈接

您可以在此處找到本文中使用的示例數據，而 CSV 格式的查詢和數據可在此處訪問。