canal 取 mysql到 elasticsearch

1.安装 canal 和 elasticsearch
教程很多，可自行搜索（我这里用的 canal 是 1.1.3 版本，es 则是 7.2的，mysql 用的是 5.7）

2.在 es 中创建 index，mapping，我这里有一个父子维关系，整个有点长

curl -XPUT "172.17.1.9:9200/index_user_v4"  -H 'Content-Type: application/json'  -d '
{
"mappings": {
"properties": {
                "user_parent":{
                     "type": "join",
                       "relations": {
                            "user": ["schoolRel","classRel"]
                         }
                  },
  "Id" : {"type" :"long"},
      "avatarUrl" : {
        "type" : "keyword"
      },
  "userId" : {
        "type" : "long"
      },
  "tableType" : {
        "type" : "keyword"
      },
      "birthDate" : {
        "type" : "date"
      },
      "gender" : {
        "type" : "text"
      },
      "identityNum" : {
        "type" : "text"
      },
      "mobile" : {
        "type" : "text"
      },
      "userCode" : {
        "type" : "keyword"
      },
      "userName" : {
        "type" : "text",
        "fields" : {
          "dim" : {
            "type" : "text"
          },
          "exact" : {
            "type" : "keyword"
          }
        }
      },

 
      "userType" : {
        "type" : "long"
      },
      "areaCode" : {
        "type" : "keyword"
      },
      "roleType" : {
        "type" : "long"
      },
      "schoolCode" : {
        "type" : "keyword"
      },
      "schoolId" : {
        "type" : "long"
      },
      "schoolName" : {
        "type" : "text"
      },
      "sno" : {
        "type" : "keyword"
      },

3.配置 canalserver 的 instance.properties 文件

canal.instance.gtidon=false

canal.instance.master.address=********:3306
canal.instance.master.journal.name=
canal.instance.master.position=
canal.instance.master.timestamp=
canal.instance.master.gtid=

canal.instance.rds.accesskey=
canal.instance.rds.secretkey=
canal.instance.rds.instanceId=

canal.instance.tsdb.enable=true

canal.instance.dbUsername=canal
canal.instance.dbPassword=jztestP6!4nKsM
canal.instance.connectionCharset = UTF-8

canal.instance.enableDruid=false

canal.instance.filter.regex=testcore.t_user,testcore.t_user_school_rel,testcore.t_school_data,testcore.t_user_class_rel,testcore.t_school_dept,testcore.t_dd_area,testcore.t_user_parenthood_rel
#canal.instance.filter.regex=testcore\\..*

canal.instance.filter.black.regex=


canal.mq.topic=example

#canal.mq.dynamicTopic=mytest1.user,mytest2\\..*,.*\\..*
canal.mq.partition=0

4.配置 canal-adapter 相关的配置
application.yml

server:
  port: 8081
logging:
  level:
	org.springframework: INFO
    com.alibaba.otter.canal.client.adapter.hbase: DEBUG
    com.alibaba.otter.canal.client.adapter.es: INFO
    com.alibaba.otter.canal.client.adapter.rdb: DEBUG


spring:
	jackson:
  	  date-format: yyyy-MM-dd HH:mm:ss
      time-zone: GMT+8
   	  default-property-inclusion: non_null

canal.conf:
  mode: tcp # kafka rocketMQ
  canalServerHost: 172.18.108.67:11111

  batchSize: 500
  syncBatchSize: 1000
  retries: 0
  timeout:
  accessKey:
  secretKey:
  srcDataSources:
defaultDS:
  url: jdbc:mysql://*******:3306/testcore?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=GMT%2B8

  username: canal
  password: ######
  canalAdapters:
  - instance: testcore
    groups:
- groupId: g1
  outerAdapters:
  - name: logger
  - name: es
    hosts: 127.0.0.1:9300
    properties:
      cluster.name: my-application

5.conf 目录下写相关的 yml 文件(格式好像挺重要的，我当时因为这个格式问题差点疯了）

t_user.yml

dataSourceKey: defaultDS
destination: testcore
upsert: true
groupId: g1
esMapping:
 _index: index_user_v4
 _type: _doc
 _id: _id
 pk: user_id
 relations:
    user_parent:
      name: user
 sql: "select concat('user',user_id) as _id,user_id as userId,'user' as tableType,user_name as userName,user_type as userType, mobile,user_code as userCode,avatar_url as avatarUrl, gender,identity_num as identityNum,birth_date as birthDate from testcore.t_user"

t_schoolrel.yml

dataSourceKey: defaultDS
destination: testcore
upsert: true
esMapping:
 _index: index_user_v4
 _type: _doc
 _id: _id
 pk: sr.id
 relations:
user_parent:
  name: schoolRel
  parent: sparent
 sql: "select concat('school',sr.id) as _id,sr.id as Id,concat('user',sr.user_id) as sparent,sr.user_id as userId,'schoolRel' as tableType,sr.user_id as sk,sr.user_id as parent,sr.school_id as schoolId,sd.school_name as schoolName,sr.role_type as roleType,sr.sno as sno,sd.area_code as areaCode,sd.school_code as schoolCode from t_user_school_rel sr LEFT JOIN t_school_data sd on sr.school_id=sd.school_id "
 skips:
  - sparent

刚好这两个是父子维关系

配通以后查看日志，大概的就是这样子的图