在 BigQuery 中查詢 Wikipedia 資料集

1. 總覽

BigQuery 是 Google 的全代管 NoOps 數據分析資料庫，價格實惠，您可以使用 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。

所有 Google Cloud 專案的專案 ID 都是不重複的名稱。本程式碼研究室稍後會將其稱為 PROJECT_ID。

本程式碼研究室使用的 BigQuery 資源，皆在 BigQuery 沙箱限制內。不需要帳單帳戶。如要移除沙箱限制，請註冊 Google Cloud 免費試用方案，並新增帳單帳戶。

您會在下一節載入維基百科資料集。

3. 建立資料集

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

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

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

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

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

啟用 Cloud Shell

1. 在 Cloud 控制台，點選「啟用 Cloud Shell」 圖示 。

如果您是首次啟動 Cloud Shell，系統會顯示中繼畫面 (位於摺疊式選單下方)，說明這個指令列環境。點選「繼續」後，這則訊息日後就不會再出現。以下是這個初次畫面的樣子：

佈建並連至 Cloud Shell 預計只需要幾分鐘。

這部虛擬機器搭載您需要的所有開發工具，並提供永久的 5GB 主目錄，而且可在 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。你會發現它支援 Tab 鍵自動完成功能。詳情請參閱 gcloud 指令列工具總覽。

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

gcloud config list project

指令輸出

[core]
project = <PROJECT_ID>

如未設定，請輸入下列指令手動設定專案：

gcloud config set project <PROJECT_ID>

指令輸出

Updated property [core/project].

將資料載入 BigQuery

為方便作業，我們已將 2019 年 4 月 10 日的部分資料上傳至 Google Cloud Storage (位置：gs://cloud-samples-data/third-party/wikimedia/pageviews/pageviews-20190410-140000.gz)，這些資料來自 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 "" 設為表示字串未加上引號。
• 將 --max_bad_records 3 設為在剖析 CSV 檔案時最多忽略 3 個錯誤。

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

5. 預覽資料集

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

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

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

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

6. 撰寫查詢

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

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

2. 如要找出 2019 年 4 月 10 日下午 2 點到 3 點的維基媒體總瀏覽次數，請編寫下列查詢：

SELECT SUM(requests)
FROM `lab.pageviews_20190410_140000`

3. 按一下「執行」：

幾秒後，結果就會列在底部，並顯示處理的資料量：

即使資料表大小為 691.4 MB，這項查詢處理的資料量仍為 123.9 MB。BigQuery 只會處理查詢中使用的資料欄位元組，因此處理的資料總量可能遠小於資料表大小。使用分群和分區，可進一步減少處理的資料量。

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

7. 更進階的查詢

尋找維基百科網頁瀏覽次數

Wikimedia 資料集包含所有 Wikimedia 專案 (包括維基百科、維基詞典、維基教科書和維基語錄) 的網頁瀏覽次數。新增 WHERE 陳述式，將查詢範圍縮小至僅限英文維基百科頁面：

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 查詢真實的維基百科網頁瀏覽資料集。您有權查詢 PB 規模的資料集！

瞭解詳情

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