Elasticsearch river 模組以插件形式支援從多種資料源同步資料。社群提供的 elasticsearch-river-jdbc 可統一透過 JDBC 從 MySQL、PostgreSQL、SQL Server、Oracle 等匯入資料。本文以 SQL Server 為例說明安裝與配置步驟。
./bin/plugin --install jdbc \
--url https://xbib.org/repository/org/xbib/elasticsearch/plugin/elasticsearch-river-jdbc/1.1.0.1/elasticsearch-river-jdbc-1.1.0.1-plugin.zip
sqljdbc4.jar 複製到 $ES_HOME/lib 目錄,重啟 ES 節點。PUT /_river/mytest_river/_meta
{
"type": "jdbc",
"jdbc": {
"driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
"url": "jdbc:sqlserver://MYSQLSERVERNAME;databaseName=MYProductDatabase",
"user": "admin",
"password": "Password",
"sql": "SELECT ProductID AS _id, CategoryID, ManufacturerID, MfName, ProductTitle, MfgPartNumber FROM MyProductsTable (NOLOCK)",
"poll": "10m",
"strategy": "simple",
"index": "myinventory",
"type": "product",
"bulk_size": 100,
"max_retries": 5,
"max_retries_wait": "30s",
"max_bulk_requests": 5,
"bulk_flush_interval": "5s"
}
}
sql:查詢語句,必須包含 _id 欄位 作為 ES 文件 ID。poll:輪詢間隔 (如 10m、1h),strategy: simple 表示每次全量查詢並以 _id 判斷新增/更新/刪除。bulk_size:每批次 Bulk API 筆數。index/type:目標索引與類型 (ES 7+ 單類型請忽略 type)。