java - Logstash JDBC插件 - 并非所有字段都从oracle进入elasticsearch

Question

我使用完全外连接将 5 个表连接在一起。有些字段名称在所有 5 个表中都是通用的，因此在连接这些表后，通用字段名称会出现，例如 id_1、id_2、type_1 等，但这些字段不会进入 elasticsearch。

请找到以下索引，执行logstash后在elasticsearch中可以找到该索引。

{
  "took": 8,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 62500,
    "max_score": 1,
    "hits": [
      {
        "_index": "replacement_local100",
        "_type": "doc",
        "_id": "OjwSN2QBjQ32BN5_Nvhv",
        "_score": 1,
        "_source": {
          "type": null,
          "product_id": 219026,
          "expiration_date": null,
          "region_id": null,
          "code": "VG89A2S1H20",
          "catalog_id": 326,
          "min_order_quantity": 1,
          "name": "product219010",
          "std_delivery_time": 0,
          "vendor_id": 1,
          "rank": 0,
          "catalog_product_id": null,
          "id": null,
          "product_group": "single",
          "country_id": null,
          "@timestamp": "2018-06-25T13:11:36.078Z",
          "status": "ACT",
          "is_visible": 0,
          "company_id": null,
          "product_type": "regular",
          "@version": "1",
          "link_id": 14801
        }
      },

完全外连接后来自 Oracle 的字段(标记字段在 elasticsearch 中不可用)。

id
name
std_delivery_time
min_order_quantity
code
status
catalog_id
expiration_date
rank
is_visible
product_type
product_group
vendor_id
product_id
type
link_id

id_1  // this field is not coming in ElasticSearch
region_id
catalog_product_id
type_1 // this field is not coming in ElasticSearch

id_2 // this field is not coming in ElasticSearch
country_id
catalog_product_id_1 // this field is not coming in ElasticSearch
type_2 // this field is not coming in ElasticSearch

id_3    // this field is not coming in ElasticSearch
company_id
catalog_product_id_2 // this field is not coming in ElasticSearch
type_3  // this field is not coming in ElasticSearch

但是，我已经映射了映射中的所有字段。请查找我的映射详细信息。

PUT replacement_local100
 {
      "settings": {
        "analysis": {
        "normalizer": {
           "my_normalizer": {
           "type": "custom",
           "char_filter": [],
           "filter": ["lowercase", "asciifolding"]
         }
       }
     }
    },
    "mappings": {
      "doc": {
        "properties": {
          "code": {
            "type": "keyword",
            "normalizer": "my_normalizer"
          },
          "id": {
            "type": "long"
          },
          "name":{
          "type": "text"
          },
          "std_delivery_time":{
          "type" : "long"
          },
          "min_order_quantity":{
          "type":"long"
          },
         "status":{
         "type":"text"
         },
         "catalog_id":{
         "type":"long"
         },
         "expiration_date":{
         "type":"text"
         },
         "rank":{
         "type":"long"
         },
         "is_visible":{
         "type":"long"
         },
         "product_type":{
         "type":"text"
         },
         "product_group":{
         "type":"text"
         },
         "vendor_id":{
         "type":"long"
         },
         "product_id":{
         "type":"long"
         },
         "type":{
         "type":"text"
         },
         "link_id":{
         "type":"long"
         },
         "id_1":{
         "type":"long"
         },
         "region_id":{
         "type":"text"
         },
         "catalog_product_id":{
         "type":"long"
         },
         "type_1":{
         "type":"text"
         },
         "id_2":{
         "type":"long"
         },
         "country_id":{
         "type":"text"
         },
         "catalog_product_id_1":{
         "type":"long"
         },
         "type_2":{
         "type":"text"
         },
         "id_3":{
         "type":"long"
         },
         "company_id":{
         "type":"long"
         },
         "catalog_product_id_2":{
         "type":"long"
         },
         "type_3":{
         "type":"text"
         }

        }
       }
      }
}

请找到我的logstash配置文件。

input {
  jdbc {
    jdbc_driver_library => "D:\1SearchEngine\data\ojdbc8.jar"
    jdbc_driver_class => "Java::oracle.jdbc.OracleDriver"
    jdbc_connection_string => "jdbc:oracle:thin:@localhost:1521/mydb"       
    jdbc_user => "user"
    jdbc_password => "aug2012"
    jdbc_fetch_size => "50000"
    statement => "select * from product
                    FULL OUTER JOIN product_link
                    ON product.id=product_link.product_id
                    FULL OUTER JOIN REGION
                    ON product.ID=REGION.CATALOG_PRODUCT_ID
                    FULL OUTER JOIN COUNTRY
                    ON product.ID = COUNTRY.CATALOG_PRODUCT_ID
                    FULL OUTER JOIN VISIBILITY
                    ON product.ID = VISIBILITY.CATALOG_PRODUCT_ID"  
    }
}

output {
  elasticsearch {
    hosts => ["localhost:9200"]
    index => "replacement_local100"

  }
}

请查找 ruby​​debug 日志

{
          "product_type" => "regular",
                  "name" => "product283944",
       "expiration_date" => nil,
            "country_id" => nil,
            "catalog_id" => 41455,
                  "type" => nil,
            "company_id" => nil,
             "vendor_id" => 1,
    "catalog_product_id" => nil,
     "std_delivery_time" => 0,
                "status" => "ACT",
            "product_id" => 284831,
                  "rank" => 0,
            "is_visible" => 1,
              "@version" => "1",
             "region_id" => nil,
            "@timestamp" => 2018-06-26T06:36:47.084Z,
    "min_order_quantity" => 0,
                    "id" => nil,
         "product_group" => "dummyCombo",
                  "code" => "VG3210JS ; VA-7482-8201",
               "link_id" => 228193
}

id_1、type_1、id_2、type_2 缺失。

Answer 1

您应该按照下面的sql重写您的statement_sql，然后重试。

SELECT product.*, region.id as region_id, country.id as coutry_id
FROM product
  FULL OUTER JOIN product_link
    ON product.id = product_link.product_id
  FULL OUTER JOIN REGION
    ON product.ID = REGION.CATALOG_PRODUCT_ID
  FULL OUTER JOIN COUNTRY
    ON product.ID = COUNTRY.CATALOG_PRODUCT_ID
  FULL OUTER JOIN VISIBILITY
    ON product.ID = VISIBILITY.CATALOG_PRODUCT_ID