sql-server - Elasticsearch返回旧的SQL结果

Question

我建立了一个索引，该索引从SQL Server中的表中提取数据

{
    "type":"jdbc",
    "jdbc": 
    {
        "driver":"com.microsoft.sqlserver.jdbc.SQLServerDriver",
        "url":"jdbc:sqlserver://[my_ip];databaseName=mega",
        "user":"sa","password":"******",
        "sql":"SELECT [OrderID],[CustomerName],[UserFullName],[Status]  FROM [Orders_Table]",
        "poll":"5s",
        "index": "mega",
        "type": "orders_search",
        "schedule" : "0 0-59 0-23 ? * *"
    }
}

问题是我收到了不相关的查询结果。

例如:[ 5220668 ]是应仅返回一次(包含在数据库中)的行键。

{

    "from" : 0, "size" : 5,
    "query": { 
        "multi_match": {
           "query": "5220668", 
           "fields": [ "_all" ]
        }
    } 
}

结果:结果有问题。
我期望数据库中仅显示一击。而是查询检索行状态的整个生命周期

{
   "took": 12,
   "timed_out": false,
   "_shards": {
      "total": 5,
      "successful": 5,
      "failed": 0
   },
   "hits": {
      "total": 4,
      "max_score": null,
      "hits": [
         {
            "_index": "mega",
            "_type": "handledorders_search",
            "_id": "AU3OlBkh6JN7xIrOkzjm",
            "_score": null,
            "_source": {
               "Status": "NEW",
               "Date": "2015-06-07T03:00:12.110Z",
               "UserFullName": "my name",
               "CustomerName": "cust name",
               "OrderID": 5220668
            },
            "sort": [
               1433646012110
            ]
         },
         {
            "_index": "mega",
            "_type": "handledorders_search",
            "_id": "AU3Ok0E-6JN7xIrOkvpF",
            "_score": null,
            "_source": {
               "Status": "NEW",
               "Date": "2015-06-07T03:00:12.110Z",
               "UserFullName": "my name",
               "CustomerName": "cust name",
               "OrderID": 5220668
            },
            "sort": [
               1433646012110
            ]
         },
         {
            "_index": "mega",
            "_type": "handledorders_search",
            "_id": "AU3Ole0-6JN7xIrOk7Yo",
            "_score": null,
            "_source": {
               "Status": "FIX",
               "Date": "2015-06-07T03:00:12.110Z",
               "UserFullName": "my name",
               "CustomerName": "cust name",
               "OrderID": 5220668
            },
            "sort": [
               1433646012110
            ]
         },
         {
            "_index": "mega",
            "_type": "handledorders_search",
            "_id": "AU3OlQL86JN7xIrOk3eH",
            "_score": null,
            "_source": {
               "Status": "CLOSE",
               "Date": "2015-06-07T03:00:12.110Z",
               "UserFullName": "my name",
               "CustomerName": "cust name",
               "ExternalOrderID": 5220668
            },
            "sort": [
               1433646012110
            ]
         }
      ]
   }
}

Answer 1

我了解您使用的是_river插件或类似的东西，并且依赖于Elasticsearch轮询MSSQL数据。

棘手的是，当文档更改时，Elasticsearch不知道是否需要更新文档或创建新文档。您知道文档是相同的，但ES却不同。您需要告诉ES文件是相同的。

有两种不同的方法。第一个是告诉ES特定字段是唯一标识符。您将需要创建一个类似于以下内容的映射

{
    "mega" : {
        "_id" : {
            "path" : "OrderId"
        }
    }
}

从1.5.0版本开始不推荐使用此方法

https://www.elastic.co/guide/en/elasticsearch/reference/current/mapping-id-field.html

另一种可能性是最简单的一种，它是在SQL初始化中将OrderId映射到_id。

更多信息 http://blog.pluralsight.com/elasticsearch-and-sql-server

The way a select statement with an alias that tells SQL Server to return the primary key field “ID” as “_id”. This is the default key convention that Elasticsearch uses for all documents. It’s important to keep this nomenclature when selecting your data so that Elasticsearch knows to update a document and not create a new one each time it polls