BigQuery 中的分區和分群

1. 簡介

BigQuery 是 PB 規模的全代管企業資料倉儲，最適合執行數據分析工作。BigQuery 採用無伺服器技術，您不需要設定及管理叢集。

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

在這個程式碼研究室中，您將使用 GCP Console 中的 BigQuery 網頁版 UI，瞭解 BigQuery 中的分區和分群功能。BigQuery 的資料表分區和分群功能有助於建構資料，以符合常見的資料存取模式。查詢特定資料範圍時，分區與分群是充分發揮 BigQuery 效能和成本效益的關鍵。如此一來，每次查詢的掃描資料量會比較少，並在查詢開始時間之前決定裁舊。

注意：除了 BigQuery 網頁版 UI 之外，您還可以使用 bq 指令列工具對 BigQuery 資料集執行作業。如要進一步瞭解 bq 指令列工具，請參閱 bq 指令列工具。

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

課程內容

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

軟硬體需求

如要完成這個研究室活動，請先確認：

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

2. 開始設定

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

建立專案

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

1. 如果您還沒有 Google 帳戶 (Gmail 或 Google Apps)，請建立帳戶。
2. 登入 Google Cloud Platform 控制台 ( console.cloud.google.com)，並建立新專案。
3. 如果沒有任何專案，請按一下「建立專案」按鈕：

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

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

3. 使用公開資料集

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

瀏覽 Stack Overflow 資料集

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

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

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

查詢 Stackoverflow 資料集

瀏覽資料集是瞭解您正在使用資料的好方法，但查詢資料集才是 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

3. 選取「執行」。這項查詢會傳回每年張貼的 Stack Overflow 貼文或問題數量。

注意：這項查詢會使用 post_questions 資料表的完整資料表名稱，包含專案名稱、資料集名稱和資料表名稱：bigquery-public-data.stackoverflow.posts_questions。

4. 建立新的資料表

在上一節中，您查詢了 BigQuery 提供的公開資料集。本節將說明如何使用現有資料表在 BigQuery 中建立新的資料表。您將建立一個新資料表，其中包含從 Stack Overflow 公開資料集「posts_questions」資料表中取樣的資料，然後查詢資料表。

建立新的資料集

如要建立資料表資料並載入至 BigQuery，請先完成下列步驟，再建立 BigQuery 資料集來存放資料：

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

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

建立含有 2018 年 StackOverflow 貼文的新資料表

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

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

2. 在「查詢編輯器」文字區域中，複製及貼上以下 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';

3. 選取「執行」。查詢會在專案的 stackoverflow 資料集中建立新的資料表 questions_2018，並在 BigQuery Stack Overflow 資料集 bigquery-public-data.stackoverflow.posts_questions 上執行查詢時產生資料。

注意：這個步驟需要約 2 分鐘的時間來建立新資料表。請稍後再進行下一個步驟。

使用 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';

3. 選取「執行」。查詢會傳回 2018 年 1 月建立且標示為「android」的 Stack Overflow 問題，以及這個問題和一些其他統計資料。
4. 根據預設，BigQuery 會快取查詢結果。執行相同查詢，您會發現 BigQuery 傳回快取結果，進而減少傳回結果的時間。
5. 再次執行相同查詢，但這次已停用 BigQuery 快取。我們會停用研究室其餘部分的快取，以公平比較分區和叢集資料表的效能，而這些資料表將在下一節執行。在查詢編輯器中按一下「More」並選取「Query settings」。
6. 在「Cache Preferences」(快取偏好設定) 下方，取消勾選「Use cache results」(使用快取的結果)。
7. 您會在查詢結果中看到查詢完成所需的時間，以及為了取得結果而處理的資料量。

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

在上一節中，您使用 Stack Overflow 公開資料集，在 BigQuery 中建立新的資料表，內含 posts_questions 資料表中的資料。我們在停用快取的情況下查詢這個資料集，並觀察到查詢效能。在本節中，您將從同一個 Stack Overflow 公開資料集的 posts_questions 資料表建立新的分區資料表，然後觀察查詢效能。

分區資料表是一種特殊資料表，將其劃分為多個區隔 (稱為「分區」)，可讓您更輕鬆地管理及查詢資料。通常您可以使用資料擷取時間、TIMESTAMP/DATE 資料欄或 INTEGER 資料欄，將大型資料表拆分為多個較小的分區。我們會建立 DATE 分區資料表。

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

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

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

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

2. 在「查詢編輯器」文字區域中，複製及貼上以下 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';

3. 選取「執行」。查詢會在專案的 stackoverflow 資料集中建立新的資料表 questions_2018_partitioned，並在 BigQuery Stack Overflow 資料集 bigquery-public-data.stackoverflow.posts_questions 上執行查詢時產生資料

注意：這個步驟需要約 2 分鐘的時間來建立新資料表。請稍後再進行下一個步驟。

使用 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';

3. 在停用 BigQuery 快取功能的情況下，選取「執行」 (請查看前一節瞭解如何停用 BigQuery 快取)。查詢會傳回 2018 年 1 月建立且標示為「android」的 Stack Overflow 問題，以及這個問題和一些其他統計資料。
4. 您會在查詢結果中看到查詢完成所需的時間，以及為了取得結果而處理的資料量。

您應該會發現，使用分區的資料表查詢的效能優於非分區，因為 BigQuery 會修剪分區，也就是只掃描處理較少資料所需的分區，執行速度更快。這麼做可將查詢費用和查詢效能最佳化。

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

在上一節中，您在 BigQuery 中使用了來自 Stack Overflow 公開資料集 posts_questions 資料表的資料，藉此建立分區資料表。我們以停用快取的方式查詢這個資料表，並觀察到使用非分區資料表和分區資料表的查詢效能。在本節中，您將從同一個 Stack Overflow 公開資料集的 posts_questions 資料表建立新的叢集資料表，然後觀察查詢效能。

在 BigQuery 中將資料表分群時，系統會自動根據資料表結構定義中一或多個資料欄的內容整理資料表資料。您指定的資料欄會用來整理相關資料。當資料寫入叢集資料表時，BigQuery 會使用叢集處理資料欄中的值排序資料。這些值可用來將資料分到 BigQuery 儲存空間中的多個區塊。叢集資料欄的順序決定了資料的排序順序。在資料表或特定分區中加入新資料時，BigQuery 會在背景自動重新分群，還原資料表或分區的排序屬性。

如要進一步瞭解如何使用叢集資料表，請按這裡。

建立含有 2018 年 Stack Overflow 貼文的新叢集資料表

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

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

2. 在「查詢編輯器」文字區域中，複製及貼上以下 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';

3. 選取「執行」。查詢會在專案的 stackoverflow 資料集中建立新的資料表 questions_2018_clustered，並在 BigQuery Stack Overflow 資料表bigquery-public-data.stackoverflow.posts_questions中執行查詢時產生資料。新資料表會按照 create_date 進行分區，並依照標記資料欄分群。

注意：這個步驟需要約 2 分鐘的時間來建立新資料表。請稍後再進行下一個步驟。

使用 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';

3. 在停用 BigQuery 快取功能的情況下，選取「執行」 (請查看前一節瞭解如何停用 BigQuery 快取)。查詢會傳回 2018 年 1 月建立且標示為「android」的 Stack Overflow 問題，以及這個問題和一些其他統計資料。
4. 您會在查詢結果中看到查詢完成所需的時間，以及為了取得結果而處理的資料量。

如果使用分區和叢集資料表，查詢掃描的資料量比分區資料表或非分區資料表少。透過分區和分群的方式整理資料，可將運算單元工作站掃描的資料量降至最低，進而改善查詢效能並發揮最佳成本效益。

7. 清除所用資源

除非您打算繼續使用堆疊溢位資料集，否則請將其刪除並刪除您為本程式碼研究室建立的專案。

刪除 BigQuery 資料集

如要刪除 BigQuery 資料集，請執行下列步驟：

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

刪除專案

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

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

恭喜！您已學習以下內容

• 如何使用 BigQuery 網頁版 UI 從現有資料表建立新資料表
• 如何建立及查詢分區與叢集資料表
• 分區和分群如何最佳化查詢效能和成本

請注意，您不需要設定或管理叢集就能使用資料集。