BigQuery 中的分區和分群

1. 簡介

BigQuery 是全代管的 PB 級企業資料倉儲，可供您以低成本進行分析。BigQuery 採用無伺服器技術，您不需要設定及管理叢集。

BigQuery 資料集位於 GCP 專案中，內含一或多個資料表。您可以使用 SQL 查詢這些資料集。

在本程式碼研究室中，您將使用 GCP 主控台中的 BigQuery 網頁版 UI，瞭解 BigQuery 中的資料分割和叢集。BigQuery 的資料表分割和叢集功能可協助您根據常見的資料存取模式，建構資料結構。查詢特定資料範圍時，分區和分群是充分發揮 BigQuery 效能並降低成本的關鍵。這樣一來，每次查詢掃描的資料量就會減少，且系統會在查詢開始前決定要修剪哪些資料。

如要進一步瞭解 BigQuery，請參閱 BigQuery 說明文件。

課程內容

  • 如何建立及查詢分區和叢集資料表
  • 比較分區和分群資料表的查詢效能

軟硬體需求

為了順利完成這個實驗室，請先確認：

  • 最新版本的 Google Chrome
  • Google Cloud Platform 帳單帳戶

2. 開始設定

如要使用 BigQuery，您需要建立 GCP 專案或選取現有專案。

建立專案

如要建立新專案，請按照下列步驟操作：

  1. 如果您沒有 Google 帳戶 (Gmail 或 Google 應用程式)，請建立帳戶。
  2. 登入 Google Cloud Platform 主控台 ( console.cloud.google.com)，然後建立新專案。
  3. 如果沒有任何專案，請按一下「建立專案」按鈕：

870a3cbd6541ee86.png

否則，請從專案選單建立新專案：

f6dff3437a20cf2.png

  1. 輸入專案名稱，然後選取「建立」。請注意，專案 ID 是所有 Google Cloud 專案中不重複的名稱。

1884405a64ce5765.png

3. 使用公開資料集

BigQuery 可讓您使用公開資料集，包括 BBC News、GitHub 存放區、Stack Overflow 和美國國家海洋暨大氣總署 (NOAA) 資料集。您不需要將這些資料集載入 BigQuery。您只要開啟資料集，即可在 BigQuery 中瀏覽及查詢資料集。在本程式碼研究室中，您將使用 Stack Overflow 公開資料集。

瀏覽 Stack Overflow 資料集

Stack Overflow 資料集包含貼文、標記、徽章、留言、使用者等相關資訊。如要在 BigQuery 網頁版 UI 中瀏覽 Stack Overflow 資料集，請按照下列步驟操作：

  1. 開啟 Stack Overflow 資料集。GCP Console 會開啟 BigQuery 網頁版 UI，並顯示 Stackoverflow 資料集的相關資訊。
  2. 在導覽面板中，選取「bigquery-public-data」。選單會隨即展開，列出公開資料集。每個資料集都包含一或多個資料表。
  3. 向下捲動並選取「stackoverflow」stackoverflow，選單會隨即展開，列出 Stack Overflow 資料集中的資料表。
  4. 選取「badges」，即可查看徽章資料表的結構定義。記下表格中的欄位名稱。
  5. 按一下「欄位名稱」上方的「預覽」，即可查看徽章資料表的樣本資料。

如要進一步瞭解 BigQuery 中提供的所有公開資料集，請參閱「Google BigQuery 公開資料集」。

查詢 Stack Overflow 資料集

瀏覽資料集是瞭解所用資料的好方法，但查詢資料集才是 BigQuery 的真正強項。本節將說明如何執行 BigQuery 查詢。此時您不需要瞭解任何 SQL。您可以複製並貼上下列查詢。

如要執行查詢，請完成下列步驟：

  1. 選取 GCP 主控台右上方的「撰寫新查詢」。
  2. 在「Query editor」(查詢編輯器) 文字區域中，複製並貼上下列 SQL 查詢。BigQuery 會驗證查詢，網頁版 UI 的文字區域下方會顯示綠色勾號，表示語法有效。
SELECT
  EXTRACT(YEAR FROM creation_date) AS creation_year,
  COUNT(*) AS total_posts
FROM `bigquery-public-data.stackoverflow.posts_questions`
GROUP BY creation_year
ORDER BY total_posts DESC
LIMIT 10
  1. 選取「Run」(執行)。這項查詢會傳回每年發布的 Stack Overflow 貼文或問題數量。

4. 建立新資料表

在上一節中，您查詢了 BigQuery 提供的公開資料集。在本節中，您將從現有資料表在 BigQuery 中建立新資料表。您將建立新資料表，其中包含從 Stack Overflow 公開資料集 posts_questions 資料表取樣的資料，然後查詢該資料表。

建立新的資料集

如要在 BigQuery 中建立及載入資料表資料，請先建立 BigQuery 資料集來保存資料，完成下列步驟：

  1. 在 GCP 控制台的導覽面板中，選取您在設定時建立的專案名稱。
  2. 在右側的詳細資料面板中，選取「建立資料集」。

acc6378c49622323.png

  1. 在「建立資料集」對話方塊中，針對「資料集 ID」輸入 stackoverflow。保持其他所有預設設定不變，並按一下「確定」。

7a2dfd8bcb8f259a.png

使用 2018 年的 StackOverflow 貼文建立新資料表

建立 BigQuery 資料集後，您可以在 BigQuery 中建立新資料表。如要使用現有資料表中的資料建立資料表，請完成下列步驟，查詢 2018 年 Stack Overflow 貼文資料集，並將結果寫入新資料表：

  1. 選取 GCP 主控台右上方的「撰寫新查詢」。

9ca55f544e8da8bd.png

  1. 在「查詢編輯器」文字區域中，複製並貼上下列 SQL 查詢，建立新資料表 (即 DDL 陳述式)。
CREATE OR REPLACE TABLE `stackoverflow.questions_2018` AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. 選取「Run」(執行)。這項查詢會在專案的 stackoverflow 資料集中建立新資料表 questions_2018，並從 BigQuery Stack Overflow 資料集 bigquery-public-data.stackoverflow.posts_questions 執行查詢，將結果存入新資料表。

使用 2018 年 Stack Overflow 貼文查詢新資料表

現在您已建立 BigQuery 資料表，接下來請執行查詢，傳回 Stack Overflow 貼文的問題和標題，以及一些其他統計資料，例如回覆數、留言數、觀看次數和收藏次數。操作步驟如下：

  1. 選取 GCP 主控台右上方的「撰寫新查詢」。
  2. 在「Query editor」(查詢編輯器) 文字區域中，複製並貼上下列 SQL 查詢
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. 選取「Run」(執行)。這項查詢會傳回 2018 年 1 月建立的 Stack Overflow 問題，這些問題標有 android，並附上問題和其他幾項統計資料。
  2. BigQuery 預設會快取查詢結果。執行相同查詢，您會發現 BigQuery 傳回結果的時間大幅縮短，因為系統是從快取傳回結果。
  3. 再次執行相同查詢，但這次請停用 BigQuery 快取。為確保與分區和叢集資料表進行效能比較時的公平性，我們會在實驗室的其餘部分停用快取。在查詢編輯器中，按一下「更多」並選取「查詢設定」。查詢設定
  4. 在「Cache preference」(快取偏好) 下，取消勾選「Use cached results」(使用快取的結果)。快取的結果選項
  5. 查詢結果會顯示查詢完成所需的時間，以及處理的資料量。

f197b022b4276338.png

5. 建立及查詢分區資料表

在上一節中，您已使用 Stack Overflow 公開資料集，在 BigQuery 中建立新的資料表，並從 posts_questions 資料表匯入資料。我們查詢這個資料集時停用了快取，並觀察查詢效能。在本節中，您將從同一個 Stack Overflow 公開資料集的 posts_questions 資料表建立新的分區資料表，並觀察查詢效能。

分區資料表是一種特殊的資料表，表內劃分多個區段 (稱為分區)，可讓您更容易管理和查詢資料。您通常可以使用資料擷取時間、TIMESTAMP/DATE 資料欄或 INTEGER 資料欄，將大型資料表分割成許多較小的分區。我們將建立 DATE 分區資料表。

如要進一步瞭解分區資料表，請參閱這篇文章。

使用 2018 年的 StackOverflow 貼文建立新的分區資料表

如要使用現有資料表或查詢中的資料建立分區資料表，請查詢 2018 年 Stack Overflow 貼文資料集，並將結果寫入新資料表，完成下列步驟：

b9d0ca4df0881f58.png

  1. 選取 GCP 主控台右上方的「撰寫新查詢」。

9ca55f544e8da8bd.png

  1. 在「查詢編輯器」文字區域中，複製並貼上下列 SQL 查詢，建立新資料表 (即 DDL 陳述式)。
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_partitioned` 
PARTITION BY DATE(creation_date) AS
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. 選取「Run」(執行)。這項查詢會在專案的 stackoverflow 資料集中建立新資料表 questions_2018_partitioned，並將對 BigQuery Stack Overflow 資料集 bigquery-public-data.stackoverflow.posts_questions 執行查詢後產生的資料載入其中

查詢含有 2018 年 Stack Overflow 貼文的分區資料表

現在您已建立 BigQuery 分區資料表，讓我們再次執行查詢，這次是在分區資料表上執行，傳回 Stack Overflow 貼文，其中包含問題和標題，以及一些其他統計資料，例如回覆、留言、瀏覽次數和收藏次數。操作步驟如下：

  1. 選取 GCP 主控台右上方的「撰寫新查詢」。
  2. 在「Query editor」(查詢編輯器) 文字區域中，複製並貼上下列 SQL 查詢
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_partitioned` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. 選取「執行」，並停用 BigQuery 快取 (請參閱上一節，瞭解如何停用 BigQuery 快取)。這項查詢會傳回 2018 年 1 月建立的 Stack Overflow 問題，這些問題標有 android，並附上問題和其他幾項統計資料。
  2. 查詢結果會顯示查詢完成所需的時間，以及處理的資料量。

ef01144374069823.png

您應該會發現，分區資料表的查詢效能優於未分區資料表，因為 BigQuery 會修剪分區，也就是只掃描必要分區、處理較少資料，因此執行速度更快。這樣可最佳化查詢費用和查詢效能。

6. 建立及查詢叢集資料表

在上一節中，您已使用 Stack Overflow 公開資料集中的 posts_questions 資料表，在 BigQuery 中建立已分割的資料表。我們查詢這個資料表時停用了快取，並觀察非分區和分區資料表的查詢效能。在本節中，您將從相同的 Stack Overflow 公開資料集 posts_questions 資料表建立新的分群資料表，並觀察查詢效能。

在 BigQuery 中，資料表會依據資料表結構定義中的一或多個資料欄內容自動分門別類。系統會按照您指定的資料欄，將相關資料歸入同一個位置。當資料寫入叢集資料表時，BigQuery 會使用叢集處理資料欄的值排序資料。將資料分到 BigQuery 儲存空間中的多個區塊。分群資料欄的順序決定了資料的排序順序。在資料表或特定分區中新增資料時，BigQuery 會在背景中自動重新進行叢集處理，以復原資料表或分區的排序屬性。

如要進一步瞭解如何使用分群資料表，請參閱這篇文章。

使用 2018 年 Stack Overflow 貼文建立新的叢集資料表

在本節中，您將根據查詢存取模式，建立以 creation_date 分區，並以 tags 資料欄叢集的新資料表。如要使用現有資料表或查詢中的資料建立叢集資料表，請完成下列步驟，查詢 2018 年 Stack Overflow 貼文資料表，並將結果寫入新資料表：

e7d9acc0dc3b9d79.png

  1. 選取 GCP 主控台右上方的「撰寫新查詢」。

9ca55f544e8da8bd.png

  1. 在「查詢編輯器」文字區域中，複製並貼上下列 SQL 查詢，建立新資料表 (即 DDL 陳述式)。
#standardSQL
CREATE OR REPLACE TABLE `stackoverflow.questions_2018_clustered`
PARTITION BY
  DATE(creation_date)
CLUSTER BY
  tags AS
SELECT
  id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count, tags
FROM
  `bigquery-public-data.stackoverflow.posts_questions`
WHERE
  creation_date BETWEEN '2018-01-01' AND '2019-01-01';
  1. 選取「執行」。這項查詢會在專案的 stackoverflow 資料集中建立新資料表 questions_2018_clustered，並將對 BigQuery Stack Overflow 資料表 bigquery-public-data.stackoverflow.posts_questions 執行查詢後產生的資料匯入其中。新資料表會依 creation_date 分區，並依標記資料欄分群。

使用 2018 年的 Stack Overflow 貼文查詢叢集資料表

您已建立 BigQuery 叢集資料表，現在請再次對分區和叢集資料表執行相同查詢，傳回 Stack Overflow 貼文，其中包含問題和標題，以及一些其他統計資料，例如回覆、留言、瀏覽次數和收藏次數。操作步驟如下：

  1. 選取 GCP 主控台右上方的「撰寫新查詢」。
  2. 在「Query editor」(查詢編輯器) 文字區域中，複製並貼上下列 SQL 查詢
SELECT id, title, accepted_answer_id, creation_date, answer_count , comment_count , favorite_count, view_count 
FROM  `stackoverflow.questions_2018_clustered` 
WHERE creation_date BETWEEN '2018-01-01' AND '2018-02-01'
AND tags = 'android';
  1. 選取「執行」，並停用 BigQuery 快取 (請參閱上一節，瞭解如何停用 BigQuery 快取)。這項查詢會傳回 2018 年 1 月建立的 Stack Overflow 問題，這些問題標有 android，並附上問題和其他幾項統計資料。
  2. 查詢結果會顯示查詢完成所需的時間，以及處理的資料量。

85e3c30d6fb3d547.png

使用分區和叢集資料表時，查詢掃描的資料量比分區資料表或非分區資料表少。分區和叢集功能可整理資料，盡量減少 slot worker 掃描的資料量，進而提升查詢效能並節省費用。

7. 清除

除非您打算繼續使用 stackoverflow 資料集，否則應刪除該資料集，以及為這個程式碼研究室建立的專案。

刪除 BigQuery 資料集

如要刪除 BigQuery 資料集，請按照下列步驟操作：

  1. 在 BigQuery 的左側導覽面板中，選取「stackoverflow」stackoverflow資料集。
  2. 在詳細資料面板中，選取「刪除資料集」67b0f5cb740cb2ec.png。
  3. 在「Delete dataset」(刪除資料集) 對話方塊中，輸入 stackoverflow，然後選取「Delete」(刪除)，確認要刪除資料集。

刪除專案

如要刪除為本程式碼研究室建立的 GCP 專案，請執行下列步驟：

  1. 在 GCP 導覽選單中，選取「IAM & Admin」(IAM 與管理)。
  2. 選取導覽面板中的「設定」。
  3. 在詳細資料面板中，確認目前的專案是您為本程式碼實驗室建立的專案，然後選取「關閉」。
  4. 在「關閉專案」對話方塊中，輸入專案的專案 ID (而非專案名稱)，然後選取「關閉」以確認。

恭喜！您已學會

  • 如何使用 BigQuery 網頁版 UI，從現有資料表建立新資料表
  • 如何建立及查詢分區和叢集資料表
  • 分區和分群如何提升查詢效能並降低成本

請注意，您不必設定或管理叢集，即可使用資料集。