使用 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?