Postgresql Connector: Configuration for partitioned tables in table whitelist

[Anoop Krishnakumar]

I am using postgresql connector (0.8). The source table is a partitioned table in postgres schema. How should I specify the table name in table whitelist? Also I have to exclude a column in the partitioned table, how should I represent the column name?

Thanks,

Anoop

[Gunnar Morling]

Hi,

If you want to capture the entire table, you would specify it just as any other table. I reckon though you'd like to capture only changes to records in one specific partition?

If so, it could be doable depending on your specific situation. Via the "snapshot.select.statement.overrides" connector option you could limit snapshotting to the partition you're interested in. But during log reading changes to all partitions will be captured, so this would only work if your partitioning is around some "logical deleted" flag where you'd only like to snapshot and stream changes to the records from the "active" partition.

If that's not enough for your use case, we could envision some more generic filtering of events. E.g. by letting the user specify some filtering predicate which is applied to the events as they come in. If you're interested, can you file a feature request in JIRA? It seems generally useful.

Regarding your second question, please look for the column.blacklist option in the connector docs (http://debezium.io/docs/connectors/postgresql/).

Hth,

--Gunnar

[Thúc Đồng Hồ]

Hi Gunna!

If i want to capture some table not all database, how can i config connector for my purpose?

--
You received this message because you are subscribed to the Google Groups "debezium" group.
To unsubscribe from this group and stop receiving emails from it, send an email to debezium+unsubscribe@googlegroups.com.
To post to this group, send email to debe...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/f46a1c2e-e83c-486b-887d-17a3f0ba65dd%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Gunnar Morling]

Check out the "table.whitelist" and ""table.blacklist" options.

That said, in general please start new threads for separate questions like this, so to keep the discussions focused. Thanks!

Am Fr., 20. Juli 2018 um 10:51 Uhr schrieb Thúc Đồng Hồ <hothu...@shippo.vn>:

Hi Gunna!

If i want to capture some table not all database, how can i config connector for my purpose?

On Fri, Jul 20, 2018 at 2:16 PM, 'Gunnar Morling' via debezium <debe...@googlegroups.com> wrote:

Hi,

If you want to capture the entire table, you would specify it just as any other table. I reckon though you'd like to capture only changes to records in one specific partition?

If so, it could be doable depending on your specific situation. Via the "snapshot.select.statement.overrides" connector option you could limit snapshotting to the partition you're interested in. But during log reading changes to all partitions will be captured, so this would only work if your partitioning is around some "logical deleted" flag where you'd only like to snapshot and stream changes to the records from the "active" partition.

If that's not enough for your use case, we could envision some more generic filtering of events. E.g. by letting the user specify some filtering predicate which is applied to the events as they come in. If you're interested, can you file a feature request in JIRA? It seems generally useful.

Regarding your second question, please look for the column.blacklist option in the connector docs (http://debezium.io/docs/connectors/postgresql/).

Hth,

--Gunnar

Am Donnerstag, 19. Juli 2018 14:48:42 UTC+2 schrieb Anoop Krishnakumar:

I am using postgresql connector (0.8). The source table is a partitioned table in postgres schema. How should I specify the table name in table whitelist? Also I have to exclude a column in the partitioned table, how should I represent the column name?

Thanks,

Anoop

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.

To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/f46a1c2e-e83c-486b-887d-17a3f0ba65dd%40googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "debezium" group.

To unsubscribe from this group and stop receiving emails from it, send an email to debezium+u...@googlegroups.com.

To post to this group, send email to debe...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/debezium/CAOnnCOxbQWz%3DMc%3Dt%3DvnuF20AcxFKd05kYKg2fYeE2Y1OQhgniA%40mail.gmail.com.

[Jiri Pechanec]

Hi,

also if you are working with a set of partiioned tables you might be interested in delivering the events from all partitions into the same topic. If it is the case please check our ByLogicalTableRouter SMT  - http://debezium.io/docs/configuration/topic-routing/

J.

[Anoop Krishnakumar]

@Gunnar: Thanks for the input.

Parent table name is 'audit' and I have daily partitions with format "audit_YYYY_MM_DD"

When I set the name of the parent table in "table.whitelist", change logs of partitioned tables where filtered out. After specifying regex in whitelist, I started getting the change logs.

Snippet of the configuration,

table.whitelist=public.audit(.*)
column.blacklist=public.audit(.*).full_log
snapshot.mode=never
transforms=Reroute
transforms.Reroute.type=io.debezium.transforms.ByLogicalTableRouter
transforms.Reroute.topic.regex=(.*)audit(.*)
transforms.Reroute.topic.replacement=$1audit

@Jiri: Rerouting is awesome. Thanks for the suggestion.

[Gunnar Morling]

Ah yes, I wasn't aware of the fact that partitions reflect in multiple tables in Postgres. So yes, using a reg ex to match all the partition table names is the way to go. Thanks for reporting back.

Cheers,

--Gunnar