BigQuery串接Google sheet的初階應用以及Data Studio的視覺化功能介紹
BigQuery串接Google sheet的初階應用 以及Data Studio的視覺化功能介紹

BigQuery串接Google sheet的初階應用以及Data Studio的視覺化功能介紹

BigQuery 基本介紹


BigQuery是一個具有高擴展性,而且查詢速度飛快的Datawarehouse,它的特性之一就是:具有Google獨特的columnar data structure[註1],因此,在查詢速度上也有相當優異的表現。

在Cloud Ace技術部落格《什麼是GCP?可以拿來吃嗎?完整介紹Google Cloud Platform》提到:4TB共100億筆的資料,只要24秒就能完成分析。由此可知,BigQuery查詢速度之快,非一般資料查詢工具所能及。  BigQuery這個產品,具有以下三大特色:

  • 可以方便下SQL指令查詢,而且在查詢速度非常快
  • 高擴充性,可以吞吐大量的資料
  • 方便進行即時的資料分析,不用重建資料表,提供商業資料分析極佳解決方案,並且內建機器學習(Machine Learning)功能。方便企業進行AI大數據分析。

這篇文章我會把重點放在BigQuery與其他Google上的服務串接使用的延伸話題中,盼能透過實際的操作範例,讓各位讀者們體驗如何應用BigQuery多元的雲端工具串連服務,從中找到適合貴公司的解決辦法。


Topic 1: Google App Script


Google App Script,是一個Google自行研發的輕量化Scripting程式。透過Google App Script,可以完成一些簡單自動化的程序,或是事件觸發(Event Trigger)的使用邏輯。它可以方便使用者在Gmail、Google Doc跟Google sheet等工具上做程式互動。

在Google sheet上讀取BigQuery table的資料

在試算表的工具列中,我們選取tools裡面的Script Editor。每當開啟一個新的App script檔案,我們都必須建立一個新的專案,才能把執行的App Script程式(.gs檔)儲存起來。在初次使用的時候,Google會要求我們授權App script個人帳戶的使用權限:(如下圖)

取得授權後,我們要從Resource的地方,開啟BigQuery的服務我們就可以執入我們的程式。在Google Sheet當中,BigQuery被視為一個API服務(第三方),因此在進階服務的功能表列中,我們要打開服務,就要找到BigQuery並且把off按鈕調整為on。(如下圖)

在我們把環境設定好了以後,我們要稍微停下來說明一下我們的資料來源(見下表)。這是一張BigQuery裡面的table(資料表),儲存了一些公司員工的基本資料。這個資料表裡面,涵蓋了一間公司員工的裡面,涵蓋了一間公司員工的名字,員工所屬的部門,主管的名稱,以及員工的薪水。等一下的App Script,會從這個employee table當中,進行資料的讀取。以便了解員工的薪資分布。

BigQuery table: employee(Preview)

請看以下是四個操作步驟:

步驟一:列出employee table裡的所有資料在試算表上(程式碼如下,請複製貼上到App Script的.gs檔案中)

function runQuery() {
// Replace this value with your Google Developer project number (It is really a number.   
// Don't confuse it with an alphanumeric project id)  
var projectNumber = '904092944562';  
if (projectNumber.length < 1) {      var errMsg = "You forgot to set a project number - So no BQ for you!";      Logger.log(errMsg);      
Browser.msgBox(errMsg);      return;  }    
var sheet = SpreadsheetApp.getActiveSheet();  
var sql = 'select * from dataset.table';    
var queryResults;  
// Inserts a Query Job  
try {    
catch (err) {    Logger.log(err);    
Browser.msgBox(err);    return;  }    
// Check on status of the Query Job  
while (queryResults.getJobComplete() == false) {    try {      queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryJob.getJobReference().getJobId());    }    catch (err) {      Logger.log(err);      Browser.msgBox(err);      return;    }  }    
// Update the amount of results  
var resultCount = queryResults.getTotalRows();  
var resultSchema = queryResults.getSchema();    
var resultValues = new Array(resultCount);  
var tableRows = queryResults.getRows();    
// Iterate through query results  
for (var i = 0; i < tableRows.length; i++) {    var cols = tableRows[i].getF();    
resultValues[i] = new Array(cols.length);    
// For each column, add values to the result array    
for (var j = 0; j < cols.length; j++) {      resultValues[i][j] = cols[j].getV();    }  }   
// Update the Spreadsheet with data from the resultValues array, starting from cell A1  sheet.getRange(1, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);    Browser.msgBox("Congrats! We are done with our first Query");}

我們複製一個呼叫BigQurey的程式碼到App Script當中,在程式裡面,我們只要提供GCP的專案號碼(Project Number),以及查詢的相對應SQL指令,就可以對我們的BigQuery table進行查詢。請注意database跟table的名字要改成BigQuery自己創建的dataset和table名稱,好讓程式可以到正確的位置找資料。改好程式碼後,在App script的介面按下run鍵,並且執行腳本中的runQuery()這個我們自己定義的function。

執行成功後在google試算表中會出現一個執行成功的訊息“Congrats! We are done with our first Query”,我們就可以看到試算表列出BigQuery的搜尋結果。了。

執行App Script後Google試算表上面會出現搜尋結果

步驟二:在BigQuery console新增一筆資料到BigQuery employee table

打開BigQuery控制台,在白色執行框框裡面,複製以下SQL指令後執行它。

INSERT INTO database.table VALUES ('Louie Ray','Bar','Friar Tuck',190);

我們在GCP平台使用BigQuery時,除了可以匯入許多不同資料來源的資料,我們還可以透過SQL語法來對它做操作。我在這裡先加入一筆新的員工資料,稍後當我們重新執行App Script的程式時,就會看到資料會有一筆新增的紀錄。

步驟三:重新執行一遍步驟一的腳本

刪除試算表中的資料,重複執行一次步驟一的程式。執行完這步以後,我們發現BigQuery table真的有更新一筆資料內容,我們的試算表也能看到最新最正確的結果。

步驟四:找出薪水在160以上的員工

如果有興趣的話,也可以在App Script程式中利用SQL語法來對BigQuery table做有條件的查詢,好比說,我要找到薪資在160(在原始的table中並沒有強調金額單位,在這裡可以看作年薪160萬台幣來理解。)以上的員工,那結果一樣會在試算表顯現。在這裡,試算表顯示的,就是薪資在160以上的員工姓名,及他的實際薪資。        

下方是執行程式碼:

function runQuery_condition() {  

// Replace this value with your Google Developer project number (It is really a number.   

// Don't confuse it with an alphanumeric project id)  

var projectNumber = '904092944562';  

if (projectNumber.length < 1) {      

var errMsg = "You forgot to set a project number - So no BQ for you!";      Logger.log(errMsg);      Browser.msgBox(errMsg);      return;  }    

var sheet = SpreadsheetApp.getActiveSheet();  

var sql = 'select name,salary from dataset.table where salary >160';    

var queryResults;  

// Inserts a Query Job  try {    

var queryRequest = BigQuery.newQueryRequest();    queryRequest.setQuery(sql).setTimeoutMs(100000);    queryResults = BigQuery.Jobs.query(queryRequest, projectNumber);  }  catch (err) {    Logger.log(err);    Browser.msgBox(err);    return;  }    

// Check on status of the Query Job  

while (queryResults.getJobComplete() == false) {    

try {      queryResults = BigQuery.Jobs.getQueryResults(projectNumber, queryJob.getJobReference().getJobId());    }    

catch (err) {      Logger.log(err);      Browser.msgBox(err);      

return;    }  }    

// Update the amount of results  

var resultCount = queryResults.getTotalRows();  

var resultSchema = queryResults.getSchema();    

var resultValues = new Array(resultCount);  

var tableRows = queryResults.getRows();    

// Iterate through query results  

for (var i = 0; i < tableRows.length; i++) {    

var cols = tableRows[i].getF();    

resultValues[i] = new Array(cols.length);    

// For each column, add values to the result array    

for (var j = 0; j < cols.length; j++) {      resultValues[i][j] = cols[j].getV();    }  }   

// Update the Spreadsheet with data from the resultValues array, starting from cell A1  sheet.getRange(1, 1, resultCount, tableRows[0].getF().length).setValues(resultValues);    Browser.msgBox("Successfully loading data from BigQuery.");}

最後在Google試算表中看到的結果就會如下圖所示。


Topic 2: Data Studio


在看完BigQuery這個資料倉儲的查詢方式後,你可能會好奇想說:「天阿,我除了可以查詢資料產生報表外,我能不能再Google上面產生我要的圖表呢?」答案是:沒有問題!萬能的Google擁有厲害的視覺化工具Data Studio,可以從你提供的資料來源中,產生報表或是圖表。


在資料來源的選擇上,它提供了將近兩百種選擇,你可以從GCP原生的17種工具,一百多個網路上的的知名公司,都是Google的合作夥伴(如:Facebook. Twitter),或者你也可以從本機直接上傳資料到Data Studio。


在取得了資料後,Data Studio就可以依照你的需求,產生各種不同的圖表。舉例來說,最常見的長條圖,以及圓餅圖、折線圖,或是使用內建的版型,很輕鬆地利用拖拉點放,完成一張讓公司主管一看就滿意的報表,讓整理資料變得更簡單。


以下簡單示範一下從我們BigQuery的tble employee裡面拉出資料後,在Data Studio當中透過工具呈現出簡單報表的過程。我可以透過報表了解我的員工的部門分佈比例,薪資多寡。當然,這只是個簡單的例子;如果要在貴公司裡的商業資料中取得更多的視覺化呈現,探索更多資料中的「insight」,Google Data Studio不失是一個好選擇。(更重要的是,它現在不用收費!) 

      

步驟一:打開Data Studio Console 

步驟二: 按一下Create,選擇要導入的資料來源

選擇資料來源以前,左上角有個Untitiled的字樣,我們可以先點選他,為我們的Report先命名。

選擇Google Connectors 中的BigQuery → project → database → table

在這裡我們延續剛才的範例,從BigQuery的employee table中,把我們的員工資料讀取出來。

步驟三:產生報表

待資料導入完,我們可以開始拉出一個表格,我們可以開始拉出一個表格式的資料做為暖身。以employee這個table為例:我們在Dimension,也就是主要呈現欄位的地方,選擇Name、Department以及salary作為顯示欄位,Metric是我們的比較指標,我們拿薪資來做排序,由高到低呈現出來。經過色彩微調後,表格大概會長得像這樣:

Date Studio 提供許多客製化的功能,可以透過點選,將您想要的設定即時呈現在你的圖表中。

步驟四:產生部門人數比圓餅圖

因為在employee table中,我們有一個部門(Department)欄位,所以透過圓餅圖,我們可以了解各部門的員工數量,在全公司的佔比多寡。我們先按下Add a chart按鈕,點選pie;因為在圖表中我們想呈現部門的百分比,所以Data裡面的Dimension我們選擇Department,Metric則是Record count作為我們計算人數比例的依據。        

產出圖表會長這樣:

依照部門名稱,顯示出所佔百分比

步驟五:產生薪資長條圖

這裡我們要怎麼設定呢?我們想要知道每一位員工的薪資高低排序,所以我把Dimension的地方設成Name,比較的主體Metrix,就是薪資,所以我們用Salary這個欄位當作Metrix。這個欄位的資料本來就是以整數的性質儲存,所以可以做值的比較。

產生長條圖如下:

由此可見,Data Studio會依照我們所給予的條件產生圖表,要客製化各種需求的chart,一點都不難!
最後,Google Data Studio同樣提供自動儲存功能,所以下次再回來使用介面的時候,可以依照你原先命名的report來產出報表,或是繼續編輯尚未完成的分析資料喔!
如果對今天的操作有興趣,想要進一步使用GCP雲端工具,請在Cloud Ace官網留下您寶貴的資料,我們會在最快的時間內與您聯繫,提供您最優惠的試用服務!


註釋1:BigQuery以分散式的Column Base方式儲存資料,並透過MapReduce的概念以Google背後強大的運算能力提供資料的查詢與分析能力,讓BigQuery以近似即時(Near Realtime)的速度提供使用者查詢與分析。參考連結:https://cloud.google.com/blog/products/data-analytics/cost-optimization-best-practices-for-bigquery

這張圖片的 alt 屬性值為空,它的檔案名稱為 commentus.png

Chao Joshua

為Cloud Ace解決方案架構師。熟悉GCP雲端服務,並著力於大數據分析方法及數據管道研究。提供企業雲端架構諮詢與技術支援。

Leave a Reply