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

1. 簡介

BigQuery 是 Google 提供的全代管數據分析資料庫，不但免人工管理，而且價格低廉。您可以使用 BigQuery 查詢 TB 規模的資料，不需要資料庫管理員或基礎架構。BigQuery 使用您熟悉的 SQL 和「即付即用」收費模式，BigQuery 可讓您專心分析資料，找出有意義的結果。在本程式碼研究室中，您將使用 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。

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

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

Cloud Shell

您將使用 Cloud Shell，這是在 Google 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. 建立資料集

建立包含資料表的資料集。

什麼是資料集？

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 編輯指令，在 Cloud Shell 的程式碼編輯器中開啟 CSV 檔案，系統隨即會開啟新的瀏覽器視窗，其中含有程式碼編輯器和 Cloud Shell 面板。

cloudshell edit customer_transactions.csv

注意：程式碼編輯器會從 Google Cloud 控制台以外的其他網域載入，因此程式碼編輯器必須搭配第三方 Cookie 才能正常運作。

在程式碼編輯器中，輸入要載入 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

按一下「檔案」>編輯。

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 載入指令也能使用 gs://my_bucket/path/to/file URIs 從 Cloud Storage 載入檔案。
• 結構定義：可在 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 查詢指令執行查詢。

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 取得的公開資料集。
• 搭配使用 BigQuery 和 TIL 中的天氣、犯罪及其他類型的資料。