使用 Elasticsearch 為 SQL Server 完成專案的純文字搜尋功能(一)- 安裝 ElasticSearch
在實作專案時很常遇到客戶提的一個看似簡單但又非常麻煩的功能,就是在網站的關鍵字搜尋引擎
文字搜尋在 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 啟動
- Elasticsearch:https://www.elastic.co/downloads/elasticsearch
- Logstash:https://www.elastic.co/downloads/logstash
- Kibana:https://www.elastic.co/downloads/kibana
在這裡要注意的是,下載完的 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 資料
- 確認資料數目是否正確