前置準備
• 安裝 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。)