Loading Data into BigQuery
這一篇我們將展示如何將資料匯入BQ，將資料匯入ＢＱ的方式有很多。我們將為大家一一的說明。
以下我們將範例我們將使用ㄧ個CSV檔案如何載入ＢＱ，一般我們可以使用圖型介面或命令介面來將資料匯入。在此我們使用命令介面來做展示。附帶一提若你的CSV檔案過大，你可以壓縮成點gz的壓縮檔。BQ支援你上傳的檔案是gz壓縮檔，它會自動解開這個壓縮檔然後才載入資料，為何我們不用圖型介面來匯入資料呢？
因為若你要上傳的資料是從你的本機上做的話，圖形介面的方式上傳的檔案不能大於10M且資料不能超過16000筆。除非你的檔案已事先上傳到Cloud storage，這樣這一個限制才不會有。若後續你的資料是要定期自動匯入的話，Cloud storage是一個很好的媒介。
首先我們有一個在本機上壓縮的 CSV檔案，這邊我們用Cloud shell來展示。若您的本機中已經安裝好gcloud及BQ的SDK,則上傳到Cloud shell的步驟可以忽略。
如上圖，當打開cloud shell之後。選擇右上角的功能列，有上傳檔案的功能。瀏覽你的本機位置後上傳該檔案。
我們在這裡展示的資料是美國各大學的學費資料，由於該資料很大，故這個檔案是壓縮過的。
在開始前我們可以使用zless command來瀏覽一下內部的資料
接下來我們要將資料匯入BQ中，第一步我們要做的是create一個新的Dataset(類似資料庫裡的Database)之後才是把CSV資料匯入table中
我們使用bq開頭的命令列
bq –location=asia-east1 mk test
上面的command,我們在台灣的region create一個test的dataset， location這個參數也可以不用加上。哪BQ就會把dataset放在你在GCP console設定的 region，
注意:BQ的資料是region範圍，意思是若你要將資料從另一個區域搬移到另一個區域會比較麻煩。尤其要移動大量的資料時。
接下來我們將資料匯入到BQ中，
bq –location=asia-east1 load –source_format=CSV –autodetect test.college ./college.csv.gz
上面的命令列，我們指定的資料格式是CSV檔，使用自動偵測的方式若你的CSV檔有,跟著是 test.college，這邊的格式是 dataset-name.table.name，最後才帶入CSV的檔案位置但結果很明顯的有問題了。
從上面的訊息來看，是第26個欄位（名稱是HBCU)的第591行有Null值的存在，上面的命令列預設是沒有NULL值的存在。關於NULL值的討論之前的文章我們有提到了。在這邊我們的處理方式是，遇到Null值的欄位我們就inster一個NULL的文字字串，請參考下面的命令列
bq –location=asia-east1 load –null_marker=NULL –source_format=CSV –autodetect test.college ./college.csv.gz
另外這個載入的資料是暫時性的需求的話，我們也可以把這個table設定成多久之後自動刪除，如下範例我們將這一個table設定三天後自動刪除
Alter table test.college
set options (
expiration_timestamp=
timestamp_add(current_timestamp(), interval 3 day),
description=”College table that expires 3 days from now”
)
載入完成之後，我們可以開始針對這個table做一個Query的範例，請看如下範例
Select
Instnm
, adm_rate_all
, first_gen
, md_earn_wne_p10
, sat_avg
from test.college
where
safe_cast(sat_avg as float64) > 1300
and safe_cast(adm_rate_all as float64) < 0.2
and safe_cast(first_gen as float64) > 0.1
order by
cast(MD_faminc as float64) asc
如上圖，這樣是有結果出來的。但是請注意到我們在Where的條件式內使用了
safe_cast( 欄位名稱 as float64)，強制將這三個欄位轉換成float64的資料形式。
如果沒有的話就會變成如下
為什麼會這樣呢？
因為BQ的欄位的自動偵測模式不一定能完全知道你的欄位資料的型態到底是什麼，若你的欄位整個cloumn裡有數字也有文字。哪麼BQ就會把這一個欄位的資料型態定義為文字字串。所以如同之前有提到在把資料匯入到BQ之前，需要把資料整理乾淨。
在GCP的服務裡，提供了Cloud dataprep這一個工具讓你能夠做好資料清理的動作，然後再從DataPrep直接無縫的匯入到BQ中。
但若只使用BQ哪該如何解決呢？
1. 若欄位數很少，你可以使用圖形介面去重新design scheam然後再將資料匯入
2. 若欄位數很多，可能有上百個欄位而你要修正的欄位只有幾個。哪麼重新設計欄位應該就很就是很煩人又沒有效率的工作方式了。
這個時候我們可以重新把欄位資料用JSON的格式匯出，修正我們要的欄位資料型態後才重新修改及匯入資料
我們可以在cloud shell使用如下命令列
bq show –format pretty –schema test.college > schema.json
在這個table schema中我們要把sat_avg, adm_rate_all, first_gen的資料型態從文字字串轉成FLOAT64
修改完後重新修正欄位及匯入資料
命令列如下
bq –location=asia-east1 load –null_marker=NULL –replace –source_format=CSV –schema=schema.json –skip_leading_rows=1 test.college ./college.csv.gz
跟之前不一樣的是
–schema= 參數指定了table schema的資料來源
–skip_leading_rows=1 ，排除匯入資料的第一個row(為這些資料是header information)
— replace , 這代表原有的舊資料都不需要了。重新用新的資料取代
這樣之前在where 函數中所用到的 safe_cast 就可以去掉。
Select
Instnm
, adm_rate_all
, first_gen
, md_earn_wne_p10
, sat_avg
from test.college
where
safe_cast(sat_avg as float64) > 1300
and safe_cast(adm_rate_all as float64) < 0.2
and safe_cast(first_gen as float64) > 0.1
order by
MD_faminc asc
以上就是將CSV資料簡單的介紹，下一篇我們將介紹BQ的資料管理(DDL and DML)
