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

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

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

Challenges

業務資料導入常見挑戰

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

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

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

圖為使用Parabola、Datapre清洗資料，流程相當複雜，資料表不斷增加時，更新起來費時

2.資料更新不即時

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

No Code Solution

不會寫程式也能自動化串接BI資料更新

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，免除了繁瑣的認證。

Stitch Import API將資料傳送至指定的Data Warehouse

Basic Concepts and System Overview | Stitch Documentation

The basic concepts and architecture of Stitch, including an overview of replication and the Stitch system.

Stitch Docs

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

步驟一、Stitch設定

✨

1.註冊免費帳號
2.搜尋「Import API」，點擊
3.為你的「Integration」命名
4.記下API token
5.選擇目的地(destination)，此處以GCP Bigquery做示範
6.輸入顯示的名稱及此描述
7.上傳GCP的憑證Json檔案、選定主機位置，以及選定資料傳輸的方式
8.Dashboard可檢視傳輸中的資料表名稱以及傳輸的資料量多寡，會依據付費方案而有差異，可以參考這裡

步驟二、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文件。

Import API | Stitch Documentation

The Stitch Import API - or IAPI for short - is a REST API that allows you to push arbitrary data into your data warehouse. With the IAPI you can replicate da...

Stitch Docs

Stitch Import API Reference | Stitch Documentation

The Stitch Import API is a REST API that allows you to push arbitrary data into your Stitch destination.

Stitch Docs

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)等。