使用 Elasticsearch 為 SQL Server 完成專案的純文字搜尋功能(一)- 安裝 ElasticSearch

Ryan Chen
10 min readMar 4, 2019

--

在實作專案時很常遇到客戶提的一個看似簡單但又非常麻煩的功能,就是在網站的關鍵字搜尋引擎

文字搜尋在 SQL Server 中可用 Like全文檢索等方式實作,但是不太建議,首先你會發現純文字比對的效能非常的差,不論是 IO 或是 CPU 成本都相當高;另一個是使用全文檢索,SQL Server 的全文檢索會將文字拆為單字詞存成索引(全文檢索索引),方便在搜尋的時候比對到關鍵字詞直接拉出資料,但是全文索引有個缺點就是拆關鍵字的方式並不是你能掌握,中文有許多日常用法或簡略說法等,還需要搭配語意資料庫、額外設定建議字等方式讓系統可以依照妳所想的內容拆出字詞(譬如:台北台北市,但如果使用者輸入 北市 就無法搜到)讓搜尋更加人性化且精準。

因此便考慮是不是可以把文字搜尋的工作交給 Elasticsearch 來做(畢竟 Elasticsearch 就是強在這種 RawData 的搜尋),然後讓 SQL Server 只做他該做的事 — 交易資料管理

在這樣的怨念底下,便燃起了一絲希望,希望可以藉由 Elasticsearch 來解救長久以來因為關鍵字搜尋帶來的糾葛以及效能問題

接下來將會依照以下方式進行,目標是安裝與設定 ELK 並可以單向同步 SQL Server 資料至 Elasticsearch

  • 架構規劃
  • 安裝 Java SE Development Kit 8
  • 安裝 ELK
  • 安裝 JDBC Driver for SQL Server
  • 啟動 Elasticsearch & Kibana
  • 建立 Elasticsearch 資料目的地
  • 設定 Logstash ,同步 SQL Server 資料
  • 確認資料數目是否正確

架構規劃

簡單畫一下自己認為可行的解決方案

前端呼叫 API ,基本的 CRUD 就由 SQL Server 處理,遇到關鍵字搜尋就推 Elasticsearch 來 Support ,而 SQL Server 與 Elasticsearch 之間的資料同步就使用 Logstash 來處理(有考慮使用 SSIS 來同步,未來會試試看)

測試環境

測試環境資訊如下:

  • 作業系統:Windows 10
  • 記憶體:2G
  • CPU:intel i5
  • 硬碟:SSD 20G

安裝 Java SE Development Kit 8

官方文件:Set up Elasticsearch

文件中提到, ELK 需要至少 Java 8 以上, 雖然目前 Java 版本已經到 9 ,但是這邊還是先選定官方建議的 Java 8 來進行安裝

下載並安裝 JDK 8:點我下載

安裝完後記得要設定 Windows 的 環境變數,將 C:\Program Files\Java\jre1.8.0_191\bin加入 Path 中(此路徑依照使用者安裝位置而會有所變動)

安裝 ELK

安裝 ELK 直接參考官網即可,在 Windows 安裝 ELK 很簡單,大致流程是,下載>解壓縮>cmd 呼叫 bat 啟動

在這裡要注意的是,下載完的 ELK 解壓縮後,資料夾路徑盡量避免空白(譬如:C:\Program Files),不然在執行 bin/logstash -f logstash-test.conf 時會出現路徑被錯置以致無法執行或讀取 config 的錯誤,如下所示

Files\logstash\config\logstash-test.conf;C:"Program

安裝 JDBC Driver for SQL Server

Elasticsearch 是 Java base ,用來連接 SQL Server 的 lib 就是 JDBC 了

下載並解壓縮 JDBC Driver 4.2 for SQL Server:點我下載

存放的檔案路徑一樣避免空白,因為 logstash 會需要讀取 JDBC 的 Lib 來進行 SQL Server 連接,如果路徑有空白一樣會出現上述路徑被錯置的情況。

解壓縮完後一樣要設定 Windows 的 環境變數,將 C:\ELK\sqljdbc_4.2\enu\jre8\sqljdbc42.jar 加入 Path 中(此路徑依照使用者安裝位置而會有所變動)

啟動 Elasticsearch & Kibana

在 Windows 的搜尋輸入 cmd 開啟命令提示字元,接下來 cd 進入到 Elasticsearch 的目錄底下後輸入 bin/elasticsearch 便可以啟動 Elasticsearch

要確認是否有啟動成功,可以在 powershell 中輸入

curl http://localhost:9200

或在瀏覽器中連結至 http://localhost:9200,有出現以下內容表示啟動成功

啟動 Kibana 的方式亦同,一樣在 Windows 的搜尋輸入 cmd 開啟命令提示字元,接下來 cd 進入到 Kibana 的目錄底下後輸入 bin/kibana 便可以啟動 kibana

接下來在瀏覽器中輸入 http://localhost:5601 ,確認是否能連接到 kibana 預設管理首頁

建立 Elasticsearch 資料目的地

確認 Elasticsearch 跟 kibana 都已經正常啟動之後,接下來可以利用 postman 或是 kibana 的 Dev Tools 在 Elasticsearch 中建立 Index 用來儲存預計要從 SQL Server 轉進來的資料

建立轉入目的地 hashtag

PUT hashtag
{
"settings" : {
"index" : {
"number_of_shards" : 5,
"number_of_replicas" : 0
}
}
}
  • number_of_shards :索引分片數,預設是 5 ,設定後無法再做修改
  • number_of_replicas :分片的副本數,預設是 1 ,設定後可再做修改

其他建立 Index 的設定可參考 官方文件

設定 Logstash ,同步 SQL Server 資料

可以在 logstash 的 config 資料夾中建立一個新的 config 檔,用來設定跟 SQL Server 的 Connection 設定以及資料 input、output 設定,以下將在 config 資料夾中建立一個 sqlserver.conf 的 logstash 測試用設定檔

input {jdbc {jdbc_connection_string => "jdbc:sqlserver://xxxxxxxxx:1433;databaseName=test_main;integratedSecurity=false;"jdbc_driver_library => "C:\ELK\sqljdbc_4.2\enu\jre8\sqljdbc42.jar"jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"jdbc_user => "ryanchen"jdbc_password => ""statement => "SELECT * FROM HashTag"}}output {elasticsearch {hosts => ["localhost:9200"]index => "hashtag"}}

其他 JDBC 的參數說明可以參考此份 官方文件 ,可設定排程執行等等

接著在 Windows 的搜尋輸入 cmd 開啟命令提示字元,接下來 cd 進入到 logstash 的目錄底下後輸入

bin\logstash.bat -f config\sqlserver.conf

執行 logstash 並且指定使用剛剛建立的 sqlserver.conf 設定檔

執行成功會如下圖

若執行時出現以下錯誤

Error: com.microsoft.sqlserver.jdbc.SQLServerDriver not loaded. :jdbc_driver_library is not set, are you sure you included

可以先檢查三個地方:

  • sqljdbc42.jar 中是否包含 SQLServerDriver.class:可使用解壓縮軟體直接打開 jar ,確認 SQLServerDriver.class 是否存在或名稱是否正確
  • 檢查 Windows 的 環境變數 中 ,Path 是否有設定 sqljdbc42.jar 的路徑
  • 在 config 檔中指定 sqljdbc42.jar 的路徑來源:強制指定config 檔中的jdbc_driver_library 路徑 ,並且避免路徑包含空白
jdbc_driver_library => “C:\ELK\sqljdbc_4.2\enu\jre8\sqljdbc42.jar”

確認資料數目是否正確

使用 kibana 的 DevTools 確認資料筆數

GET hashtag/_count

結論

第一階段成功安裝 ELK 以及單向同步資料至 Elasticsearch,接下來將會著重在兩個部分

  • 使用 API 呼叫 Elasticsearch 取得包含關鍵字的資料
  • Elasticsearch 與 SQL Server 全文檢索的效能比較。

此次設定項目如下:

  • 架構規劃
  • 安裝 Java SE Development Kit 8
  • 安裝 ELK
  • 安裝 JDBC Driver for SQL Server
  • 啟動 Elasticsearch & Kibana
  • 建立 Elasticsearch 資料目的地
  • 設定 Logstash ,同步 SQL Server 資料
  • 確認資料數目是否正確

參考資料

Sign up to discover human stories that deepen your understanding of the world.

--

--

Ryan Chen
Ryan Chen

Written by Ryan Chen

Database Administrator, Project Manager, Programer

No responses yet

What are your thoughts?