mysql 一對(duì)多關(guān)系同步到es,剛好es需要的是一個(gè)字段對(duì)應(yīng)逗號(hào)分割的值。這樣就解決了mysql多表關(guān)聯(lián)查詢很慢的弊端。但是es中給如何把逗號(hào)分割的字符串做為查詢條件呢，答案如下：先看一個(gè)簡(jiǎn)單的例子：
添加一個(gè)逗號(hào)分詞
PUT info-flow-test4
{
"settings": {
"analysis": {
"analyzer": {
"my_anal": {
"type": "pattern",
"pattern": ","
}
}
}
},
"mappings": {
"properties": {
"id": {
"type": "keyword"
},
"content_id": {
"type": "long"
},
"content_type": {
"type": "integer"
},
"tags": {
"type": "text",
"analyzer": "my_anal"
}
}
}
}添加一個(gè)文檔
POST /info-flow-test4/_doc
{
"tags": "1,2,33"
}
POST /info-flow-test4/_search
{
"query": {
"match": {
"tags": "2"
}
}
}
返回結(jié)果：
{
"took" : 0,
"timed_out" : false,
"_shards" : {
"total" : 1,
"successful" : 1,
"skipped" : 0,
"failed" : 0
},
"hits" : {
"total" : {
"value" : 1,
"relation" : "eq"
},
"max_score" : 0.2876821,
"hits" : [
{
"_index" : "info-flow-test4",
"_type" : "_doc",
"_id" : "MUFNDX8BciPKIrWg9Qc6",
"_score" : 0.2876821,
"_source" : {
"tags" : "1,2,33"
}
}
]
}
}
?
以上就是逗號(hào)分割的字段如何在es中檢索出來。
項(xiàng)目中實(shí)戰(zhàn)：通過canaladapter 同步一對(duì)多的關(guān)系到es, canaladapter配置文件如下：
dataSourceKey: defaultDS
destination: canalserver
outerAdapterKey: essync
groupId: g1
esMapping:
_index: wx_cp_user_contact_rel_all
_id: id
# upsert: true
# pk: id
sql: "select
CONCAT(a.corp_id, '_', a.follow_user_id, '_', a.external_userid) as id,
a.external_userid,
a.follow_user_id,
a.follow_user_remark,
a.follow_user_description ,
date_format( a.follow_user_createtime, '%Y-%m-%d %H:%I:%S') as follow_user_createtime,
a.follow_user_tags_group_name,
a.follow_user_tags_tag_name ,
a.follow_user_tags_type ,
a.follow_user_remark_corp_name ,
a.address,
a.corp_id ,
a.follow_user_remark_mobiles ,
a.follow_user_state ,
a.status ,
a. birthday,
date_format( a.link_time, '%Y-%m-%d %H:%I:%S') as link_time,
date_format( a.create_time, '%Y-%m-%d %H:%I:%S') as create_time,
a.follow_user_add_way,
b.name,
b.avatar,
b.type,
b.gender,
b.unionid,
b.position,
b.corp_name ,
b.corp_full_name,
b.external_profile,
b.phone,
d.broadband_umber,
e.mobile,
f.tags
from
wx_cp_user_contact_rel a
left join wx_cp_user_contact b on a.external_userid = b.external_userid and a.corp_id = b.corp_id
left join wx_cp_user_contact_ext_rel d on a.external_userid = d.external_userid and a.follow_user_id = d.user_id and a.corp_id = d.corp_id
left join wx_cp_user e on e.user_id = a.follow_user_id and e.corp_id = a.corp_id
left join (select ec_user_id ,user_id , GROUP_CONCAT(tag_id) as tags,corp_id from wx_cp_user_contact_tag_rel group by ec_user_id,user_id,corp_id) f on a.external_userid = f.ec_user_id and a.follow_user_id = f.user_id and a.corp_id = f.corp_id
"
# objFields:
# _labels: array:;
etlCondition: "where a.corp_id ={}"
commitBatch: 3000
es索引建立：文章來源：http://www.zghlxwxcb.cn/news/detail-587505.html
PUT wx_cp_user_contact_rel_all/
{
"settings": {
"analysis": {
"analyzer": {
"my_anal": {
"type": "pattern",
"pattern": ","
}
}
}
},
"mappings" : {
"properties" : {
"address" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"avatar" : {
"type" : "keyword"
},
"birthday" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"broadband_umber" : {
"type" : "keyword"
},
"corp_full_name" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"corp_id" : {
"type" : "keyword"
},
"corp_name" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"create_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"develop_phone" : {
"type" : "keyword"
},
"external_profile" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"external_userid" : {
"type" : "keyword"
},
"follow_user_add_way" : {
"type" : "keyword"
},
"follow_user_createtime" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"follow_user_description" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"follow_user_id" : {
"type" : "keyword"
},
"follow_user_remark" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"follow_user_remark_corp_name" : {
"type" : "text",
"analyzer" : "ik_max_word"
},
"follow_user_remark_iop_mobiles" : {
"type" : "keyword"
},
"follow_user_remark_mobiles" : {
"type" : "keyword"
},
"follow_user_state" : {
"type" : "keyword"
},
"follow_user_tags_group_name" : {
"type" : "keyword"
},
"follow_user_tags_tag_name" : {
"type" : "keyword"
},
"follow_user_tags_type" : {
"type" : "keyword"
},
"gender" : {
"type" : "integer"
},
"is_has_tag" : {
"type" : "integer"
},
"job_number" : {
"type" : "keyword"
},
"link_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"mobile_update_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"name" : {
"type" : "keyword"
},
"phone" : {
"type" : "text",
"fields" : {
"keyword" : {
"type" : "keyword",
"ignore_above" : 256
}
}
},
"position" : {
"type" : "keyword"
},
"status" : {
"type" : "short"
},
"type" : {
"type" : "integer"
},
"unionid" : {
"type" : "keyword"
},
"unionid_mobile" : {
"type" : "keyword"
},
"update_time" : {
"type" : "date",
"format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
},
"user_code" : {
"type" : "keyword"
},
"mobile" : {
"type" : "keyword"
},
"tags": {
"type": "text",
"analyzer": "my_anal"
}
}
}
}項(xiàng)目中 員工給客戶大的標(biāo)簽，是一對(duì)多的關(guān)系，但是為了在es中能對(duì)標(biāo)簽做完檢索條件。不得已才有此方案，重點(diǎn)關(guān)注上次 tags 字段（完結(jié)）文章來源地址http://www.zghlxwxcb.cn/news/detail-587505.html
到了這里，關(guān)于canaladapter 同步mysql(一對(duì)多關(guān)系)到es，es 添加逗號(hào)分詞查詢的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
