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

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

    1. 添加一个逗号分词
    2.  
    3. PUT info-flow-test4
    4. {
    5.   "settings": {
    6.     "analysis": {
    7.       "analyzer": {
    8.         "my_anal": {
    9.           "type": "pattern",
    10.           "pattern": ","
    11.         }
    12.       }
    13.     }
    14.   },
    15.   "mappings": {
    16.     "properties": {
    17.       "id": {
    18.         "type": "keyword"
    19.       },
    20.       "content_id": {
    21.         "type": "long"
    22.       },
    23.       "content_type": {
    24.         "type": "integer"
    25.       },
    26.       "tags": {
    27.         "type": "text",
    28.         "analyzer": "my_anal"
    29.       }
    30.     }
    31.   }
    32. }
    1. 添加一个文档
    2. POST /info-flow-test4/_doc
    3. {
    4.   "tags": "1,2,33"
    5. }
    6.  
    7. POST /info-flow-test4/_search
    8. {
    9.   "query": {
    10.     "match": {
    11.       "tags": "2"
    12.     }
    13.   }
    14. }
    15. 返回结果：
    16. {
    17.   "took" : 0,
    18.   "timed_out" : false,
    19.   "_shards" : {
    20.     "total" : 1,
    21.     "successful" : 1,
    22.     "skipped" : 0,
    23.     "failed" : 0
    24.   },
    25.   "hits" : {
    26.     "total" : {
    27.       "value" : 1,
    28.       "relation" : "eq"
    29.     },
    30.     "max_score" : 0.2876821,
    31.     "hits" : [
    32.       {
    33.         "_index" : "info-flow-test4",
    34.         "_type" : "_doc",
    35.         "_id" : "MUFNDX8BciPKIrWg9Qc6",
    36.         "_score" : 0.2876821,
    37.         "_source" : {
    38.           "tags" : "1,2,33"
    39.         }
    40.       }
    41.     ]
    42.   }
    43. }

     

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

    项目中实战：通过canaladapter 同步一对多的关系到es, canaladapter配置文件如下：

    1. dataSourceKey: defaultDS
    2. destination: canalserver
    3. outerAdapterKey: essync
    4. groupId: g1
    5. esMapping:
    6.   _index: wx_cp_user_contact_rel_all
    7.   _id: id
    8.   #  upsert: true
    9.   #  pk: id
    10.   sql: "select
    11.                 	CONCAT(a.corp_id, '_', a.follow_user_id, '_', a.external_userid) as id,
    12.                 	a.external_userid,
    13.                 	a.follow_user_id,
    14.                 	a.follow_user_remark,
    15.                 	a.follow_user_description ,
    16.                 	date_format( a.follow_user_createtime, '%Y-%m-%d %H:%I:%S') as follow_user_createtime,
    17.                 	a.follow_user_tags_group_name,
    18.                 	a.follow_user_tags_tag_name ,
    19.                 	a.follow_user_tags_type ,
    20.                 	a.follow_user_remark_corp_name ,
    21.                 	a.address,
    22.                 	a.corp_id ,
    23.                 	a.follow_user_remark_mobiles ,
    24.                 	a.follow_user_state ,
    25.                 	a.status ,
    26.                 	a. birthday,
    27.                 	date_format( a.link_time, '%Y-%m-%d %H:%I:%S') as link_time,
    28.                 	date_format( a.create_time, '%Y-%m-%d %H:%I:%S') as create_time,
    29.                 	a.follow_user_add_way,
    30.                 	b.name,
    31.                 	b.avatar,
    32.                 	b.type,
    33.                 	b.gender,
    34.                 	b.unionid,
    35.                 	b.position,
    36.                 	b.corp_name ,
    37.                 	b.corp_full_name,
    38.                 	b.external_profile,
    39.                 	b.phone,
    40.                 	d.broadband_umber,
    41.                 	e.mobile,
    42.                 	f.tags
    43.                 from
    44.                 	wx_cp_user_contact_rel a
    45.                 left join wx_cp_user_contact b on	a.external_userid = b.external_userid	and a.corp_id = b.corp_id
    46.                 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
    47.                 left join wx_cp_user e on  e.user_id = a.follow_user_id and e.corp_id = a.corp_id
    48.                 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
    49. "
    50.   #  objFields:
    51.   #    _labels: array:;
    52.   etlCondition: "where a.corp_id ={}"
    53.   commitBatch: 3000

    es索引建立：

    1. PUT wx_cp_user_contact_rel_all/
    2.  {
    3.     "settings": {
    4.     "analysis": {
    5.       "analyzer": {
    6.         "my_anal": {
    7.           "type": "pattern",
    8.           "pattern": ","
    9.         }
    10.       }
    11.     }
    12.   },
    13.     "mappings" : {
    14.       "properties" : {
    15.         "address" : {
    16.           "type" : "text",
    17.           "analyzer" : "ik_max_word"
    18.         },
    19.         "avatar" : {
    20.           "type" : "keyword"
    21.         },
    22.         "birthday" : {
    23.           "type" : "date",
    24.           "format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
    25.         },
    26.         "broadband_umber" : {
    27.           "type" : "keyword"
    28.         },
    29.         "corp_full_name" : {
    30.           "type" : "text",
    31.           "analyzer" : "ik_max_word"
    32.         },
    33.         "corp_id" : {
    34.           "type" : "keyword"
    35.         },
    36.         "corp_name" : {
    37.           "type" : "text",
    38.           "analyzer" : "ik_max_word"
    39.         },
    40.         "create_time" : {
    41.           "type" : "date",
    42.           "format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
    43.         },
    44.         "develop_phone" : {
    45.           "type" : "keyword"
    46.         },
    47.         "external_profile" : {
    48.           "type" : "text",
    49.           "analyzer" : "ik_max_word"
    50.         },
    51.         "external_userid" : {
    52.           "type" : "keyword"
    53.         },
    54.         "follow_user_add_way" : {
    55.           "type" : "keyword"
    56.         },
    57.         "follow_user_createtime" : {
    58.           "type" : "date",
    59.           "format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
    60.         },
    61.         "follow_user_description" : {
    62.           "type" : "text",
    63.           "analyzer" : "ik_max_word"
    64.         },
    65.         "follow_user_id" : {
    66.           "type" : "keyword"
    67.         },
    68.         "follow_user_remark" : {
    69.           "type" : "text",
    70.           "analyzer" : "ik_max_word"
    71.         },
    72.         "follow_user_remark_corp_name" : {
    73.           "type" : "text",
    74.           "analyzer" : "ik_max_word"
    75.         },
    76.         "follow_user_remark_iop_mobiles" : {
    77.           "type" : "keyword"
    78.         },
    79.         "follow_user_remark_mobiles" : {
    80.           "type" : "keyword"
    81.         },
    82.         "follow_user_state" : {
    83.           "type" : "keyword"
    84.         },
    85.         "follow_user_tags_group_name" : {
    86.           "type" : "keyword"
    87.         },
    88.         "follow_user_tags_tag_name" : {
    89.           "type" : "keyword"
    90.         },
    91.         "follow_user_tags_type" : {
    92.           "type" : "keyword"
    93.         },
    94.         "gender" : {
    95.           "type" : "integer"
    96.         },
    97.         "is_has_tag" : {
    98.           "type" : "integer"
    99.         },
    100.         "job_number" : {
    101.           "type" : "keyword"
    102.         },
    103.         "link_time" : {
    104.           "type" : "date",
    105.           "format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
    106.         },
    107.         "mobile_update_time" : {
    108.           "type" : "date",
    109.           "format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
    110.         },
    111.         "name" : {
    112.           "type" : "keyword"
    113.         },
    114.         "phone" : {
    115.           "type" : "text",
    116.           "fields" : {
    117.             "keyword" : {
    118.               "type" : "keyword",
    119.               "ignore_above" : 256
    120.             }
    121.           }
    122.         },
    123.         "position" : {
    124.           "type" : "keyword"
    125.         },
    126.         "status" : {
    127.           "type" : "short"
    128.         },
    129.         "type" : {
    130.           "type" : "integer"
    131.         },
    132.         "unionid" : {
    133.           "type" : "keyword"
    134.         },
    135.         "unionid_mobile" : {
    136.           "type" : "keyword"
    137.         },
    138.         "update_time" : {
    139.           "type" : "date",
    140.           "format" : "yyyy-MM-dd HH:mm:ss||yyyy-MM-dd HH:mm:ss.S||strict_date_optional_time||epoch_millis"
    141.         },
    142.         "user_code" : {
    143.           "type" : "keyword"
    144.         },
    145.          "mobile" : {
    146.           "type" : "keyword"
    147.         },
    148.          "tags": {
    149.         "type": "text",
    150.         "analyzer": "my_anal"
    151.       }
    152.       }
    153.     }
    154.   }

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

  • 相关阅读:
    RPA厂商有哪些？国内头部5家RPA厂商的分析对比
    zookeeper本地部署和集群搭建
    【JS】使用wavesurfer播放网络音频（Vue）
    全面分析“由于找不到vcruntime140_1.dll,无法继续执行代码”问题的解决方法
    GD32搭建Keil MDK开发环境
    【6】c++设计模式——＞UML表示类之间的依赖关系
    Android Media Framework - 开篇
    基于matlab GUI的数字图像处理系统
    FFmepg使用指南
    页面功能并不是所有用户都能执行 点击判断用户权限路由是否进行跳转
  • 原文地址：https://blog.csdn.net/wzgbgz/article/details/126773709