文章段落
Data Management ( DDL and DML)
這一篇我們來講一下在ＢＱ上的資料管理，例如我們需要刪除資料可以用如下的命令
bq rm dataset_name.table_name
是直接刪除單一個table
或是
bq rm -f -f dataset_name
其中 -r 是 recursively, -f force
也就是不管該dataset有什麼資料直接強制刪除而且也不用再跳出確認是否要刪除的訊息。
以上是用BQ在cloud shell的作法，當然也可以在SQL語法直接做這一類的操作
例如
Drop table if exists dataset_name.table_name
在之前的篇章也有提到也可以設定table自動到期就刪除了選項，範例請參考BigQuery –匯入資料此篇文章。
BQ的DDL(Data Definition Language)跟一般的DataBase一樣有，create/drop/alter table的等操作。當然也有DML(Data Manipulation Language)–delete/insert/merge等功能可以使用，但到目前為止無法使用DML來copy table。例如
bq cp source(dataset_name.table_name) new(dataset_name.table_name)
當中可以使用參數
-a 或是 –append_table ，留存原來的資料。或是
-noappend_table，不留存原來的資料覆蓋新的資料上去。
BQ的copy table是非常快速的。不論你的資料量有多少，這個可以解決一般RDBMS要copy 一個大的table做操作時需要等待很長的時間。而BQ只要幾秒鐘就可以完成這件事情。
如何有效的匯入資料
上一篇我們使用的CSV的資料檔來匯進BQ,但其實使用CSV檔是非常沒有效率的。基本上建議使用Avro的檔案格式來匯入BQ,關於Avro的介紹大家可以去參照Apache Avro的官網有詳細的介紹。但可惜的是打開Avro的檔案你會發現你看不懂資料內容是長得如何的，若你需要原始檔案是可容易解讀並能夠比CSV檔匯入的效率好，哪麼使用JSON的檔案格式匯入。但JSON 的缺點就是同樣資料筆數的檔案大小會比CSV來得大。故如何拿捏匯入的檔案格式取決與你的匯入資料時的考量點。BQ還支援了Paruet及及ORC兩種檔案格式這兩個格式很相近，大家有興趣可以去研究一下。
雖然講哪麼多檔案格式匯入的效率都比ＣＳＶ來得好，但真實的是世界中沒哪麼理想。很多的data source還是以CSV為大宗的資料來源。前一篇有提到過若你從本地端上傳CSV的檔案它的大小與資料筆數是有限制的，唯一要匯入大檔案的CSV就必需要透過Cloud storage來作為媒介，但可能上傳這個大的CSV檔案上去就也要花時間了，我們該如何加效率呢？
我們可以使用guutil這一個cloud storage的command line(請先確定你的本機有安裝）使用multithreaded upload ,將一個大檔案同時拆分成很多個小檔案上傳後，檔案會在cloud storage自重組回來。
gsutil -m cp source_file gs://bucket/location
然後再將資料匯入BQ
bpload gs://bucket/location/your.csv
所以根據上面及前一篇提到的，CSV檔案如果很大我們該如何處理載入bq的效率呢？
這邊會建議將CSV檔案壓縮後透過multithreaded upload到cloud storage再進入BQ。這樣的方式將會快很多，但相對的因為使用了Cloud storage的功能。定期地去清理cloud storage的資料也是重要的一環，因為儲存資料也是要錢的。除非龜公司需要因為公司的政策需要保存這些資料。Cloud storage有有歸檔的功能能夠讓你們再節省一些費用。
需要注意的是若你的匯入的資料量很大卻沒有先跟Google購買專屬的BQ資源的話，哪麼在匯入大量資料的同時需要注意GCP是有quota的。
詳請請參照GCP的文件
https://cloud.google.com/bigquery/quotas#load_jobs
既然匯入資料哪麼麻煩，有沒有可能直接使用BQ的分析engine來query外部的資料呢？
BQ現行支援了Federated Queries，在不移動資料的狀態下直接使用BQ分析。
現階段BQ支援的外部資料來源有
Cloud storage/big table/Cloud SQL/Google driver
但有一好沒有兩好，雖然不用在移動資料做分析，但因為是透過網路去分析外部資料所以效能一定是比把資料放在BQ裡差。
哪要如何使用這一個功能呢？
基本上有三個步驟
1. 使用 bq mkdef –產生這一個外部資料的定義檔案
2. 使用 bq mk , 讓 bq去讀取外部資料的定義檔
3. 驗證無問題後即可使用bq來分析
當然我們也可以使用WEB UI介面來施作，請參考下圖
在create table 時，要將table指定成”external table”
若是使用bq command，我們必需要產生外部資料的的table definition檔案
例如資料是在cloud storage的CSV檔案
bq mkdef –source_format=CSV \
–autodetect \
gs://bucket/your.csv \
> /tmp/yourcsv.json
之後再用bq mk命令列
bq mk –external_table_definition=/tmp/your.csv \
dataset_name.table_name
但這個cloud storage 的bucket有一堆 CSV檔案，哪該怎麼辦呢？
只要在檔案名稱後加*
例如
gs://bucket/want_file_*
哪麼何時才需要使用Federated Queries呢？
1. 你需要初期驗證資料,也就是你可以把外部資料視為是在stage環境。這樣可以省下匯入BQ的時間與放在BQ內的儲存價錢。
2. 你的資料可能是經常變動的，例如Google sheet檔案。是經常性變動。經常性的匯入BQ是無效率的
3. 外部資料的特性是比較符合在原來的外部資料。BQ只是偶爾使用，例入Big table , 它是low-latency/high-volume/streaming ingest
另外之前有提到ＢＱ支援多種的檔案型態的匯入，若你的來源檔是CSV會JSON檔案哪會建議你在匯入BQ時要做好schema的定義與資料清理。否則就會如同我們在前一篇看到匯入CSV檔案後有一些錯誤需要在BQ裡被修正。
