搭配 Python 使用 BigQuery

1. 總覽

BigQuery 是 Google 推出的 PB 級全代管低成本數據分析資料倉儲。由於 BigQuery 沒有可管理的基礎架構，更不需要資料庫管理員，因此免除了營運方面的工作，讓您可專心分析資料以找出有意義的結果、使用熟悉的 SQL，以及利用我們的即付即用模型。

在本程式碼研究室中，您將使用 Google Cloud Python 用戶端程式庫，透過 Python 查詢 BigQuery 公開資料集。

課程內容

如何使用 Cloud Shell
如何啟用 BigQuery API
如何驗證 API 要求
如何安裝 Python 用戶端程式庫
如何查詢莎士比亞的作品
如何查詢 GitHub 資料集
如何調整快取和顯示統計資料

軟硬體需求

Google Cloud 專案
Chrome 或 Firefox 瀏覽器
熟悉 Python

問卷調查

您會如何使用本教學課程？

僅閱讀

閱讀並完成練習

你對 Python 的使用體驗如何？

新手

中級

熟練

您對使用 Google Cloud 服務的體驗滿意嗎？

新手

中級

熟練

2. 設定和需求條件

自修實驗室環境設定

登入 Google Cloud 控制台，然後建立新專案或重複使用現有專案。如果沒有 Gmail 或 Google Workspace 帳戶，請先建立帳戶。

專案名稱是這個專案參與者的顯示名稱。這是 Google API 未使用的字元字串，您隨時可以更新。
專案 ID 在所有 Google Cloud 專案中不得重複，且設定後即無法變更。Cloud 控制台會自動產生專屬字串，通常您不需要在意該字串為何。在大多數程式碼研究室中，您需要參照專案 ID (通常會標示為 PROJECT_ID)，因此如果您不喜歡該字串，可以產生另一個隨機字串，或是嘗試使用自己的字串，看看是否可用。專案建立後，系統就會「凍結」該值。
還有第三個值，也就是部分 API 使用的「專案編號」。如要進一步瞭解這三種值，請參閱說明文件。

接著，您需要在 Cloud 控制台中啟用帳單，才能使用 Cloud 資源/API。完成本程式碼研究室的費用應該不高，甚至完全免費。如要停用資源，避免在本教學課程結束後繼續產生帳單費用，請按照程式碼研究室結尾的「清除」操作說明操作。Google Cloud 新使用者可參加價值$300 美元的免費試用計畫。

啟動 Cloud Shell

雖然可以透過筆電遠端操作 Google Cloud，但在本程式碼研究室中，您將使用 Google Cloud Shell，這是可在雲端執行的指令列環境。

啟用 Cloud Shell

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

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

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

這部虛擬機器搭載您需要的所有開發工具，並提供永久的 5GB 主目錄，而且可在 Google Cloud 運作，大幅提升網路效能並強化驗證功能。本程式碼研究室幾乎所有工作都可在瀏覽器或 Chromebook 上完成。

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

在 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`

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

gcloud config list project

指令輸出

[core]
project = <PROJECT_ID>

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

gcloud config set project <PROJECT_ID>

指令輸出

Updated property [core/project].

3. 啟用 API

所有 Google Cloud 專案預設都會啟用 BigQuery API。您可以在 Cloud Shell 中執行下列指令，確認是否為 BigQuery 專案擁有者：

gcloud services list

您應該會看到 BigQuery 列於其中：

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

如果 BigQuery API 未啟用，您可以在 Cloud Shell 中使用下列指令啟用：

gcloud services enable bigquery.googleapis.com

4. 驗證 API 要求

如要向 BigQuery API 提出要求，必須使用「服務帳戶」。「服務帳戶」屬於您的專案，Google Cloud Python 用戶端程式庫會使用此帳戶提出 BigQuery API 要求。服務帳戶與其他使用者帳戶一樣，都是以電子郵件地址表示。在本節中，您將使用 Cloud SDK 建立服務帳戶，然後建立以服務帳戶身分進行驗證所需的憑證。

首先，請設定 PROJECT_ID 環境變數：

export PROJECT_ID=$(gcloud config get-value core/project)

接著，請使用下列方式建立新的服務帳戶，用以存取 BigQuery API：

gcloud iam service-accounts create my-bigquery-sa \
  --display-name "my bigquery service account"

接著，建立 Python 程式碼使用的憑證，用以登入新的服務帳戶。輸入下列指令，建立憑證並儲存為 JSON 檔案 ~/key.json：

gcloud iam service-accounts keys create ~/key.json \
  --iam-account my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com

最後，設定 GOOGLE_APPLICATION_CREDENTIALS 環境變數，BigQuery Python 用戶端程式庫 (下一個步驟會說明) 會使用這個變數尋找憑證。環境變數應設為您所建立 JSON 憑證檔案的完整路徑，方法如下：

export GOOGLE_APPLICATION_CREDENTIALS=~/key.json

您可以進一步瞭解如何驗證 BigQuery API。

5. 設定存取控管機制

BigQuery 使用 Identity and Access Management (IAM) 管理資源存取權。BigQuery 有許多預先定義的角色 (使用者、資料擁有者、資料檢視者等)，您可以指派給上一個步驟中建立的服務帳戶。如要進一步瞭解存取權控管，請參閱 BigQuery 說明文件中的這篇文章。

查詢公開資料集前，請確認服務帳戶至少具備 roles/bigquery.user 角色。在 Cloud Shell 中執行下列指令，將使用者角色指派給服務帳戶：

gcloud projects add-iam-policy-binding ${PROJECT_ID} \
  --member "serviceAccount:my-bigquery-sa@${PROJECT_ID}.iam.gserviceaccount.com" \
  --role "roles/bigquery.user"

您可以執行下列指令，確認服務帳戶是否具備使用者角色：

gcloud projects get-iam-policy $PROJECT_ID

畫面應顯示如下：

bindings:
- members:
  - serviceAccount:my-bigquery-sa@<PROJECT_ID>.iam.gserviceaccount.com
  role: roles/bigquery.user
...

6. 安裝用戶端程式庫

安裝 BigQuery Python 用戶端程式庫：

pip3 install --user --upgrade google-cloud-bigquery

您現在可以開始使用 BigQuery API 撰寫程式碼了！

7. 查詢莎士比亞的作品

公開資料集是儲存在 BigQuery 中且可供一般大眾使用的任何資料集。有其他許多公開資料集可供您查詢。有些資料集是由 Google 託管，但大多數是由第三方託管。詳情請參閱「公開資料集」頁面。

除了公開資料集外，BigQuery 還提供了有限數量的範例資料表供您查詢。這些資料表位於 bigquery-public-data:samples 資料集中。samples 資料集中的 shakespeare 資料表包含莎士比亞作品的文字索引。能夠指出每個文字在各語料庫中出現的次數。

在這個步驟中，您會查詢 shakespeare 資料表。

首先，請在 Cloud Shell 中建立簡單的 Python 應用程式，用來執行 Translation API 範例。

mkdir bigquery-demo
cd bigquery-demo
touch app.py

從 Cloud Shell 右上方開啟程式碼編輯器：

前往 bigquery-demo 資料夾中的 app.py 檔案，並將程式碼替換成以下程式碼。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT corpus AS title, COUNT(word) AS unique_words
    FROM `bigquery-public-data.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    title = row['title']
    unique_words = row['unique_words']
    print(f'{title:<20} | {unique_words}')

請花一到兩分鐘研究程式碼，瞭解資料表的查詢方式。

回到 Cloud Shell，執行應用程式：

python3 app.py

畫面上應會顯示字詞清單和出現次數：

hamlet               | 5318
kinghenryv           | 5104
cymbeline            | 4875
troilusandcressida   | 4795
kinglear             | 4784
kingrichardiii       | 4713
2kinghenryvi         | 4683
coriolanus           | 4653
2kinghenryiv         | 4605
antonyandcleopatra   | 4582

8. 查詢 GitHub 資料集

為進一步熟悉 BigQuery，您現在要對 GitHub 公開資料集發出查詢。您可以在 GitHub 上找到最常見的提交訊息。您也會使用 BigQuery 的 Web 控制台預覽及執行臨時查詢。

如要查看資料內容，請在 BigQuery 網頁版 UI 中開啟 GitHub 資料集：

開啟 github_repos 表格

按一下「預覽」按鈕，查看資料的樣貌：

前往 bigquery_demo 資料夾中的 app.py 檔案，並將程式碼替換成以下程式碼。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
results = client.query(query)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

請花一到兩分鐘研究程式碼，瞭解如何查詢表格中最常見的提交訊息。

回到 Cloud Shell，執行應用程式：

python3 app.py

畫面上應會顯示提交訊息清單和出現次數：

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862

9. 快取與統計資料

BigQuery 會快取查詢結果。因此後續查詢所需時間較短。您可以使用查詢選項停用快取。BigQuery 也會追蹤查詢的統計資料，例如建立時間、結束時間和處理的位元組總數。

在這個步驟中，您將停用快取，並顯示查詢的統計資料。

前往 bigquery_demo 資料夾中的 app.py 檔案，並將程式碼替換成以下程式碼。

from google.cloud import bigquery

client = bigquery.Client()

query = """
    SELECT subject AS subject, COUNT(*) AS num_duplicates
    FROM bigquery-public-data.github_repos.commits
    GROUP BY subject
    ORDER BY num_duplicates
    DESC LIMIT 10
"""
job_config = bigquery.job.QueryJobConfig(use_query_cache=False)
results = client.query(query, job_config=job_config)

for row in results:
    subject = row['subject']
    num_duplicates = row['num_duplicates']
    print(f'{subject:<20} | {num_duplicates:>9,}')

print('-'*60)
print(f'Created: {results.created}')
print(f'Ended:   {results.ended}')
print(f'Bytes:   {results.total_bytes_processed:,}')

請注意以下幾點：首先，導入 QueryJobConfig 並將 use_query_cache 設為 false，即可停用快取。其次，您從工作物件存取查詢的統計資料。

回到 Cloud Shell，執行應用程式：

python3 app.py

與先前一樣，您應該會看到提交訊息清單和出現次數。此外，您也應該會在結尾看到一些查詢統計資料：

Update README.md     | 1,685,515
Initial commit       | 1,577,543
update               |   211,017
                     |   155,280
Create README.md     |   153,711
Add files via upload |   152,354
initial commit       |   145,224
first commit         |   110,314
Update index.html    |    91,893
Update README        |    88,862
------------------------------------------------------------
Created: 2020-04-03 13:30:08.801000+00:00
Ended:   2020-04-03 13:30:15.334000+00:00
Bytes:   2,868,251,894

10. 將資料載入 BigQuery

如要查詢自己的資料，請將資料載入 BigQuery。BigQuery 支援從許多來源載入資料，包括 Cloud Storage、其他 Google 服務和其他可讀取的來源。您甚至可以使用串流插入作業串流資料。詳情請參閱「將資料載入 BigQuery」頁面。

在這個步驟中，您會將儲存在 Cloud Storage 中的 JSON 檔案載入 BigQuery 資料表。JSON 檔案位於 gs://cloud-samples-data/bigquery/us-states/us-states.json

如要查看 JSON 檔案內容，可以使用 gsutil 指令列工具在 Cloud Shell 中下載：

gsutil cp gs://cloud-samples-data/bigquery/us-states/us-states.json .

您可以看到其中包含美國各州清單，每個州都是獨立成行的 JSON 文件：

head us-states.json

{"name": "Alabama", "post_abbr": "AL"}
{"name": "Alaska", "post_abbr":  "AK"}
...

如要將這個 JSON 檔案載入 BigQuery，請前往 bigquery_demo 資料夾中的 app.py 檔案，然後將程式碼替換為下列內容。

from google.cloud import bigquery

client = bigquery.Client()

gcs_uri = 'gs://cloud-samples-data/bigquery/us-states/us-states.json'

dataset = client.create_dataset('us_states_dataset')
table = dataset.table('us_states_table')

job_config = bigquery.job.LoadJobConfig()
job_config.schema = [
    bigquery.SchemaField('name', 'STRING'),
    bigquery.SchemaField('post_abbr', 'STRING'),
]
job_config.source_format = bigquery.SourceFormat.NEWLINE_DELIMITED_JSON

load_job = client.load_table_from_uri(gcs_uri, table, job_config=job_config)

print('JSON file loaded to BigQuery')

請花一兩分鐘瞭解程式碼如何載入 JSON 檔案，以及如何在資料集下建立具有結構定義的資料表。

回到 Cloud Shell，執行應用程式：

python3 app.py

系統會在 BigQuery 中建立資料集和資料表。

如要確認資料集已建立，請前往 BigQuery 控制台。您應該會看到新的資料集和資料表。切換至資料表的「預覽」分頁，即可查看資料：

11. 恭喜！

您已瞭解如何搭配使用 BigQuery 和 Python！

清理

如要避免系統向您的 Google Cloud 帳戶收取您在本教學課程中所用資源的相關費用：

前往 Cloud Console 中的「管理資源」頁面。
在專案清單中選取專案，然後按一下「刪除」。
在對話方塊中輸入專案 ID，然後按一下「Shut down」(關閉) 刪除專案。

瞭解詳情

Google BigQuery：https://cloud.google.com/bigquery/docs/
Google Cloud 上的 Python：https://cloud.google.com/python/
Python 適用的 Cloud 用戶端程式庫：https://googleapis.github.io/google-cloud-python/

授權

這項內容採用的授權為 Creative Commons 姓名標示 2.0 通用授權。