使用Datalab及BigQuery進行大數據分析[Part 2]

利用Storage及BigQuery，在Datalab中結合標準SQL及Python預測KKBOX使用者訂閱流失機率

YL-Tsai

24 min readSep 25, 2018

GCP的三項產品，BigQuery, Storage, 以及Datalab，圖片來源 https://pse.is/AHBLZ。

在系列文的Part1中，我們成功了建置了一個雲端的Jupyter notebook環境，事實上在datalab的指令之下，我們串聯了GOP上多項獨立的產品包含:

一台google機房的虛擬機器(VM)，稱為Compute Engine，帶有開機硬碟(20G)及永久磁碟(200GB)。
一個Storage中的Bucket(備份資料)。

Storage是什麼?

大致上來說，是外接式硬碟的概念，根據官方說明，是有經過穩定性優化，以及讀取速率的優化的產品，除了可以當作一般儲存區，還有各式各樣的優勢，想瞭解可以參考Google Storage。

目前來說，就暫時把它當作外接硬碟，接著我們還需要匯入到BigQuery。

BigQuery又是?

大致上來說，是一個優化過的資料庫系統，我們可以透過ANSI-2011標準資料庫語法，進行初步的聚合並輸出結果，詳情也可以參考Google BigQuery，寫得非常清楚。

我們不能直接把Compute Engine的規格提高然後分析大數據就好?

當然可以，但是升級到最大能夠有多少RAM?

GCP的Compute Engine提供的最大記憶體規格是8CPU，52 GB RAM。在本文示範資料集上是行得通的。

但是，萬一之後你需要分析TB及PB等級的資料呢?

到了那種情況當然就會有各種解決方案，例如分散式儲存及分散式計算等等，而BigQuery也是其中一種解決方案。所以對於學習SQL我們可以這樣想:

Python使我們可以很簡潔的分析數據，各式各樣的EDA及訓練模型，SQL對應到的資料庫技術則可以擴展我們可處理的數據規模，到達GB,TB及PB等級，事實上也是這樣的，因此，本篇分析文章適合:

在資料科學領域已經大概掌握了Python，想要學習新的語言以拓展自己可以處理的數據規模之讀者。
想要了解在雲端上如何處理大量資料分析走馬看花的讀者。

若次初次接觸SQL的讀者，建議可以花一些時間在學習基本的SQL語法，而我是Kaggle Learn — SQL課程開始。當初筆者大約在這上面花1個禮拜的時間，就開始著手進入專案，SQL語法基本上要入門並不會門檻很高。

所以，整個架構是這樣的:

開始下載及上傳資料

從Storage上傳到BigQuery，接著用Datalab直接分析，在Docker容器內已經幫我們把各種API接好了，同時，整個GCP的主控台也都是中文的UI，這讓我們有極大的操作方便，所以在執行以下步驟時，使用網頁的圖形化介面點一點即可，筆者相信這個部分難不倒各位讀者，如果真的有困難，你也可以留言，或是寄信到我的信箱(yltsai0609@gmail.com)討論，以下是筆者的操作流程:

從Kaggle上的KKBox’s Churn Prediction Challenge使用Kaggle API下載資料集到你的筆電上(本地端)。
從網友InfiniteWing重新標註的資料集下載訓練集(註1)。
將檔案上傳到storage。
進入BigQuery，並進入舊版UI(註2)，在BigQuery中建立一個資料集，建立資料表，依序將資料從storage上傳至BigQuery(註3)。

註 1 : 當時這項比賽在比賽期間，資料非常的原始，不少Kaggler依照主辦方的定義自己全新標註訓練資料集，得到乾淨度較高的資料，如今比賽已經結束，我們跳過重新標註資料的部分。註 2 : 筆者自己在2個月前分析時，還沒有新版的UI，中間過程有使用過新版UI上船資料集，但是當時Schema的部分有時候讀取不正確，如果各位讀者使用新版UI沒有問題，則可以忽略，不過筆者建議轉為舊版UI。註 3 : 選擇從storage上傳時，舊版UI會需要填入路徑，storage的路徑可以在storage --> 點選資料夾 --> 點選總覽 --> gsutil 連結。
例如我的就是gs://kk_data。

路徑的部分:gs://kk_data/members_v3.csv 意思為google storage://bucket_name/dataname ，這個部分是不是像極了在本地端硬碟中資料位置呢? 筆者我是覺得頗直覺的。
目標資料表則是幫你上傳的資料取個名子，BigQuery的結構為資料集(Dataset) --> 資料表(Table) --> 資料表綱要(Schema) --> 每一列之數值(字串)(Values) ，勾選自動偵測即可(automatcially detect)，接著按下藍色的建立資料表(Create Table)。
然後依序類推上傳sample_submission_v2，transactions，transactions_v2，user_label_201713，userlogs，userlogs_v2，總和7張資料表。

完成之後會長成下面這個樣子:

開啟Datalab整理資料表

從CloudShell連線進入Datalab，並開啟notebook，如果忘記怎麼做的讀者可以回到上篇文章複習一下。

%bq tables list

%bq 是datalab中的bigquery magic commend，我們不用import bigquery的module就可以使用6, 7成的功能，我個人是覺得蠻夠用的，甚至是dryrun的部分，正規的模組支援的也沒有magic commend順手，所以我還蠻常使用的，你也可以使用 %bq -h 來查看其他功能。

我們也看到了我們要合併的資料表，分別是user_log, user_log_v2以及transactions, transactions_v2。

%bq tables view -n kkbox-210108.DATASET.user_logs

使用 %bq table view -n table_name 當作pandas當中的 df.head() 來使用，使用view是不會花費到查詢費用的，bigquery的使用中，每個月的前1TB查詢是免費的，超過則會收費，但是比起Compute Engine，是蠻便宜的，資費可以查看官方文件以量計價的部分。

對於這樣一個28GB的大檔案，我們終於可以透過bigquery輕鬆的處理，看到各個欄位的名稱之後，寫一個query來進行插入:

寫好query之後進行dryrun:

%bq dryrun -q Concat

這則是告訴我們，這個query會掃描2GB，結果會存在雲端，不會動用到我們虛擬機器中的佔存，如同我們剛剛所說的，BigQuery每個月的前1TB免費，雖然我們的資料比起TB小多了，但這個習慣在資料量越來越大時就越來越重要。

執行query:

%bq execute -q Concat

觀察列的數目，從392,106,543變成410,502,905，這意味著我們插入成功了。

輸入%bq table view -n table_name 來確認

%bq tables view -n kkbox-210108.DATASET.user_logs

完成之後把user_logs_v2這張表刪除。

將資料表列出來確認成功刪除:

%bq tables list

我們就完成了資料表的串接，接下來transactions及transations_v2就是完全一模一樣的過程，讀者可以自己操作一次，這邊直接把程式碼都放在一起:

開始分析

這裡筆者換了一個資料集，名為kk_Data，但是和剛剛操作的範例是完全一樣的，剛剛的DATASET資料集只是為了重頭示範一次。

本文將會使用BigQuery以及Python分析KKBOX的顧客流失預測挑戰賽(WSDM — KKBox’s Churn Prediction Challenge)，做一些探索性分析以及訓練預測模型，我們開始吧!

開一個新的notebook，先看一下目標變量

%bq tables view -n kkbox-210108.kk_Data.user_label_201703

我們可以看到總共有886,500 (886k rows)的使用者，經過匿名處理，目標變量is_churn意義為是否流失，根據官方的描述，若為3月流失，定義為，在3/1~3/31到期的使用者，並且在+30天後沒有訂閱行為，則會被判定為流失，即4/1~4/30若沒有交易資料，則會被系統標記 is_churn = 1。而測試集則要順推一個月。

檢查是否有NULL/NA 及重複的資料，以及is_churn的分位數:

%bq dryrun -q check%bq execute -q check

dryrun告訴我們將會掃描47MB，很OK，是個很小的資料，事實上直接用pnadas也不成問題，這邊作為一個使用SQL語法上的練習。

沒有null，皆為獨立使用者，流失的使用者非常少，少於25%。

接著我們來看一下流失比例，首先載入python的所需套件:

首先datalab中的套件和本地端有些不同，目前google所提供的datalab中的各種套件，有些不是最新的，這有時候會讓我們使用上有一些不舒服，不過和BigQuery配合可以處理大規模的資料，也只好祈禱趕快更新了，根據筆者的經驗，比較有差別的是scipy以及seaborn，這個部分讀者需要注意。

此外datalab的上方工具列最右邊有一個Kernel，我們可以轉成python3(註1)，使用上會比較順暢，如果習慣python2.7的朋友，就使用預設即可，這邊使用python3。

註 1 : 目前18/09/25，datalab中的python3版本為python3.5，而時下最新的版本為   3.7。

這裡筆者從Stackoverflow上找到了這個square scale的自定義涵數，這會讓我們畫的圖更漂亮一些，在有一些skew程度的資料很好用，skew更大的話我們則直接使用log scale:

資料來源 : https://stackoverflow.com/questions/42277989/square-root-scale-using-matplotlib-python

接著將初步聚合的資料讀取到pandas中:

pandas會告訴我們執行時間，跑了多少容量，以及價格，最後加上傳輸資料的時間等細節資訊。而這裡必須將Kernel轉成python3，不然會有沒有抓取到bigquery magic command的錯誤訊息。

接著就可以回到pandas中使用熟悉的工具做EDA:

流失與否的計數統計，X軸為流失與否，y軸則為累積數量，使用squareroot scale，並計算確切的百分比。

這裡在y_scale使用了squareroot，我們可以在資料有不小傾斜程度時好好觀察資料，約有4.9%的使用者在3月流失，是沒有流失的1/19倍:

好消息來說，kkbox的使用者並沒有非常大比例的流失
壞消息來說，我們將要處理一個不平衡的資料集，我們可能需要對資料集調整訓練時的類別權重，或是過濾掉一些資料。

接著來看看我們的大怪獸user_logs:

%bq tables view -n kkbox-210108.kk_Data.user_logs

資料工程方面，這個table有41,052,905(41 millions rows)，在技術上仍然可以透過調整資料型態讀進pandas，而這裡採用BigQuery進行掃描來初步聚合。

首先我們可以看到使用者名稱是重複出現的，顯然是紀錄了當天的聽歌行為，而主辦方已經初步聚合了當天的聽歌的情況，分成聽歌百分比，25%, 50%, 75%, 98.5%, 100%, 以及每天聽了幾首不同的歌曲，以及總聽歌秒數。

是否重複我們已經從view當中得知，所以我們測試是否有null，以及透過分位數來了解資料大致分布:

%bq dryrun -q check
%bq execute -q check

dryrun告訴我們會掃描26GB的資料，距離1TB，還算是夠用的，不用擔心。

和原始資料表對照，沒有Null值。

接著我們看一下簡單的資料分布，如同pandas中的descirbe，如同那句格言，資料越大，困擾越大，光是寫query就可以寫一長串......:

%bq dryrun -q check
%bq execute -q check

為了方便閱讀，我們用將結果轉成dataframe並做一些排序處理:

這個步驟有幾個點要注意的地方:

資料工程方面

上一個方法中我們使用pandas中的函數read_gbq，這裡則是用bigquery的magic command直接得到dataframe，兩種方法都是可行的，但讀者目前偏好第2種，原因在於，整個流程包含dryrun時，方法二的程式碼更簡潔。
bigquery轉成dataframe時似乎有時候會把排序打亂，兩個方法處理這個問題，資料少的時候可以先跑%bq 確認，再轉成dataframe時在確認是否有亂掉，方法二則是先給定行/列名稱。這裡採用方法一。

結果分析方面

我們從total_secs的結果可以看到有異常值的出現，這顯示了如果我們需要使用到這個特徵需要進行資料清理。而是否要清理資料則是需要評估我們是否會用到這些特徵，如果確認不會用到，就省了清理的功夫，畢竟資料清理真的是很麻煩但是又必要的事。
使用者聽歌25%, 50%, 75%的比例不高，中位數非別為1, 2, 0，我們可以推測其卡歌率不高，某個程度上有可能是使用者們滿意自己所聽的音樂。
此資料集據官方描述是以一天為一筆，我們可以粗估使用者每天大約會聽17+2~19首歌。
這裡的最大值都是值得懷疑的，官方文件寫說user_logs為每日紀錄，我們觀察num_100這一列，一首歌4分鐘也就是240秒，平均來說一天(86400)最多只有可能聽到360首完整撥放的歌曲，因此要取用這些資料，勢必要做清理及過濾。
關於異常值，事實上由於處理手續的繁複，有時候我們也會觀察異常值的比例，如果比例不高，這意味著有意義的資料還是佔了大多數，有時候在時間成本的考量下，也有可能直接當作noise處理。

特徵抽取

有了初步對資料集的認識，我們來思考即將流失的顧客可能有怎樣的行為，並抽取出特徵:

針對每一個使用者，計算其過去六個月以來聽歌100%的歌曲數 / 總聽歌曲數，並假設異常值比例不高，當作noise，將特徵命名為使用者潛在滿意度(user_latent_satisfaction)。
針對每一個使用者，計算其過去六個月以來總共聽歌的天數，一條紀錄計算為一條，將特徵命名為聽歌天數(day_listen)。

從BigQuery抽取出特徵 — 使用者潛在滿意度(user_latent_satisfaction)

%bq dryrun -q query
%bq extract -f csv -H -p 'gs://kk_data/ft_satisfaction.csv' -q query --verbose

dry run顯示掃描38GB，並將其抽取到Storage中。

%gcs read --object gs://kk_data/ft_satisfaction.csv -v df_ft_satis

使用magic command將sotrage中的檔案讀取出來，下面透過StringIO讀進pandas。

df_ft_satis = pd.read_csv(StringIO(df_ft_satis))
df_ft_satis.info()

這裡筆者使用了CTE的概念，在SQL Hunting day4中有提到， Common table expression，只在寫query當時產生，隨後消失，類似暫存的概念，而這樣寫query使得我們的query可讀性增加。
關於資料存取與轉換，起初使用了read_gbq，和bigquery中to_dataframe兩種，關於這兩種的優劣上面已經比較過，然而這兩種在速度的表現上不太好，有時候等太久，於是筆者研究了一下，一個較快的方式但是較為麻煩就是先存到storage，在讀進pandas，上面實作了這一點。
補充上一點，筆者在此資料集實測時，最慢的為使用bigqueryAPI，約80s，最快的為存到storage在讀進pandas，約20s，速度為4倍。

在此資料及中，使用方法及大約所需時間:  1) BgiQueryAPI (BigQuery magic command) ~80s
  2) pandas read_gbq ~ 55s
  3) storage first, then read to pandas ~ 20s

有興趣的讀者可以看筆者在github中notebook的實測，這裡不列出來。

接下來抽取聽歌天數(day_listen)。

%bq dryrun -q query
%bq extract -f csv -H -p 'gs://kk_data/ft_day_listen.csv' -q query --verbose

從storage中讀取進來

%gcs read --object gs://kk_data/ft_day_listen.csv -v df_ft_day_listen

使用StringIO讀進pandas，並將兩個特徵透過匿名使用者名稱(msno)拼起來。

df_ft_day_listen = pd.read_csv(StringIO(df_ft_day_listen))
df_train = pd.merge(df_ft_day_listen, df_ft_satis, how='left',on=['msno'])

接下來對測試集作完全一樣的事情，但由於我們是抽取過去六個月內，因此日期需要改成:

WHERE date > 20161001 AND date < 20170331

其餘的部分，只要將JOIN的表換成submission，接著dataframe命名多加一個sub，這裡作者就不重複(但是你可以在最底下gist找到完整的程式碼。)

抽取完成之後我們就可以用python中的pandas及sklearn進行建模了，各位讀者應該也感覺到了資料大時，整個運轉會變得需要注意很多工程問題，儲存問題，效能問題等等，這是一位資料科學家習得能夠分析GB，TB，甚至PB等級所需要了解的。

探索性分析(Exploratory Data Analysis)

按照是否流失(is_churn)為一個維度，個別觀察分布情況以及聽歌天數(day_listen)以及使用者潛在滿意度(user_latent_satisfaction)兩變數散佈圖。

聽歌天數(day_listen)以及使用者潛在滿意度(user_latent_satisfaction)以及是否流失(is_churn)的皮爾森相關係數圖。

從聽歌天數的分布我們可以看到，過去六個月以來聽歌天數較少的，流失機率較高，符合我們的直覺猜測。
從使用者潛在滿意度可以看到，流失的潛在滿意度在統計上確實是比較低，但幅度沒有很大(兩條曲線平移的程度沒有差很多)，潛在滿意度為0時，流失的機率則比較小，這一點是令人匪夷所思的，如果聽完整歌曲的比例為零，預期應該是常常卡歌，流失率較高，此時有幾個處理程序:

1) 這個情況的比例高不高? 從分布上看起來是不高的，所以影響的流失的層面較小2) 評估資料抽取出來的資料工程難度，衡量投資的時間及最後可能達成的效益比例為何3) 將潛在滿意度很靠近0的資料抽取出來看，觀察使用者行為，並做更多的推測4) 在驗證集上做實驗，嘗試各種推測使否讓loss降低

從散佈圖可以看出來和相關係數圖來看，聽歌天數和使用者潛是相關的，我們可以看到聽歌天數越高時，散佈圖越藍色，表示了滿意度在統計上是上升的。
而兩個特徵和是否流失的相關係數很低，這是因為noise很大，我們可以將特徵切分為聽歌天數等級(例如六個月內，聽得很少，聽得多，聽得很多)，來降低noise並進一步萃取特徵，然而目標的預測為log_loss而非binary classification，下面將會比較有將特徵切分以及原始特徵的驗證結果。

缺失值(Missing Values)

關於缺失值有幾項討論:

由於我們資料的抽取時間為20160901–20170228，對於3月流失的客戶而言，這樣的抽取方式無法擷取到3月的行為，這是需要注意解釋性的部分。
如果只抽取一個月，缺失值比例會比較高，依序兩個月，三個月，缺失值比例會遞減，這對於我們機器學習模型來說，抽取越多個月表示資料將會越完整，但是，太久遠的資料對於目標函數(3月是否成為流失顧客)，的參考價值也會降低，因此這裡有兩個效應拉扯，筆者使用驗證集來測試抽取時間的參數應該幾個月比較好，最終測試得到的結果是抽取6個月的數據。

填補策略

常見的方法有中位數，平均數，及眾數，利用其他特徵做統計推測，利用其他特徵做Regression等。手續繁複程度依序遞增。筆者自己嘗試了一下，填入中位數會引起很大的bias，將會影響模型預測，然而，在找到適合的其他特徵進行推測之前，先放著，代價同樣的是對於目標分布是有bias。而這裡筆者採取這個做法:

先全部填入 -1 ，當作一個特徵類別，在有些時候效果還算不錯。本篇文章中採取這個做法。

聽歌天數(day_listen)

在剛剛第一輪EDA當中我們決定切分成幾個等級來降低noise:

從此特徵工程中我們可以看出:

聽歌天數(day_listen)越多，則流失的機率越低，其效應是單調性的。
缺失值全部分成一類，出乎意料的有著更低的流失率，在找到填補缺失值的好方法之前，將所有缺失值列為一個類別有可能可以幫助預測，需要透過驗證集來做實驗。
5個類別(包含缺失值)，的數量佔比最少都有17%以上，這使得流失率的準確度是可以相信的。

模型訓練及驗證

將所需要的模型(隨機森林以及XGBoost準備好)，並列出目前有的特徵:

選取特徵，分別區分聽歌天數(day_listen_bins)以及原始特徵(day_listen)，並建立隨機森林模型訓練函數:

day_lis_bins = ['msno','day_listen_level','is_churn']
day_lis = ['msno','day_listen','is_churn']

這裡將資料切20%出來做為驗證集，而在參數調整上並沒有最佳化，只設定了300棵樹以及切分條件為5%，僅僅為了防止overfitting，由於流失的樣本數很少，將其權重設定較高。

接著開始訓練:

將結果存成csv，下載下來，從本地端提交到Kaggle:

觀察兩個模型預測的機率，驗證集分數(Val_score)，以及提交分數(Public_score):

原始特徵的log_loss略小於有切分過的特徵，但是差異非常小，為0.00024。
有切分過的特徵的預測可以從綠色線看到，會是特定的數值，和藍色的細緻度相比，較為簡單，我們必須判斷目前的情況是underfitting還是overfitting來選擇，筆者認為目前的情況是underfitting的，等級切分雖然過濾掉noise但也過濾掉有價值的資訊，因此筆者選擇原始特徵。

使用者潛在滿意度

在畫一次分布圖，用 ax.axhline 確認流失與否的分布峰值位置:

峰值位置經過確認之後僅僅差了0.03，未流失的峰值位置為0.75，流失的峰值位置為0.73，這很有可能僅僅只是noise，接著我們丟到模型裡做實驗，一樣地我們使用隨機森林，訓練完畢後存到storage並提交到Kaggle。

print('val_log_loss:', np.around(satis_val_score,decimals=6))
print('pub_log_loss:', 0.15693)

比起聽歌天數的log_loss(0.15707)，確實下降了一些，這讓我們做這樣的推測，使用者潛在滿意度(user_latent_satisfaction)確實對是否流失有影響，但就我們目前的定義(100%歌曲數 / 總歌曲數)所達成的效應很小，數量級在0.001，如果有其他更好評斷使用者滿意度的資料在這個部分可以做得更好。

其餘特徵

在此資料集的所有資料表中，依序還有相當多的特徵，讀者應該也發現了，寫SQL-query，儲存到storage，在讀取，整個過程手續是繁複的，因此在硬體資源有限的情況下，處理大數據的另一個方法則是了解資料型態，並對儲存空間以及計算方式做優化。

筆者將會把這些內容包含在下一篇文章中，並且詳細的紀錄下特徵工程的步驟，讓我們的顧客流失預測模型達到當時比賽的Top5%!

完整程式碼

GitHub

後記

從鐵達尼號資料集跳到KKBOX顧客流失預測的資料集後，資料工程難度大幅上升，同時，髒資料也越來越多，這時候發現，很多分析是無法盡善盡美的，資料集永遠都有新的不乾淨的問題，以及問不完的分析問題，這時候筆者採取的作法則是一一評估每個問題的影響程度，如果影響較小，就當作noise，而對於分析問題，選擇那些對我們要達成的目標(這裡為預測流失)可能有較大影響力的分析開始處理，畢竟在現實生活中，需要交差的結果以及業務目標，或是顧客所需，都會有時間壓力，而技術也是有限的，因此，挑選影響力最大的項目以及估算髒資料的影響力就變成重要的事情，希望讀者也從這篇分析裡學到想要學的，同時，筆者的Medium上也會繼續記錄各種我嘗試過Machine Learning的side project!