快速啟用 Google Cloud 大數據神器：BigQuery

前置準備

     •   安裝 Cloud SDK
     •   啟用 BigQuery API

檢視一個 Table

1. 檢視 schema ，(指令)bq show projectId:datasetId.tableId，下面會使用原本的範例表格做測試

$ bq show publicdata:samples.shakespeare

// 輸出
Table publicdata:samples.shakespeare
Last modified                  Schema                 Total Rows   Total Bytes   Expiration   Time Partitioning   Labels
----------------- ------------------------------------ ------------ ------------- ------------ ------------------- --------
27 Aug 05:43:49   |- word: string (required)           164656       6432064
                  |- word_count: integer (required)
                  |- corpus: string (required)
                  |- corpus_date: integer (required)

執行一個 query

1. 指令為 bq query “sql語法敘述”，除非使用 IGNORE CASE

//以下範例會計算包含 `raisin` 這個字出現幾次，Query 出來的字串是有區別大小寫的，除非使用 SQL 語法的 IGNORE CASE
$ bq query "SELECT word, SUM(word_count) as count FROM publicdata:samples.shakespeare WHERE word CONTAINS 'raisin' GROUP BY word"

Waiting on bqjob_r2a94d315577a3c31_000001635db3b9b2_1 ... (0s) Current status: DONE
+---------------+-------+
|     word      | count |
+---------------+-------+
| dispraisingly |     1 |
| praising      |     8 |
| Praising      |     4 |
| raising       |     5 |
| dispraising   |     2 |
| raisins       |     1 |
+---------------+-------+

2. 如果 query 的是 table 裡面沒有的字，那就不會回傳結果

$ bq query "SELECT word FROM publicdata:samples.shakespeare WHERE word = 'huzzah' IGNORE CASE"

新建一個 table

Step 1. 下載資料

dataset 是由很多 table所組成的。

1. 下載一包美國新生兒的名字資料，大約 7MB的 公開資料，並解壓縮。
2. 任意打開一個檔案 yob2010.txt，資料格式為 CSV。有三欄：姓名、性別、該名字的總數。
3. 複製 yob2010.txt 到目前的工作路徑上。

Step 2. 新建一個 dataset

1. 建立一個新的 dataset。dataset名稱可以長達 1024個字，無法以數字、底線、空白開頭。

//建立一個名為 babynames 的 datase
$ bq mk babynames
Dataset 'myprojectid:babynames' successfully created.

2. 確認是否新增成功

$ bq ls
datasetId
------------
olddataset
babynames

Step 3. 上傳 table

bq load用來新增或更新 table
1. 命名新的 table 為 names2010，以下為 bq load的指令參數

     •   datasetID: babynames
     •   tableID: names2010
     •   source: yob2010.txt
     •   schema: name:string,gender:string,count:integer

$ bq load babynames.names2010 yob2010.txt name:string,gender:string,count:integer
Upload complete.
Waiting on bqjob_rce23f00841d5675_000001635dcfa7b5_1 ... (0s) Current status: DONE

2. 確認是否有加入到 babynames dataset

$ bq ls babynames
 tableId    Type    Labels   Time Partitioning
----------- ------- -------- -------------------
names2010   TABLE

3. 查看該 table的 schema

$ bq show babynames.names2010
Table myprojectid:babynames.names2010
Last modified         Schema         Total Rows   Total Bytes   Expiration
----------------- ------------------- ------------ ------------- ------------
13 Mar 15:31:00   |- name: string     34041        653855
                  |- gender: string
                  |- count: integer

預設是用 UTF-8進行解碼，如果需要使用 ISO-8859-1(Latin-1)解碼，則需要多加 -E參數

Step 4. 執行 query

1. Query 最多人取的女生名字前五名

$ bq query "SELECT name,count FROM babynames.names2010 WHERE gender = 'F' ORDER BY count DESC LIMIT 5"
Waiting on bqjob_r62d51afcc3b02589_000001635dd986b9_1 ... (0s) Current status: DONE
+----------+-------+
|   name   | count |
+----------+-------+
| Isabella | 22905 |
| Sophia   | 20639 |
| Emma     | 17338 |
| Olivia   | 17022 |
| Ava      | 15429 |
+----------+-------+

2. Query 最不常見的五個男生名字

$ bq query "SELECT name,count FROM babynames.names2010 WHERE gender = 'M' ORDER BY count ASC LIMIT 5"
Waiting on job_556ba2e5aad340a7b2818c3e3280b7a3 ... (1s) Current status: DONE
+----------+-------+
|   name   | COUNT |
+----------+-------+
| Aarian   |     5 |
| Aaidan   |     5 |
| Aamarion |     5 |
| Aadhavan |     5 |
| Aaqib    |     5 |
+----------+-------+

Step 5. 刪除 dataset

1. 刪除 dataset

//刪除
$ bq rm -r babynames

延伸閱讀

https://blog.gcp.expert/bigquery-manage-gcp-cost/

(原文翻譯自 Google Cloud。)