canaladapter 同步mysql(一对多关系)到es，es 添加逗号分词查询

不一样的烟火@

已于 2022-09-08 23:04:08 修改

阅读量1.5k

点赞数 1

分类专栏： canal 文章标签： elasticsearch 大数据搜索引擎

于 2022-09-08 22:50:40 首次发布

本文链接：https://blog.csdn.net/wzgbgz/article/details/126773709

版权

canal 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

mysql 一对多关系同步到es,刚好es需要的是一个字段对应逗号分割的值。这样就解决了mysql多表关联查询很慢的弊端。但是es中给如何把逗号分割的字符串做为查询条件呢，答案如下：先看一个简单的例子：

添加一个逗号分词

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"
      }
    }
  }
}

添加一个文档
POST /info-flow-test4/_doc
{
  "tags": "1,2,33"
}

POST /info-flow-test4/_search
{
  "query": {
    "match": {
      "tags": "2"
    }
  }
}
返回结果：
{
  "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"
        }
      }
    ]
  }
}

以上就是逗号分割的字段如何在es中检索出来。

项目中实战：通过canaladapter 同步一对多的关系到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索引建立：

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"
      }
      }
    }
  }

项目中员工给客户大的标签，是一对多的关系，但是为了在es中能对标签做完检索条件。不得已才有此方案，重点关注上次 tags 字段（完结）