Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Yves_s
Contributor III
Contributor III

When are filters applied

Hello,

I have a question about filters, I would like to know when the filter is applied.

During a full load, he filter is set when reading the table or juste before writing to the target?

same question for cdc mode.

thank you for your reply 

Yves Savean

Labels (1)
  • Other

1 Solution

Accepted Solutions
john_wang
Support
Support

Hello @Yves_s ,

For a given scenario, you can try to find out the filter expression from task log file. The steps are:

1. Enable SOURCE_UNLOAD to Verbose;

2. Run the Full Load task.

3. Check the task log file to see if the source unload statement contains "WHERE" clause or not. 

     For example below statement does NOT contain the "WHERE" clause:

2021-11-11T17:45:19:246029 [SOURCE_UNLOAD   ]V:  Construct statement execute internal: 'SELECT `id`,`name`  FROM `sourcedb`.`kit`'

     If the filter takes action in source database then Replicate adds "WHERE" sub-clause in above SQL.

 

Let me know if you need additional assistance.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

3 Replies
john_wang
Support
Support

Hello @Yves_s ,

This is a good question however it's too wide to cover all scenarios. I'm trying to list out the common behaviors.

There are 2 places we define a filer: Table settings level (support in all versions), and Global rules filter (which is supported from Replicate version 2021.5).

For both types of filters, the filter operations can be performed in source backend database,  and/or in Replicate server by Replicate sorter component, all records are filtered prior to send to target applying component.

 

Table settings level (FL means "Full Load")

Filter type Mode Filtering  processed position
Filter Conditions FL + CDC process on source DB if possible, or on Replicate Server
Record Selection Condition FL + CDC process on Replicate server
Fullload Passthru Filter FL mode ONLY process on source DB

 

Global rules filter

Filter type Mode Filtering processed position
Filter by columns FL + CDC process on Replicate server
Filter by record attributes CDC mode ONLY process on Replicate server

 

Take note Replicate try to perform the filter on source DB if possible to minimize the network data transferring volume. However if you want to use complex filter conditions (for example with "and" and "or" conditions etc) then the filter will be done on Replicate server.

Let me know if you need any additional information.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Yves_s
Contributor III
Contributor III
Author

thanks for these informations,

if we take the case of a full load db2 z to kafka with table filter

how it works for the cases:

first case : column filter(primary key)
second case : columns filter(composite key(2 or more))
third case : filter between 2 dates written in a column

john_wang
Support
Support

Hello @Yves_s ,

For a given scenario, you can try to find out the filter expression from task log file. The steps are:

1. Enable SOURCE_UNLOAD to Verbose;

2. Run the Full Load task.

3. Check the task log file to see if the source unload statement contains "WHERE" clause or not. 

     For example below statement does NOT contain the "WHERE" clause:

2021-11-11T17:45:19:246029 [SOURCE_UNLOAD   ]V:  Construct statement execute internal: 'SELECT `id`,`name`  FROM `sourcedb`.`kit`'

     If the filter takes action in source database then Replicate adds "WHERE" sub-clause in above SQL.

 

Let me know if you need additional assistance.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!