搭配 Python 使用 BigQuery

1. 總覽

BigQuery 是 Google 推出的 PB 規模全代管數據分析資料倉儲系統，由於 BigQuery 沒有基礎架構且不需管理，也不需要資料庫管理員，因此免人工管理。因此，您可以專心分析資料，找出有意義的結果、使用熟悉的 SQL，以及運用我們的即付即用模式。

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

課程內容

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

軟硬體需求

• Google Cloud 專案
• 瀏覽器，例如 Chrome 或 Firefox
• 熟悉使用 Python

問卷調查

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

僅供閱讀 閱讀並完成練習

您對 Python 的使用體驗有何評價？

新手 中級 還算容易

針對使用 Google Cloud 服務的經驗，您會給予什麼評價？

新手 中級 還算容易

2. 設定和需求

自修環境設定

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

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

注意：專案 ID 必須是全域唯一的，而且選定後即無法使用。您是該 ID 唯一的使用者。刪除專案之後，就無法再使用該 ID。

注意：如果您使用 Gmail 帳戶，請保留預設位置的「無機構」。如果您使用的是 Google Workspace 帳戶，請選擇適合貴機構的位置。

2. 接下來，您需要在 Cloud 控制台中啟用計費功能，才能使用 Cloud 資源/API。執行這個程式碼研究室並不會產生任何費用，如果有的話。如要關閉資源，以免產生本教學課程結束後產生的費用，請按照任「清除所用資源」操作請參閱本程式碼研究室結尾處的操作說明。Google Cloud 的新使用者符合 $300 美元免費試用計畫的資格。

啟動 Cloud Shell

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

啟用 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].

3. 啟用 API

根據預設，所有 Google Cloud 專案都應啟用 BigQuery API。您可以在 Cloud Shell 中使用下列指令來檢查結果是否屬實：系統應該會列出 BigQuery：

gcloud services list

畫面上應會列出 BigQuery：

NAME                              TITLE
bigquery.googleapis.com           BigQuery API

...

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

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 有數個預先定義的角色 (使用者、dataOwner、dataViewer 等)，可供指派給您在上一個步驟中建立的服務帳戶。如要進一步瞭解存取權控管，請參閱 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 資料表。

注意：您可以在這裡查看 BigQuery 控制台中「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

注意：如果收到 PermissionDenied 錯誤 (403)，請確認驗證 API 要求步驟按照的步驟操作。

• 檢查是否已定義憑證環境變數：echo $GOOGLE_APPLICATION_CREDENTIALS
• 您應該會看到憑證檔案的完整路徑：/home/$USER/key.json
• 接著，檢查是否已建立憑證：cat $GOOGLE_APPLICATION_CREDENTIALS
• 您應該會看到類似下列內容：{"type": "service_account","project_id": "PROJECT_ID",...}

如果有任何錯誤，請再次查看「Authenticate API 要求」步驟。

8. 查詢 GitHub 資料集

為了進一步瞭解 BigQuery，現在請查詢 GitHub 公開資料集。您可以在 GitHub 找到最常見的修訂版本訊息。您也可以使用 BigQuery 的網路控制台預覽及執行臨時查詢。

如要查看資料看起來的模樣，請在 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. 恭喜！

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

清理

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

• 在 Cloud 控制台中，前往「管理資源」頁面。
• 在專案清單中，選取您的專案，然後按一下「Delete」(刪除)。
• 在對話方塊中輸入專案 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/

授權

這項內容採用的是創用 CC 姓名標示 2.0 通用授權。