不繫之舟

首頁
其它

小試 R | Batch call API through DB query and export to csv

目前導入的 SFDC App 提供了 Fuzzy Search 的功能，讓 user 在新增客戶前可以輸入部份條件，查找是否有類似資料，以避免重覆新增。

只不過此功能非硬性規定，user 也可bypass 不用，因此小主管想知道最近新增的客戶是否真的都是全新的？亦或是 user 略過沒檢查？

想法：將最近新增的客戶資料扔進 Fuzzy Search API 再查找一次，扣掉自己這一筆，若還找得到其它分數相近者 (分數越高代表越相似)，則代表 user 可能在新增客戶前沒有使用查找功能，有待進一步改善。

作法：一開始有點趕，就先手工業把新增資料撈出後，再用 jmeter 設好後 batch call API。回來的結果又得再手工業處理。為了 save me from sweat，外加現在實在不太想裝肥肥的 .net 工具，試看看用 R 能否將整個 process 自動化。

程式概念如下

Step 1 : 從 DB 撈出最近新增的客戶，包含 ID、Name、Country 等，做成一個 View

Step 2 : 讓 View 的 ID 成為 call API 的 input，這邊要 batch call 處理

Step 3 : 每一個 query 可能會回傳零到多筆結果 (找不到相似的 or 找到多筆)，把這個結果存成 csv 供 user 參考

概念挺簡單的，但因為遠離 R 許久，對各種 object 的玩弄法早已忘光光，還是 try 了好一陣子，這邊列幾個學到的用法

設定 SQL Server 連線，把 sql query 結果吃到 R object

# Set SQL Connection
library(RODBC)
myconn <-odbcConnect("DB_SERV_NAME", uid="user1", pwd="iampwd")
qstr = "select ID, ACCOUNT_NAME, BILLINGCOUNTRY, CREATEDBY, CREATEDDATE from VW_ACCOUNT_DATA"
qresult <- sqlQuery(myconn, qstr)
# close connection to release resource
close(myconn)

把 string 黏起來

paste，中間不要留空白要加 sep=""

# goal url = http://dev-server.myteam.com/api/FuzzySearchAccount?Country=FranceName=XYZ SchoolAddr=1127 Hartford Road
api_serv = "http://dev-server.myteam.com/"
api_path = "api/FuzzySearchAccount?"
api_param1 = "Country="
api_param2 = "&Name="
api_param3 = "&Addr="
api_url = paste(api_serv, api_path,
                api_param1,qresult$COUNTRY,
                api_param2,qresult$NAME,
                api_param3,qresult$ADDR,
                sep="")

# append api_url to original query set    
a = cbind(qresult, api_url)

上面這段出來的 a 長這個樣子，等於把前面 view 的結果再黏上一欄

把 API 回傳的 json 全部黏在一起

這邊花最久的時間，後來靠這篇的方法才順利 Combining pages of JSON data with jsonlite ↗。裡面的 Automatically combining many pages
Sample code 可參考一下

#store all pages in a list first
baseurl <- "https://projects.propublica.org/nonprofits/api/v1/search.json?order=revenue&sort_order=desc"
pages <- list()
for(i in 0:20){
  mydata <- fromJSON(paste0(baseurl, "&page=", i))
  message("Retrieving page ", i)
  pages[[i+1]] <- mydata$filings
}

#combine all into one
filings <- rbind.pages(pages)

#check output
nrow(filings)

Error Handling

error handling 也很重要，一開始沒注意 URL decode，而導致 API call fail 讓 for-loop 停止，參考Skipping error in for-loop ↗的寫法跳過

 tryCatch({
    pages[[i+1]] <- mydata$Results
    }
    , error=function(e){cat("Call FuzzySearch Error :", URLencode(accnt,reserved=0) , "\n")
    }
   )

網址 encode

mydata <- fromJSON(URLencode(accnt,reserved=0), flatten = TRUE)

資料匯成 xlsx

直覺的一行指令

library(xlsx)
write.xlsx(Results, paste(proc_path,"\\", batch_no,".xlsx", sep=""))

至此大功告成，只要執行 R Script，就可以把上圖抓資料→Query→存成 XLS 的流程一指搞定

後記

R 語法不難，比較傷腦筋的是要很清楚各種 object 的結構與語法、彼此之間的轉換，還有跟其它 data type (如 json, xml, sql )的相互應用。這需要做大量的練習才會記得。附上過程中發現的好文，就是針對上述應用的練習。

15 Easy Solutions To Your Data Frame Problems In R ↗
This R Data Import Tutorial Is Everything You Need ↗