Is there a way to set a “where” condition for a ta...
# plugins-generald
devon_seitz
01/30/2023, 5:54 PMIs there a way to set a “where” condition for a table select for non-translog based syncs? Like only sync data where a column = X identifier?
t
thomas_briggs
01/30/2023, 6:09 PMThe two ways I know of to do this are:
1. Defining a view that includes the WHERE clause and selecting from the view instead of the table
2. a mapper that filters out the undesirable data
The former is more efficient from a database/data movement perspective; the latter works when you can't modify the source (of if the source isn't a relational database...)
thomas_briggs
01/30/2023, 6:10 PMBut there may be other options. It depends on the tap you're using too; there's no reason a tap couldn't implement such a feature. 🤷🏻♂️
d
devon_seitz
01/30/2023, 6:56 PMWhat do you mean with a mapper on #2?
devon_seitz
01/30/2023, 6:56 PMUnfortuantly we cannot modify the source PG database
t
thomas_briggs
01/30/2023, 8:00 PMI haven't done it myself, to be honest, but I believe you can use the map-transformer to define a filter that will remove records before they're passed to the target.
d
devon_seitz
01/30/2023, 8:16 PMoh interesting
devon_seitz
01/30/2023, 8:16 PMi wasn’t aware of this
devon_seitz
01/30/2023, 8:16 PMty!
t
thomas_briggs
01/30/2023, 8:16 PMMy pleasure 🙂
c
christoph
01/30/2023, 8:43 PMYeah. I agree. A mapper would do the trick. Your pipeline will still extract the unwanted rows, but your target will only get the desired rows.
christoph
01/30/2023, 8:44 PMSetting up the mapper config takes a bit getting used to, but once you get the hang of it, it's really powerful.
p
pat_nadolny
01/31/2023, 3:23 PMI think what has been said is great - one extra potential approach, if I'm understanding correctly, is to set your bookmarks for a table using state set. I think then on the next run the tap would know that state exists and to run an incremental sync starting at those bookmarks