文章段落
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當中，進行資料的讀取。以便了解員工的薪資分布。
請看以下是四個操作步驟：
步驟一：列出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的搜尋結果。了。
步驟二：在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（現 Looker Studio）
在看完BigQuery這個資料倉儲的查詢方式後，你可能會好奇想說：「天阿，我除了可以查詢資料產生報表外，我能不能再Google上面產生我要的圖表呢？」答案是：沒有問題！萬能的Google擁有厲害的視覺化工具 Looker Studio，可以從你提供的資料來源中，產生報表或是圖表。
在資料來源的選擇上，它提供了將近兩百種選擇，你可以從GCP原生的17種工具，一百多個網路上的的知名公司，都是Google的合作夥伴（如：Facebook. Twitter），或者你也可以從本機直接上傳資料到 Looker Studio。
在取得了資料後，Looker Studio 就可以依照你的需求，產生各種不同的圖表。舉例來說，最常見的長條圖，以及圓餅圖、折線圖，或是使用內建的版型，很輕鬆地利用拖拉點放，完成一張讓公司主管一看就滿意的報表，讓整理資料變得更簡單。
以下簡單示範一下從我們BigQuery的tble employee裡面拉出資料後，在 Looker Studio 當中透過工具呈現出簡單報表的過程。我可以透過報表了解我的員工的部門分佈比例，薪資多寡。當然，這只是個簡單的例子；如果要在貴公司裡的商業資料中取得更多的視覺化呈現，探索更多資料中的「insight」，Looker Studio 不失是一個好選擇。（更重要的是，它現在不用收費！）
步驟一：打開 Looker 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。這個欄位的資料本來就是以整數的性質儲存，所以可以做值的比較。
產生長條圖如下：
由此可見，Looker Studio 會依照我們所給予的條件產生圖表，要客製化各種需求的chart，一點都不難！最後，Looker Studio 同樣提供自動儲存功能，所以下次再回來使用介面的時候，可以依照你原先命名的report來產出報表，或是繼續編輯尚未完成的分析資料喔！
如果對今天的操作有興趣，想要進一步使用GCP雲端工具，請在Cloud Ace官網留下您寶貴的資料，我們會在最快的時間內與您聯繫，提供您最優惠的試用服務！
以上是 BigQuery 串接 Google sheet 及 Looker Studio 產出視覺化圖表的完整教學，如果想進一步了解這次介紹的內容或 GCP 其他服務都歡迎聯絡我們獲得更進一步的資訊，我們會在最快的時間內與您聯繫。
註釋1：BigQuery以分散式的Column Base方式儲存資料，並透過MapReduce的概念以Google背後強大的運算能力提供資料的查詢與分析能力，讓BigQuery以近似即時(Near Realtime)的速度提供使用者查詢與分析。參考連結：https://cloud.google.com/blog/products/data-analytics/cost-optimization-best-practices-for-bigquery
