canal中es7映射字段和数据同步使用总结踩坑

官方文档：

适配器表映射文件

dataSourceKey: defaultDS        # 源数据源的key, 对应上面配置的srcDataSources中的值
outerAdapterKey: exampleKey     # 对应application.yml中es配置的key 
destination: example            # cannal的instance或者MQ的topic
groupId:                        # 对应MQ模式下的groupId, 只会同步对应groupId的数据
esMapping:
  _index: mytest_user           # es 的索引名称
  _type: _doc                   # es 的type名称, es7下无需配置此项
  _id: _id                      # es 的_id, 如果不配置该项必须配置下面的pk项_id则会由es自动分配
#  pk: id                       # 如果不需要_id, 则需要指定一个属性为主键属性
  # sql映射
  sql: "select a.id as _id, a.name as _name, a.role_id as _role_id, b.role_name as _role_name,
        a.c_time as _c_time, c.labels as _labels from user a
        left join role b on b.id=a.role_id
        left join (select user_id, group_concat(label order by id desc separator ';') as labels from label
        group by user_id) c on c.user_id=a.id"
#  objFields:
#    _labels: array:;           # 数组或者对象属性, array:; 代表以;字段里面是以;分隔的
#    _obj: object               # json对象
  etlCondition: "where a.c_time>='{0}'"     # etl 的条件参数
  commitBatch: 3000                         # 提交批大小

sql映射说明

sql支持多表关联自由组合, 但是有一定的限制:

1. 主表不能为子查询语句
2. 只能使用left outer join即最左表一定要是主表
3. 关联从表如果是子查询不能有多张表
4. 主sql中不能有where查询条件(从表子查询中可以有where条件但是不推荐, 可能会造成数据同步的不一致, 比如修改了where条件中的字段内容)
5. 关联条件只允许主外键的’='操作不能出现其他常量判断比如: on a.role_id=b.id and b.statues=1
6. 关联条件必须要有一个字段出现在主查询语句中比如: on a.role_id=b.id 其中的 a.role_id 或者 b.id 必须出现在主select语句中

Elastic Search的mapping 属性与sql的查询值将一一对应(不支持 select *), 比如: select a.id as _id, a.name, a.email as _email from user, 其中name将映射到es mapping的name field, _email将 映射到mapping的_email field, 这里以别名(如果有别名)作为最终的映射字段. 这里的_id可以填写到配置文件的 _id: _id映射.

官方文档路径

https://github.com/alibaba/canal/wiki/Sync-ES

自定义报错总结

1.报错信息：In aggregated query without GROUP BY

 ERROR c.a.otter.canal.client.adapter.es7x.etl.ESEtlService - com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'cxstar_oa.d.id'; this is incompatible with sql_mode=only_full_group_by
java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'cxstar_oa.d.id'; this is incompatible with sql_mode=only_full_group_by

出现原因：

在MySQL5.7.5后，默认开启了ONLY_FULL_GROUP_BY，所以导致了之前的一些SQL无法正常执行，其实，是我们的SQL不规范造成的，因为group by 之后，返回的一些数据是不确定的，所以才会出现这个错误。

yml配置如下：

dataSourceKey: defaultDS # 源数据源的key, 对应上面配置的srcDataSources中的值
destination: example  # canal的instance或者MQ的topic
groupId: g1 # 对应MQ模式下的groupId, 只会同步对应groupId的数据
esVersion: es7
esMapping:
  _index: cnoa_data_1987051001 # es 的索引名称
  _id: _id  # es 的_id, 如果不配置该项必须配置下面的pk项_id则会由es自动分配
  sql: "SELECT
        d.id as _id,
        d.update_by as `4`,
        d.update_time as `5`,
        d.remark as `99003`,
        GROUP_CONCAT( da.archive_sid SEPARATOR ',') as `350050001`,
        GROUP_CONCAT( ds.subject_id SEPARATOR ',') as `331440001`
    FROM 
        data d 
        LEFT JOIN data_archive da on d.id=da.data_id 
        LEFT JOIN data_subject ds on d.id=ds.data_id"        # sql映射
  #etlCondition: "where p.id>={}"   #etl的条件参数
  objFields:
    350050001: array:,
    331440001: array:,
  commitBatch: 3000   # 提交批大小

这里我使用了GROUP_CONCAT导致报错

解决方案：

使用子查询

SELECT
        d.id as _id,
        d.title as `10100001`,
        d.isbn as `281450001`,
		d.id as _id,
        d.update_by as `4`,
        d.update_time as `5`,
        d.remark as `99003`,
		da1.archiveSids as `350050001`,
		ds1.subjectIds as `331440001`
    FROM 
		data d 
		LEFT JOIN 
		(select da.data_id,GROUP_CONCAT(da.archive_sid SEPARATOR ',') as archiveSids from data_archive da GROUP BY da.data_id) as da1  
		on d.id=da1.data_id 
		LEFT JOIN 
		(select ds.data_id,GROUP_CONCAT(ds.subject_id SEPARATOR ',') as subjectIds from data_subject ds GROUP BY ds.data_id) as ds1 
		on d.id=ds1.data_id
		where d.id=490076

2.数据不能增量同步

首先排查数据库配置是否正确，接着执行全量同步的命令

curl http://127.0.0.1:8189/etl/es7/data.yml -X POST

如果发现可以同步到es中，那么查看es7中的配置文件的sql语句中表名称是否加了反引号！

3.多个适配器实例报错： {“succeeded”:false,“errorMessage”:“Task not found”}

配置key导致，直接注释