安装canal-adapter1.1.5配置ES映射出现的坑

打开源码查看

此方法此行报错

查看报null错误的类

初步判定为ESmapping出来错误，让我们在仔细查看日志，说明它能正确解析出索引库和类型，说明错误极大可能出现在sql语句方面。

仔细查看sql语句，这是一条非常复杂的sql，预测sql过于复杂，解析失败，修改sql语句

select a.id AS id, a.id AS houseId, a.room, a.parlour, a.floor, a.total_floor AS totalFloor, a.watch_times AS watchTimes, 
a.build_year AS buildYear,a.cover,a.title, a.price, a.area, a.create_time AS createTime, a.last_update_time AS lastUpdateTime,
a.city_en_name AS cityEnName, a.region_en_name AS regionEnName, a.direction, a.distance_to_subway AS distanceToSubway,
b.subway_line_name AS subwayLineName, b.subway_station_name AS subwayStationName, a.street, a.district, 
b.description,b.layout_desc AS layoutDesc, b.traffic, b.round_service AS roundService, b.rent_way AS rentWay, 
(SELECT GROUP_CONCAT(name SEPARATOR ',') from house_tag WHERE a.id = house_id) AS tags 
from house a LEFT JOIN house_detail b on a.id = b.house_id    

修改后，去掉表的关联查询，只查询单表

  sql: "select a.id AS id, a.id AS houseId, a.room, a.parlour, a.floor, a.total_floor AS totalFloor, a.watch_times AS watchTimes, a.build_year AS buildYear,a.cover,a.title, a.price, a.area, a.create_time AS createTime, a.last_update_time AS lastUpdateTime,a.city_en_name AS cityEnName, a.region_en_name AS regionEnName, a.direction, a.distance_to_subway AS distanceToSubway, a.street, a.district from house a"

再次运行canal-adapter，修改数据库数据成功。

如果我们一定要关联表查询映射怎么办

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

我得sql语句最终修改方案，希望对你有用

dataSourceKey: defaultDS   #指定数据源，这个值和adapter的application.yml文件中配置的srcDataSources值对应。
destination: example       #指定canal-server中配置的某个实例的名字
groupId: g1                #组ID，默认就好
esMapping:                 #ES的mapping（映射）
  _index: searchroom            #要同步到的ES的索引名称（自定义）
  _type: house              #ES索引的类型名称（自定义）
  _id: _id                 #ES标示文档的唯一标示，通常对应数据表中的主键ID字段
  #pk: id                  #如果不需要_id, 则需要指定一个属性为主键属性
  sql: "select a.id AS id, a.id AS houseId, a.id AS _id,a.room, a.parlour, a.floor, a.total_floor AS totalFloor, 
        a.watch_times AS watchTimes, a.build_year AS buildYear,a.cover,a.title, a.price, a.area, a.create_time AS createTime, 
        a.last_update_time AS lastUpdateTime,a.city_en_name AS cityEnName, a.region_en_name AS regionEnName, a.direction, 
        a.distance_to_subway AS distanceToSubway,b.subway_line_name AS subwayLineName, b.subway_station_name AS subwayStationName, 
        a.street, a.district, b.description,b.layout_desc AS layoutDesc, b.traffic, b.round_service AS roundService, b.rent_way AS rentWay ,c.tags AS tags 
        from house a LEFT JOIN house_detail b on a.id = b.house_id LEFT JOIN (SELECT house_id,GROUP_CONCAT(name SEPARATOR ',') AS tags from house_tag GROUP BY house_id) c ON c.house_id = a.id "
  #这里就是数据表中的每个字段到ES索引中叫什么名字的sql映射，注意映射到es中的每个字段都要是唯一的，不能重复。
  #etlCondition: "where t.occur_time>='{0}'"
  commitBatch: 3000

期间更新数据，同步时还报了一个错，仔细看我箭头所指错误与sql，后来添加了a.id as _id，即可解决

2020-04-24 14:05:15.652 [pool-1-thread-1] ERROR com.alibaba.otter.canal.client.adapter.support.Util - sqlRs has error, sql: SELECT * FROM (select a.id AS id, a.id AS houseId, a.room, a.parlour, a.floor, a.total_floor AS totalFloor, a.watch_times AS watchTimes, a.build_year AS buildYear,a.cover,a.title, a.price, a.area, a.create_time AS createTime, a.last_update_time AS lastUpdateTime,a.city_en_name AS cityEnName, a.region_en_name AS regionEnName, a.direction, a.distance_to_subway AS distanceToSubway,b.subway_line_name AS subwayLineName, b.subway_station_name AS subwayStationName, a.street, a.district, b.description,b.layout_desc AS layoutDesc, b.traffic, b.round_service AS roundService, b.rent_way AS rentWay from house a LEFT JOIN house_detail b on a.id = b.house_id) _v WHERE _v._id=? AND _v.houseId=?  
2020-04-24 14:05:15.657 [pool-1-thread-1] ERROR c.a.o.canal.client.adapter.es.core.service.ESSyncService - sync error, es index: searchroom, DML : Dml{destination='example', database='searchroom', table='house_detail', type='UPDATE', es=1587708315000, ts=1587708315509, sql='', data=[{id=53, description=房屋描述ooooo, layout_desc=户型介绍, traffic=交通出行, round_service=周边配套, rent_way=1, address=3号院1号楼, subway_line_id=1, subway_line_name=13号线, subway_station_id=8, subway_station_name=回龙观, house_id=46}], old=[{description=房屋描述hhhaa}]}
2020-04-24 14:05:15.671 [pool-1-thread-1] ERROR c.a.o.canal.adapter.launcher.loader.CanalAdapterWorker - java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '_v._id' in 'where clause'
java.lang.RuntimeException: java.lang.RuntimeException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '_v._id' in 'where clause'
	at com.alibaba.otter.canal.client.adapter.es.core.service.ESSyncService.sync(ESSyncService.java:112) ~[na:na]
	at com.alibaba.otter.canal.client.adapter.es.core.service.ESSyncService.sync(ESSyncService.java:60) ~[na:na]
	at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.sync(ESAdapter.java:116) ~[na:na]
	at com.alibaba.otter.canal.client.adapter.es.core.ESAdapter.sync(ESAdapter.java:95) ~[na:na]
	at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.batchSync(AbstractCanalAdapterWorker.java:201) ~[client-adapter.launcher-1.1.5-SNAPSHOT.jar:na]
	at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$1(AbstractCanalAdapterWorker.java:62) ~[client-adapter.launcher-1.1.5-SNAPSHOT.jar:na]
	at java.util.concurrent.CopyOnWriteArrayList.forEach(Unknown Source) ~[na:1.8.0_151]
	at com.alibaba.otter.canal.adapter.launcher.loader.AbstractCanalAdapterWorker.lambda$null$2(AbstractCanalAdapterWorker.java:58) ~[client-adapter.launcher-1.1.5-SNAPSHOT.jar:na]
	at java.util.concurrent.FutureTask.run(Unknown Source) ~[na:1.8.0_151]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[na:1.8.0_151]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[na:1.8.0_151]
	at java.lang.Thread.run(Unknown Source) ~[na:1.8.0_151]