不會寫程式也能製作BI報表 - 用Stitch Data 自動化更新你的Bigquery資料庫

經營電商、網路行銷帶來最大的資產莫過於累積成千上萬筆的業務資料，如果可以將這些數據資料善加運用，轉化為有價值的決策洞見，就會形成商業智慧(BI, Business Intelligence)，成為企業決策的重要基石。

近年來數據驅動(Data Driven)與資料視覺化(Data Visualization)相當熱門，許多公司會將這些重要的資訊整理成BI報表，提供內部使用者分析資料，並依此擬定符合業務邏輯的商業決策。之前曾撰文討論如何透過ETL工具(如：Dataprep、Parabola)清洗好Raw Data之後，再上傳到GCP等雲端資料倉儲空間(Data Warehouse)，最後再透過Matabase製作BI報表，這次我們要解決的問題是如何將業務資料即時、準確地同步更新到資料倉儲空間，讓BI報表可以反應即時情況？

資料導入最傳統的做法莫過於將這些資料以csv或excel檔案的形式，透過ETL工具(如：Dataprep、Parabola)清洗好之後，再上傳到GCP等資料倉儲，但這整個過程中還是必須仰賴人工作業，無法做到即時更新資料庫，尤其當資料集、資料表不斷增加時，其實是非常耗費人力的。

1. 資料必須透過人工整理，相當費力

資料必須仰賴人工整理成csv或excel檔案，再透過Dataprep工具將清洗完的資料直接傳送到Google Bigquery。即便有了ETL工具能夠減少資料整理錯誤的可能，但過程中人為的介入仍可能導致資料錯誤，而且還會產生許多不必要的重複檔案。隨著資料越來越繁雜的時候，維護整個流程就會相當辛苦。

2.資料更新不即時

人工更新資料最大的問題就是更新的速度不即時，特別是當電商有訂單、庫存大量變化的時候，無法及時反映到報表，讓業務、營運的同仁無法第一時間做出決策。

Google Bigquery有提供API可以透過Xano傳遞資料，但在開發上速度會比較慢，因為必須先解決伺服器對伺服器的授權問題(OAuth)，而且更新資料的Http Request稍微複雜了一點。有興趣可以參考以下連結：

本文會以Stitch Data 做範例，一個協助將資料轉移到資料倉儲(Data Warehouse)的雲端(Cloud-based)ETL工具(Extract, Transform, Load)雲端服務，擁有強大的跨平台整合能力，例如可以連接Amazon S3、Google Bigquery、Microsoft SQL Server、PostgreSQL、Oracle等知名的資料庫平台，也提供像是Facebook Ads、Google Ads、Google Analytics分析工具上的整合，可以直接串接資料到你的BI報表。好處是可以透過簡單易用的Import API直接將資料透過Stitch傳送到目的地(destination)，例如Google Bigquery，免除了繁瑣的認證。

以下將實作如何透過Xano串接Stitch Import API將資料自動同步到Google Bigquery

步驟一、Stitch設定

步驟二、Xano API實作

基本上Stitch Import API是透過REST API的方法(Http verb)將資料傳送到Stitch，Stitch則是協助將資料轉送到我們指定的資料倉儲地點，因此傳入的參數只接受JSON格式。

1.必要參數：

Access token即為設定integration時所記下的API token，將這組token以Authorizaion: Bearer的形式放入Http Request中的Header

Authorization: Bearer < [access_token] >

Client ID，如文件中說明，此參數在設定完Stitch Integration後可由網址中找到

https://app.stitchdata.com/v2/client/XXXX/pipeline/connections
/ XXXX is where your client ID will be

詳細操作可點擊下方連結參考官方API文件。

2.以批次傳送資料(Send a batch data to Stitch)為例：

如文件中說明，若要將資料傳送到Stitch，必要參數除了Access token、Client ID以外，尚有三種，外加data一共四種：table_name即資料表名稱、schema資料結構、messages變數是資料會打包成為一個物件後放入陣列中、key_names是資料表的主鍵或外鍵，以文字陣列的形式作為參數。

其中message為一個物件陣列(Object Array)，可從文件中得知其參數為action、sequence、data，action預設為upsert方法，表示資料會依據主鍵更新資料，sequence表示資料傳入時的序列，以毫秒的unix time表示，最後則是傳入的資料以物件表示，並且資料格式需與定義好的Schema相符合，例如id可能是數值型態、name是文字型態、age是整數型態(integer)，其他還有像是浮點數(number)、布林值(boolean)等。

知道API參數要放哪些內容之後，就可以在Xano上實現。

接著要傳入的資料可以直接從Xano的query all records取得(如圖程式第9行起)，超方便！但因為資料要轉換成message的陣列，必須在後面使用For Each loop方法，迭代將資料改寫成指定的物件陣列，會依照schema而有所不同。

最後就是Run & debug，看看API打出去有沒有出現錯誤，官方文件中有說明不同的Status Codes所代表的涵意各是什麼，200 OK表示成功把資料傳入、201 Created表示新增資料成功，202 Accepted表示所有資料等待傳入。

上述程式如果沒有問題，就可以將程式納入Xano的排程(background task)中，就完成自動化更新資料庫囉！

步驟三、Google Bigquery檢視資料

順利將資料打入Stitch之後，可以在Stitch網站的Dashboard中看到是否有正在傳輸的資料，同時也可以到Bugquery中檢視是否已經將資料正確匯入。匯入完畢後，Metabase就可以自行連動，完成報表自動化更新！