在 BigQuery 中查詢 Wikipedia 資料集

1. 總覽

BigQuery 是 Google 的全代管數據分析資料庫，不但免人工管理，而且價格低廉。您可以使用 BigQuery 查詢 TB 規模的資料，不必管理基礎架構，也不需要資料庫管理員。BigQuery 使用您熟悉的 SQL，並且採用「即付即用」模式。這項服務可讓您專心分析資料，找出有意義的深入分析結果。

在本程式碼研究室中，您將使用 BigQuery 探索 Wikipedia 資料集。

課程內容

• 如何使用 BigQuery
• 如何將實際資料集載入 BigQuery
• 如何撰寫查詢，以便取得大型資料集的深入分析資訊

軟硬體需求

• Google Cloud 專案
• 瀏覽器，例如 Google Chrome 或 Firefox

問卷調查

您會如何使用這個教學課程？

唯讀 閱讀並完成練習

您對 Google Cloud 使用體驗的評價如何？

新手 中級 還算容易

2. 設定和需求

啟用 BigQuery

如果您還沒有 Google 帳戶，請先建立帳戶。

1. 登入 Google Cloud 控制台，然後前往 BigQuery。您也可以在瀏覽器中輸入下列網址，直接開啟 BigQuery 網頁版 UI。

https://console.cloud.google.com/bigquery

2. 接受服務條款。
3. 您必須先建立專案，才能使用 BigQuery。請依照提示建立新專案。

選擇專案名稱並記下專案 ID。

專案 ID 是所有 Google Cloud 專案中的專屬名稱。稍後在本程式碼研究室中會稱為 PROJECT_ID。

本程式碼研究室會使用 BigQuery 沙箱限制內的 BigQuery 資源。不需要建立帳單帳戶。如果您日後想要移除沙箱限制，可以註冊 Google Cloud 免費試用方案來新增帳單帳戶。

您會在下一節中載入 Wikipedia 資料集。

3. 建立資料集

首先，請在專案中建立新的資料集。資料集由多份資料表組成。

1. 如要建立資料集，請按一下資源窗格下方的專案名稱，然後按一下「建立資料集」：

2. 輸入 lab 做為資料集 ID：

3. 按一下「建立資料集」，建立空白資料集。

4. 使用 bq 指令列程式載入資料

啟用 Cloud Shell

1. 在 Cloud 控制台中，按一下「啟用 Cloud Shell」圖示 。

如果您先前從未啟動 Cloud Shell，您會看見中繼畫面 (需捲動位置)，說明螢幕內容。如果出現這種情況，請按一下「繼續」 (之後不會再顯示)。以下是單次畫面的外觀：

佈建並連線至 Cloud Shell 只需幾分鐘的時間。

這個虛擬機器搭載您需要的所有開發工具。提供永久的 5 GB 主目錄，而且在 Google Cloud 中運作，大幅提高網路效能和驗證能力。在本程式碼研究室中，您的大部分作業都可以透過瀏覽器或 Chromebook 完成。

連線至 Cloud Shell 後，您應會發現自己通過驗證，且專案已設為您的專案 ID。

2. 在 Cloud Shell 中執行下列指令，確認您已通過驗證：

gcloud auth list

指令輸出

 Credentialed Accounts
ACTIVE  ACCOUNT
*       <my_account>@<my_domain.com>

To set the active account, run:
    $ gcloud config set account `ACCOUNT`

注意：gcloud 指令列工具是 Google Cloud 中功能強大的整合式指令列工具。已預先安裝於 Cloud Shell 中。您會發現系統支援分頁完成功能。如要瞭解詳情，請參閱 gcloud 指令列工具總覽。

3. 在 Cloud Shell 中執行下列指令，確認 gcloud 指令知道您的專案：

gcloud config list project

指令輸出

[core]
project = <PROJECT_ID>

如果尚未設定，請使用下列指令進行設定：

gcloud config set project <PROJECT_ID>

指令輸出

Updated property [core/project].

將資料載入 BigQuery

為了方便起見，您可以在 Google Cloud Storage 的 gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz 查看 2019 年 4 月 10 日的 Wikimedia 網頁瀏覽量資料集資料。資料檔案是 GZip 的 CSV 檔案。您可以使用 bq 指令列公用程式直接載入這個檔案。在載入指令的過程中，您也必須說明檔案的結構定義。

您可以在 API 控制台或指令列中找到專案 ID：「gcloud config list」然後找出「project」的值。

此外，首次執行 bq 時，您可能需要選取預設專案。選取完畢後，您可以重新執行上述指令來載入資料。

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --allow_jagged_rows \
  --quote "" \
  --max_bad_records 3 \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_140000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz \
  wiki,title,requests:integer,zero:integer

資料載入可能需要幾分鐘的時間。請等待作業完成。

你使用了下列幾種進階選項來載入網頁瀏覽檔案：

• 設定 --source_format CSV，指示將檔案剖析為 CSV 檔案。CSV 是預設格式，因此可以選擇是否要執行這個步驟。
• 設定 --field_delimiter " " 表示使用單一空格來分隔欄位。
• 將 --allow_jagged_rows 設為包含欄數較少的資料列，並忽略載入 CSV 檔案時出現的錯誤。
• 設定 --quote "" 表示字串未加上引號。
• 在剖析 CSV 檔案時，將 --max_bad_records 3 設為忽略最多 3 個錯誤。

如要進一步瞭解 bq 指令列，請參閱說明文件。

5. 預覽資料集

在 BigQuery 控制台中，開啟您剛剛載入的其中一個資料表。

1. 展開專案。
2. 展開資料集。
3. 選取資料表。

您可以在「結構定義」分頁中查看資料表結構定義。4. 前往「詳細資料」分頁，即可查看表格包含的資料量：

5. 開啟「預覽」分頁，查看資料表中的所選資料列。

6. 撰寫查詢

1. 按一下「撰寫新查詢」：

系統會顯示「查詢編輯器」：

2. 請編寫以下查詢，找出 2019 年 4 月 10 日下午 2 點到 3 點之間的 Wikimedia 觀看總次數：

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. 按一下「Run」：

幾秒後，結果會列在下方，也會顯示處理的資料量：

雖然資料表為 691.4 MB，但這項查詢處理了 123.9 MB。BigQuery 只會處理查詢中使用的資料欄位元組數，因此處理的資料量可能會大幅少於資料表大小。有了叢集和分區功能，您就能進一步降低系統處理的資料量。

您每個月最多可免費處理/查詢 1 TB 的資料。詳情請參閱 BigQuery 定價頁面。

7. 更多進階查詢

查看維基百科頁面瀏覽次數

Wikimedia 資料集包含所有 Wikimedia 專案 (包括維基百科、維基百科、維基百科和維基報價) 的網頁瀏覽次數。新增 WHERE 陳述式，將查詢範圍縮小為英文 Wikipedia 頁面：

SELECT SUM(requests), wiki
FROM `lab.pageviews_20190410_140000`
WHERE wiki = "en"
GROUP BY wiki

請注意，在查詢額外的資料欄 wiki 後，系統處理的資料量從 124 MB 增加到 204 MB。

BigQuery 支援許多常見的 SQL 子句 (例如 CONTAINS、GROUP BY, ORDER BY)，以及許多匯總函式。此外，您也可以使用規則運算式來查詢文字欄位！試試其中一個選項：

SELECT title, SUM(requests) requests
FROM `lab.pageviews_20190410_140000`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

如要進一步瞭解 BigQuery 語法，請參閱 BigQuery 查詢參考說明文件。

查詢多個資料表

您可以使用萬用字元資料表選取要用來建立聯集的資料表。

1. 首先，將下一個小時的網頁檢視載入至新的資料表，建立第二個要查詢的資料表：

bq load \
  --source_format CSV \
  --field_delimiter " " \
  --quote "" \
  $GOOGLE_CLOUD_PROJECT:lab.pageviews_20190410_150000 \
  gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-150000.gz \
  wiki,title,requests:integer,zero:integer

2. 在查詢編輯器中，使用「pageviews_2019」查詢資料表，查詢所載入的兩個資料表使用前置字串：

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

您可以使用 _TABLE_SUFFIX 虛擬資料欄進一步篩選表格。這項查詢限定於對應 4 月 10 日的資料表。

SELECT title, SUM(requests) requests
FROM `lab.pageviews_2019*`
WHERE
  _TABLE_SUFFIX BETWEEN '0410' AND '0410_9999999'
  AND wiki = "en"
  AND REGEXP_CONTAINS(title, 'Red.*t')
GROUP BY title
ORDER BY requests DESC

8. 清除所用資源

視需要刪除您使用 bq rm 指令建立的資料集。使用 -r 旗標移除其中包含的任何資料表。

bq rm -r lab

9. 恭喜！

您已使用 BigQuery 和 SQL 查詢實際的 Wikipedia 網頁瀏覽量資料集。您可以查詢 PB 規模的資料集！

瞭解詳情

• 歡迎前往 BigQuery subreddit，瞭解其他人如何運用 BigQuery。
• 尋找 BigQuery 中提供的公開資料集。
• 瞭解如何將資料載入 BigQuery。