debezium table whitelist with regular expression

Question

I have some database table with bg_ and cp_ prefix like "bg_table1", "bg_table2" and "cp_table1".There are also some other tables without any prefix such as my_table1, user_action and so forth.

I have two debezium postgreSQL connectors and trying to configure table.whitelist property by following Debezium - http://debezium.io/docs/connectors/postgresql/#connector-properties. My requirements are as follows:

• in the first connector I want to load the tables whose names start with bg_ or cp_
• in the second connector I want to load the rest of the tables - does not start with bg_ or cp_

I am using something like below in the connector configuration but does not work:

First Connector Config:

"table.whitelist": "public.bg_*,public.cp_*" 

Second Connector Config:

"table.whitelist": ""  Cannot figure out need your help

It would be great if someone could help me figure it out. Thanks in advance!

Answer 1

Try this for your whitelist:

"table.whitelist": "public\.(bg|cp)_.*"

Demo

And try this for your blacklist:

"table.whitelist": "public\.(?!(bg|cp)_)[^_]+_.*"

Demo

The first pattern should pretty much work on any regex engine. The second pattern uses a negative lookahead. It would only work if your regex engine supported that. If it doesn't, then it will be harder to write a pattern with the logic you want to use.

Note: In the context of Java code you may need to double up backslashes to escape things like dots. That is, you might have to use the following version, e.g.

"table.whitelist": "public\\.(bg|cp)_.*"