整合PostgreSQL資料庫的R中文文本探勘 / Chinese Text Mining with R and PostgreSQL
R的文本探勘(text mining)大多是基於純文字檔案進行，而我將文本探勘處理的資料輸入、輸出儲存整合到PostgreSQL資料庫，讓R的文本探勘能夠更容易跟其他系統整合。這篇文本探勘中進行了HTML內文擷取、新詞加入與斷詞處理、符號過濾、英數字過濾、停用字過濾、最小詞彙長度與頻率過濾等處理步驟。以下介紹系統架構跟R Script的設定，並以我的網頁為資料來源示範如何進行文本探勘。
基本的系統架構 / Basic System Architecture
大多數的文本探勘教學，如「用R進行中文 text Mining」，都是使用檔案系統儲存純文字檔案，再利用DirSource()來讀取資料夾底下的純文字檔案，以此進行文本探勘。但檔案系統並不是管理資料的理想做法，而且這樣子的模式也很難跟其他的系統整合。因此我參考大家用R進行文本探勘的做法，將整個架構與PostgreSQL資料庫整合。
上圖是這個系統的主要架構。「使用者」是指能夠操作R跟管理資料庫的人，「R環境」可以參考我架設的RStudio Server，「資料庫」則是使用PostgreSQL資料庫。整個系統的運作流程大致上如下：
- 使用者在資料庫中新增要分析的文本。
- 使用者設定R Script。
- 使用者執行R Script。
- R Script根據設定的內容進行以下動作：
- 從資料庫載入要分析的文本資料、斷詞用的新詞、停用字
- 進行文本探勘處理
- 將處理結果儲存到資料庫
- 使用者到資料庫查看處理結果
整合式的系統架構 / Integrated System Architecture
為了方便說明，本文尚未跟其他系統進行整合，乍看之下並沒有其他系統的角色在裡面。如果要其他系統整合的話，系統架構圖中的使用者就是換成其他角色：
上圖是以PHP為例的架構。PHP可以用exec()指令來執行R Script。詳細做法可以參考Integrating PHP and R。
環境配置 / Environment Setup
在這個架構中，需要特別說明的是R環境跟資料庫的配置。
R環境配置 / R Setup
因為本文的架構是由使用者來驅動整個流程，因此R環境就需要有一個讓使用者方便運作的介面。在此使用的是RStudio Server，架設方式請看我另一篇「開箱即用的R運作環境！RStudio Server OpenVZ虛擬機器分享」的說明。
當然，你也可以使用自己的R環境。但是因為文本探勘會需要tm、tmcn、Rwordseg、XML、以及連結資料庫的RPostgreSQL等套件，請務必確認自己使用的R環境是否已經安裝了這些套件。在R安裝套件可能會遭遇很多困難，有必要的話請參考我另外一篇「R套件怎麼裝不起來？Ubuntu中舊版R安裝套件的方法」的說明。
PostgreSQL資料庫配置 / PostgreSQL Database Setup
本系統所搭配的PostgreSQL資料庫名為「text_mining」。資料庫中至少需要4種不同的資料表。建立資料庫的SQL檔案可以由此下載：
建立資料庫「text_mining」之後，可以使用psql工具匯入上面的SQL檔案。做法可以參考「PostgreSQL的備份與復原」這篇的說明。
資料庫架構說明 / Schema Description
「text_mining」資料庫裡面有4張表，以下說明定義：
資料表「doc」 / Table: doc
儲存需要探勘的文本。
- doc_id: 主鍵，也代表文件的代號
- fulltext: 要探勘的文本，純文字格式。可以儲存HTML網頁或XML格式。
資料表「new_word」 / Table: new_word
自訂斷詞使用的新詞。
- new_word_id: 主鍵
- term: 新詞
資料表「stop_word」 / Table: stop_word
自訂停用字，這些字會被過濾掉，不會出現在探勘結果中。
- stop_word_id: 主鍵
- term: 停用字
資料表「term_freq」 / Table: term_freq
探勘結果。
- term_freq_id: 主鍵
- doc_id: 文件代號，文本的外鍵，對應到表格「doc」的主鍵
- term: 探勘出來的詞彙
- speech: 詞彙對應的詞性
- freq: 在這份文件中的頻率
視表「view_term_freq_sum」 / View: view_term_freq_sum
除了四張表格之外，我還額外整理了一張視表(view)，方便輸出到文字雲之中。
- word: 詞彙
- freq: 出現頻率
正式使用的時候，我們可以擴充資料表「doc」，讓它有更多其他可以用來過濾的欄位。例如我們可以加上「title」、「author」等欄位，這樣就可以更容易比較不同類型的文本探勘結果。
跟其他系統實際整合的時候，我們可以用其他資料表來取代資料表「doc」。舉例來說，我可能想要探勘的文本是儲存在討論區資料表中，那麼我只要在下面R Script設定中調整查詢文本的SQL語法即可。
資料庫的管理 / How to Manage Database?
使用者要管理PostgreSQL資料庫的方法很多，最基本的就是使用PostgreSQL內建的pgAdmin。Windows底下安裝PostgreSQL的時候就會一併安裝pgAdmin。pgAdmin建立資料庫與資料表、查詢資料很容易，但是卻不容易修改資料，所以我還會搭配phpPgAdmin使用。
phpPgAdmin是類似phpMyAdmin的網頁版PostgreSQL資料庫管理工具。架設起來之後，只要有瀏覽器就能夠直接管理資料庫，而且也比較不會像是pgAdmin管理資料庫的時候常常遭遇PostgreSQL權限設定的問題。phpPgAdmin在Linux裡面可以輕易用指令來安裝。以Ubuntu為例，安裝phpPgAdmin的apt-get指令如下：
sudo apt-get install phppgadmin -y安裝之後就可以用網址加上phppgadmin來開啟管理介面：
當然，在這之前要先架設PostgreSQL資料庫的伺服器才行。
關於PostgreSQL跟關聯式資料庫的內容相當複雜，許多科系會以「關聯式資料庫管理」為課名開設一整個學期的課程，表示這門學問實在不是三言兩語就能夠講得清楚。如果想要學習PostgreSQL的話，可以參考PostgreSQL 8.0.0 中文文件來學習。
文本探勘R腳本的設定與執行 / Text Mining R Script
接下來是關鍵的R Script內容。基於程式寫作習慣，我偏好將設定跟執行部分拆開成兩個部分。我們主要要修改的內容在設定部分，執行部分都直接操作即可。這些腳本都可以直接輸入到R Console中執行。
設定部分的R腳本 / Configuration R Script
文本探勘有很多細節可供調整，像是是否要使用停用字、新詞，是否要從HTML文本當中抽取指定範圍的文本(通常是只找<body></body>之間的內容)，是否要清除標點符號、數字、或是英文，要找出來的詞頻最短長度以及最少頻率是多少。許多參數的設定都在這個R Script中調整。參數的說明請看# 開頭的R註解。
雖然設定大部分都用預設值即可，但是資料庫連線設定請務必修改。
文本探勘設定部分的R腳本可以在此下載：
- GitHub: text_mining.config.r
# 文本查詢，至少要查詢doc_id跟fulltext兩個欄位
sql.content <- "SELECT doc_id, fulltext FROM doc"
# 新詞查詢
sql.newwords <- "SELECT term FROM new_word"
# 停用字查詢
sql.stopwords <- "SELECT term FROM stop_word"
# 過濾器設定
library("XML") # 先載入XML處理工具
filter.xpath.enable <- TRUE # 是否使用xpath過濾，可以取出XML/HTML指定的範圍
filter.xpath <- "/html/body" # xpath
filter.xpath.type <- xmlValue # xpath類型
filter.removePunctuation <- TRUE # 是否清除標點符號
filter.removeNumbers <- TRUE # 是否清除數字
filter.removeEnglish <- FALSE # 是否清除英文
filter.speech.enable <- TRUE # 是否使用詞性過濾
filter.speech <- c("n"); # 詞性設定，n表示名詞
filter.term.min.length <- 2 # 最小詞彙長度
filter.term.min.freq <- 3 # 詞彙最少頻率
# 資料庫設定
db.host <- "192.168.56.152" # 資料庫主機位置
db.port <- 5432 # 資料庫連接埠
db.user <- "postgres" # 資料庫登入帳號
db.password <- "password" # 資料庫登入密碼
db.name <- "text_mining" # 資料庫名稱
# 輸出結果詞頻資料表設定
db.term_freq.table_name <- "term_freq" # 詞頻表格
db.term_freq.field_name.doc_id <- "doc_id" # 文件編號
db.term_freq.field_name.term <- "term" # 探勘出來的詞彙
db.term_freq.field_name.speech <- "speech" # 詞性
db.term_freq.field_name.freq <- "freq" # 頻率執行部分的R腳本 / Excuting R Script
以下R腳本通常不需要修改，列出來僅供學習R之用。文本探勘執行部分的R腳本可以在此下載：
- GitHub: text_mining.excu.r
# 引用函式
library("RPostgreSQL") # PostgreSQL資料庫連線需要的套件
library("tm") # 文本探勘工具
library("tmcn") # 文本探勘中文包
library("Rwordseg") # 中文斷詞工具
library("XML") # XML處理工具
# 資料庫連接
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv, dbname = db.name,
host = db.host, port = db.port,
user = db.user, password = db.password)
# 資料庫查詢
db.content <- dbGetQuery(con, sql.content)
# 如果有文本資料的話
if (length(colnames(db.content)) > 0) {
# 查詢其他資料
db.newwords <-dbGetQuery(con, sql.newwords)
db.stopwords <- dbGetQuery(con, sql.stopwords)
# 新詞加入斷詞器
if (length(colnames(db.newwords)) > 0) {
insertWords(db.newwords[,1])
}
# 儲存詞性
df.speech <- list()
# HTML的body抽取處理
if (filter.xpath.enable == TRUE) {
tryCatch({
html <- htmlParse(db.content[,2], encoding="utf8")
db.content[,2] <- xpathSApply(html, filter.xpath, filter.xpath.type)
})
}
# 要輸入的文本
final_df <- as.data.frame(t(db.content[,2]))
colnames(final_df)<-db.content[,1]
# 建立文本物件
R_corpus <- Corpus(VectorSource(final_df), list(language = NA))
# 清除標點符號
if (filter.removePunctuation == TRUE) {
R_corpus <- tm_map(R_corpus, removePunctuation)
}
# 清除數字
if (filter.removeNumbers == TRUE) {
R_corpus <- tm_map(R_corpus, removeNumbers)
}
# 清除大小寫英文與數字
if (filter.removeEnglish == TRUE) {
R_corpus <- tm_map(R_corpus, function(word) {
gsub("[A-Za-z]", "", word)
})
}
# 斷詞
R_corpus <- tm_map(R_corpus, segmentCN, nature = TRUE)
# 詞性過濾前的調整
if (filter.removeEnglish == FALSE) {
# 如果不過濾英文，那就關閉詞性過濾的設定
filter.speech.enable <- FALSE
}
# 詞性過濾
R_corpus <- tm_map(R_corpus, function(word) {
if (filter.speech.enable == TRUE) {
filter.words <- word[(match(names(word), filter.speech, nomatch = -1) > 0)]
df.speech[filter.words] <<- names(filter.words)
filter.words
} else {
df.speech[word] <<- names(word)
word
}
})
# 停用字設定
if (length(colnames(db.stopwords)) > 0) {
my.stopwords <- c(stopwordsCN(), stopwords("english"), db.stopwords[,1])
} else {
my.stopwords <- c(stopwordsCN(), stopwords("english"))
}
# 製作詞彙陣列
R_corpus <- Corpus(VectorSource(R_corpus))
tdm <- FALSE
tdm <- TermDocumentMatrix(R_corpus, control = list(wordLengths = c(filter.term.min.length, Inf), stopwords = my.stopwords))
if (is.object(tdm) == TRUE) {
m1 <- as.matrix(tdm) # 詞彙分佈儲存在m1裡面
# 儲存結果之前先清除該文本的詞頻資料
where_sql <- ""
for (doc_id in colnames(m1)) {
if (where_sql != "") {
where_sql <- paste0(where_sql, ' OR')
}
where_sql <- paste0(where_sql, " ",db.term_freq.field_name.doc_id," = '",doc_id, "'")
}
if (where_sql != "") {
sql.term_freq.delete.docs = paste0("DELETE FROM ",db.term_freq.table_name," WHERE ", where_sql)
dbSendQuery(con, sql.term_freq.delete.docs)
}
# 儲存詞頻結果，寫入資料庫
for (doc_id in colnames(m1)) {
for (term in rownames(m1)) {
freq = m1[term,doc_id]
if (freq > 0 && freq > filter.term.min.freq) {
sql.term_freq.insert = paste0("INSERT INTO term_freq (",db.term_freq.field_name.doc_id,",",db.term_freq.field_name.term,",",db.term_freq.field_name.speech,",",db.term_freq.field_name.freq,") VALUES ('", doc_id, "', '",term,"', '",df.speech[term],"', ",freq,")")
dbSendQuery(con, sql.term_freq.insert)
}
}
}
} # if (is.object(tdm) == TRUE) {
} # if (length(colnames(db.content)) > 0) {文本探勘展示 / Text Mining Demo
配置完成之後，接下來我們就試著做一次文本探勘的流程。以下我以本部落格中的「開箱即用的R運作環境！RStudio Server OpenVZ虛擬機器分享」和「R的文字雲怎麼都是□亂碼？wordcloud套件需要中文字形」兩篇文章的原始碼作為文本探勘的資料來源，展示整個流程如何運作。
1. 取得文本 / Get Coupus
我們的目標是抓取以下兩個網頁的原始碼：
開啟網頁之後，按滑鼠右鍵，進入「檢視網頁原始碼」。
選取並複製全文，這樣我們就可以取得這個網頁的原始碼。
2. 插入到資料表doc / Insert into "doc" table
接下來我們到doc資料表中插入新資料，將網頁原始碼貼到「fulltext」欄位中。第一個doc_id欄位是由資料庫自動填入的主鍵，我們自己不需要輸入資料。
就這樣子，成功新增兩筆資料。
我將新增這兩筆資料的SQL語法另外存成以下檔案，你可以直接匯入SQL語法來新增資料：
- GitHub: text_mining_doc_demo.sql
3. R環境中進行文本探勘 / Text Mining with R
再來到R環境中，執行R Script。順利的話，就能不出現任何錯誤訊息地運作結束。
4. 查詢文本探勘結果 / Check Text Mining Result
探勘結果會儲存在資料表「term_freq」中，你可以檢查各文件所使用的詞彙以及出現的頻率。
如果要看全部詞彙的頻率，可以查看視表「view_term_freq_sum」。這裡面出現最多的是「布丁」，次數是70次，其次是英文字「name」跟「false」。
5. 調整文本探勘的設定 / Change Configuration
上面探勘的結果中，可以發現有大量英文字混雜其中。如果想要移除英文字，可以將R Script設定部分的filter.removeEnglish設為TRUE (注意，大小寫有區分)：
filter.removeEnglish <- TRUE # 是否清除英文另一方面，有些詞彙看起來意義不明，例如「name」或「什麼」。如果想要將這個詞彙排除在探勘結果之外，那麼我們可以把這些詞彙加入資料表「stop_word」之中：
這樣子這些詞彙就會在下次文本探勘的時候自動被過濾掉了。
結語 / Conclusion
有人可能會問說，怎麼這個文本探勘最後沒有畫文字雲(word cloud)？
這是因為詞頻的計算就已經算是完成我的任務了，所以這篇就只做到儲存詞頻結果。光是做到這樣，我就能用SQL來查詢出現頻率最高的詞彙，也可以比較不同類型文本之間使用詞彙的差異。比起畫成文字雲，做成簡單的表格來比較還更為實用。
接下來可以用這個詞頻結果來做很多事情，除了可以畫文字雲之外，也可以根據用詞的不同來做文本分群。但是這些工作是可以分開介紹的，只要資料存在資料庫裡面，要做甚麼分享都不難。
不過，如果要做全文檢索的話，可以進一步改用PostgreSQL內建的全文檢索功能來處理，詳細可以看我之前所撰寫的「以PHP與PostgreSQL實作簡易中文全文檢索功能—概念說明篇」。
另外也可能會有人問到，為什麼一開始文本的蒐集要靠手動輸入，而不是自動上網抓取？感覺手動步驟太多，好像不太厲害。
這是因為我認為文本探勘的文本來源取得方式各異，比較常見的情況都應該是拆開來處理，不一定是要用R取得文本。舉例來說，我可能是用DSpace架設的數位典藏作為文本來源，也可能是分析PHP架設的留言板內容，我甚至可以用WinHTTrack這個圖形化工具就可以下載整個網站，實在是不用堅持一定要用R來做網路爬蟲。
甚至我認為比起R來說，關聯式資料庫的管理跟資料處理應該是更多人熟悉的領域。所以R就做好你的文本探勘資料處理，資料的儲存就交給關聯式資料庫吧。
PostgreSQL不適合進行文本探勘 / Don’t Do Text Mining in PostgreSQL
最後來講這篇準備過程中的學到一個教訓：不要在資料庫中做文本探勘。
這篇R Script文本探勘處理手續中有幾個過濾器：
- filter.xpath：從XML或從HTML中抽取部分文本的功能
- filter.removePunctuation：清除標點符號
- filter.removeNumbers：清除數字
- filter.removeEnglish：清除英文
後三個功能可以簡單地用正規表達式搭配PostgreSQL的regexp_replace()功能來處理。但是第一個功能卻意外地很困難。PostgreSQL有XML功能，但這只能處理正規的XML文件，意外地其實不能處理大部分的HTTP網頁。雖然我們會以為HTML網頁也是使用標籤，可能可以跟HTML相容，但實際上兩者之間還是有不小的差距。
如果在PostgreSQL裡面將普通的HTML當做XML處理，可能遭遇到的問題包括：
- 不能處理文件類型宣告：就是網頁原始碼第一行
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> - 不能處理<html>標籤中的xmlns名稱空間，有這項屬性就會出錯
- 不能處理標籤屬性中使用單引號「'」
- 不能處理HTML的註解格式 <!-- -->
- 不能處理HTML特殊字元，例如空白「 」
上述需要處理的情況實在是太多了，一開始我試著寫一個超級複雜的正規表達式，希望用來刪除上面這些可能造成網頁轉換XML失敗的危險因子，但是一來正規表達式相當複雜、需要考量的問題太多，套用在各種複雜的網頁上可能會有很多出乎意料之外的結果，二來最關鍵的一點就是，在PostgreSQL上跑正規表達式實在是太慢了。
因為研究正規表達式一直不是很順利，我就試著換到R環境之中，使用XML套件的htmlParse()功能來處理網頁，沒想到處理速度快得天差地遠，完全打消我在PostgreSQL上做文本探勘的念頭。
就這樣的，我最後還是選擇在R Script進行文本過濾。而且我把過濾器的設定額外拆開寫在設定裡面，這樣子就可以依照需求動態啟動或取消各項過濾了。
最後想想，各種工具各有所長，PostgreSQL還是乖乖儲存資料就好，如果要進行文本探勘的話，還是用像R這種專門的工具吧。(剛好跟前面的感想相反XD)
您好：
回覆刪除拜讀執行後，會產生錯誤訊息如下，煩請撥冗賜教，謝謝！
Error in FUN(X[[i]], ...) : Please input character!
Show Traceback 呈現：
6.
stop("Please input character!")
5.
FUN(X[[i]], ...)
4.
lapply(X, FUN, ...)
3.
mclapply(content(x), FUN, ...)
2.
tm_map.VCorpus(R_corpus, segmentCN, nature = TRUE)
1.
tm_map(R_corpus, segmentCN, nature = TRUE)
To St W,
刪除這是因為新版R的問題。新版的R連帶影響到新版的tm跟segmentCN套件，全部都不能用這篇文章的R Script。
可以看我在另一篇最後的討論：
http://blog.pulipuli.info/2016/11/r-draw-word-cloud-in-r.html#postcatar-draw-word-cloud-in-r.html0_anchor5
這篇文章的R是用3.0.2版，請試著去找這一版的R來安裝，然後再努力克服套件安裝的問題。
這裡下載Windows的R 3.0.2版，https://mirrors.tuna.tsinghua.edu.cn/CRAN/bin/windows/base/old/3.0.2/
但是Windows的R會有中文亂碼的問題，無法完全克服，我建議改用Linux
舊版套件安裝的問題請看這一篇：http://blog.pulipuli.info/2016/11/rubuntur-how-to-install-archived.html
你也可以架設虛擬機器RStudio Server來做這件事情，我保證絕對可以正常運作
http://blog.pulipuli.info/2016/11/rrstudio-server-openvz-standalone-r.html
好的，
刪除我再試看看，
感謝您熱心回覆！
To St W,
刪除不客氣，祝你能夠順利進行文本探勘！