使用完全外部联接将所有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"
}
}
请找到rubydebug日志
{
"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 .
1 回答
您应该按照下面的sql重新编写
statement_sql，然后重试 .