使用 BigQuery 的 bq 指令列工具載入及查詢資料

1. 簡介

BigQuery 是 Google 提供的全代管 NoOps 數據分析資料庫，價格相當實惠，您可以使用 BigQuery 查詢 TB 規模的資料，不必管理基礎架構，也不需要資料庫管理員。BigQuery 使用熟悉的 SQL，並採用「即付即用」的計費模式。您可專心分析資料，找出有意義的深入分析結果。在本程式碼研究室中，您將使用 bq 指令列工具，將本機 CSV 檔案載入至新的 BigQuery 資料表。

課程內容

• 如何使用 BigQuery 的 bq 指令列工具
• 如何將本機資料檔案載入 BigQuery 資料表

軟硬體需求

• 具備 Google Cloud 專案
• 瀏覽器，例如 Google Chrome

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 免費試用方案，並新增帳單帳戶。

Cloud Shell

您將使用 Cloud Shell，這是 Google Cloud 中執行的指令列環境。

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

3. 建立資料集

建立資料集來存放資料表。

什麼是資料集？

BigQuery 資料集是資料表的集合，資料集中的所有資料表都儲存在同一個資料位置。您也可以附加自訂存取權控管，限制資料集及其資料表的存取權。

建立資料集

在 Cloud Shell 中，使用 bq mk 指令建立名為「bq_load_codelab」的資料集。

bq mk bq_load_codelab

查看資料集屬性

使用 bq show 指令查看資料集屬性，確認您已建立資料集。

bq show bq_load_codelab

輸出結果應該會類似下列內容：

Dataset my-project:bq_load_codelab

   Last modified           ACLs          Labels
 ----------------- -------------------- --------
  15 Jun 14:12:49   Owners:
                      projectOwners,
                      [e-mail]
                    Writers:
                      projectWriters
                    Readers:
                      projectReaders

4. 建立資料檔案

BigQuery 可以載入多種資料格式的資料，包括以換行符分隔的 JSON、Avro 和 CSV。為求簡單易懂，我們將使用 CSV。

建立 CSV 檔案

在 Cloud Shell 中建立空白的 CSV 檔案。

touch customer_transactions.csv

在 Cloud Shell 中執行 cloudshell edit 指令，在程式碼編輯器中開啟 CSV 檔案。這會開啟新的瀏覽器視窗，其中包含程式碼編輯器和 Cloud Shell 面板。

cloudshell edit customer_transactions.csv

注意：程式碼編輯器需要第三方 Cookie 才能運作，因為程式碼編輯器是從與 Google Cloud 控制台不同的網域載入。

在程式碼編輯器中，輸入以半形逗號分隔的值，載入至 BigQuery。

ID,Zipcode,Timestamp,Amount,Feedback,SKU
c123,78757,2018-02-14 17:01:39Z,1.20,4.7,he4rt5
c456,10012,2018-03-14 15:09:26Z,53.60,3.1,ppiieee
c123,78741,2018-04-01 05:59:47Z,5.98,2.0,ch0c0

依序點選「檔案」>「編輯」，即可儲存 CSV 檔案。

5. 載入資料

使用 bq load 指令將 CSV 檔案載入 BigQuery 資料表。

bq load \
    --source_format=CSV \
    --skip_leading_rows=1 \
    bq_load_codelab.customer_transactions \
    ./customer_transactions.csv \
    id:string,zip:string,ttime:timestamp,amount:numeric,fdbk:float,sku:string

您使用了下列選項：

• --source_format=CSV 會在剖析資料檔案時使用 CSV 資料格式。
• --skip_leading_rows=1 會略過 CSV 檔案的第一行，因為這是標頭列。
• Bq_load_codelab.customer_transactions—the first positional argument— 定義資料應載入的資料表。
• ./customer_transactions.csv (第二個位置引數) 會定義要載入的檔案。除了本機檔案，bq load 指令也可以載入 Cloud Storage 中的檔案 (使用 gs://my_bucket/path/to/file URIs)。
• 結構定義，可透過 JSON 結構定義檔或以逗號分隔的清單定義。(為簡化作業，您使用了以半形逗號分隔的清單)。

您在 customer_transactions 資料表中使用下列結構定義：

• Id:string：客戶 ID
• Zip:string：美國郵遞區號
• Ttime:timestamp：交易發生的日期和時間
• Amount:numeric：交易金額 (數值資料欄會以十進位格式儲存資料，這對貨幣值很有用)。
• Fdbk:float：交易意見調查的評分
• Sku:string：所購商品的 ID

取得資料表詳細資料

顯示表格屬性，確認表格已載入。

bq show bq_load_codelab.customer_transactions

輸出內容：

Table my-project:bq_load_codelab.customer_transactions

   Last modified          Schema          Total Rows   Total Bytes
 ----------------- --------------------- ------------ -------------
  15 Jun 15:13:55   |- id: string         3            159
                    |- zip: string
                    |- ttime: timestamp
                    |- amount: numeric
                    |- fdbk: float
                    |- sku: string

6. 查詢資料

資料載入完畢後，您可以使用 BigQuery 網頁版 UI、bq 指令或 API 查詢資料。只要您有權讀取資料集，查詢就能將資料與任何資料集 (或多個資料集，只要這些資料集位於同一個位置) 聯結。

執行標準 SQL 查詢，將資料集與美國郵遞區號資料集聯結，並依美國州別加總交易。使用 bq query 指令執行查詢。

bq query --nouse_legacy_sql '
SELECT SUM(c.amount) AS amount_total, z.state_code AS state_code
FROM `bq_load_codelab.customer_transactions` c
JOIN `bigquery-public-data.utility_us.zipcode_area` z
ON c.zip = z.zipcode
GROUP BY state_code
'

該指令應會輸出類似下列內容：

Waiting on bqjob_r26...05a15b38_1 ... (1s) Current status: DONE   
+--------------+------------+
| amount_total | state_code |
+--------------+------------+
|         53.6 | NY         |
|         7.18 | TX         |
+--------------+------------+

您執行的查詢使用了公開資料集和私人資料集。如要進一步瞭解，請參閱同一項查詢的註解版本：

#standardSQL
SELECT
  /* Total of all transactions in the state. */
  SUM(c.amount) AS amount_total,

  /* State corresponding to the transaction's zipcode. */
  z.state_code AS state_code

/* Query the table you just constructed.
 * Note: If you omit the project from the table ID,
 *       the dataset is read from your project. */
FROM `bq_load_codelab.customer_transactions` c

/* Join the table to the zipcode public dataset. */
JOIN `bigquery-public-data.utility_us.zipcode_area` z

/* Find the state corresponding to the transaction's zipcode. */
ON c.zip = z.zipcode

/* Group over all transactions by state. */
GROUP BY state_code

7. 清除所用資源

使用 bq rm 指令刪除您建立的資料集。使用 -r 標記移除其中的所有資料表。

bq rm -r bq_load_codelab

8. 恭喜！

您已將資料表上傳至 BigQuery 並進行查詢！

涵蓋內容

• 使用 bq 指令列工具與 BigQuery 互動。
• 使用 BigQuery 查詢，將您的資料和公開資料集連結。

後續步驟

請點選下列連結瞭解更多資訊：

• bq 指令列工具
• 將資料載入 BigQuery 的其他方式。
• 其他可透過 BigQuery 存取的公開資料集。
• TIL with BigQuery 中的天氣、犯罪和其他類型的資料。