BigQuery –匯入資料 part 2

Data Management ( DDL and DML)

這一篇我們來講一下在BQ上的資料管理,例如我們需要刪除資料可以用如下的命令

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兩種檔案格式這兩個格式很相近,大家有興趣可以去研究一下。
雖然講哪麼多檔案格式匯入的效率都比CSV來得好,但真實的是世界中沒哪麼理想。很多的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

另外之前有提到BQ支援多種的檔案型態的匯入,若你的來源檔是CSV會JSON檔案哪會建議你在匯入BQ時要做好schema的定義與資料清理。否則就會如同我們在前一篇看到匯入CSV檔案後有一些錯誤需要在BQ裡被修正。