canal 同步mysql数据到clickhouse 忽略delete update

张映 发表于 2021-09-15

分类目录: clickhouse

标签:, , , ,

canal支持mysql到clickhouse,本以为canal会把mysql dml语法转换成clickhouse dml语法,可是并没有。

配置canal.properties

# vim deployer/conf/canal.properties //修改以下内容

canal.instance.filter.query.ddl = true   //过滤修改表结构
canal.instance.filter.dml.update = true  //过滤更新
canal.instance.filter.dml.delete = true  //过滤删除

deployer 先重启
adapter 后重启

如果不过滤,会报以下错误

Caused by: java.lang.Throwable: Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 1 ('UPDATE'): UPDATE `tanktest`.`tank` SET `name`='tank10' WHERE `id`=10 . Expected one of: Query, SHOW, SELECT, CREATE, DROP, TRUNCATE, OPTIMIZE TABLE, CREATE POLICY, ALTER POLICY, CREATE PROFILE, ALTER PROFILE, SYSTEM, DESCRIBE, DETACH, ATTACH, ALTER QUOTA, CREATE QUOTA, ALTER ROLE, CREATE ROLE, ALTER ROW POLICY, CREATE ROW POLICY, ALTER SETTINGS PROFILE, CREATE SETTINGS PROFILE, ALTER USER, CREATE USER, GRANT, REVOKE, SET ROLE, SET ROLE DEFAULT, SET DEFAULT ROLE, SHOW GRANTS, KILL, SELECT subquery, DESC, SET, WITH, list of elements, ALTER query, ALTER TABLE, ALTER LIVE VIEW, CREATE LIVE VIEW query, CREATE DATABASE query, CREATE VIEW query, CREATE DICTIONARY, GRANT or REVOKE query, Query with output, SHOW PROCESSLIST query, SHOW ACCESS query, SHOW PROCESSLIST, SHOW ACCESS, RENAME query, RENAME TABLE, EXCHANGE TABLES, RENAME DICTIONARY, RENAME DATABASE, SET query, ShowAccessEntitiesQuery, SHOW CREATE QUOTA query, SHOW CREATE, SHOW GRANTS query, SHOW PRIVILEGES query, SHOW PRIVILEGES, SYSTEM query, EXISTS or SHOW CREATE query, EXISTS, USE query, USE, WATCH query, WATCH, CHECK TABLE, DESCRIBE query, DROP access entity query, DROP query, EXPLAIN, EXTERNAL DDL query, EXTERNAL DDL FROM, INSERT query, INSERT INTO, KILL QUERY query, OPTIMIZE query, SELECT query, CREATE TABLE or ATTACH TABLE query, CREATE QUOTA or ALTER QUOTA query, CREATE ROLE or ALTER ROLE query, CREATE ROW POLICY or ALTER ROW POLICY query, CREATE SETTINGS PROFILE or ALTER SETTINGS PROFILE query, CREATE USER or ALTER USER query, SELECT query, possibly with UNION, SET ROLE or SET DEFAULT ROLE query, SHOW [TEMPORARY] TABLES|DATABASES|CLUSTERS|CLUSTER 'name' [[NOT] [I]LIKE 'str'] [LIMIT expr], SELECT query, subquery, possibly with UNION (version 20.8.3.18)



转载请注明
作者:海底苍鹰
地址:http://blog.51yip.com/clickhouse/2536.html