snapshot.select.statement.overrides (SQL Server)

[Kyley Jex]

I'm performing CDC for SQL Server but the snapshot.select.statement.overrides only appears to be working for the first table in the list.

Here are my property configurations:

        props.setProperty("table.include.list", "dbo.Patients, dbo.Visits, dbo.VisitActivities");

    props.setProperty("snapshot.select.statement.overrides", "dbo.Patients, dbo.Visits, dbo.VisitActivities");

    props.setProperty("snapshot.select.statement.overrides.dbo.Patients", "SELECT * FROM dbo.Patients WHERE DateCreated > '01/01/2021 00:00:00.000'");

    props.setProperty("snapshot.select.statement.overrides.dbo.Visits", "SELECT * FROM dbo.Visits WHERE DateCreated > '01/01/2021 00:00:00.000'");

    props.setProperty("snapshot.select.statement.overrides.dbo.VisitActivities", "SELECT * FROM dbo.VisitActivities WHERE ActivityDate > '01/01/2021 00:00:00.000'");

From the application logs, I can verify that the properties are set for the SqlServerConnectorTask.  However, when the snapshot selects are performed, the override select statement is only being applied to the first table.

Here is log statement for the first table (dbo.Patients):

2021-04-13 16:49:22,628 INFO   ||  For table 'MMM360DB.dbo.Patients' using select statement: 'SELECT [Patients].[PatientKey],[Patients].[EnterprisePatientKey],[Patients].[FacilityKey],[Patients].[MedicalRecordNumber],[Patients].[DateInterfaced],[Patients].[DateCreated] FROM dbo.Patients WHERE DateCreated > '01/01/2021 00:00:00.000''   [io.debezium.relational.RelationalSnapshotChangeEventSource]

Here is the abbreviated log statement for the other tables (dbo.Visits, dbo.VisitActivities):

2021-04-13 16:49:22,739 INFO   ||  For table 'MMM360DB.dbo.Visits' using select statement: 'SELECT [Visits].[VisitKey],[Visits].[FacilityKey],... FROM [dbo].[Visits]'   [io.debezium.relational.RelationalSnapshotChangeEventSource]

2021-04-13 16:49:23,389 INFO   ||  For table 'MMM360DB.dbo.VisitActivities' using select statement: 'SELECT [VisitActivities].[VisitActivityKey],... FROM [dbo].[VisitActivities]'   [io.debezium.relational.RelationalSnapshotChangeEventSource]

What am I missing?

Cheers,

Kyley

[Kyley Jex]

I discovered the problem...spaces between the comma-separated list of fully-qualified tables.

For example, I had configured the property with the following value, which also includes a space after the comma:

props.setProperty("snapshot.select.statement.overrides", "dbo.Patients, dbo.Visits, dbo.VisitActivities");

However, when I remove the space, the select statements work for all of the tables:

props.setProperty("snapshot.select.statement.overrides", "dbo.Patients,dbo.Visits,dbo.VisitActivities");

The use of spaces between the "comma-separated values" for other properties works correctly.  For example, the spaces between the table name in "table.include.list" works correctly.  I would suggest that this is an error in the parsing of this particular property and should be fixed.  

Kyley

[Kyley Jex]

Out of curiosity I reviewed how the parsing of the "table.include.list" property is handled differently than "snapshot.select.statement.overrides".

The "table.include.list" uses the TokenStream to parse each individual value.

The "snapshot.select.statement.overrides" uses a simple String.split(",") to parse each individual value.  Because the split is just using the comma, I see now why any whitespace between the table names will be included as part of the value (which won't match later with the select statement property).

If desired, a simple fix for this would be to trim() the tableList and then use regex ("\\s*,\\s*") for the split.

/* RelationalDatabaseConnectorConfig.getSnapshotSelectOverridesByTable */

for (String table : tableList.trim().split(""\\s*,\\s*"")) {

I appreciate the work done on this project and look forward to further implementations.

Kyley

[jiri.p...@gmail.com]

Hi,

thanks for the report. Could you please raise a Jira issue with that? Also would you be willing to contribute a PR as well?

Thanks a lot

J.

[Kyley Jex]

I've created the following JIRA issue: https://issues.redhat.com/browse/DBZ-3429

I'm happy to contribute a PR for this as well.  However, I would also like to contribute a test case for this change, but as I'm less familiar with the project code, I may submit the PR without a test case.  If anyone can assist with how I could create a test case that exercises the code in the RelationalDatabaseConnectorConfig.getSnapshotSelectOverridesByTable(...) method, that would be very helpful.

Kyley